写点什么

只需三步!慢日志去无踪

用户头像
数据君
关注
发布于: 2020 年 12 月 10 日

| 作者 ******王文安,腾讯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 来说还是很有用的。



发布于: 2020 年 12 月 10 日阅读数: 46
用户头像

数据君

关注

还未添加个人签名 2020.11.05 加入

还未添加个人简介

评论

发布
暂无评论
只需三步!慢日志去无踪