QA视角的MySQL探险之旅：穿越慢查询沼泽，揭示实战避坑指南

上面了解了SQL查询语句的执行过程，以及索引，那么什么是慢查询呢？

慢查询定义：慢查询就是那些执行慢的SQL语句，包括crud（创建（Create）、更新（Update）、读取（Read）和删除（Delete）操作），一般是查询，所以称为慢查询。

慢查询的出现会给服务带来非常不好的影响，轻则影响用户体验，服务响应变慢、无返回等，重则会出现因慢查询占用大量内存导致MySQL服务直接挂掉，进而导致整个系统瘫痪；因此，我们应该重视慢查询问题，尽早发现，尽早解决。

产生慢查询的大部分原因是未命中索引 ，也会有少部分因为网络抖动或者刷脏页问题 ，本文主要讨论索引未命中的场景

为了优化慢查询的问题，先要发现慢查询，主要有以下两种手段：

间接发现：添加接口响应时间的监控

慢查询的出现会让系统响应变慢，比如接口返回超时，可以通过添加接口监控来监测，出现超时的场景就去排查是否存在慢查询。

但是这种方式不够直接，因为接口响应慢的因素有很多，可能是网络原因、测试服务器资源等问题，无法快速定位是否是慢查询；

主动发现：监控慢查询日志

数据库开启慢查询日志（通常会存储在文件中，可自行设置），监控慢查询日志，如果新增慢查询便立即发送通知。这种方式我个人比较推崇，可以第一时间发现并着手分析优化。

发现慢查询以后，需要着手优化，通常有以下几种情况：

  情况1：通过explain你可能会发现，SQL压根没走任何索引，而且现在表中的数据量巨大无比。

解决：建合适索引

  情况2：通过explain查看SQL执行计划中的key字段。如果发现优化器选择的Key和你预期的Key不一样。那显然是优化器选错了索引。

解决：最快的解决方案就是：force index ，强制指定索引，当然还有其他方案，后面实践会展开说明。当你发现即使你使用了force index之后，查询依然很慢。这就意味着，你需要设计一个更好的索引。

此外：也可能是数据量太大了，即使走了索引依然超过了阈值。

这种情况其实挺糟糕的，DBA同学能做的依然只能是kill这些出事的SQL。最好的解决方案其实是分表，比如将大表拆分成128张小表。如果来不急做分表，可能这条SQL面临被下线的风险。

接下面来看两个分析优化的例子。

分析优化1-正确添加索引：

数据库版本：5.7.13

我们有个日志表，数据的量级200w+

目标比较简单，查询某个脚本的历史执行日志，通过脚本id（script_id）去查询,优化之前的SQL执行情况，时间大概1.8s，如下图所示：

查看执行计划：

EXPLAIN SELECT * FROM `prepare_data_log` WHERE script_id = 24;

结果如下图所示：

从图中可以看出，type列的值是ALL，走的全表扫描；key字段是NULL，没有使用任何索引；

优化方案是加索引：在script_id上添加二级索引：ALTER TABLE prepare_data_log ADD INDEX script_id (script_id); 

加索引之后，执行时间从1.8s->0s，详见下图，可见，合理的添加索引可以显著的提升查询性能。

再看下执行计划：

从图中可以看出，type列的值是ref，表示走了索引；key字段是索引的名字script_id，使用了刚创建的索引，符合预期；

分析优化2-选择正确的索引

上面的例子添加了索引，执行器也选择了期望的索引，那MySQL会一直选择期望的索引么？答案是：不一定

看一个例子：数据表数据有10W，包含id（自增主键）、a、b、c三列，其中a=b=c=id，a和b两列都有二级索引。查询语句如下：

M2

从条件上看，这个查询没有符合条件的记录，因此会返回空集合。执行之前，可以先设想一下，如果你来选择索引，会选择哪一个呢？为了便于分析，我们先来看一下 a、b 这两个索引的结构图。

如果使用索引 a 进行查询，那么就是扫描索引 a 的前 1000 个值，然后取到对应的 id，再到主键索引上去查出每一行，然后根据字段 b 来过滤。显然这样需要扫描 1000 行。

如果使用索引 b 进行查询，那么就是扫描索引 b 的最后 50001 个值，与上面的执行过程相同，也是需要回到主键索引上取值再判断，所以需要扫描 50001 行。所以你一定会想，如果使用索引 a 的话，执行速度明显会快很多。那么，执行器会按照我们期望的选择么？

下图是执行 explain 的结果：

可以看到，返回结果中 key 字段显示，这次优化器选择了索引 b，而 rows 字段显示需要扫描的行数是 50210。从这个结果中可见 MySQL “选错了”索引。

在日常工作中也遇到过几次选错索引的例子，通常开发的做法是采用 force index 强行选择一个索引。

  方案一：force index 强行选择一个索引。刚开始分析时，我们认为选择索引 a 会更好。现在，我们就来看看执行效果：

从使用不同索引的语句执行耗时可以看到，原本语句需要执行 0.06 秒，而当你使用 force index(a) 的时候，只用了 0.01 秒，比优化器的选择快了 6倍。也就是说，优化器没有选择正确的索引，force index 起到了“矫正”的作用。

这个方案并不优雅，一来这么写不优美，二来如果索引改了名字，这个语句也得改，显得很麻烦。而且如果以后迁移到别的数据库的话，这个语法还可能会不兼容。所以，数据库的问题最好还是在数据库内部来解决。

  方案二：修改SQL，引导走期望索引。既然优化器放弃了使用索引 a，说明 a 还不够合适，所以第二种方法就是，我们可以考虑修改语句，引导 MySQL 使用我们期望的索引。比如，在这个例子里，显然把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。我们来看看改之后的效果：

之前优化器选择使用索引 b，是因为它认为使用索引 b 可以避免排序（b 本身是索引，已经是有序的了，如果选择索引 b 的话，不需要再做排序，只需要遍历），所以即使扫描行数多，也判定为代价更小。现在 order by b,a 这种写法，要求按照 b,a 排序，就意味着使用这两个索引都需要排序。因此，扫描行数成了影响决策的主要条件，于是此时优化器选了只需要扫描 1000 行的索引 a。

当然，这种修改并不是通用的优化手段，只是刚好在这个语句里面有 limit 1，因此如果有满足条件的记录， order by b limit 1 和 order by b,a limit 1 都会返回 b 是最小的那一行，逻辑上一致，才可以这么做。

  方案三：在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

在日常工作中遇到慢查询，可以根据实际的应用场景灵活的选择合适的解决办法。当然，优化的前提是先要清楚影响优化器选择索引的因素。