背景
迄今为止写了那么久的代码,数不胜数的SQL,有的时候光顾着做需求,学技术,但是很少有时间沉淀下来,去回头看看自己走过的技术道路,今天正好以解决一个慢sql为契机,再次回溯SQL慢查计划,一篇漫长的总结梳理拉开帷幕…
explain 核心字段
话不多说大家都知道怎么使用查询计划,如下图所示:
但是实际影响我们查询性能 尤其是只有**type、possible_keys 、key、key_len、ref、rows、 filtered、Extra**
其实最终决定是否需要优化也就只需 key 、key_len、rows、Extra【文末分析】
注意在分析之前,声明一点,由于MySQL官方对查询计划设计和描述是细致入微的,因此我们掌握的时候应该基于实际生产场景去看,而不是照本宣科,一一列举所有的查询计划相关的功能点,因此我们能覆盖核心指标即可
select_type
查询语句的类型 ,常见的有四种:
- primary: 首先执行的查询
- subquery: 代表当前是一个子查询
select * from s1 where id in ( select id from s2) or id = 2;
结果:其中s1是primary,s2是sub_query, 其中or必不可少,因为
- simple: 简单的查询不涉及union、关联查询
# 普通单表查询肯定是simple
# 稍微复杂的举例说明
select * from s1 as ap left join s2 as cp on ap.id = cp.id
结果: s1、s2都是simple
- union :被union的表就是这个类型
select * from s1 uninon select * from s2
结果:s1 是primary、s2是union
type
这个指标就比较多了,而且是较为核心的指标,在阿里巴巴的数据库规范中有一个要求是:
一个sql查询计划的type至少应该是range,力求达到ref
type【按优 => 劣排序】 | 说明 |
---|---|
system | 只有查询系统表才会有 |
const | 基于唯一索引查询,如主键、唯一索引 |
eq_ref | 子查询中做联表查询,例如:select * from s1 where type in (select id from s2 where s2.id = s1.id ); 对于s2来说就是type=eq_ref |
ref | 命中索引的查询,任何一个二级、唯一索引 |
fulltext | 采用全文索引 针对text类型字段专属,不过NoSQL出来之后基本没遇到过有人这么设计 |
ref_or_null | 命中索引的查询,和ref的区别是 该索引所在列在设计之初允许为null,而对null有单独一个索引存储 |
merge_index | 命中2个及以上的索引,注意不是用到2个及以上的索引,是取索引的并集,一定出现or 连接多个使用到索引的条件。例如:select * from s1 where id =2 or update_time = '2023-01-01' . 其中id 和update_time 是两个单独索引 |
unique_subquery | 独立的子查询中做联表查询,和eq_ref的区别在于 unique_subquery 需要所关联的列不是唯一索引 并且有 or 连接的条件,例如select * from s1 where type in (select id from s2 where s2.status = s1.id ) or id = 2; 对于s2来说status 不是一个唯一索引,并且外层有or连接,所以type=unique_subquery |
range | 这个有过开发的都知道,多出现在in 和 时间字段的范围查询 |
index | 全索引扫描,最经典就是查询条件期望用到索引,没满足联合索引最左前缀,例如: select * from s1 where status = 1 (其中type,status) 是联合索引 |
all | 这个更简单,只要没走索引基本就是这个 |
possible_key
在执行sql根据sql解析的结果预判可能使用的索引, 这个基本看看就好,都不具备参考价值
key
查询计划实际使用到的索引,这个最具有说服力,即possible_key 可能是A,但是key是B 那么一定是走了B的索引
key_len
这个挺好玩的,代表使用索引列的长度,默认情况是索引列定义类型的大小,如int: 4字节、long :8字节、datetime: 5个字节,而对于char、varchar字符串类型来说需要注意:
索引长度 char()和varchar()索引 长度的计算公式:key_len=基础长度 * 列长度 + 1(如果是允许null) + 2(如果是变长列 (Character Set基础长度utf8mb4=4,utf8=3,gbk=2,latin1=1)
例如:
select * from s1 where name='lkg'
# 其中(statu,name)是联合索引, status 是int类型,不允许为null,name是varchar(20)name允许为null,字符集是utf8
# 因此最终的key_len = 4 + 20 * 3 + 1 + 2 = 67
注意:在判断是否走了复合索引时,改值的大小可以直接作为参考结论
ref
这个依靠上面讲到type指标,当使用的type是等值查询例如;eq_ref、ref、ref_or_null、unique_subquery、那么ref就展示对应列字段,
此外除了表示用到索引的列字段,还有其他几种常见情况
- null: 没使用等值查询 or 没有用到索引
- const :如果type是const即使用了唯一索引作为查询, ref也是const,代表只使用到1次查询就可以获取结果
- func : 在子查询中做关联,并且外部有or 做条件,上面那个type =unique_subquery 就是同一个case
rows
扫描结果集的行数,这个非常重要,决定索引好坏或者优化前后对比最为直观的数据表示,绝大部分的慢sql 只需要看这个扫描的行数就能基本定位一定是sql语句有问题了
filtered
预测扫描结果row中有多少百分比命中,越高越好,一般来说都应该是100%
Extra
这个代表索引使用情况的额外描述信息,如果说只看row就能决定索引好坏的作为初步判断,那么只看Extra也可以,只不过它是原理来说明这一点,row是比较直观的
实际情况比较多,这里选择几个最为常见的,关键看方法论
Extra结果 | 分析 |
---|---|
Using Index | 走索引【select和where都只使用了索引列, 且索引列是等值查询】 |
Using where | 分情况 : A: 完全没走索引 B: 使用了索引 但是还有其他非索引列的筛选条件,仍需要回表 C: 对索引使用范围查询,且无覆盖索引 |
Using where; Using Index | 分情况 : A: 没直接走索引【例如不符合最左前缀. 通过type=index基本就是这类case】 B: 使用了索引,也覆盖了select列,但是索引列使用了范围查询 |
NULL | 查询条件走了索引,但是select 列有非索引列的字段,需要回表【等值查询才会出现, 即ref=const】 |
Using Index condition | 走了索引,也覆盖了索引,但是因为排序、分组等操作使用了无索引列,导致server不得不重新回表【不过这个代价是很小的】 |
Using Index condition ; Using where | 整体走了索引,并且优化器帮你做了索引下推,但是因为仍需要到server做进一步条件过滤 |
Using filesort | 使用文件排序,这个如果在Using where后面性能会非常差,很有可能出现全表扫描或者全索引扫描 |
排查秘籍
通过上面的概念描述和举例,我们可以轻松得出结论:实际生产过程中基本只需要关注type, key 、key_len、rows、Extra 这四个核心指标答应
- 先看rows 是否行数很多 一般来说大于 1w 就需要注意了
- 其次看Extra 命中的索引信息情况
- key 是否有值,无则说明基本没走索引
- type 看下是不是满足最低range标准
- 如果是复合索引,再重点看下key_len 是否符合预期
预告
下一篇我将计划针对不同的慢sql 提供实战分析和解决方案,思路当然全部来自本篇,敬请期待。