<返回更多

这操作绝了,只需三步,慢日志去无踪

2021-04-27  今日头条  代码小当家
加入收藏

作为一个 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 来说还是很有用的。

声明:本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▍相关推荐
更多资讯 >>>