MySQL explain命令总结

explain有什么用(执行计划)

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

explain命令支持SELECT, DELETE, INSERT, REPLACE, UPDATE语句，它可以输出SQL语句的执行计划，通过执行计划我们可以了解MySQL是如何执行给定的SQL语句的。

具体来说，explain的输出包含如下信息：

• 对于SELECT语句中使用到的每张表，输出一行对应的信息
• 表信息的顺序就是MySQL处理该语句时读取表的顺序
  • 这意味着MySQL将会从第一张表读取一行数据，然后从第二张表找到一个匹配的行，然后再从第三张表找到一个匹配的行，以此类推

explain输出分析

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type 表示是简单的还是复杂的SELECT查询
table	table_name	The table for the output row 表示
partitions	partitions	The matching partitions
type	access_type	The join type 查询类型：全表扫描、索引扫描
possible_keys	possible_keys	The possible indexes to choose 可能会用到的索引
key	key	The index actually chosen 最后选择的索引
key_len	key_length	The length of the chosen key 索引字段的长度
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined 此次查询需要扫描的行数
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information 额外信息

id

id是 SELECT语句的标识符，每个SELECT语句对应一个id。

前面提到了，对于SQL语句中用到的每张表，会输出一行，如果输出中的某一行是UNION的结果，那么该行的id为NULL，且table列的值为<union M, N>

id大小和执行顺序的关系：

• id 相同， 执行顺序由上至下
• id 不同， 如果是子查询， id 的序号会递增， id 值越大优先级越高， 越先被执行
• join时，出现在上面的是驱动表

select_type

参考：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_select_type

示例：https://www.cnblogs.com/ysviewvicn/p/9824044.html

select_type表示SELECT的类型。

下面列举常见的select_type：

• SIMPLE意味着不包含任何的子查询或UNION
• 如果SQL语句包含子查询或UNION等复杂的子部分，则最外层的部分被标记为PRIMARY
• SUBQUERY用于表示不在FROM子句中的子查询的SELECT的类型
• DERIVED用于表示在FROM子句中的子查询中的SELECT的类型，MySQL会将该子查询的结果放入一个临时表中，被称作“派生表”，因为这个临时表是从子查询中派生出来的。
• union语句中第二个和随后的是SELECT会被标记为UNION
• 用来从UNION的匿名临时表检索结果的SELECT被标记为UNION RESULT。

table

一开始提到了，对于SQL语句中用到的每一行表，explain会输出一行信息。table就表示这一行信息对应哪个表。

EXPLAIN
SELECT *
FROM sakila.film
      INNER JOIN sakila.film_actor USING (film_id)
      INNER JOIN sakila.actor USING (actor_id);

上面的SQL语句的执行计划如下：

根据EXPLAIN命令的输出，我们可以推断出优化器生成的指令树的结构如下（左侧深度优先原则）：

派生表 derived

当FROM子句中有子查询时，table列是 <derivedN>的形式，其中N是子查询的id，它总是向前引用（也即总是指向EXPLAIN输出中的下一行，因为下一行编号大先被执行）。

联合 union

当存在union时，select_type为UNION RESULT的table列包含一个参与UNION的id列表<unionM,N>，它总是后向引用。

type

参考：

[1] https://mengkang.net/1124.html

[2] 《高性能MySQL》

[3] 《Understanding MySQL Internals》

MySQL官方文档说type列表示关联类型 (join type)，其实并非所有时刻，type描述的都是两表连接的行为，因为有时候只需要访问但张表，所以更准确的说，type表示访问类型，也就是表示MySQL如何查找表中的行。

下面是常见的访问类型 (type取值)，性能从最差到最优：

(1) ALL

即全表扫描。

这里也有个例外，例如在查询中使用的LIMIT，或者Extra列中显示“Using distinct/not exists”

(2) index

即全索引扫描，会按照索引顺序扫描全部索引。它的优点是避免了排序，缺点是可能带来大量的随机IO (例如MyISAM的索引)。

• 如果Extra列显示“Using index”，说明正在使用覆盖索引，不需要回表，此时开销比全表扫描要小。

(3) range

即范围索引扫描。和全索引扫描不同，它无需扫描所有索引，仅需扫描指定范围内的索引，因此性能比全索引扫描要稍好一些。常见的会使用范围扫描的条件有BETWEEN、>等等。

对于IN()、OR条件，有时也会显示为范围扫描。但是，这和前述的BETWEEN、>其实是不同的访问类型，在性能上有重要差异，可以具体参考“第5章 什么是范围条件”

(4) ref

和eq_ref以及const类似，ref也是用到了索引，只和单个值进行比较。

但不同的是，ref中参与比较的索引是非唯一索引 或 唯一索引的非唯一前缀，因此可能有多行匹配结果。

与eq_ref不同的是，ref不一定非得出现在多表联合查询，单表查询只要用到了非唯一索引和单个值进行比较也是可以的，例如：

# 根据索引（非主键，非唯一索引），匹配到多行
SELECT * FROM ref_table WHERE key_column=expr;

当然，和eq_ref类似，ref也可用于多表联合查询，其参考值同样来自于之前的表的列组成的表达式或常量，只不过是对于每一个取值，可以有多行匹配（因为参与比较的索引不是唯一索引）：

# 多表关联查询，单个索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

# 多表关联查询，联合索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

ref_or_null是ref之上的变体，它意味着MySQL必须在初次查找的结果里进行第二次查找以找出NULL条目

(5) eq_ref

出现该访问类型也说明用到了索引，并且是主键或唯一索引。与const类似，该访问也是和单个值进行比较，也最多只返回一行，只不过它是对前面所有表的列组成的表达式或常量的每个取值，最多只返回一行 (One row is read from this table for each combination of rows from the previous tables)。

因此，eq_ref访问类型中的参考值来自于其他表，所以只有多表联合查询才会出现该类型。

explain select straight_join *
from film_actor fa
         inner join actor a on fa.actor_id = a.actor_id;

其中，actor是演员表，actor_id是主键；film_actor中，actor_id不是唯一索引，不唯一可多次出现。

straight_join是为了防止MySQL对我们的join顺序进行优化，保持SQL语句中书写的join顺序。

下面是上述语句的执行计划：

对于fa是全表扫描，对于a是eq_ref。由于表a中actor_id是唯一主键，所以对于fa中每一行的fa.actor_id，MySQL可以确定至多只有一行匹配，所以是eq_ref类型。

(6) const, system

system：MySQL官方文档是这样说的，表中只有1条记录的特殊情况 (系统表)。那么一般的表应该不会出现这个访问类型。

const： 当你在WHERE子句中将主键或唯一索引和一个常量（单个值）进行比较时，会出现该访问类型，该访问类型最多有一条结果。

⚠️ 注意：上面说的主键或唯一索引指的是主键或唯一索引的全部部分，因为一个主键或唯一索引可能由多列组成，WHERE子句中必须出现所有列和分别和一个常量比较，才能是const类型​

# 单一主键
SELECT * FROM tbl_name WHERE primary_key=1;
# 联合主键 (part1, part2才构成一个主键)
SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

possible_keys

表示查询可以使用哪些索引，最后并不一定能用到这所有的索引，甚至连索引都用不到，因此该列参考价值不大。

key

表示MySQL最终决定使用的索引

优化思路：若key字段为NULL，考虑：

• 是不是可以添加索引
• 修改语句，使得我们利用上现存索引

key_len

表示MySQL索引中被使用的字节数。

注意：key_len是通过查找表的定义计算字节数的，因此并非总能反映实际使用的字节数。

key_len的计算方法

ref

前面说到了，key列表示MySQL最终决定使用的索引，而ref则代表该索引列将会和哪一列或者常量进行比较。

rows

表示MySQL为了找到所需的行而要读取的大约行数。

把explain输出中所有行的rows相乘，可以得到总的需要读取的大约行数。

filtered

是一个百分比，表示当前行的rows将有多少被过滤出来：rows × filtered = 过滤出来的行

过滤出来的行才会和后面的表进行join。

Extra

好的extra信息

using index

表示使用到了覆盖索引。

⚠️ 对于聚簇索引，即使用到了该聚簇索引，extra字段可也以没有using index

using where

表示使用where来过滤了结果，意味着MySQL服务器将在存储引擎检索行后再进行过滤。

distict

not exists

MySQL can perform a LEFT JOIN “missing rows” optimization that quickly eliminates rows from consideration.

不好的信息

using filesort

https://www.percona.com/blog/2009/03/05/what-does-using-filesort-mean-in-mysql/

当一次查询用不上索引的排序特性时，就会进行filesort，filesort需要开辟一块额外的内存(临时表)进行排序，如果要处理的数据过多，会将临时表分配在磁盘上以减少内存消耗

using temporary

MySQL有时需要借助临时表来完成查询，常出现在包含GROUP BY和ORDER BY的语句中