【MySQL】Explain执行计划(十七)

🚗MySQL学习·十七站~
🚩本文已收录至专栏：MySQL通关路
❤️每章节附章节思维导图，文末附全文思维导图，感谢各位点赞收藏支持~
⭐学习汇总贴，超详细思维导图：【MySQL】学习汇总(完整思维导图)

一.引入

当我们需要对SQL语句进行优化时必须先分析其性能时，EXPLAIN是一个不可或缺的神器，它可以帮助我们获取 MySQL 如何执行 SELECT 语句的信息，例如

• 表如何连接和连接的顺序
• 哪些索引可以使用
• 是否使用了索引
• 实际使用了哪些索引
• 扫描的数据行数
• …

通过使用Explain执行计划可以说一条SQL语句的“身体状况”完全展现在我们眼前了，借此便可以着手分析它的"健康状况"，并为它开出“诊断方案”，最后"实施救治"。

Explain执行计划的使用非常简单，只需要在执行的SQL语句前面加上Explain即可，例如：

Explain 待执行的SQL语句

例如执行如下SQL：

explain select * from user where id = 1668983497699696641;

可以看到如下结果：

这就是SQL语句的“身体状况”说明书概述：

字段	含义
id	Select查询的序列号，表示查询中执行select子句或者是操作表的顺序 (id相同，执行顺序从上到下；id不同，值越大，越先执行)
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接 或者子查询）、PRIMARY（主查询，即外层的查询）、 UNION（UNION 中的第二个或者后面的查询语句）、 SUBQUERY（SELECT / WHERE之后包含了子查询）等
table	使用的是哪张表
partitions	分区表命中的分区情况
type	数据扫描类型，性能由好到差的连接类型为NULL、system、const、 eq_ref、ref、range、 index、all
possible_key	在这张表上可能会使用到的索引，一个或多个
key	实际使用的索引，如果为NULL，则没有使用索引
key_len	表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好
ref	表查找值所用到的字段名或常量
rows	预计需要扫描的行数，在innodb引擎的表中，是一个估计值， 可能并不总是准确的
filtered	表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好
extra	执行过程中一些特殊情况补充说明信息

接下来我们就来一起详细解读一下这张说明书，学习一下如何分析它的症状，其中需要重点关注的是type、extra 列，至于如何“救治”那当然是交给你啦~

二.id

表示Select查询的序列号，表示查询中执行Select子句或者是操作表的顺序。

id相同，执行顺序从上到下；id不同，值越大，越先执行。

例如，我们执行带子查询的SQL，子查询语句id往往是较大的，表示其先执行。

三.select_type

表示Select的类型，常见的取值有:

• SIMPLE：简单表，即不使用表连接 或者 子查询
• PRIMARY：主查询，即当查询中包含子查询时，最外层的查询被标记为 PRIMARY
• UNION：UNION 中的第二个或者后面的查询语句
• SUBQUERY：SELECT / WHERE之后包含了子查询
• …

例如，下面SQL语句中，外层查询为PRIMARY主查询，WHERE之后所包含的查询为SUBQUERY子查询。

四.table

表示使用的是哪张表。

例如，外层查询使用了post表，子查询使用了user表。

五.partitions

表示分区表命中的分区情况，如果表没有进行分区，或者查询没有命中任何分区（例如全表扫描），则该列的值通常为 NULL 。

补充：

当数据表中的数据量很大时，为了提高操作的效率，可能会对表进行分区操作。
所谓的分区就是将一个表分解成多个区块进行操作和保存，从而降低每次操作的数据，提高性能，而对应用来说是透明的，从逻辑上看就只是一个表（这里跟分库分表的访问不一样），但是物理上的这个表可能是由多个物理分区组成，每个分区都是一个独立的对象，可以进行独立处理。

六.type(重要)

表示数据扫描类型，通常用来衡量SQL的执行效率，性能由好到差的连接类型为：

• null：一般不太可能优化到NULL，除非在查询的时候不访问任何表，比如Select ‘A’

• system：一般出现在访问系统表时

• const（结果只有一条的主键或唯一索引扫描）：表示该表最多有一个匹配记录，一般出现在使用主键或者唯一索引访问时

• eq_ref（唯一索引扫描）：通常出现在联表的情况下，表示主键索引或唯一索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录

• ref（非唯一索引扫描）：一般出现在使用非唯一性索引访问时，可能会匹配到多个记录

• range（索引范围扫描）：一般出现在对索引字段进行范围匹配，比如使用 < 、>、in、between 等关键词

• index（全索引扫描）：只遍历索引树，且只从索引树中获取数据

• all（全表扫描）：表示该查询将遍历全表以找到匹配行，这是最糟糕的一种查询方式

从range开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。例如，将用作查询条件的字段设置为索引、思考能否设置为唯一索引…

例如，针对下述post表的userId创建了非唯一索引，因此在查询post表时选择了ref非唯一索引扫描，而user的id字段为主键，选择了const级别扫描。

七.possible_key

表示在这张表上可能会使用到的索引，一个或多个，不代表一定会使用到。

例如，查询涉及到的字段存在联合索引和其他类型索引，优化器将根据所执行的SQL语句选择一个合适的进行使用。

八.key

表示实际使用的索引，如果为NULL，则没有使用索引。

通常，我们在查询条件中使用到了索引字段，该列都会显示所使用到的索引名称。如果使用了索引字段，但是此处显示为NULL，排除mysql估计使用索引比全表扫描更慢的情况，就要仔细想一下索引使用的是不是有问题，例如命中了索引失效的常见几种情况：联合索引非最左匹配、对索引使用函数、左或左右模糊匹配…

九.key_len

表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越索引效果好。

通常情况下，**我们可以根据key_len来判断联合索引是否完全生效。**例如，key显示使用到了联合索引，但是key_len显示的字节数与联合索引实际字节数不符合时，说明只使用到了部分索引。

十.ref

表示表查找值所用到的字段名或常量。通常值为：const（常量），func，NULL，字段名（post.userId）

例如，下述1和2都为const表示用到的为常量(1668904042690179073)。

十一.rows

表示预计需要扫描的行数。，在innodb引擎的表中，是一个估计值， 可能并不总是准确的。通常，较小的 rows 值通常表示查询效率较高，因为这代表需要处理的数据量较少。

例如，对于一个简单的根据主键进行查询的操作，rows 值通常会很小，可能为 1 ，因为通过主键可以直接定位到唯一的一行数据。

相反，如果一个查询没有使用合适的索引或者索引无法有效地过滤数据，rows 值可能会很大。

比如，一个没有合适索引的全表扫描查询，rows 值可能等于表中的总行数。

十二.filtered

表示**返回结果的行数占需读取行数的百分比， filtered 的值越大越好。**它表示在根据索引条件进行初步筛选后，还需要对结果集进行进一步筛选以得到最终结果的比例。

• 较高的 filtered 值（接近 100%）通常表示通过索引条件已经能够很好地筛选出接近最终结果的行，这可能意味着查询的效率相对较高。
• 较低的 filtered 值（例如 10% 或更低）则可能表示在初步筛选后，还需要对大量的行进行进一步的筛选，这可能会影响查询的性能。

十三.extra

表示执行过程中一些特殊情况补充说明信息，例如：

• Using filesort ：当查询语句中包含 order by 操作，而且无法利用索引完成排序操作的时候， 这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。

• Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。

• Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。

• Using where：表示 MySQL 在存储引擎检索行后，需要应用 WHERE 子句来过滤结果。

• Backward index scan：表示 MySQL 正在以相反的方向，即从后往前扫描索引，在大多数情况下，正向的索引扫描通常更有效率，出现反向索引可能扫描影响了查询性能。

十四.全文概览