只需三步！慢日志去无踪

| 作者 ******王文安，腾讯CSIG数据库专项的数据库工程师，主要负责腾讯云数据库 MySQL 的相关的工作，热爱技术，欢迎留言进行交流。******





作为一个 DBA，想必都有过被慢查询折腾的经历，一个慢查询有时候真的很让人抓狂，本文对常规和非常规手段进行了整理，由浅及深，简单介绍几个慢查询的分析手段。



需要说明的是，下面所有的手段都是原生支持的功能（≥MySQL 5.6），因此在各类 RDS 和原生数据库中都不会有什么使用上的差异，这里图方便就用腾讯云数据库 MySQL 来作为测试环境了，版本为 5.7。



第一步：EXPLAIN



最先登场的毫无疑问就是 EXPLAIN 语句了，用过 MySQL 的人应该都知道这个查看 SQL 语句执行计划的命令，详细的资料在网上有很多，这里就略过了。一般来说，95% 的慢查询问题只需要 EXPLAIN 就可以解决了。手工执行的时候，在 Extra 列里面，避免出现Use Temporary Table和Using file sort这类关键字，TYPE 列中也尽量避免 ALL 类型（全表扫描）出现。



其实目前这个最常用的功能在腾讯云上可以直接用 DBbrain 来进行操作了。DBbrain 会分析 SQL 语句并给出加索引的建议。在DBbrain中选择对应的实例，进入 SQL 诊断的 tab 下，点击具体的慢查询就可以看到加索引的建议了：





第二步：PROFILE



既然 EXPLAIN 能看到 SQL 的执行计划，能判断出来有没有好好利用索引，DBbrain 也能给出索引的优化建议，那么慢查询的分析为什么还会有三步曲？



原因很简单，MySQL 慢查询，并不一定慢在有没有索引；SQL 的执行环节中任意一环出了问题都会表现为查询变慢，所以用了索引，EXPLAIN 的结果也很完美，但是还是慢，怎么办？



这时候，就需要 PROFILE 来帮忙了，这个命令可以详细的列出 SQL 语句在每一个步骤消耗的时间，前提（缺点）是先执行一遍语句。



PROFILE 默认是关闭的，所以需要在 client 端先打开，操作如下：



set session profiling = 1;



在实际的生产环境中，可能会需要加大profile的队列，保证想要查看的 PROFILE 结果还保存着，因此可以用如下操作来增加 PROFILE 的队列大小



set session profiling\history\size = 50;



到这一步，PROFILE 的功能就开启了，这里先删除索引，简单试一下 SQL 语句，EXPLAIN 一下看看输出





TYPE 列是 ALL，显然这种语句是不合格的，“假设”索引“觉得”没问题，但是这个语句还是比预想的要慢，那么可以看看这条语句各个阶段的耗时，先执行一次 select，然后再查看 PROFILE 的结果：





可以看到 id 为 11 的那一行就是执行过的语句，这时候使用show profile block io,cpu,memory,source for query 11;来查看统计信息：





Sending data 并不只是在服务器端和客户端之间 Sending data，还包括了从磁盘读取数据的时间，因为这个查询执行了全表扫描，所以这个时间会比较高，当然索引的效率不高也会导致这部分时间比较久。



如果还有 order by 的话，这里面也会出现 Sort 相关的信息。



经过了这两部曲之后，基本上一个 SQL 为什么慢，慢在哪里基本上可以定位出来了，那么最后的手段主要是解决什么问题呢？



第三步：OPTIMIZER\_TRACE



OPTIMIZER\TRACE 是 MySQL 5.6 添加的新功能，顾名思义，这个功能可以看到内部查询计划的 TRACE 信息，从而可以知道 MySQL 是如何在众多索引中选中最“棒”的那个。一般来说，这个最“棒”的索引选错了，就需要根据 OPTIMIZER\TRACE 的信息来判断为什么会选错，是 MySQL 的配置原因，还是 SQL 某些地方写的不好导致 MySQL 误判了。



开启这个功能的方式如下：



set session optimizer\_trace='enabled=on';



随便执行一个 EXPLAIN 语句，生成一个执行计划，然后在information\chema.optimizer\trace的表里面查找这一条语句对应的信息：





内容是非常长的 JSON 格式，所以推荐把结果转存到其他地方，然后用 JSON 的转换工具来辅助查看，如果要看索引的选择情况，就重点关注这个 JSON 的ref\optimizer\key\_uses，rows\_estimation 及之后的部分，这里会展示索引选择相关的信息，截取一部分结果作为示例：





在这里面能看到详细的统计信息，包括 cost，预计的 rows，在之后的内容中也会显示最终选择的索引：





通常来说，cost 数值越低，代表这个执行计划的执行速度越快。



总结



其实在绝大多数的情况下，EXPLAIN 完全可以胜任，在腾讯云平台上的话，用 DBbrain 即可，PROFILE 一般是用来决定分析和判断的方向，看看是哪个阶段比较慢。OPTIMIZER\_TRACE 主要用来分析各种疑难杂症，比如说优化器为什么没有选择索引而是全表扫描？为什么优化器没有选择效率较好的索引，而是选择了一个效率较差的索引（order by，limit）等等。



总而言之，通过这三步曲的排查，基本上 SQL 的问题就都能找出来了，好好掌握这些基本技能对于 DBA 来说还是很有用的。