MySQL执行计划 EXPLAIN

介绍

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；

等等还有很多。

补充

1. 在EXPLAIN关键字后面，继续添加FORMAT=JSON，输出json格式的字符串，里面有更加详细的执行计划
2. 在使用EXPLAIN查看执行计划后，可以使用SHOW WARNINGS语句来查看执行计划的扩展信息。

参考：
《MySQL是怎样运行的》