介绍
MySQL优化器在基于成本、规则对SQL语句优化后,会生成一个执行计划。执行计划展示了执行SQL查询的具体方式。
使用起来比较的简单,在SQL语句前添加EXPLAIN
关键字并执行即可。当然,EXPLAIN
不仅可以加在SELECT 语句前面,也可以加在DELETE、INSERT等等语句前面。不过查看查询语句还是最主要的。
以EXPLAIN SELECT * FROM t_test
为例,从结果中我们可以看到很多字段:
搞清楚每个字段什么意思,知道了SQL的执行顺序,方便为自己的SQL进行优化。
id
查看查询语句的执行计划,SQL语句以SELECT
关键字开头。那么SQL语句中出现的每一个SELECT都有一个id与之对应。一个SELECT对应一个id。
select_type
在一个复杂的查询语句中,可能包含多个SELECT查询语句。每一个SELECT代表的查询都定义了一个select_type属性,根据select_type属性我们可以执行单个的SELECT查询语句在整个复杂的查询语句中的作用。
select_type属性值还是有很多分类的:
-
SIMPLE
Simple SELECT(not using UNION or subqueries)
SQL查询语句中不包含UNION或子查询的,都是SIMPLE类型的。比如:简单的单表查询,联接查询(内联查询,外联查询)。
-
PRIMARY
Outermost SELECT
复杂的SQL查询(包含UNION或子查询)语句中,最外层的SELECT。
-
UNION
Second or later SELECT statement in a UNION
SQL查询语句中包含UNION(UNION ALL),第一个UNION关键字前的SELECT查询的select_type为PRIMARY,第一个UNION关键字后的SELECT的select_type为UNION。
-
UNION RESULT
Result of a UNION
MySQL中使用临时表对UNION查询的去重操作,对这个临时表的查询,select_type为UNION RESULT。
SQL查询语句中,UNION与UNION ALL的区别在于,UNION会对两个查询的结果集进行去重,排序;而UNION ALL仅仅只是合并两个查询结果集。
-
SUBQUERY
First SELECT in subquery
包含子查询的SQL查询语句不能够转为对应的半连接形式,且该子查询是不想关子查询,查询优化器决定采用将该子查询物化的方式执行。那么子查询的第一个SELECT关键字代表的查询,其select_type为SUBQUERY。根据文档英文翻译的大白话就是子查询中第一个SELECT的查询。
select_type为SUBQUERY的子查询会被查询优化器物化,所以该子查询只会执行一次。
-
DEPENDENT SUBQUERY
First SELECT in subquery, dependent on outer query
包含子查询的SQL查询语句不能够转为对应的半连接形式,并且该子查询被查询优化器转换为相关子查询的形式,则子查询的第一个SELECT关键字代表的查询,其select_type为DEPENDENT SUBQUERY。大白话:子查询条件过滤的值依赖于外层的查询。
select_type为DEPENDENT SUBQUERY的子查询可能会执行多次。
-
DEPENDENT UNION
Second or later SELECT statement in a UNION, dependent on outer query
同样,UNION关键字后的SELECT子查询,过滤条件依赖于外层的查询。
-
DERIVED
Derived table
在包含派生表的查询中,如果以物化派生表的方式执行查询,那么派生表对应子查询的select_type为Derived table。
子查询可以出现在SELECT关键字之后,也可以出现在FROM关键字之后,也可以出现在WHERE关键字之后。
包含在FROM关键字语句中的子查询,MySQL将其结果放入一个临时表中,也称为派生表。
-
MATERIALIZED
Materialized subquery
SQL查询语句中,查询优化器将子查询物化之后与外层的查询进行联接查询,该子查询的select_type为MATERIALIZED。
-
UNCACHEABLE SUBQUERY
A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
子查询的结果无法缓存,且必须针对外部查询的每一行重新进行评估
-
UNCACHEABLE UNION
The second or later SELECT in a UNION that belongs to an uncacheable subquery
UNION关键字后的子查询,且不能被缓存。
table
顾名思义,执行计划中每一行所需要查询的表
partitions
分区。记录当前的查询作用于哪个分区中。
当使用PARTITION对表进行了分区,记录查询的结果所在的分区。
type
访问类型(访问方式)。记录MySQL对其table列中的表执行查询访问的方式。类型有很多,效率也各不相同。
-
system
表使用的存储引擎的统计数据精确(MyISAM),且表中只有一条记录。
-
const
当主键索引或唯一索引列与常数进行比较,表中最多有一条记录与之匹配,只扫描一次。速度比较快。
-
eq_ref
关联查询中,驱动表是通过主键索引或唯一索引(且值不为NULL)的列等值匹配。如果主键索引或值不为NULL的唯一索引是符合索引,则所有的索引列都必须进行等值匹配。
-
ref
SQL查询中,普通索引列与常量进行等值匹配。
此外,在关联查询中,被驱动表的普通索引列与驱动表的列执行匹配时,被驱动表的查询中type也是ref。
或者使用唯一索引的部分前缀进行匹配时。
-
fulltext
全文索引
-
ref_or_null
SQL查询中,对普通索引列(该列的值可以为NULL)执行等值匹配。
-
index_merge
使用索引合并的方式来对表进行查询。
-
unique_subquery
类似被驱动表的eq_ref访问方式,unique_subquery针对包含IN的子查询语句。如果查询优化器将IN子查询转换为EXISTS子查询,且子查询在转换后可以使用主键索引或唯一索引(值不为NULL)进行等值匹配。
-
index_subquery
与unique_subquery类似,只是在访问子查询中使用的普通索引。
-
range
当使用索引获取一些单点扫描区间的记录(IN),或范围区间的记录(BETWEEN,>,<)。
-
index
需要扫描全部的索引记录。
对于InnoDB存储引擎来说,当需要进行全表扫描,且对主键进行排序时,type的值也是index。
-
ALL
全表扫描。
possible_keys
表示在查询语句中,可能用到的索引有哪些。
key
与possible_keys不同的是,key列表示的是实际用到的索引有哪些。
key_len
用于记录在SQL查询中使用到的索引的字节,根据这个值可以计算出具体使用了哪些索引列来形成扫描区间。
ref
显示在key列中的索引与之进行等值匹配的值是什么,可以是:const(常量)、NULL、字段名
rows
记录在这个查询中估计要读取的记录行数。
当使用全表扫描时,rows列表示预计扫描该表的估计行数;
当使用索引查询时,rows列表示预计扫描的索引行数。
filtered
该值为百分比,使用(rows * filtered) / 100的结果用于与其他表进行结果处理
Extra
用于补充一些额外信息。
比如:
在使用覆盖索引执行查询时,提示:Using index;
在WHERE条件中条件判断永远为false,提示:Impossible WHERE;
在对结果集在内存中或者磁盘中进行排序时,提示:Using filesort;
等等还有很多。
补充
- 在
EXPLAIN
关键字后面,继续添加FORMAT=JSON
,输出json格式的字符串,里面有更加详细的执行计划 - 在使用
EXPLAIN
查看执行计划后,可以使用SHOW WARNINGS
语句来查看执行计划的扩展信息。
参考:
《MySQL是怎样运行的》