详解 MySQL 执行计划 -- Explain

1. 引言

此前我们介绍过 MySQL 性能优化的相关内容： Mysql Innodb 性能优化

其中提到了执行计划 Explain 的使用，那么，究竟 explain 命令的结果中每一项数据都表示了什么意思呢？本文我们就来详细说明一下。

2. explain 与优化器优化

遇到 SQL 执行超时，大部分原因是查询造成了扫描行数过多的问题，所以我们首先来判断是否是这个原因造成的。 通常我们通过 explain 命令来获取 select 语句的执行计划，就是在 select 语句前增加 explain 关键字。 此前，我们介绍过 mysql 的执行过程： mysql 异常情况下的事务安全 — 详解 mysql redolog

一旦 select 语句前加了 explain 关键字，那么 mysql server 在优化器完成执行计划生成后就会立即返回，不会调用引擎进行实际的执行。 通过执行 explain 命令可以看到标的读取顺序、读取操作的类型、使用的索引、扫描的行数等信息。

我们看到，explain 命令执行后，提示了 1 Warnings，如果优化器对所执行的 SQL 语句进行了优化，就会以 warnings 的形式保存优化后的 SQL。 此时我们执行 show warnings 可以看到 warnings 的具体信息，也就是经过优化器优化后的 sql 语句。

• 在 5.7 以前的版本中，想要显示 partitions 需要使用 explain partitions 命令，想要显示 filtered 需要使用 explain extended命令
• 在5.7版本后，默认 explain 直接显示 partitions 和 filtered 中的信息

3. explain 结果中字段解析

3.1. id — 查询序列号

explain 结果中，id 是用来标识多个 sql 执行顺序的序号，id 越大，越先执行，id 相同时，由上至下执行。

3.2. select_type — 查询类型

select_type 提供了对表的查询类型，有以下这些具体的选项。

3.2.1. SIMPLE

simple 表示没有子查询也没有 union 查询的简单查询。

3.2.2. PRIMARY

primary 表示在存在子查询或 union 查询的语句中最外层的查询，他被称为“首要表”，通常他的 id 要小于统一查询中的子查询与 union 查询，表示他最后被执行。

3.2.3. DERIVED

表示该查询是在首要表 from 列表中包含的子查询，DERIVED 表示这是一个针对临时表的查询。

3.2.4. SUBQUERY

subquery 表示在 select 的结果字段列表或 where 条件中包含的子查询。

3.2.5. UNION

union 表示一个 union 查询的子查询。

3.2.6. UNION RESULT

union 子查询结束后，在对首要表执行最终的查询前，需要进行一次子表的连接过程，这个查询类型就标识了这一过程。

3.3. table

explain 结果集中，table 字段表示该查询所使用的表。 这个字段的值可能是表名、表的别名，在存在派生表、子查询或 union 查询的场景下则是临时表的标识符。

3.4. partitions

如果查询是基于分区表的话，会显示查询将访问的分区。

3.5. type

查询计划的连接类型，下面我们按照最佳类型到最差类型排序来看看都有哪些连接类型。 一般认为，前五种是非常理想的类型。

3.5.1. NULL

type 为 Null 表示在优化器优化后就已经得到结果，不需要再通过执行器访问存储引擎来实现查询操作，例如：

explain select max(id) from person;

3.5.2. const

当使用主键或惟一键进行查询且只匹配一行时，此时的连接类型是 const，表示通过聚簇索引可以直接定位到具体的全部数据。

3.5.3. eq_ref

对于每个来自于前面的表的行组合，从该表中读取一行，并通过这一行可以通过主键或惟一键定位到聚簇索引中具体的一系列数据。 例如：

explain select * from person, dept where person.id = dept.id;

这个查询联立了两个表，mysql 首先需要查询出 dept 表的全部数据，此处我们暂且不去关心这个查询的连接类型。 接下来，mysql 通过查询出的 dept 表中每一行记录的 id 值到 person 中通过主键进行查询，这一查询行为的连接类型就是 eq_ref。

3.5.4. ref

通过既不是唯一索引也不是主键的索引来查询的连接类型就是 ref。

3.5.5. ref_or_null

ref_or_null 与 ref 十分类似，唯一的区别在于该结果集中包含有空行。

3.5.6. range

基于索引的范围查询，包含 >、>=、<、<=、between、in 等操作。

3.5.7. index

扫描整个索引树，在索引上全表扫描，通常应该避免，但他比最后要介绍的 all 性能要好一些，因为：

1. 扫描过程不需要访问磁盘，只需要对内存中的索引树进行扫描
2. 索引本身带有排序，因此不需要在扫描后进行额外的排序工作

3.5.8. all

对全表所有行进行扫描，通常性能非常差，需要尽量避免。 通常可以通过增加一些索引来避免 all 连接类型的出现。

3.6. possible_keys

possible_keys 表示 MySQL 可以从中选择查找表中的行的索引。 如果此列是 NULL，则没有相关的索引。

3.7. key

key 列指出了 MySQL 优化器最终选定的索引。 但有时，优化器最终选择的索引并不符合我们的预期，我们可以通过 force index 关键字强制指定本次查询所使用的 SQL：

SELECT * FROM code, test force index(idx_codeid) WHERE code.id = test.code_id;

但这是非常不推荐使用的，通常大部分情况下应该信任优化器的选择，即便是你有更好的索引选择，一旦强行指定就意味着这条语句不会随着数据库的变化，如数据的增长或新索引的添加等方面而变化，这往往是十分重要的。

3.8. key_len

key_len 列定义了 mysql 在索引里可能使用的最大字节数，因此该数字越小说明执行效率越高。 由于此时并没有经过执行器进行实际的查询，所以此处的索引最大长度是根据表定义计算出来的，并不是实际使用的索引长度。

3.9. ref

该字段显示使用哪个列或常数与 key 一起从表中选择数据行，有三种选项可选：

1. NULL — 不使用索引
2. func — 用于索引匹配的值是函数计算的结果
3. const — 用于索引匹配的值是常量，包括 SQL 中直接指定的值或其他子查询或 union 查询返回的数据

3.10. rows

该字段算是 explain 返回结果中最为重要的一个字段了，他展示了优化器评估的将要读取的行数。

3.11. filtered

filtered 字段是一个百分比，如果有子查询或 union 查询存在，那么这个值就是他所依赖的前一个表与当前表连接的行数占所有需要读取的行数（rows 值）的比例。

3.12. extra

除上述信息之外的其他信息，他同样是十分重要的信息，他是若干个字符串的组合，随着版本的更新，还在不断增加，具体可以参考官方文档： https://dev.mysql.com/doc/refman/5.7/en/explain-output.html。

4. 参考资料

http://dev.mysql.com/doc/refman/5.5/en/explain-output.html。 https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html。 https://www.cnblogs.com/tufujie/p/9413852.html。 https://www.cnblogs.com/wangfengming/articles/8275448.html。