MySQL EXPLAIN

工作中时，我们在数据库创建了索引，那我们如何知道我们利用上了索引没有？

EXPLAIN语句提供了MySQL如何执行语句的信息，从而知道MySQL是如何处理你的SQL语句的，可以帮助选择更好的索引和写出更优化的查询语句。EXPLAIN适用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句。

下面是使用 explain 的例子：

mysql> explain select * from test;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	test	ALL	NULL	NULL	NULL	NULL	2	NULL

在旧的MySQL版本中，分区和扩展信息是使用EXPLAIN PARTITIONS和EXPLAIN extended生成的：
1）explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以 得到优化后的查询语句，从而看出优化器优化了什么。
2）explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。
这些语法仍然可以用于向后兼容性，但是分区和扩展输出在新版本默认启用，因此PARTITIONS和extended关键字是多余的，不建议使用。

explain 中的列

字段	含义
id	该语句的唯一标识
select_type	查询类型
table	表名
partitions	匹配的分区
type	联接类型
possible_keys	可能的索引选择
key	实际选择的素引
key_len	索引的长度
ref	索引的哪一列被引用了
rows	估计要扫描的行
filtered	表示符合查询条件的数据百分比
Extra	附加信息

接下来我们将解读 explain 中每个列的信息：

1.id列

这是查询中SELECT的序号，如果是其他行并集结果，则为NULL。如果explain的结果包括多个不同的id值，则数字越大越先执行；而对于相同id的行，则表示从上往下依次执行。

2.select_type列

查词类型	作用
SIMPLE	简单查询（未使用UNION或子查询）
PRIMARY	复杂查询中最外层的查询
UNION	在UNION中的第二个和随后的SELECT被标记为UNION。如果UNION被FROM子句中的子查询包含，那么它的第一个SELECT会被标记为 DERIVED
DEPENDENT UNION	UNION中的第二个或后面的查询，依赖了外面的查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT（不在 from 子句中）
DEPENDENT SUBQUERY	子查询中的第一个SELECT，依赖了外面的查询
DERIVED	用来表示包含在FROM子句的子查询中的SELECT， MySQL会递归执行并将结果放到一个临时表中。MySQL内部将其称为是Derived table（派生表），因为该临时表是从子查询派生出来的
DEPENDENT DERIVED	派生表，依赖了其他的表
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	子查询，结果无法缓存，必须针对外部查询的每一行重新评估
UNCACHEABLE UNION	属于UNCACHEABLE SUBQUERY的第二个或后面的查询

例子：

mysql> explain select * from film where id = 2;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	film	const	PRIMARY	PRIMARY	4	const	1	NULL

mysql> explain select 1 union all select 1;

mysql> explain select (select 1 from t_jxkh_xnyx where id = 1) from (select * from t_jxkh_pici where id = 1) pc;

3.table列

表示当前这一行正在访问哪张表，如果SQL定义了别名，则展示表的别名。
当 from 子句中有子查询时，table列是格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。可以是以下值之一 :
<unionM,N>：表示id值为M和N的行并集。
< derivedN>：该行指向id值为n的行的派生表结果。例如，派生表可能来自from子句中的子查询。
< subqueryN>：该行指向id值为n的行的物化子查询结果。

4.partitions

当前查询匹配记录的分区。对于未分区的表，返回NULL

5.type

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行。
依次从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，好的sql查询至少达到range级别，最好能达到ref。

NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。

mysql> explain select min(id) from t1;

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计。

const：表示通过索引一次就找到了，const用于primary key 或者 unique索引的等值查询。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const。

mysql> explain select * from t1 where id=1;

eq_ref：primary key 或 unique not null 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

explain select * from t_jxkh_khcjzf a left join t_jxkh_pici b on a.pici_fk = b.id;

ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。

explain select * from t_jxkh_khcjzf where gh='gh';

fulltext：全文索引。

ref_or_null：类似ref，但是可以搜索值为NULL的行。

SELECT * FROM t_jxkh_khcjzf WHERE gh='gh' OR gh IS NULL;

index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引。

explain select * from t_jxkh_khcjzf where gh='1111' or id=2;

unique_subquery：该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引。

index_subquery：和unique_subquery类似，只是子查询使用的是非唯一索引。

range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引。

explain select * from t_jxkh_khcjzf where id>1

index：Full Index Scan全索引扫描，和ALL类似，只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发：
如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain的Extra 列的结果是Using index。index通常比ALL快，因为索引的大小通常小于表数据。
按索引的顺序来查找数据行，执行了全表扫描。此时，explain的Extra列的结果不会出现Uses index。

explain select id from t_jxkh_khcjzf

ALL：Full Table Scan全表扫描，性能最差。

6.possible_keys

这一列显示查询可能使用哪些索引来查找，但不一定被查询实际使用。如果此列为NULL，则没有相关索引。在这种情况下，您可以通过检查WHERE子句来检查它是否引用了一些适合建立索引的列，如果是，创建适当的索引，从而提高查询的性能。

7.key

这一列显示mysql实际使用索引。
如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

8.key_len

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。如果key列显示NULL, key_len列也显示NULL。由于存储格式，当字段允许为NULL时，key_len比不允许为空时大1字节。

key_len计算规则如下：

字符串
char(n)：n字节长度，如果是utf-8，则长度 3n
varchar(n)：多2字节存储字符串长度，如果是utf-8，则长度 3n + 2
数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节　　
时间类型　
date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL。

varchr(n)变长字段且允许NULL=n*(character set: utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(n)变长字段且不允许NULL =n*(character set: utf8=3,gbk=2,latin1=1)+2(变长字段)
char(n)固定字段且允许NULL=n*(character set: utf8=3,gbk=2,latin1=1)+1(NULL)
char(n)固定字段且不允许NULL=n*(character set: utf8=3,gbk=2,latin1=1)

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

9.ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名。
如果ref是一个函数，则使用的值是函数的结果。要想查看是哪个函数，可在EXPLAIN语句之后紧跟一个SHOW WARNINGS语句。

10.rows

MySQL估算会扫描的行数，注意这个不是结果集里的行数，数值越小越好。对于InnoDB表，这个数字是一个估计值，可能并不总是准确的。

11.filtered

表示符合查询条件的数据百分比，最大值为100，这意味着没有发生行过滤，从100开始递减的值表示过滤量的增加。用rows × filtered可获得和下一张表连接的行数。例如rows = 1000，filtered = 50%，则和下一张表连接的行数是500。

在MySQL 5.7之前，想要显示此字段需使用explain extended命令；
MySQL.5.7及更高版本，explain默认就会展示filtered。

12.Extra

展示有关本次查询的附加信息。常见的重要值如下：
distinct：一旦mysql找到了与行相联合匹配的行，就不再搜索了。

select DISTINCT a.gh from t_jxkh_xnyx a left join t_jxkh_khcjzf b on a.gh=b.gh

Using index：这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。同上个例子，gh是索引列。

Using where：mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。

explain select * from t_jxkh_khcjzf where id > 1

Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

select DISTINCT gh from t_jxkh_xnyx

select DISTINCT gh from t_jxkh_khcjzf

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

select gh from t_jxkh_xnyx order by gh

select gh from t_jxkh_khcjzf order by gh

Impossible WHERE：WHERE子句始终为false，不会命中任何行。

select gh from t_jxkh_khcjzf WHERE 1=2

Impossible HAVING：HAVING子句始终为false，不会命中任何行。
No tables used：当此查询没有FROM子句或拥有FROM DUAL子句时出现。例如：

explain select 1

Not exists：MySQL能对LEFT JOIN优化，在找到符合LEFT JOIN的行后，不会为上一行组合中检查此表中的更多行。例如：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

参考：https://zhuanlan.zhihu.com/p/281517471
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html