SQL EXPLAIN解析器

前言

explain所有人都应该很熟悉，通过它可以知道SQL是如何执行的，虽然不是100%管用，但是至少大多数场景通过explain的输出结果能直观的看到执行计划的相关信息。

早一些的版本explain还只能查看select语句，现在已经能支持delete，update，insert，replace了。

字段参数

id 查询编号

查询编号，如果没有子查询或者联合查询的话，就只有一条，如果是联合查询的话，那么会出现一条id为null的记录，并且标志查询结果，因为union结果会放到临时表中，所以我们看到这里的表名是<union1,2>这种格式。

select_type 关联类型

关联类型，决定访问表的方式。

类型	描述
SIMPLE	简单select（不使用union或子查询）
PRIMARY	最外层的select
UNION	Union中第二个或之后的select语句
UNION RESULT	UNION的结果，union语句中第二个select开始后面所有select
DEPENDENT UNION	Union中第二个或之后的select语句取决于外面的查询
SUBQUERY	子查询中的第一个SELECT，结果不依赖于外部查询
DERIVED	派生表的SELECT, FROM子句的子查询
Materialized	物化子查询
DEPENDENT SUBQUERY	结果集无法缓存的子查询，必须重新评估外部查询的每一行
UNCACHEABLE UNION	Union中的第二个或之后的select，属于无法缓存的子查询

SIMPLE

简单查询，代表没有子查询或者union。

PRIMARY

如果不是简单查询，那么最外层查询就会被标记成PRIMARY。

UNION & UNION RESULT

从上图可以看出来了，包含联合查询，第一个被标记成了PRIMARY，union之后的查询被标记成UNION，以及最后产生的UNION RESULT

DERIVED

用来标记出现在from里的子查询，这个结果会放入临时表中，也叫做派生表。

对于低版本的Mysql显示如上图，高一点可能看到的还是PRIMARY，因为被Mysql优化了。下面换一个版本的Mysql和SQL执行可以验证到这个结果。

SUBQUERY

不在from里的子查询。

DEPENDENT

代表关联子查询（子查询使用了外部查询包含的列），和UNION，SUBQUERY组合产生不同的结果。

UNCACHEABLE

代表不能缓存的子查询，也可以和UNION，SUBQUERY组合产生不同的结果。

MATERIALIZED

物化子查询是Mysql对子查询的优化，第一次执行子查询时会将结果保存到临时表，物化子查询只需要执行一次。

比如上述DERIVED就是物化的一种体现，与之对应的就是DEPENDENT，每次子查询都需要重新调用。

这个结果无法直观的看出来，可以用FORMAT=JSON命令查看materialized_from_subquery字段。

table 表名

显示表名，从上述的一些图中可以观察到UNION_RESULT和DERIVED显示的表名都有一些自己的命名规则。

比如UNION_RESULT产生的是<unionM,N>，DERIVED产生的是。

partitions 数据的分区信息

数据的分区信息，没有分区忽略就好了。

type 关联类型

关联类型，决定通过什么方式找到每一行数据。以下按照速度由快到慢。

优先级	类型	描述
1	system	只有一条数据的系统表
2	const	主键或者唯一索引查询,最终常量查询
3	eq_ref	主键或者唯一索引,最多返回一条数据
4	ref	最左前缀匹配,非主键和唯一索引查询,返回多条数据
5	fulltext	FULLTEXT索引
6	ref_or_nul	ref的特殊情况,还有 is null 多进行一次查找
7	index_merge	索引合并
8	unique_subquery	和eq_ref类似,in(select pk)特殊场景出现
9	index_subquery	非唯一索引的unque_subquery
10	range	范围查询
11	index	和ALL类似,只是按照索引顺序扫表
12	ALL	全表扫描

system > const > eq_ref > ref > fulltext > ref_or_nul l> index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。

system & const

通常是最快的查找方式，代表Mysql通过优化最终转换成常量查询，最常规的做法就是直接通过主键或者唯一索引查询。

system是const的一个特例（只有一行数据的系统表），随便找一张系统表，就插入一条数据就可以看到system了。

eq_ref

通常通过主键索引或者唯一索引查询时会看到eq_ref，它最多只返回一条数据。user_id是唯一索引，为了测试就关联以下主键索引。

ref

通过索引查找，但是和eq_ref不同，ref可能匹配到多条符合条件的数据，比如最左前缀匹配或者不是主键和唯一索引。

fulltext

使用FULLTEXT索引

ref_or_null

和ref类似，但是还要进行一次查询找到NULL的数据。

这相当于是对于IS NULL查询的优化，如果表数据量太少的话，或许能看到这里类型是全表扫描。

index_merge

索引合并是在Mysql5.1之后引入的，就像下面的一个OR查询，按照原来的想法要么用name的索引，要么就是用age的索引，有了索引合并就不一样了。

对于这种单表查询（无法跨表合并）用到了多个索引的情况，每个索引都可能返回一个结果，Mysql会对结果进行取并集、交集，这就是索引合并了。

unique_subquery

按照官方文档所说，unique_subquery只是eq_ref的一个特例，对于下图中这种 in 的语句查询会出现以提高查询效率。

由于Mysql会对select进行优化，基本无法出现这个场景，只能用update这种语句了。

index_subquery

和unique_subquery类似，只是针对的是非唯一索引。

range

范围查询，其实就是带有限制条件的索引扫描。

常见的范围查询比如between and，>，<，like，in 都有可能出现range。

index

跟全表扫描类似，只是扫表是按照索引顺序进行。

ALL

全表扫描，没啥好说的。

possible_keys 可使用索引

可以使用哪些索引。

key 实际使用索引

实际决定使用哪个索引。

key_len 索引字段的可能最大长度

索引字段的可能最大长度，不是表中实际数据使用的长度。

ref 实际使用列或常量

表示key展示的索引实际使用的列或者常量。

rows 执行结果行数

查询数据需要读取的行数，只是一个预估的数值，但是能很直观的看出SQL的优劣了。

filtered 符合条件的记录数百分比

5.1版本之后新增字段，表示针对符合查询条件的记录数的百分比估算，用rows和filtered相乘可以计算出关联表的行数。

Extra 解析后的附加信息

解析查询的附加额外信息，这个太多了，有兴趣可以自己看官方文档，列举一些常见的:

Using index

使用覆盖索引。

Using index condition

使用索引下推，索引下推简单来说就是加上了条件筛选，减少了回表的操作。

Using temporary

排序使用了临时表。

Using filesort

使用外部索引文件排序，但是不能从这里看出是内存还是磁盘排序，我们只能知道更消耗性能。

Using where

where过滤。

Zero limit

除非你写个LIMIT 0。

Using sort_union(), Using union(), sing intersect()

使用了索引合并。