MySQL的Explain命令详解
2017-10-21 19:50:26 
阿炯
在MySQL中，当需要对某条SQL查询语句进行分析时，我们经常会使用 explain 命令 或 desc 命令进行操作，分析SQL语句时，explain 和 desc 的作用是一样的。使用 explain 命令可以分析出SQL查询语句中索引的使用情况、扫描的行数、扫描的类型等等，以便帮助我们对索引和SQL语句进行优化。使用方法：在SQL查询语句前面，加上 explain 或 desc 即可。explain命令的语法说明见官网文档，这里略过。该命令主要作用是输出MySQL的optimizer对SQL的执行计划，也即，MySQL会解释如何处理输入的SQL(是否使用索引，使用哪个索引，多表以什么顺序及什么关联字段做join)。explain的提示可以帮助大家意识到哪些字段应该建索引，也可以帮大家确认SQL执行时optimizer是否会以合理的顺序来join多张表。
MySQL本身的功能架构分为三个部分，分别是应用层、逻辑层、物理层，不只是MySQL ，其他大多数数据库产品都是按这种架构来进行划分的。
应用层，主要负责与客户端进行交互，建立链接，记住链接状态，返回数据，响应请求，这一层是和客户端打交道的。
逻辑层，主要负责查询处理、事务管理等其他数据库功能处理，以查询为例。
首先接收到查询SQL之后，数据库会立即分配一个线程对其进行处理，第一步查询处理器会对SQL查询进行优化，优化后会生成执行计划，然后交由计划执行器来执行。
计划执行器需要访问更底层的事务管理器，存储管理器来操作数据，他们各自的分工各有不同，最终通过调用物理层的文件获取到查询结构信息，将最终结果响应给应用层。
物理层，实际物理磁盘上存储的文件，主要有分文数据文件，日志文件。
通过上面的描述，生成执行计划是执行一条SQL必不可少的步骤，一条SQL性能的好坏，可以通过查看执行计划很直观的看出来，执行计划提供了各种查询类型与级别，方面我们进行查看以及为作为性能分析的依据。
explain命令会为SQL中出现的每张表返回一行信息来说明optimizer将会如何操作这张表，其输出中列出的表次序也是MySQL实际执行SQL时对各表的处理顺序。MySQL以nested-loop算法处理所有的join操作，算法原理说明在这里，对认识join的行为有帮助，建议理解。MySQL优化器是基于开销来工作的，它并不提供任何的QEP的位置。这意味着QEP是在每条SQL语句执行的时候动态地计算出来的。在MySQL存储过程中的SQL语句也是在每次执行时计算QEP的。存储过程缓存仅仅解析查询树。
在5.6.10版本后，是可以直接对dml语句进行explain分析操作的。MySQL 优化器是基于开销来工作的，它并不提供任何的QEP的位置。这意味着QEP 是在每条SQL 语句执行的时候动态地计算出来的，在MySQL 存储过程中的SQL语句也是在每次执行时计算QEP的，存储过程缓存仅仅解析查询树。
作为一名日常DBA，最常重复的任务就是在生产环境中检查和优化运行的SQL语句。在MySQL软件安装、配置以及正常运行之后，监控数据库的性能问题就成为一项经常重复的工作。了解如何正确地截取有问题的SQL语句以及检查并做适当的调整，这已经成为一个专业DBA的必备技能。尽管MySQL是一个关系型数据库管理系统(RDBMS)，有Oracle或者SQL Server背景的有经验的数据库管理员还是需要学习如何在MySQL术语中正确地应用SQL查询分析理论，而这需要阅读并理解查询执行计划(QEP)，了解MySQL优化器功能的限制和不足，还要理解不同的MySQL存储引擎是如何改变索引的高效使用方式的。
SQL语句的优化不仅仅是数据库管理员的责任。本书将帮助读者理解MySQL索引和存储引擎是如何运行的，这对一个由数据架构师设计的优化过的数据库来说是更重要的实现考虑因素。软件开发人员将能够截取和分析所有SQL语句，以此来确保性能瓶颈能够在开发早期被发现然后由合适的人去处理。
优化SQL语句是改进性能和扩展性的一个关键部分。MySQL的EXPLAIN命令用于SQL语句的查询执行计划(QEP)。这条命令的输出结果能够让我们了解MySQL优化器是如何执行SQL语句的，这条命令并没有提供任何调整建议，但它能够提供重要的信息帮助你做出调优决策。
--引用自《Effective MySQL》之MySQL EXPLAIN命令详解。
执行下面的语句：
explain select id,first_name from freeoa where id=3 \G
结果如下：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: freeoa
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
explain针对每张表输出的每行记录均包含下面几个字段：
| 项 | 说明 |
| id | MySQL Query Optimizer 选定的执行计划中查询的序列号。表示查询中执行 select 子句或操作表的顺序,id 值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。 |
| select_type 查询类型 | 说明 |
| SIMPLE | 简单的 select 查询,不使用 union 及子查询 |
| PRIMARY | 最外层的 select 查询 |
| UNION | UNION 中的第二个或随后的 select 查询,不 依赖于外部查询的结果集 |
| DEPENDENT UNION | UNION 中的第二个或随后的 select 查询,依 赖于外部查询的结果集 |
| SUBQUERY | 子查询中的第一个 select 查询,不依赖于外 部查询的结果集 |
| DEPENDENT SUBQUERY | 子查询中的第一个 select 查询,依赖于外部 查询的结果集 |
| DERIVED | 用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些子查询, 把结果放在临时表里。 |
| UNCACHEABLE SUBQUERY | 结果集不能被缓存的子查询,必须重新为外 层查询的每一行进行评估。 |
| UNCACHEABLE UNION | UNION 中的第二个或随后的 select 查询,属 于不可缓存的子查询 |
| 项 | 说明 |
| table | 输出行所引用的表 |
| type 重要的项,显示连接使用的类型,按最优到最差的类型排序 | 说明 |
| system | 表仅有一行(=系统表)。这是 const 连接类型的一个特例。 |
| const | const 用于用常数值比较 PRIMARY KEY 时。当 查询的表仅有一行时,使用 System。 |
| eq_ref | const 用于用常数值比较 PRIMARY KEY 时。当 查询的表仅有一行时,使用 System。 |
| ref | 连接不能基于关键字选择单个行,可能查找 到多个符合条件的行。 叫做 ref 是因为索引要 跟某个参考值相比较。这个参考值或者是一 个常数,或者是来自一个表里的多表查询的 结果值。 |
| ref_or_null | 如同 ref, 但是 MySQL 必须在初次查找的结果 里找出 null 条目,然后进行二次查找。 |
| index_merge | 说明索引合并优化被使用了。 |
| unique_subquery | 在某些 IN 查询中使用此种类型,而不是常规的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr) |
| index_subquery | 在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与 unique_subquery 类似,但是查询的是非唯一 性索引: value IN (SELECT key_column FROM single_table WHERE some_expr) |
| range | 只检索给定范围的行,使用一个索引来选择 行。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可 以使用 range。 |
| index | 全表扫描,只是扫描表的时候按照索引次序 进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。 |
| all | 最坏的情况,从头到尾全表扫描。 |
| 项 | 说明 |
| possible_keys | 指出 MySQL 能在该表中使用哪些索引有助于 查询。如果为空,说明没有可用的索引。 |
| 项 | 说明 |
| key | MySQL 实际从 possible_key 选择使用的索引。 如果为 NULL,则没有使用索引。很少的情况 下,MYSQL 会选择优化不足的索引。这种情 况下,可以在 SELECT 语句中使用 USE INDEX (indexname)来强制使用一个索引或者用 IGNORE INDEX(indexname)来强制 MYSQL 忽略索引 |
| 项 | 说明 |
| key_len | 使用的索引的长度。在不损失精确性的情况 下,长度越短越好。 |
| 项 | 说明 |
| ref | 显示索引的哪一列被使用了 |
| 项 | 说明 |
| rows | MYSQL 认为必须检查的用来返回请求数据的行数 |
| 项 | 说明 |
| rows | MYSQL 认为必须检查的用来返回请求数据的行数 |
extra 中出现以下 2 项意味着 MYSQL 根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化。
| extra 项 | 说明 |
| Using filesort | 表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为“文件排序” |
| Using temporary | 表示 MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。 |
在这个QEP中，我们看到使用了一个索引，且估计只有一行数据将被获取。本章中将会详细介绍如何读取并解释这些信息，QEP中每个行的所有列表如下所示：
id
select_type
table
partitions(这一列只有在EXPLAIN PARTITIONS语法中才会出现)
possible_keys
key
key_len
ref
rows
filtered(这一列只有在EXPLAINED EXTENDED语法中才会出现)
Extra
这些列展示了SELECT语句对每一个表的QEP。一个表可能和一个物理模式表或者在SQL执行时生成的内部临时表(例如从子查询或者合并操作会产生内部临时表)相关联。
EXPLAIN列的大众解释：
table：显示这一行的数据是关于哪张表的
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：MYSQL认为必须检查的用来返回请求数据的行数
Extra：关于MYSQL如何解析查询的额外信息。如果看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢
extra列返回的描述的意义
Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
Range checked for each Record(index map:#):没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释(按照效率高低的顺序排序)
system 表只有一行：system表。这是const连接类型的特殊情况
const:表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如，利用最左边前缀)时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好，因为索引一般小于表数据)
ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免
可以参考MySQLReference Manual的相关章节获得更多信息。下面我们将按照分析以及快速有效地理解QEP的重要程度的顺序来介绍这些列，对explain分析结果中的各个参数进行详细说明。
| Column | JSON Name | Meaning |
|---|---|---|
| id | select_id | The SELECT identifier |
| select_type | None | The SELECT type |
| 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 |
1. id
explain 得到的结果集的id编号，id列是在QEP中展示的表的连续引用。
2. select_type
select_type，即SQL查询语句的类型，准确地说，应该是当前的select语句操作table的方式。select_type列提供了各种表示table列引用的使用方式的类型，最常见的值包括SIMPLE、PRIMARY、DERIVED和UNION。其他可能的值还有UNION RESULT、DEPENDENTSUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION以及UNCACHEABLE QUERY。常见的值有以下几种：
SIMPLE 它表示简单的单表查询，没有union和子查询。
PRIMARY 它表示主表(也就是最外层的表)查询。这个类型通常可以在DERIVED和UNION时见到。
DERIVED 它表示派生表查询，派生表本身不是一个物理表。
DEPENDENT SUBQUERY 它表示子查询。
UNION 它表示 union 语句中的查询。
UNION RESULT 这是一系列定义在UNION语句中的表的返回结果。当select_type为这个值时，经常可以看到table的值是<unionN,M>，这说明匹配的id行是这个集合的一部分。
| select_type Value | JSON Name | Meaning |
|---|---|---|
| SIMPLE | None | Simple SELECT (not using UNION or subqueries) |
| PRIMARY | None | Outermost SELECT |
| UNION | None | Second or later SELECT statement in a UNION |
| DEPENDENT UNION | dependent (true) | Second or later SELECT statement in a UNION, dependent on outer query |
| UNION RESULT | union_result | Result of a UNION. |
| SUBQUERY | None | First SELECT in subquery |
| DEPENDENT SUBQUERY | dependent (true) | First SELECT in subquery, dependent on outer query |
| DERIVED | None | Derived table SELECT (subquery in FROM clause) |
| MATERIALIZED | materialized_from_subquery | Materialized subquery |
| UNCACHEABLE SUBQUERY | cacheable(false) | A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query |
| UNCACHEABLE UNION | cacheable(false) | The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) |
1). SIMPLE
对于不包含子查询和其他复杂语法的简单查询，这是一个常见的类型。
2). PRIMARY
这是为更复杂的查询而创建的首要表(也就是最外层的表)。这个类型通常可以在DERIVED 和UNION 类型混合使用时见到。
3). DERIVED
当一个表不是一个物理表时，那么就被叫做DERIVED。下面的SQL 语句给出了一个QEP 中DERIVED select-type 类型的
示例：
mysql> EXPLAIN SELECT MAX(id)
-> FROM (SELECT id FROM users WHERE first = 'west') c;
4). DEPENDENT SUBQUERY
这个select-type 值是为使用子查询而定义的。下面的SQL语句提供了这个值：
mysql> EXPLAIN SELECT p.*
-> FROM parent p
-> WHERE p.id NOT IN (SELECT c.parent_id FROM child c);
5). UNION
这是UNION 语句其中的一个SQL 元素。
6). UNION RESULT
这是一系列定义在UNION 语句中的表的返回结果。当select_type 为这个值时，经常可以看到table 的值是<unionN,M>，
这说明匹配的id 行是这个集合的一部分。下面的SQL产生了一个UNION和UNION RESULT select-type：
mysql> EXPLAIN SELECT p.* FROM parent p WHERE p.val LIKE 'a%'
-> UNION
-> SELECT p.* FROM parent p WHERE p.id > 5;
select_type的示例说明
(1).UNION:
当通过union来连接多个查询结果时，第二个之后的select其select_type为UNION。
mysql> explain select * from t_order where order_id=100 union select * from t_order where order_id=200;
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | t_order | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | UNION | t_order | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
3 rows in set (0.34 sec)
(2).DEPENDENT UNION与DEPENDENT SUBQUERY:
当union作为子查询时，其中第二个union的select_type就是DEPENDENT UNION。第一个子查询的select_type则是DEPENDENT SUBQUERY。
mysql> explain select * from t_order where order_id in (select order_id from t_order where order_id=100 union select order_id from t_order where order_id=200);
+----+--------------------+------------+-------+---------------+---------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+-------+---------------+---------+---------+-------+--------+-------------+
| 1 | PRIMARY | t_order | ALL | NULL | NULL | NULL | NULL | 100453 | Using where |
| 2 | DEPENDENT SUBQUERY | t_order | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 3 | DEPENDENT UNION | t_order | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------------+------------+-------+---------------+---------+---------+-------+--------+-------------+
4 rows in set (0.03 sec)
(3).SUBQUERY:
子查询中的第一个select其select_type为SUBQUERY。
mysql> explain select * from t_order where order_id=(select order_id from t_order where order_id=100);
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | t_order | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | SUBQUERY | t_order | const | PRIMARY | PRIMARY | 4 | | 1 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.03 sec)
(4).DERIVED:
当子查询是from子句时，其select_type为DERIVED。
mysql> explain select * from (select order_id from t_order where order_id=100) a;
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | t_order | const | PRIMARY | PRIMARY | 4 | | 1 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
2 rows in set (0.03 sec)
3. table
它表示和当前的输出结果相关的表。table列是EXPLAIN命令输出结果中的一个单独行的唯一标识符。这个值可能是表名、表的别名或者一个为查询产生临时表的标识符，如派生表、子查询或集合。
4. type
type 是一个非常重要的参数，也较为复杂，它表示了当前的查询所使用的查找数据行的扫描方式或访问类型。访问类型有很多种，比如：全表扫描、索引扫描、范围扫描、唯一索引扫描、常数引用扫描等等。这里列的这些，速度是从慢到快，扫描的行数也是从多到少。现在，我们就根据这个顺序介绍几个常见的扫描方式：
ALL 全表扫描，表示需要扫描整张表，才能获取到需要的数据。不会使用到索引，一般来说，它的性能最差。通常可以通过对某些字段合理建索引来避免全表扫描。如：
desc select id,first_name from people where gender=2;
index 索引扫描，表示仅从索引中扫描获取数据，index 和 all 差不多，都需要扫描全部的记录，只不过 index 是从索引中扫描全部记录获取需要的数据。虽然从索引中扫描获取了数据，但实际上并没有用到索引加快查找速度的功能。也就是说索引失效了，性能也较差。如：
explain select id,last_name,first_name,birthday from people where first_name='Cuba';
该值表明MySQL执行本步计划时扫描的是index tree，而ALL则是扫全表，它可能在两种场景下出现：
case1. 本步计划查询数据集时select语句需要返回的字段是该数据集索引字段的最左前缀匹配集。如table A中已建立含2个字段的联合索引(f1, f2)，则select f1 from A where f2 = 'yyy'可能会触发MySQL扫描index tree，这种情况下，执行计划的Extra字段会包含"Using index"来表明它扫描的是index tree，因为f1是(f1, f2)最终前缀匹配集中的1个元素；而select f2 from A where f2 = 'yyy'则会扫描全表。
case2. 本步计划按照索引顺序进行全表扫描来查找符合条件的数据。这种情况下，执行计划的Extra字段不会包含"Using index"，这种全表扫描也是必须优化的场景。
range 范围扫描，表示在给定的范围内进行扫描，会使用到索引，性能较好。该值表明本步执行计划只操作单表且符合查询条件的记录不只1条，可能出现在有in或between操作的SQL中。只限于单表操作场景也是其与前面提到的join_type取值为'ref'场景的区别，因为ref可能出现在单表/多表join操作场景下。如：
explain select id,first_name from people where id < 3;
或
explain select id,first_name from people where last_name BETWEEN 'Allen' And 'Clinton';
ref 常规索引匹配扫描，表示用到的索引是常规索引(也叫普通索引)，性能较好。需要注意的是，复合索引中的列顺序，要想使索引生效，需遵循最左前缀匹配法则。该值表明本步执行计划操作的数据集中关联字段是索引字段但不只有1条记录符合上步执行计划操作的数据集的关联条件。
符合关联条件的记录不只1条表明关联字段非primary key或unique index，当符合关联条件的记录数比较少时，这种join_type='ref'的场景还是比较合理的，但它显然不如join_type='eq_ref'高效。如：
explain select id,first_name from people where last_name='Allen' and first_name='Cuba';
eq_ref 索引关联扫描，联表查询时，如果关联的键是主键，就会出现这个值，性能较好。该值表明本步执行计划操作的数据集中关联字段是索引字段且只有1条记录符合上步执行计划操作的数据集的关联条件。这是对多表做关联查询时，可能得到的最优的join类型(因为它通常表明关联的字段是本步执行计划要操作的表的primary key或unique index)。
system/const 常量引用扫描，表示用到的索引是主键或唯一索引，索引会等值匹配一个定值，性能最好。表明上述"table"字段代表的数据集中，最多只有1行记录命中本步执行计划的查询条件，例如这步执行计划的sql的where子句以某张表的primary key或unique index与常数做比较时，该执行计划对应的type字段取值就是const。
system只是const值的一个特例，它表示本步执行计划要操作的数据集中只有1行记录。它们只可能出现在单表查询SQL的type字段取值中。如：
explain select id,first_name from people where id=3;ref_or_null
该join type类型与ref的场景类似，但它表明MySQL会对包含NULL值的字段做额外搜索。例如下面SQL的join type就是ref_or_null：
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
index_merge
该值表明MySQL会对本步执行计划进行index merge优化，触发index merge的SQL通常包含'or'操作，常见实例如下：
SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key=30;
SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1=t1.some_col;
SELECT * FROM t1, t2 WHERE t1.key1=1 AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);
type列代表QEP中指定的表使用的连接方式，下面是最常用的几种连接方式：
const：当这个表最多只有一行匹配的行时出现
system：这是const的特例，当表只有一个row时会出现
eq_ref：这个值表示有一行是为了每个之前确定的表而读取的
ref：这个值表示所有具有匹配的索引值的行都被用到
range：这个值表示所有符合一个给定范围值的索引行都被用到
ALL：这个值表示需要一次全表扫描
其他类型的值还有fulltext、ref_or_null、index_merge、unique_subquery、index_subquery以及index。想了解更多信息可以访问官方文档，下面举一些type的使用示例：
(1).system，const
见上面(4).DERIVED的例子。其中第一行的type就是为system，第二行是const，这两种联接类型是最快的。
(2).eq_ref
在t_order表中的order_id是主键，t_order_ext表中的order_id也是主键，该表可以认为是订单表的补充信息表，他们的关系是1对1，在下面的例子中可以看到b表的连接类型是eq_ref，这是极快的联接类型。
mysql> explain select * from t_order a,t_order_ext b where a.order_id=b.order_id;
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+
| 1 | SIMPLE | b | ALL | order_id | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test.b.order_id | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+
2 rows in set (0.00 sec)
(3).ref
下面的例子在上面的例子上略作了修改，加上了条件。此时b表的联接类型变成了ref。因为所有与a表中order_id=100的匹配记录都将会从b表获取。这是比较常见的联接类型。
mysql> explain select * from t_order a,t_order_ext b where a.order_id=b.order_id and a.order_id=100;
+----+-------------+-------+-------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+-------+------+-------+
| 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | b | ref | order_id | order_id | 4 | const | 1 | |
+----+-------------+-------+-------+---------------+----------+---------+-------+------+-------+
2 rows in set (0.00 sec)
(4).ref_or_null
user_id字段是一个可以为空的字段，并对该字段创建了一个索引。在下面的查询中可以看到联接类型为ref_or_null，这是mysql为含有null的字段专门做的处理。在我们的表设计中应当尽量避免索引字段为NULL，因为这会额外的耗费mysql的处理时间来做优化。
mysql> explain select * from t_order where user_id=100 or user_id is null;
+----+-------------+---------+-------------+---------------+---------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------------+---------------+---------+---------+-------+-------+-------------+
| 1 | SIMPLE | t_order | ref_or_null | user_id | user_id | 5 | const | 50325 | Using where |
+----+-------------+---------+-------------+---------------+---------+---------+-------+-------+-------------+
1 row in set (0.00 sec)
(5).index_merge
经常出现在使用一张表中的多个索引时。mysql会将多个索引合并在一起，如下例:
mysql> explain select * from t_order where order_id=100 or user_id=10;
+----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
| 1 | SIMPLE | t_order | index_merge | PRIMARY,user_id | PRIMARY,user_id | 4,5 | NULL | 2 | Using union(PRIMARY,user_id); Using where |
+----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
1 row in set (0.09 sec)
(6).unique_subquery
该联接类型用于替换value IN (SELECT primary_key FROM single_table WHERE some_expr)这样的子查询的ref。注意ref列，其中第二行显示的是func，表明unique_subquery是一个函数，而不是一个普通的ref。
mysql> explain select * from t_order where order_id in (select order_id from t_order where user_id=10);
+----+--------------------+---------+-----------------+-----------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+-----------------+-----------------+---------+---------+------+--------+-------------+
| 1 | PRIMARY | t_order | ALL | NULL | NULL | NULL | NULL | 100649 | Using where |
| 2 | DEPENDENT SUBQUERY | t_order | unique_subquery | PRIMARY,user_id | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+---------+-----------------+-----------------+---------+---------+------+--------+-------------+
2 rows in set (0.00 sec)
(7).index_subquery
该联接类型与上面的太像了，唯一的差别就是子查询查的不是主键而是非唯一索引。
mysql> explain select * from t_order where user_id in (select user_id from t_order where order_id>10);
+----+--------------------+---------+----------------+-----------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+----------------+-----------------+---------+---------+------+--------+--------------------------+
| 1 | PRIMARY | t_order | ALL | NULL | NULL | NULL | NULL | 100649 | Using where |
| 2 | DEPENDENT SUBQUERY | t_order | index_subquery | PRIMARY,user_id | user_id | 5 | func | 50324 | Using index; Using where |
+----+--------------------+---------+----------------+-----------------+---------+---------+------+--------+--------------------------+
2 rows in set (0.00 sec)
(8).range
按指定的范围进行检索，很常见。
mysql> explain select * from t_order where user_id in (100,200,300);
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | t_order | range | user_id | user_id | 5 | NULL | 3 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
(9).index
在进行统计时非常常见，此联接类型实际上会扫描索引树，仅比ALL快些。
mysql> explain select count(*) from t_order;
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | t_order | index | NULL | user_id | 5 | NULL | 100649 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)
(10).ALL
完整的扫描全表，最慢的联接类型，尽可能的避免。
mysql> explain select * from t_order;
+----+-------------+---------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------+
| 1 | SIMPLE | t_order | ALL | NULL | NULL | NULL | NULL | 100649 | |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.00 sec)
5. possible_keys
表示当前查询可能用到的索引，这个值不太重要。possible_keys列指出优化器为查询选定的索引。该字段的值是可能被MySQL用作索引的字段，若值为NULL，则没有字段会被用作索引，因此查询效率不会高，这种情况下，需要优化数据表的索引结构。
一个会列出大量可能的索引(例如多于3个)的QEP意味着备选索引数量太多了，同时也可能提示存在一个无效的单列索引。可以用第2章详细介绍过的SHOW INDEXES命令来检查索引是否有效且是否具有合适的基数。为查询确定QEP的速度也会影响到查询的性能。如果发现有大量的可能的索引，则意味着这些索引没有被使用到。相关的QEP列还包括key列。
6. key
表示当前查询实际用到的索引，key列指出优化器选择使用的索引。一般来说SQL查询中的每个表都仅使用一个索引，也存在索引合并的少数例外情况，如给定表上用到了两个或者更多索引。和key列相关的列还包括possible_keys、rows以及key_len。该字段的值是MySQL真正用到的索引。
注意：该字段的值有可能不是possible_keys列出的候选索引字段，例如，当前查询SQL要返回的字段是数据表某索引字段的最左前缀匹配字段，但SQL的where条件中没有使用数据表的索引字段，则此时possible_keys可能为NULL，而key字段的值可能是那个能cover住待查询字段的数据表索引字段，此时，MySQL会扫描索引树，虽然低效，但比起扫描全表还是要快。这种场景也正是本文前面解释join_type='index'时提到的case1。此外，在select语句中借助"force index或"use index"可以强制MySQL使用possible_keys中列出的候选索引字段。
7. key_len
表示当前查询用到的索引的长度(字节数)。key_len列定义了用于SQL语句的连接条件的键的长度，此列值对于确认索引的有效性以及多列索引中用到的列的数目很重要。该字段的值表明上述key字段的length，当MySQL将某联合索引字段作为SQL执行时用到的索引时，key_len字段可以暗示MySQL真正在什么程度上(多长的最左前缀匹配字段)使用了该联合索引。若key字段的值为NULL，则key_len字段值也为NULL。
此列的一些示例值如下所示：
key_len: 4 // INT NOT NULL
key_len: 5 //INT NULL
key_len: 30 // CHAR(30) NOT NULL
key_len: 32 //VARCHAR(30) NOT NULL
key_len: 92 // VARCHAR(30)NULL CHARSET=utf8
从这些示例中可以看出，是否可以为空、可变长度的列以及字符集都会影响到表索引的内部内存大小。key_len列的值只和用在连接和WHERE条件中的索引的列有关。索引中的其他列会在ORDER BY或者GROUP BY语句中被用到。
8. ref
表示索引的哪一列被用到了，如果是等值匹配索引，则是一个常数 const，可以被用来标识那些用来进行索引比较的列或者常量。该字段的值表明数据表中的哪列或哪个constants会被用于与key字段指定的索引做比较。
9. rows
表示当前查询实际扫描的行数，值越小越好，这个参数非常重要。rows列提供了试图分析所有存在于累计结果集中的行数目的MySQL优化器估计值，QEP很容易描述这个很困难的统计量。查询中总的读操作数量是基于合并之前行的每一行的rows值的连续积累而得出的，这是一种嵌套行算法。
以连接两个表的QEP为例。通过id=1这个条件找到的第一行的rows值为1，这等于对第一个表做了一次读操作。第二行是通过id=2找到的，rows的值为5。这等于有5次读操作符合当前1的积累量。参考两个表，读操作的总数目是6。在另一个QEP中，第一rows的值是5，第二rows的值是1。这等于第一个表有5次读操作，对5个积累量中每个都有一个读操作。因此两个表总的读操作的次数是10(5+5)次。
最好的估计值是1，一般来说这种情况发生在当寻找的行在表中可以通过主键或者唯一键找到的时候。可以使用SHOW STATUS命令来查看实际的行操作，这个命令可以提供最佳的确认物理行操作的方式。通过SHOW STATUS 命令可以查看实际的行操作，该命令表明物理读操作数量大幅增加。
10. Extra
表示当前查询的额外信息，比如是根据什么方式排序的，获取数据的方式等。Extra列提供了有关不同种类的MySQL优化器路径的一系列额外信息，可以包含多个值，可以有很多不同的取值，并且这些值还在随着MySQL新版本的发布而进一步增加。下面给出常用值的列表，列举几个Extra参数常见的值：
using filesort 表示当前查询做了额外的步骤将结果集进行文件排序。如果看到这个值，就需要进行优化了。
using temporary 表示当前查询使用了一个临时表来存储结果。如果看到这个值，也需要进行优化。
using index 表示只使用索引就可以满足全部的查询需求，不需要再回表查询数据行，通常称之为索引覆盖查询。
通常，如果我们明确知道了哪条SQL查询语句效率很差，就可以用 explain 或 desc 来分析该SQL语句。但大多数情况下，我们并不知道是哪些SQL查询语句有问题，一般都是开启MySQL的慢查询日志，通过慢查询日志来确定查询效率较慢的问题SQL，然后再对这些问题SQL进行分析和优化。
1). Using where
这个值表示查询使用了where语句来处理结果——例如执行全表扫描。如果也用到了索引，那么行的限制条件是通过获取必要的数据之后处理读缓冲区来实现的。
2). Using temporary
这个值表示使用了内部临时(基于内存的)表。一个查询可能用到多个临时表。有很多原因都会导致MySQL在执行查询期间创建临时表。两个常见的原因是在来自不同表的列上使用了DISTINCT，或者使用了不同的ORDER BY和GROUP BY列。
想了解更多内容可以访问这里。
可以强制指定一个临时表使用基于磁盘的MyISAM存储引擎。这样做的原因主要有两个：
内部临时表占用的空间超过min(tmp_table_size，max_ heap_table_size)系统变量的限制
使用了TEXT/BLOB列
3). Using filesort
这是ORDER BY语句的结果。这可能是一个CPU密集型的过程。可以通过选择合适的索引来改进性能，用索引来为查询结果排序。
4). Using index
这个值重点强调了只需要使用索引就可以满足查询表的要求，不需要直接访问表数据。
5). Using join buffer
这个值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能。
6). Impossible where
这个值强调了where语句会导致没有符合条件的行。请看下面的示例：
mysql> EXPLAIN SELECT * FROMuser WHERE 1=2;
7). Select tables optimized away
这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。请看下面的示例：
mysql> EXPLAIN SELECT COUNT(*) FROM (SELECT id FROM users WHERE first = 'west') c
8). Distinct
这个值意味着MySQL在找到第一个匹配的行之后就会停止搜索其他行。
9). Index merges
当MySQL决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型。
Using sort_union(...)
Using union(...)
Using intersect(...)
你可以从下面的地址找到更全面的值的列表，下面将列举extra的使用示例：
(1).Distinct
MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。对于此项没有找到合适的例子，求指点。
(2).Not exists
因为b表中的order_id是主键，不可能为NULL，所以mysql在用a表的order_id扫描t_order表，并查找b表的行时，如果在b表发现一个匹配的行就不再继续扫描b了，因为b表中的order_id字段不可能为NULL。这样避免了对b表的多次扫描。
mysql> explain select count(1) from t_order a left join t_order_ext b on a.order_id=b.order_id where b.order_id is null;
+----+-------------+-------+-------+---------------+--------------+---------+-----------------+--------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+-----------------+--------+--------------------------------------+
| 1 | SIMPLE | a | index | NULL | express_type | 1 | NULL | 100395 | Using index |
| 1 | SIMPLE | b | ref | order_id | order_id | 4 | test.a.order_id | 1 | Using where; Using index; Not exists |
+----+-------------+-------+-------+---------------+--------------+---------+-----------------+--------+--------------------------------------+
2 rows in set (0.01 sec)
(3).Range checked for each record
这种情况是mysql没有发现好的索引可用，速度比没有索引要快得多。
mysql> explain select * from t_order t, t_order_ext s where s.order_id>=t.order_id and s.order_id<=t.order_id and t.express_type>5;
+----+-------------+-------+-------+----------------------+--------------+---------+------+------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------------+--------------+---------+------+------+------------------------------------------------+
| 1 | SIMPLE | t | range | PRIMARY,express_type | express_type | 1 | NULL | 1 | Using where |
| 1 | SIMPLE | s | ALL | order_id | NULL | NULL | NULL | 1 | Range checked for each record (index map: 0x1) |
+----+-------------+-------+-------+----------------------+--------------+---------+------+------+------------------------------------------------+
2 rows in set (0.00 sec)
(4).Using filesort
在有排序子句的情况下很常见的一种情况。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行。
mysql> explain select * from t_order order by express_type;
+----+-------------+---------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+--------+----------------+
| 1 | SIMPLE | t_order | ALL | NULL | NULL | NULL | NULL | 100395 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+--------+----------------+
1 row in set (0.00 sec)
(5).Using index
这是性能很高的一种情况。当查询所需的数据可以直接从索引树中检索到时，就会出现。上面的例子中有很多这样的例子，不再多举例了。
(6).Using temporary
发生这种情况一般都是需要进行优化的，mysql需要创建一张临时表用来处理此类查询。
mysql> explain select * from t_order a left join t_order_ext b on a.order_id=b.order_id group by b.order_id;
+----+-------------+-------+------+---------------+----------+---------+-----------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-----------------+--------+---------------------------------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 100395 | Using temporary; Using filesort |
| 1 | SIMPLE | b | ref | order_id | order_id | 4 | test.a.order_id | 1 | |
+----+-------------+-------+------+---------------+----------+---------+-----------------+--------+---------------------------------+
2 rows in set (0.00 sec)
(7).Using where
当有where子句时，extra都会有说明。
(8).Using sort_union(...)/Using union(...)/Using intersect(...)
下面的例子中user_id是一个检索范围，此时mysql会使用sort_union函数来进行索引的合并。而当user_id是一个固定值时，请参看上面type说明5.index_merge的例子，此时会使用union函数进行索引合并。
mysql> explain select * from t_order where order_id=100 or user_id>10;
+----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+------------------------------------------------+
| 1 | SIMPLE | t_order | index_merge | PRIMARY,user_id | user_id,PRIMARY | 5,4 | NULL | 2 | Using sort_union(user_id,PRIMARY); Using where |
+----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+------------------------------------------------+
1 row in set (0.00 sec)
对于Using intersect的例子可以参看下例，user_id与express_type发生了索引交叉合并。
mysql> explain select * from t_order where express_type=1 and user_id=100;
+----+-------------+---------+-------------+----------------------+----------------------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------------+----------------------+----------------------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | t_order | index_merge | user_id,express_type | user_id,express_type | 5,1 | NULL | 1 | Using intersect(user_id,express_type); Using where |
+----+-------------+---------+-------------+----------------------+----------------------+---------+------+------+----------------------------------------------------+
1 row in set (0.00 sec)
(9).Using index for group-by
表明可以在索引中找到分组所需的所有数据，不需要查询实际的表。
mysql> explain select user_id from t_order group by user_id;
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t_order | range | NULL | user_id | 5 | NULL | 3 | Using index for group-by |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
除了上面的三个说明，还需要注意rows的数值，多行之间的数值是乘积的关系，可以估算大概要处理的行数，如果乘积很大，那就很有优化的必要了。
11. filtered
filtered列给出了一个百分比的值，这个百分比值和rows列的值一起使用，可以估计出那些将要和QEP中的前一个表进行连接的行的数目。前一个表就是指id列的值比当前表的id小的表。这一列只有在EXPLAIN EXTENDED语句中才会出现。12. partitions
partitions列代表给定表所使用的分区。这一列只会在EXPLAIN PARTITIONS语句中出现。
查询结构中有12列，理解每一列的含义，对理解执行计划至关重要，下面用一个表格的形式进行说明。
| 列名 | 说明 |
|---|---|
| id | SELECT识别符，这是SELECT的查询序列号。 |
| select_type | SELECT类型,可以为以下任何一种:
|
| table | 输出的行所引用的表 |
| partitions | 如果查询是基于分区表的话，显示查询将访问的分区。 |
| type | 联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
一般来说，得保证查询至少达到range级别，最好能达到ref。 |
| possible_keys | 指出MySQL能使用哪个索引在该表中找到行 |
| key | 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 |
| key_len | 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。在不损失精确性的情况下，长度越短越好 |
| ref | 显示使用哪个列或常数与key一起从表中选择行。 |
| rows | 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。 |
| filtered | 显示了通过条件过滤出的行数的百分比估计值。 |
| Extra | 该列包含MySQL解决查询的详细信息
|
参考来源：
8.8.2 EXPLAIN Output Format
MySQL索引原理及慢查询优化
该文章最后由 阿炯 于 2018-06-11 13:29:16 更新，目前是第 2 版。