沉淀之华: 【慢查询上篇】Explain 到底该怎么看，数据库查询计划深入剖析，不怕你学不会

背景

迄今为止写了那么久的代码，数不胜数的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就展示对应列字段,

此外除了表示用到索引的列字段，还有其他几种常见情况

1. null: 没使用等值查询 or 没有用到索引
2. const ：如果type是const即使用了唯一索引作为查询， ref也是const，代表只使用到1次查询就可以获取结果
3. 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 这四个核心指标答应

1. 先看rows 是否行数很多 一般来说大于 1w 就需要注意了
2. 其次看Extra 命中的索引信息情况
3. key 是否有值，无则说明基本没走索引
4. type 看下是不是满足最低range标准
5. 如果是复合索引，再重点看下key_len 是否符合预期

预告

下一篇我将计划针对不同的慢sql 提供实战分析和解决方案，思路当然全部来自本篇，敬请期待。