关于MySQL中EXPLAIN语句的最全介绍
MySQL应该是我们平时用得最多的一个关系型数据库了吧，毕竟开源免费而且功能强大。但是如果想知道一条SQL语句具体是怎么执行的，总不能每次都看源码吧，而官方就提供了一个工具——EXPLAIN语句，可以查看一条SQL的具体执行计划。
其实EXPLAIN我平时用得也不少，但EXPLAIN的输出信息实在比较丰富，所以每次都是根据输出结果然后上网定向查，过后就又忘了。这一次终于下定决心要把EXPLAIN的所有情况做一个总结。
本文介绍的MySQL版本
5.7
EXPLAIN的使用场景与功能
- EXPLAIN后面如果跟的是SQL语句，则只适用于DML，官方文档直接说明：SELECT, DELETE, INSERT, REPLACE和 UPDATE 语句。也就是说你想用EXPLAIN去查看MySQL是怎么CREATE或者DROP一张表的，这是不行滴。
- EXPLAIN可以用来输出一条SQL的具体执行计划，包括多个表之间的连接顺序，如何连接以及索引的使用等等。
- EXPLAIN也可以用于显示某个连接中执行语句的执行计划，格式类似于EXPLAIN FOR CONNECTION connection_id。
- 针对SELECT语句，EXPLAIN可以输出更多的执行计划信息，方法是在EXPLAIN后执行SHOW WARNINGS。
- EXPLAIN可以输出查询语句中有关分区表的信息，分区简单而言，就是通过一定的规则将单表做切分，然后可以以不同的形式存储于不同的物理区域，因为跟本文关联不是很大，所以不做重点介绍。
- EXPLAIN后面紧跟表名的话跟DESCRIBE的作用相同。
根据我自己的使用经验，我们平时使用EXPLAIN最多的用途就是查看某条SQL有没有用到索引、用了哪个索引、是否有文件排序、表的关联顺序是否和预期一致等等和SQL执行速度相关的情况，主要就是用来解决慢SQL。
EXPLIAN的输出信息
由于情况类似，这里以SELECT语句为例，EXPLAIN会根据SELECT语句中涉及到的每个表(包括临时表)都输出一行信息。每一行都包含很多列，下面重点介绍每一列的含义，毕竟这最常用。
输出列介绍
下面的表格列出了所有EXPLAIN语句输出的列，后面会详细说明每一列的情况。
| 列名 | 含义 |
|---|---|
| id | SELECT标识符 |
| select_type | SELECT类型 |
| table | 本行信息对应的表名称 |
| partitions | 语句匹配到的分区 |
| type | 连接类型 |
| possible_keys | 有可能用到的索引 |
| key | 实际选择的索引 |
| key_len | 被选择的索引长度 |
| ref | 索引比较列 |
| rows | 扫描行数的估计值 |
| filtered | 按表条件过滤的行数所占总数的百分比 |
| Extra | 扩展信息 |
id
这是MySQL执行查表操作的标识符，除了引用其他表并集结果的情况下会为null，其实一般都是有值的，而且一般是大于0的整数。id的作用就是表明了每个表之间的查询顺序，两条规则：
- id越大，越先被执行
- id相同，靠前的先执行
select_type
下面的表格列出了select_type的所有可能值。
| 可取值 | 含义 |
|---|---|
| SIMPLE | 简单SELECT（不使用 UNION或子查询） |
| PRIMARY | 最外层的SELECT |
| UNION | UNION语句中第二个或者再之后的SELECT语句 |
| DEPENDENT UNION | 跟UNION类似，区别在于当前语句依赖于外部查询 |
| UNION RESULT | UNION语句的结果 |
| SUBQUERY | 子查询中第一个SELECT语句 |
| DEPENDENT SUBQUERY | 跟SUBQUERY类似，区别在于当前查询依赖于外部查询 |
| DERIVED | 派生表 |
| MATERIALIZED | 物化子查询 |
| UNCACHEABLE SUBQUERY | 子查询，其结果无法缓存，必须针对外部查询的每一行重新进行评估 |
| UNCACHEABLE UNION | UNION语句中第二个或者再之后的SELECT语句，结果无法缓存(参考UNCACHEABKE SUBQUERY) |
这里DEPENDENT SUBQUERY和UNCACHEABLE SUBQUERY是有区别的，DEPENDENT SUBQUERY针对外部查询中的每个值重新评估(重复值评估一次)，而UNCACHEABLE SUBQUERY会针对外部的每个值都评估一次(重复值重复评估)。
- SIMPLE
- PRIMARY & UNION & UNION RESULT
- DEPENDENT UNION & UNCACHEABLE SUBQUERY
- SUBQUERY
- DEPENDENT SUBQUERY
- DERIVED
- MATERIALIZED
这是一种对子查询的优化，不必针对每个外层循环执行子查询
- UNCACHEABLE UNION
table
输出当前行所引用的表的名称，也有以下其他的情况：
- <unionm,n style="box-sizing: border-box;">：即id为M和N的查询结果的并集</unionm,n>
- <derivedn style="box-sizing: border-box;">：id为N的查询结果的派生表，比如一个FROM子句中的子查询</derivedn>
- <subqueryn style="box-sizing: border-box;">：id为N的物化子查询结果，可以认为是一个内存临时表，用于加快查询速度的</subqueryn>
partitions
一般为null，毕竟MySQL的分区功能比较少用，如果有值，代表当前查询匹配到的分区。
type
连接类型，用于表示多个表之间是怎么连接的，可以取的值需要详细介绍，不同取值差异巨大。
- system
该表只有一行(即系统表)。这是const联接类型的特例 ，一般在业务开发中不太常见。
- const
该表最多有一个匹配行，本次查询开始的时候就已经获取到这个唯一值了，所以后续的所有查询都将本次查询到的内容视为常量，常见的就是主键或者唯一索引的等值比较。
// emp_no是主键
explain select * from employees where emp_no = 10021;
- eq_ref
之前查询到的结果中每一行在当前查询中最多匹配一行，是除了system和 const类型之外最好的联接类型。常见的就是：主键或者唯一索引 = {之前查询的某个列值}
// emp_no是employees表的主键
explain select * from employees, dept_manager where employees.emp_no = dept_manager.emp_no;
- ref
与eq_ref类似，但是之前查询到的结果中每一行在当前查询中可能匹配到多行(索引匹配)。常见的就是：非唯一索引 = {之前查询的某个列值}
// dept_no是dept_manager表的普通索引explain select * from departments, dept_manager where departments.dept_no = dept_manager.dept_no;
- fulltext
使用fulltext类型的索引进行连接，这里就不做具体展开了，可以认为和ref类似，但是索引类型是fulltext。
- ref_or_null
就是ref加上一个null值查询，需要索引所在列允许值为null。
- index_merge
使用了索引合并的优化，其实就是MySQL用到了多个索引，最常见的就是两个索引=值的or连接查询
// 这里dept_no和emp_no都有各自的索引explain select * from dept_manager where dept_no = "d009" or emp_no = 110183;
- unique_subquery
官方介绍说是唯一索引的查找条件中用到了子查询，替代某些eq_ref的IN查询。但是我按照官方例子试了还是eq_ref。
- index_subquery
这个就是普通索引的查找条件用了子查询，替代某些ref的IN查询。同样，我也试不出来
- range
索引在某个范围内的查询。
- index
与ALL其实差不多，只是MySQL扫描了索引树，有两种情况
- 覆盖索引，通过扫描索引就拿到了数据，不用回表，Extra会显示Using index。
- 通过扫描索引来扫描全表，Extra不会显示Using index。
- ALL
全表扫描，最烂的一种情况，要避免。
possible_keys
可能用到的索引，注意仅仅是可能，如果这一列没有值，那你要紧张一下了。。
key
实际使用到的索引。通常情况下是possible_keys中的某一个，但是也有特殊情况，比如MySQL根据where条件列出了所有可能用到的索引，但是实际查询的列被另一个索引完全覆盖(覆盖索引)，这个时候key就有可能是这个覆盖索引，因为虽然这个索引并不能用来过滤，但根据这个索引全部扫描一次更快(不用回表)。
某些情况下先ANALYZE TABLE table_name会影响索引的选择，因为统计信息也是MySQL选择索引的重要参考条件。
key_len
使用到的索引的长度，有时候不用使用全部索引长度就可以过滤完成，由于索引存储格式的原因，允许为null的那些列对应的索引会长一个单位(相比那些not null的列)。
ref
显示哪些列或者常量被用于索引的比较。如果值是func，那么代表索引的比较条件是一个函数结果，可以通过SHOW WARNINGS查看更具体的结果。
rows
查询需要扫描的行数，对于InnoDB来说，是个估计值。
filtered
表示按表条件过滤的表行的估计百分比，最大值为100，这表示未过滤任何行。所以该值越小表明当前过滤条件越有效。
Extra(不知道为啥就这个字段是大写开头)
包含一些执行计划的扩展信息，包含以下这些可选值，常见的有详细分析。
- const row not found
查询空表的情况，但实际我没试出来
- Deleting all rows
MyISAM引擎快速删除所有表格记录时会显示这个值
- Distinct
使用了distinct
- FirstMatch(
tbl_name)
当前面表的查询策略使用了半联接FirstMatch联接快捷方式策略
- Full scan on NULL key
子查询优化无法利用到索引时的一种备选方案
- Impossible HAVING
HAVING子句中的条件永远不满足，结果一定是空
- Impossible WHERE
WHERE子句中的条件永远不满足，结果一定是空
- Impossible WHERE noticed after reading const tables
MySQL已经读取了所有 const（和 system）表，并判断该WHERE子句始终为false。
- LooseScan(
m..n)
使用了半连接的LooseScan策略。m 和 n是索引部分的编号。
- No matching min/max row
使用MIN和MAX函数的时候发现没有满足条件的记录
- no matching row in const table
对于具有联接的查询，存在一个空表或没有满足唯一索引条件的行的表。
- No matching rows after partition pruning
对于DELETE或 UPDATE，在分区修剪后，优化器未发现任何要删除或更新的内容。类似于Impossible WHERE for SELECT语句。
- No tables used
没有FROM子句
- Not exists
对LEFT JOIN 的一种优化，在找到符合LEFT JOIN条件的一行后，不再检查更多行是否满足条件。
这个例子中dept_manager.emp_no定义为not null
- Plan isn't ready yet
使用EXPLAIN FOR CONNECTION才会出现的值，当优化器未完成为在指定连接中执行的语句创建执行计划时，就会出现此值。
- Range checked for each record (index map:
N)
MySQL找不到很好的索引来使用，但是发现在知道先前表中的列值之后可能会使用某些索引。
- Scanned
Ndatabases
查询INFORMATION_SCHEMA中的表时，显示具体查询了多少目录，可取0、1或者all。
- Select tables optimized away
要查询的信息是现成的，不要遍历索引或者表，比较有代表性的例子就是查询引擎是MyISAM的某个表的总记录数，因为MyISAM引擎是记录这个数据的，所以直接获取即可。
-
Skip_open_table,Open_frm_only,Open_full_table
查询INFORMATION_SCHEMA才会出现的值
-
Skip_open_table：不需要打开表文件，通过扫描数据库目录就可以了。
-
Open_frm_only：只需要打开数据库表的.frm文件。
-
Open_full_table：未优化，.frm、.MYD和 .MYI文件都必须被打开。
-
Start temporary,End temporary
这表明临时表用于半联接重复淘汰策略，不常见。
- unique row not found
对于诸如SELECT … FROM tbl_name的查询，没有行满足表上的UNIQUE索引或PRIMARY KEY的条件。
- Using filesort
需要额外进行一次排序及查找，应该尽量避免。实际上MySQL会按照WHERE条件找出所有排序键和行记录指针，然后按照排序键进行一次排序，然后再根据指针查出所有记录。
这是一种比较常见的情况，order by后面的列没有建立索引，而where条件后的列建立了索引，优化器最终选择了按照emp_no查询记录，此时的逻辑就是：
-
按照emp_no 小于 20000查出所有birth_date及对应记录的指针；
-
根据birth_date排序；
-
根据指针查出所有记录后返回。
-
Using index
简单来说就是不需要回表，查询的数据直接根据索引就能拿到，不需要查询真正的数据行。
上面的查询因为dept_name上有索引，所以这里直接根据索引就能拿到需要查询的数据。还有一种特殊情况，看下面的索引
由于索引的结构设计，key是索引列的值，value是聚簇索引(一般就是主键)，所以查询列带上聚簇索引包含的列依然可以Using index。
- Using index condition
这里涉及一个概念，即索引条件下推（ICP），5.6版本后提供的新特性。where中关于索引的过滤条件下推到存储引擎减少不必要的网络IO，有两个重要的特点：
- 单表单索引
- 聚集索引无效
下面的例子我是为了测试特地加了first_name和last_name的联合索引。
- Using index for group-by
与Using index类似，表示MySQL找到了一个索引，该索引可用于检索GROUP BY或 DISTINCT查询的所有列，但是不需要对实际表进行任何额外的磁盘访问。
-
Using join buffer (Block Nested Loop),Using join buffer (Batched Key Access)
本次查询之前的查询结果被缓冲起来，然后本次查询是与上一次的连接是通过读取缓冲区的数据来执行的，Block Nested Loop和Batched Key Access是两种不同的算法。
- Using MRR
使用了多范围读取优化。MRR的目的是为了减少磁盘的随机IO（存储引擎先按照聚集索引排序再从磁盘获取数据），而且如果不是想获取数据的所有列，MRR是不具有优势的。二级索引在物理存储上是不连续的，所以如果没有MRR，随机IO将会很明显(大多数情况)。
-
Using sort_union(...),Using union(...),Using intersect(...)
索引合并优化时用到的优化算法，比如下面这个，两个索引的结果是通过union连接的。
Using temporary
为了执行该查询，MySQL需要创建一个临时表来保存结果。常见的就是GROUP BY和 ORDER BY子句后面跟着不同的列。
Using where
用WHERE子句作过滤，限制行记录范围去匹配下一个表或最终发送到客户端的行记录。
Using where with pushed condition
适用于NDB集群，类似于索引下推，将where条件下推到数据节点来避免一些无谓的网络消耗。
Zero limit
含有一个LIMIT 0子句，选不到任何记录。
写在最后
其中重要的几个就是 key、type 、rows、extra，其中key为null时，说明没有使用到索引，需要调整索引。type为ALL的地方,需要进行优化，一般需要达到ref、eq_ref级别，范围查找需要达到range。extra有Using filesort、Using temporary 的一定需要优化，根据rows可以直观看出优化结果。
