方法论:慢查询分析三步曲–云惠网

avatar
avatar
云惠网小编
337
文章
0
评论
2020年7月24日08:23:59 评论 18 次浏览 1887字阅读6分17秒
摘要

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

前言

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

所有的手段都是原生支持的功能(>= 5.6),因此在各类 RDS 和原生数据库中都不会有什么使用上的差异,这里图方便就用腾讯云数据库 MySQL 来作为测试环境了,版本为 5.7。

一步曲:EXPLAIN

最先登场的毫无疑问就是 EXPLAIN 语句了,用过 MySQL 的人应该都知道这个查看 SQL 语句执行计划的命令。详细的资料在网上有很多,这里就略过了。一般来说,在 Extra 列里面,避免出现Use Temporary TableUsing file sort这类关键字,TYPE 列中也尽量避免 ALL 类型出现,尽量让查询的 where 条件用上索引。

二步曲:PROFILE

既然 EXPLAIN 能看到 SQL 的执行计划,能判断出来有没有好好利用索引,那么慢查询的分析为什么还会有三步曲?

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

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

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

 set session profiling = 1;

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

set session profiling_history_size = 50;

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

EXPLAIN 结果

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

PROFILE 结果

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

PROFILE 结果

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_usesrows_estimation 及之后的部分,这里会展示索引选择相关的信息,截取一部分结果作为示例:

结果展示

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

结果展示

总结

其实在绝大多数的情况下,EXPLAIN 完全可以胜任,PROFILE 在实际工作中其实用得比较少。反而是 OPTIMIZER_TRACE 会时不时拿出来分析各种疑难杂症,比如说优化器为什么没有选择索引而是全表扫描?为什么优化器没有选择效率较好的索引,而是选择了一个效率较差的索引(order by,limit)等等。

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

腾讯云618
avatar
腾讯云618
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: