一、性能瓶颈产生的原因

1，关系型数据库的先天缺陷：为了保证ACID，也就是原子性，隔离性，一致性，持久性，所以事务必须串行执行，也就是任何一个事务在处理期间，其他事务必须在等待，等待，等待，如果是大事务的话，需要锁定太多的数据，造成大量的阻塞和锁超时，回滚所需时间较长，执行时间长，容易造成主从延迟。

2、学院派的误导：要为了范式而范式，上学的时候经典教程里的什么范式是在企业内部管理系统开发为主和存储系统昂贵的时代产物。虽然有减少数据冗余、更新操作快，数据表比较小等优点，但是在互联网时代相对于多表快速查询，小事务快速读写这些特性，完全按照范式进行数据库设计，难以进行索引优化，从而降低数据库性能，实际上适度的反范式化，是提高数据库性能的有效手段。当然不能数据库冗余的太离谱了，太多的列当然也会造成更坑爹的性能问题。

3，运维人员的失误：不当的配置，不当的硬件，不当的管理都会影响数据库的性能，这些虽然都是入门问题，但是老实说，非常多的公司做不到，对运维人员配置不足，不够重视，或者说是运维人员意识不强是主要原因，好在都是简单问题，不管问题如何严重，基本万能的百度都可以帮你找到办法，也就是个工作量的问题。或者是钱的问题，不行把硬盘都SSD化试试？

4，数据库设计缺陷：数据库表结构，索引设计不当和闲的蛋疼的查询语句，这个是性能瓶颈最主要的问题，优化这方面的问题，也是最体现技术人员功力的地方，相对事半功倍。

本文也主要围绕这数据库设计的关键点展开，什么参数配置，基准测试这类，要不就是需要较多的人力参与，通常小公司是没这么多人的，道理都懂，谁也做不到而已，要不就是百度下可以找到方法的，就不做赘述了。

二、性能优化的思路，数据库性能优化几步走：

2.1选择合适的引擎:

2.2、选择合适的字段:

当一列可以有多种数据类型选择的时候，要优先数字类，优先数字类，优先选数字类，重要的话说三遍！其次是选择日期类型和二进制类型，万不得已再用字符串，计算机这个东西，实在太不擅长处理字符串了，具体原因，是计算机的先天缺陷，自行百度去吧。另外考虑清楚VARCHAR和CHAR各自不同的场景，字段不要太长，但是也有足够使用，还有最重要的是，主键尽可能小。

2.3、 选择合适的索引：

B-tree索引能够加快数据查询速度，更适合进行范围查找，适合全值匹配的查询、匹配最左前缀的查询、匹配列前缀查询、范围值的查询、精确匹配左前列并范围匹配另外一列、只访问索引的查询。但是如果不是按最左列开始查找，则无法使用，也不能跳过索引中的列，Not in和<>操作无法使用索引，如果查询有某个列的范围查询，则有右边所有列都无法使用索引。

Hash索引是基于Hash表实现的，只有查询条件精确匹配Hash索引的所有列时，才能使用到hash索引，对于Hash索引中所有的列，存储引擎会为每一行计算一个Hash码，Hash索引中存储的就是Hash码。使用Hash查询时，必须两次查找，大部分因为存在内存中，所以不会明显影响性能，但是要小心特殊情况出现，Hash无法使用排序，不支持范围和部分索引查找，另外hash冲突无法避免，所以不适合用于很多重复数据的列查找。

最后最重要的是，索引会增加写操作成本，太多的索引也增加查询优化器的选择时间，所以没事儿干就清理下没有被使用的索引，更新索引统计信息并减少索引碎片，是一件很有价值的事情。

2.4 索引优化策略

索引列上不能使用表达式和函数，这个我不想说了，真有人这么干的，如果你们公司有，那就换掉吧。

索引的选择性是不重复的索引值和表记录的记录数的比值，据我个人以前的工作经验，在查询结果占数据总量20%以上是全表扫描更快，0.1%以下是索引扫描更快，20%到0.1%之间的范围， 因为CPU.内存，磁盘性能、服务器整体性能甚至空气，电压，心情等诡异的原因，只有测试过才知道那个更快那个更慢，要根据测试结果来指导索引优化更为妥当，实在没有人能总结什么经验教训 ，所以有句万能的正确的废话，叫看情况而定！

建立联合索引的时候，要制定合适的索引列顺序，经常会被使用到的列优先，选择性高的列优先，宽度小的列优先。

2.5 优化SQL性能

首先来看；SQL语句的执行有五步，具体如下

1，客户端发送SQL请求给服务器

2、服务器检查是否可以在查询缓存中命中该SQL

3、服务器端进行SQL解析，预处理，再由优化生成的执行计划

4、根据执行计划，调用存储引擎API来查询数据

5、将结果返回给客户端

通常认为其中第二步和第三步是性能优化的关键，但也不排除其他几步成为瓶颈的可能性。

所以，查询缓存对SQL性能影响较大，有几个参数必须要关注

query_cache_type  设置查询缓存是否可用

query_cache_size   设置查询缓存的内存大小

query_cache_limit   设置查询缓存可用存储的最大值

query_cache_wlock_invalidate  设置数据表被锁后是否返回缓存中的数据

query_cache_min_res_unit  设置查询缓存分配的内存块最小单位。

对于SQL执行下，又包含了几步子过程

先检查查询中所涉及的表和数据列是否存在名字或者别名是否存在歧义等；

等语法检查全部通过了，查询优化器，就可以生成查询计划了！

这里要注意统计信息不准确会导致生成错误的执行计划，执行过程中的成本估算不等于实际的执行计划的成本，MYSQL优化所认为的最优可能与你认为的不同，MYSQL从不考虑其他并发查询，可能会影响当前查询速度，MYSQL有时候会基于一些固定的规则来生成执行计划，MYSQL不会考虑不受器其控制的成本等，所以虽然MYSQL很强大，但是也很靠不住，多看执行计划，多做性能测试，木有太好的办法解决。

还有，查询优化器优化偷偷的会干这些活儿，比如重新定义表的关联顺序，将外连接转化成内连接，使用等价变换规则，将一个表达式转化为一个常数，将子查询改成关联查询等等。

在优化过程中，需要习惯使用Profile度量SQL执行的消耗时间，准确的选择优化的阶段，对其进行优化，或者根据性能测试结果来最判断，最好不要主观臆断。

具体做法有，对大表的结构进行修改，一般来说先建立新表，在老表做触发器和排它锁，数据不断同步到新表，等新表完全启用后，再停用老表‘；把NOT IN过滤改成LEFT JOIN；使用汇总表优化查询等

老实说，这里么有太多的技巧去闪展腾挪，根据实际运行的效果，去调整表结构，索引，和执行方式是一个长期的过程，没有什么方法是在所有场景中都可以适用的。

如果表级优化不能满足需求，需要对数据库进行整体改造最常见的有

1、加钱：不要笑，扩充硬件，增加内存，网卡更换，这个是最省事儿的，当然也是有局限的。

2、分库：一个实例中的多个数据库拆分到不同的实例，比如订单、用户、促销本来放在一个节点，可以分开三个节点部署，这种模式对读效率很很大优化，对写负载，基本没毛啥用。

3、分表：一个库中的表分离到不同数据库，比如表的水平拆分，水平分片是数据库的优化终极大招，其难度最大，维护成本高，比如对订单表，按年，月，甚至日来分片，也有按地区，按商品类型来分的，花样很多，原则上要避免跨分片查询，不得以用汇总表来做处理，分区键要尽可能使各分片中的数据分区。对于无需分片的表，可以在每个分片中存储一份相同的数据，或者使用额外的节点统一存储。

总结：

MYSQL数据库优化过程，没有什么不传之秘，也没有什么方法是一劳永逸的，还是那句万能的废话，一切看情况而定！另外重要的话说三遍，性能优化的分表工作是MYSQL终极大招，也是工程浩大的事情，如果不打算花个三五个月去做，乘早洗洗睡了，当热，如果是阿猫阿狗类的公司，自己关起门来自嗨，另当别论！

作者：田浩沛
链接：https://www.jianshu.com/p/b3a73c7f3f22