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是通过查找表的定义计算字节数的,因此并非总能反映实际使用的字节数。
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
的语句中