从零开始的SQL修炼手册-实战篇

前言

上一篇理论篇不太行，虽说我很想多加些自己的东西进去，但是理论和原理解读这玩意烂大街了，确实不太好原创了，不过这一期总算是加上了私货。看了网上几十篇优秀文章，整合我自己的实践经验，最后写了实践篇出来。目标是提出一套切实可用的慢SQL优化方法论，我不会像公众号和其他文章一样教你有哪些点可以用，我就教你一步步怎么操作，这牛逼吧，保姆式教程了，有手就行。

本文先会给出九个完整的优化步骤，然后用一个实际案例使用优化步骤去一步步优化，并且会从场景、表设计、数据库配置、架构四方面给出进一步的优化手段。同时之前的优秀文章也不是白看的，我会给出地址以及我个人的见解。

总之，本文是想成为慢SQL优化的指导性手册，看了我的就不用看别的了，这才是本文的终极目标啊(JOJO立)!

慢SQL优化步骤--索引

1.规范名称

杜绝无意义的别名(a,t)，大小写按照数据库设计字段的实际情况展示(Inventory_Item_Id)，字段名前面带对应的表名(表.字段)

作用：

1. 方便开发人员理解字段意义以及清晰看到归属表

2. 减少MySQL对字段归属表的解析。即使没有MySQL也会自动给你加上，用show warnings就能看到优化后的语句

2.排除所有select *语句

不仅仅是整个语句返回的字段，包含子查询以及JOIN连接查询，只查询需要的列。主要作用是为了减少数据的传输，提高查询效率

3.小表驱动大表

在explain的rows一列可以看见MySQL需要扫描读取的行数，如果有join语句或者in和exist语句，在不影响最终结果集的情况下，务必使用小表驱动大表。rows数字大的是大表，小的是小表，正确的用法是

• 小表 left join 大表和大表 right join 小表

• in后面跟的是小表，exists后面跟的是大表

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。优化的目标是尽可能减少JOIN中Nested Loop的循环次数，因此优先用小结果集驱动大结果集。

如果难以判断哪个是大表，哪个是小表，可以用inner join连接，MySQL会自动选择小表去驱动大表

PS：但我们在真实优化的时候，需要考虑到业务场景和表设计的情况来决定是否需要调整位置

4.创建索引

查看各个查询的type，目标是尽可能通过索引将ALL优化为ref，对where,on,group by,order by 中出现的列使用索引

在各表加上索引之后，再次explain语句，查看type、possible keys和key三列，如果type转为ref，表示使用了索引，key展示的是实际使用的索引，possible keys展示了可能使用的索引。

索引创建规则(避免MySQL无视索引直接全表扫描)

• 对数据量较少的表无须建立索引

• 避免对区分度小的字段作为索引

• 对较小的数据列使用索引，这样会使索引文件更小，同时内存中也可以装载更多的索引键

• 避免选择大型数据类型的列作为索引,比如说较长的字符串等,这种可以使用前缀索引

• 不要过多创建索引且对经常修改的字段不要创建索引，除了增加额外的磁盘空间外，对于DML操作的速度影响很大，因为其每增删改一次就得重新建立索引

• 使用联合索引，可以减少文件索引大小，在使用时速度要优于多个单列索引。尽量扩展索引,而不要新增索引，但是不要组合过多的列,一般在5列以下

索引使用技巧

• 要遵循最左匹配原则--联合索引a,b,c，包含索引a,ab,abc

• 隐式转换导致索引失效,例如 字符串类型为数字时不添加引号---必然不走

• 对索引列进行运算(+ - * / !)或者使用函数,导致索引失效---必然不走

• 对于 not in, in, !=, not exist也会导致索引失效---根据优化成本计算，不一定

• 对于like "%_" 百分号在前面也会导致索引失效---必然不走

• or两边的字段都为索引,这是必要条件---根据优化成本计算，不一定

or语句测试

当or两边字段都是索引，查询如下图，用到了索引，因为两个字段两个索引，所以这里MySQL自动进行了索引合并，注意不能用联合索引，否则也不走

当只有左边或右边是索引的时候，不走索引

5.覆盖索引

当子查询或者JOIN连接，即使存在possible keys的情况下，type依然是ALL，说明MySQL引擎认为全表查询比走这个索引更快(参见索引创建规则)，如果没有possible keys，说明当前索引失效(参见索引使用技巧)。这时候可以考虑使用覆盖索引，即将select和group by以及order by后的字段联合起来做一个多列索引

覆盖索引：索引数据中包含所查询的列数据，因此不用回表，即可拿到数据，优化中的理想情况

回表： 先通过非聚簇索引找到主键，再通过主键索引获取行记录

6.减少查询中传输数据

当语句中包含group by时，如果业务允许，使用having筛选减少传输数据

优先使用where，因为having只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前筛选记录，如果能通过where字句限制记录的数量，那就能减少这方面的开销。having中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where语句后面。

7.查看Extra信息

如果extra中出现using filesort或者using temporary，通常出现在 GROUP BY 或 ORDER BY 语句中，且排序或分组没有基于索引，此时MySQL需要使用文件在内存中进行排序，因为使用索引排序的性能好于使用文件排序，所以出现这种情况可以考虑通过添加索引进行优化。

因为索引的结构是B+树，索引中的数据是按照一定顺序进行排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作

除此之外，按信息处理即可，详情见前文理论篇

8.子查询有些情况下可以被更有效的连接（JOIN）替代。

因为连接（JOIN）不需要在内存中创建临时表来完成

9.调整where字句中的连接顺序

MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，以求最快速度缩小结果集。

优化结束后

可以通过查看MYSQL优化器重写后的SQL，来帮助我们认识什么样的SQL才是MySQL认证的优秀SQL

如何查看MySQL优化后的语句

# 仅在服务器环境下或通过Navicat进入命令列界面
explain SELECT * FROM `student` where `name` = 1 and `age` = 1;
# 再执行
show warnings;
# 结果如下：
/* select#1 */ select `mytest`.`student`.`age` AS `age`,`mytest`.`student`.`name` AS `name`,`mytest`.`student`.`year` AS `year` from `mytest`.`student` where ((`mytest`.`student`.`age` = 1) and (`mytest`.`student`.`name` = 1))

count(*)优化为count(0)

最左匹配优化

为什么要做这个事呢？我们知道Mysql有一个最左匹配原则，那么如果我的索引建的是age，name，那我以name，age这样的顺序去查询能否使用到索引呢？实际上是可以的，就是因为Mysql查询优化器可以帮助我们自动对SQL的执行顺序等进行优化，以选取代价最低的方式进行查询（注意是代价最低，不是时间最短）

优化案例

背景描述

SQL如图，老项目，不能修改表设计，SQL语句的返回结果不能修改

优化前是非常恐怖的6分钟，无疑这样的SQL是需要优化的

首先毫无疑问先用explain，为啥不用show profile或者optimizer_trace？因为show profile是查询硬件消耗用的，硬件考虑肯定是在软件之后。其次optimizer_trace是在咱们优化之后没达到预期目标时，需要分析MySQL优化逻辑的时候去考虑，因为MySQL优化器基于成本考虑，可能不一定是时间上的最优解，我们可以选择指定索引之类的手段去变更MySQL的执行逻辑。

综上所述，三架马车中explain无疑是优化SQL的第一选择，想要了解以上三种优化手段的，可以看看专栏里的理论篇。

explain如图，五个查询，具体红框圈出来了。首先来粗略的看看问题，基表查询肯定要加索引，type为ALL以及Extra中都显示了需要修改。从SQL的解释结果可以看出，原开发是有部分优化了，可能是由于表后期数据膨胀后，导致性能低下。现在我们就可以用到上面优化步骤一步步操作了。

优化操作

3. 规范名称。大小写统一按照真实表名和字段名进行修改，像是a，b之类的无意义别名也要更换。

4. 排除select *。这个SQL的主表只返回了一个参数，在join查询和子查询的时候也有意识的避免了，做的不错。

5. 小表驱动大表。从解释结果看是一个很明显的大表(18w9)mtl_onhand_quantities_detail左连接驱动小表(2w8)od_delivery_center操作。一旦调整位置的话，肯定会涉及SQL的修改，因此我们需要结合业务场景理清SQL的逻辑。

1. 该SQL在页面上的作用是展示库存，由SQL可看出计算逻辑是现有量-属性号-保有量。

2. 通过下面where的相关参数，可以发现有一个关联参数，item_id物料Id，od_delivery_center表中的逻辑是一个产品名称对应多个物料Id。在其他库存表，物料和库存表的对应关系都是一样的，一对多。

3. 业务逻辑就是要得出每个产品下所有料的库存，而od_delivery_center表中存有产品数据，因此判断可以用该表驱动mtl_onhand_quantities_detail表

6. 创建索引。按照步骤里的索引创建规则和使用技巧操作。

7. 覆盖索引的技巧有时候有奇效，比如select X from...，这种我给X建个索引。

到这一步的时候，优化先暂停一下，看看效果。索引的建立不是一蹴而就，是要根据实际情况进行动态调整的。常规的咱们改了索引就用explain看看效果，想深入了解的就用optimizer_trace 看看MySQL优化器如何选择。接下来我们跟着案例看看到这一步的优化效果。

再看下查询时间

对比一下优化前后的explain结果

从结果分析变快的原因。

1.修改后的SQL，小表驱动大表，从rows一列看数量大大减少，传输数据变少相应查询效率变高。

2.同时表查询基本命中了索引。主表查询可能是MySQL基于成本考虑没有使用索引，而是全表查询。

虽然如上操作后有了巨量的提升，但是根据上面的索引优化步骤，还有几个操作可以尝试一下。

8. 减少查询中传输数据。这一块的减少数据，我的意思是偏向于业务场景的，比如上面这个例子再优化。我在处理的时候，询问相关同事，发现业务上面返回库存数为0的数据其实是没有意义的。因此我们可以修改SQL，将结果为0的数据排除，having中加上条件，最后查询时间减少到了0.4s。

9. 查看Extra信息.查看主表列，using temporay和using filesort还在提醒我们可以优化。我之前用过两个单列索引，发现MySQL放在了possible keys里，并没有使用，于是用上覆盖索引的技巧，把两个条件列做一个联合索引，哎，没毛病，速度再次提升。explain可以看到using index说明覆盖索引，key也表示用到索引了。

10. 剩下的8和9其实一般用处不大，这个案例的提升，普通的优化已经看不出来了，所以这里不演示了。

经过我总结的优化套路之后，这个SQL从原本的347s优化到了0.34s，质的飞跃，哈哈。

场景优化

深分页查询优化

该方案适用于主键id自增的表，可以把Limit查询转换成某个位置的查询。select * from abc where id>10000 limit 10;

优化insert语句

多线程写入，注意线程数必须小于最大连接数，并且线程并非越多越好，根据实际情况调整。

索引合并优化导致死锁

这是一位大佬在排查慢SQL时遇到的问题，问题是索引合并导致死锁：https://juejin.cn/post/7117986579938803725。索引合并优化的意思官网可查，这里简单说一下，因为文中大佬没讲。一个SQL的where语句后的条件字段有多个索引，比如where a=xx and b=yy 单表建了两个单列索引a和b，那么MySQL会取a和b索引结果集的并集，也就是说会命中多个索引，而不是只命中某个单列索引，类似于MySQL自动建了一个联合索引ab的感觉。

表设计优化

11. 将字段多的表分解成多个表有些字段使用频率高，有些低，数据量大时，会由于使用频率低的存在而变慢，可以考虑分开。例如列表和详情展示的字段可以分开。

12. 对于经常联合查询的表，可以考虑建立中间表或者冗余字段，避免联查，多采用单表查询。

数据库配置优化

连接数过小

MySQL连接器用于管理客户端与MySQL之间的长连接，最大连接数根据版本不同默认值会有不同，例如5.7版本默认151条。

连接数过小会导致请求堵塞的问题。比如此时有大量查询并发请求，那么超过连接数的请求只能等待前面的请求执行完成后才能开始执行，体现在客户端上就是并发时部分SQL请求特别慢，但是单独放在MySQL服务上又很快。

连接数过小可以从数据库和客户端两侧进行调整。

数据库调整

查询MySQL当前最大连接数
show variables like '%max_connections%';
查询历史最大连接数
show status like 'max_used_connections%';

5.7版本默认为151，通过max_used_connections / max_connections * 100% （理想值≈ 85%） 配置最大连接数

临时修改命令为
set GLOBAL max_connections=1000;
通过修改MySQL的ini配置文件my.ini
[mysqld]部分添加,然后重启MySQL
max_connections=1000

应用侧

我们通常使用代码进行连接的时候，最好用上连接池，比如druid。池化技术能有效的减少连接创建销毁的次数，按照我们的实际情况去调整连接池相应的参数，比如这里我们可以调整最大连接数。

buffer pool太小

我们在前面的数据库查询流程里，提到了进了innodb之后，会有一层内存buffer pool，用于将磁盘数据页加载到内存页中，只要查询到buffer pool里有，就可以直接返回，否则就要走磁盘IO，那就慢了。也就是说，如果我的buffer pool 越大，那我们能放的数据页就越多，相应的，sql查询时就更可能命中buffer pool，那查询速度自然就更快了。

可以通过下面的命令查询到buffer pool的大小，单位是Byte。
查看buffer pool大小  /1024/1024得到MB
show global variables like 'innodb_buffer_pool_size';
查看buffer pool的缓存命中率
show status like 'Innodb_buffer_pool_%';
设置buffer pool大小，这里设置为512MB
set global innodb_buffer_pool_size = 536870912;

• Innodb_buffer_pool_read_requests表示读请求的次数。

• Innodb_buffer_pool_reads 表示从物理磁盘中读取数据的请求次数。

• buffer pool 命中率 = 1 - (Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%

一般情况下buffer pool命中率都在99%以上，如果低于这个值，才需要考虑加大innodb buffer pool的大小。

推荐好文，大佬写的不错，数据库连接数和buffer pool相关的配置我从这里学习的，图文并茂：https://juejin.cn/post/7117804246069673992

架构优化

• 读/写分离（主库写，从库读）

• 分库分表

如果以上操作都没有效果，那说明MySQL可能不适合你的业务了，这时候选择别的数据库，比如ES，可能是更好的选择。

大佬团队好文

https://juejin.cn/post/7080836756571947038

字节大佬们的文章相对干货较多，不爱讲故事。这篇文章是讲的如何建立一个慢SQL的采集监控治理，图很多，像是PPT粘过来的。更多的是从架构的方向来思考问题，对于一线的开发人员，意义更大的可能是最后一张图的细则。对于架构师来说算是一个不错的思路，架构图和表设计都出来了，还有流程图，挺详细的。

https://juejin.cn/post/7101581835527782414

飞书大佬们的文章，感觉就是公众号正常水平，深了一点不太深，也不够广，说是慢SQL优化实际上是抓着索引使劲。写的点挺多，要说水文也不是，一般吧。不过有蛮多的实践截图以及操作细则，从实践这点看比较不错。

https://juejin.cn/post/7090692731571273741

美团大佬们的文章一般图文并茂，可读性很好，可能是沉淀比较久，美团技术公众号我一直有看，感觉偏实践的干货还是比较多。这一篇优化基于代价去讲，探讨了MySQL优化器选择优化方案的底层逻辑，有具体案例，但是没有针对优化器进行详细的解释算是一点点遗憾，不过这个看我理论篇里optimizer_trace的部分即可。

写在最后

实践篇终于写完了，哎，连续加了两天大的，一天到晚上12点，一天到2点，人都麻了，太难了。最近脾气很暴躁，很容易就控制不住，就想着写写东西，来平复下心情，顺便还能学习总结，也算一举两得了。

至此从零开始的SQL修炼手册-优化篇完结了，分为上下两篇，理论篇不知道为啥没获得掘金推荐，不过也没啥纠结的，读者可以从专栏里关联过去看。从零开始的SQL修炼手册，介绍了三种SQL分析工具以及多种方式的优化，不过我自己也不是特别满意，硬件篇受限于我个人的认知，其实不是很完善，我的重心还是偏向于软件。

和架构组的同事聊了聊日志收集以及云原生的一些技术栈相关的东西，感觉很有意思，希望自己能有实践的场景。

下一篇，我在想要写点啥，理论的东西我不太想写，因为网上的东西已经够多了，我自己也是从网络上学习，与其去汇总一篇文章，我更希望去原创一篇带有我自己实践经验的文章。其实我个人有道云囤了不少东西，比如Kafka，ES，seata，streamsets，一些应用方案，但是像是之前写的几篇一样比较完整的很少，没有构成一个完整的知识体系，写出来就和百家号或者公众号水文一样没啥意思。希望读者能给我点建议，拜托啦。