Explain被称为执行计划，在语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，模拟MySQL优化器来执行SQL语句，执行查询时，会返回执行计划的信息，并不执行这条SQL。（如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）。

Explain可以用来分析SQL语句和表结构的性能瓶颈。通过explain的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、索引使用情况 哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息、表如何连接。

一、数据准备

image.png

二、字段解释

示例查询：

  1. explain select * from t_user;

image.png

2.1 ID：查询编号

id列是一个有顺序的编号，是查询的顺序号，有几个 select 就显示几行。id的顺序是按 select 出现的顺序增长的。id值越大，优先级越高，越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行。

2.2 select_type：查询类型

（1）SIMPLE/simple：表明当前行对应的select是简单查询，不包含子查询和union表连接。

（2）PRIMARY/primary：表明当前行对应的select是复杂查询中最外层的 select(主查询)。

（3）SUBQUERY/subquery：当前行对应的select是包含在 select 中的子查询（不在 from 子句中）(子查询中的第一个 SELECT )。

（4）derived (衍生)：表明当前行对应的select是包含在 from 子句中的子查询。

（5）UNION/union：当前行对应的select是在 union 中的第二个和随后的 select查询语句。

（6）union result：表明当前行对应的select是从 union 临时表检索结果的 select

MySQL会创建一个临时表来存放子查询的查询结果。用如下的示例说明：

explain select (select 1 fromt_user where user_id=1) from (select * from t_group where group_id=1) tmp;

image.png

2.3 table：输出结果集的表

table列的结果表明当前行对应的select正在访问哪个表。
当查询的子句中有子查询时，table列是 格式，表示当前的select依赖 id=N结果行的查询，要先执行 id序号=N 的查询。
当存在 union 时，UNION RESULT 的 table 列的值为，N1和N2表示参与 union 的select 行的id序号。

2.4 type：表的连接类型

type列的结果表明当前行对应的select的关联类型或访问类型，也就是优化器决定怎么查找数据表中的行，以及查找数据行记录的大概范围
性能由好到差为 system、const、eq_reg、ref、range、index、ALL。一般来说，要保证查询达到range级别，最好达到ref。

（1）null：MySQL优化器在优化阶段分解查询语句，在优化过程中就已经可以得到结果，那么在执行阶段就不用再访问表或索引。

explain select min(user_id) from t_user;

image.png
这时的函数min，在索引列user_id中选取最小值，可以直接查找索引来完成，不需要执行时再访问数据表。

（2）const和system：const出现在用 primary key（主键） 或 unique key（唯一键） 的所有列与常数比较时，优化器对查询进行优化并将其部分查询转化成一个常量。最多有一个匹配行，读取1次，速度非常快。而system是const的特例，表中数据只有一条匹配时为system。
（system：表中仅有一行，即常量表）
（const：单表中最多有一个匹配行，例如 primary key 或者 unique index ）
此时可以用explain extended+show warnings查看执行结果。

explain extended select * from (select * from t_user where user_id = 1) tmp;

show warnings;

image.png
image.png

（3）eq_ref：primary key（主键）或 unique key（唯一键）索引的所有构成部分被join使用，只会返回一条符合条件的数据行。这是仅次于const的连接类型。
（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用 primary key 或者 unique index ）

explain select * from t_group_user gu left join t_group g ong.group_id = gu.group_id;

image.png

（4) ref：与eq_ref相比，ref类型不是使用primary key（主键） 或 unique key（唯一键）等唯一索引，而是使用普通索引或者联合唯一性索引的部分前缀，索引和某个值相比较，可能会找到符合条件的多个数据行。
（ref 与 eq_ref 类似，区别在于不是使用 primary key 或者 unique index ，而是使用普通的索引）、 ref_or_null （ 与 ref 类似，区别在于条件中包含对 NULL 的查询 ） 

explain select * from t_group where group_name= 'group1';

image.png
（5）range (范围)：出现在 in(),between ,> ,<, >= 等操作符中。使用一个索引来查询给定范围的行。
（单表中的范围查询）

（6）index：扫描全表索引（index是从索引中读取的，所有字段都有索引，而all是从硬盘中读取，比ALL要快）。
（对于前面的每一行，都通过查询索引来得到数据）

explain select * from t_group;

image.png

（7）ALL：即全表扫描，需要从头到尾去查找所需要的行。（一般这种情况下这需要增加索引来进行查询优化）

explain select * from t_user;

image.png

index_merge ( 索引合并优化 )
unique_subquery （ in 的后面是一个查询主键字段的子查询）
index_subquery （ 与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询）

2.5 possible_keys列

这一列的结果表明查询可能应用在这张表中的索引。
如果possible_keys列的结果是null，则表明没有相关的索引。这时，可以通过优化where子句，增加恰当的索引来提升查询性能。

但有些时候也会出现出现possible_keys 列有结果，而后面的key列显示 null 的情况，这是因为此时表中数据不多，优化器认为查询索引对查询帮助不大，所以没有走索引查询而是进行了全表扫描。

2.6 key列

这一列表明优化器实际使用的索引来优化对该表的访问。如果没有使用索引，则该列是 null。
（很少的情况下，Mysql会选择优化不足的索引。这种情况下，可以在SELECT语句 中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制Mysql忽略索引。）

2.7 key_len列

这一列表明了索引使用的字节数，通过这个值可以大致估算出具体使用了联合索引中的前几个列。举个例子，假设在A、B、C三个字段上建立索引，如果全部使用这3个索引的时候，key_len列是12，那么当key_len出现为4的时候，可能是仅使用了一个索引；
（使用的索引的长度。在不损失精确性的情况下，长度越短越好。）

2.8 ref列

（显示索引的哪一列被使用了，如果可能的话，是一个常数。）

2.9 rows列

这一列表明 用来返回请求数据优化器大概要读取并检测的行数。跟实际的数据行数大部分情况是不一致的。
（Mysql认为必须检查的用来返回请求数据的行数。）

2.10 filtered列

MySQL 5.7缺省就会输出filtered

指返回结果的行占需要读到的行(rows列的值)的百分比。filtered值只对index和all的扫描有效。filtered的值真正有效的场合要where条件中的列上建有索引，又要执行计划不走索引的range或ref扫描，而走全表扫描ALL或覆盖索引扫描。

2.11 Extra列

这一列表明 Mysql如何解析查询的额外信息，这一列的取值对优化SQL非常有参考意义。
（坏的例子是Using temporary和Using filesort，意思Mysql根本不能使用索引，结果是检索会很慢。）

常见的重要取值如下：

（1）Using index：所有被查询的字段都是索引列(称为覆盖索引)，并且where条件是索引的前导列，出现这样的结果，是性能高的表现。
（查询列仅仅使用了索引中的信息而没有读取实际的行数据返回的，发生在对表的全部的请求列都是同一组索引的部分的时候。）

explain select group_id,group_name from t_group;

image.png

（2）Using where：被查询的列使用了where筛选，即(可能通过索引)先查出对应的列，然后通过where去过滤相应的数据；具体是否使用到了索引，还需要具体去看type列。

explain select * from t_user where user_name='user1';

image.png

（3）Using where; Using index：被查询的列被索引覆盖，并且where条件是索引列之一但是不是索引的前导列，也就是没有办法直接通过索引来查询到符合条件的数据。

explain select * from t_group where group_name = 'group1';

image.png

（4）null：被查询的列没有被索引覆盖，但where条件是索引的前导列，此时用到了索引，但是部分查询列未被索引覆盖，必须通过“回表查询”来实现，不是纯粹地用到了索引，也不是完全没用到索引。

explain select * from t_user where user_id='1';

image.png

(5) Using temporary：表明需要通过创建临时表来处理查询。出现这种情况一般需要进行优化，用索引来优化。创建临时表的情况：当数据量大，且使用 distinct，group by，orderby，子查询等。

explain select distinct user_name from t_user;

image.png

explain select distinct group_name fromt_group;        # group_name是索引列

image.png

(6) Using filesort：在使用order by的情况下出现，mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况要考虑使用索引来优化。

explain select * from t_user orderby user_name;

image.png

explain select * from t_group order bygroup_name;        # group_name是索引列

image.png

(7) Using join buffer
强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，根据查询的具体情况可能需要添加索引来改进。

-- t1和t2的name字段都无索引
(root@yayun-mysql-server) [test]>explain select t1.name from t1 inner join t2 on t1.name=t2.name;
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 |                                |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+

(8) Using index condition：下推索引（MySQL 5.6之后的）的时候会出现；所谓下推索引，例如在name 和 age 上建立索引，那么查询条件为 name like "张%" and age = 20 的时候，因为遵循最左原则，age无法使用索引，MySQL5.6之后，下推索引后，会在like筛选后，在同一棵索引树上筛选age=20的数据

(9) Impossible where
这个值强调了where语句导致没有符合条件的行。

[test]>EXPLAIN SELECT * FROM t1 WHERE 1=2;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

(10) Select tables optimized away
这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。

[test]>explain select max(id) from t1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

(11) Index merges
当MySQL 决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型。

Using sort_union(...)
Using union(...)
Using intersect(...)

三、查询优化建议

首先看 type列，如果有类型是 all 时，表示预计会进行全表扫描（fulltable scan）。建议创建适当的索引，通过索引检索避免全表扫描。

再来看 Extra 列，如果有出现 Using temporary 或者 Using filesort 则要多加关注：

  • Using temporary：表示需要创建临时表以满足需求，通常是因为GROUP BY的列没有索引，或者GROUP BY和ORDER BY的列不一样，也需要创建临时表，建议添加适当的索引。

  • Using filesort：表示无法利用索引完成排序，也有可能是因为多表连接时，排序字段不是驱动表中的字段，因此也没办法利用索引完成排序，建议添加适当的索引。

  • Using where：通常是因为全表扫描或全索引扫描时（type 列显示为 ALL 或index），又加上了WHERE条件，建议添加适当的索引。

四、总结

MySQL不同版本Explain表现差异很大，有些场景，从语句层面看，是要使用到索引，但经过优化器分析，结合表中现有数据，如果MySQL认为全表扫描性能更优，则会使用全表扫描。实际运用的时候，要拿到具体的SQL语句，然后explain去分析。

参考：
语雀|MySQL的执行计划
博客园|EXPLAIN 命令详解