MySQL Explain详解
在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。（QEP：sql生成一个执行计划query Execution plain）
mysql> explain select * from servers; +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | servers | ALL | NULL | NULL | NULL | NULL | 1 | NULL | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.03 sec)
expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面对这些字段出现的可能进行解释：
一、 id
含义
select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
id的情况有三种，分别是：
- id相同表示加载表的顺序是从上到下。
- id不同id值越大，优先级越高，越先被执行。id有相同，也有不同，同时存在。
- id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。
实战
第一步、预备工作(创建部门表、员工表)
第二步：分别分析一下id的不同情况。
1、id相同的情况
小总结：id相同，表示加载表的顺序是从往下，从上图看出，首先加载dept表，再加载emp表。
2、id不相同的情况
小总结：id不相同，id的值越大，优先级越高越先被执行。从上图可以看出，首先是执行的是id=2的这一行，也就子查询，执行的是表dept，然后再执行emp表。
3、id存在相同，也存在不同，同时存在。
小总结：这个就是id存在相同，也存在不同，是同时存在的。首先可以把它们分为两组，id相同的为一组，id不同的为一组，这两组中，id的值越大的越先执行，首先执行的id=2的这一行，去加载emp表，然后，再去执行id=1的组，id相同，从上往下执行，那么执行的应该是“<derived2>”，这个“derived”表示的是衍生的意思，而其中的“2”表示的是id=2的这一行。
二、select_type
显示查询中每个select子句的类型
(1)SIMPLE
简单的SELECT语句(不包括UNION操作或子查询操作)
(2)PRIMARY/UNION
PRIMARY:查询中最外层的SELECT(如两表做UNION或者存在子查询的外层的表操作为PRIMARY,内层的操作为UNION)
UNION:UNION操作中,查询中处于内层的SELECT(内层的SELECT语句与外层的SELECT语句没有依赖关系)
(3)DEPENDENT UNION/UNIOIN RESULT
DEPENDENT UNION:UNION操作中,查询中处于内层的SELECT(内层的SELECT语句与外层的SELECT语句有依赖关系)
UNION RESULT:UNION操作的结果,id值通常为NULL
(4)SUBQUERY/DEPENDENT SUBQUERY
SUBQUERY:子查询中首个SELECT(如果有多个子查询存在):
DEPENDENT SUBQUERY:子查询中首个SELECT,但依赖于外层的表(如果有多个子查询存在)
(5)DERIVED/MATERIALIZED
DERIVED:被驱动的SELECT子查询(子查询位于FROM子句)
MATERIALIZED:被物化的子查询
(6)UNCACHEABLE SUBQUERY/UNCACHEABLE UNION
UNCACHEABLE SUBQUERY:对于外层的主表,子查询不可被物化,每次都需要计算(耗时操作)
UNCACHEABLE UNION:UNION操作中,内层的不可被物化的子查询(类似于UNCACHEABLE SUBQUERY)
三、table
显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)
mysql> explain select * from (select * from ( select * from t1 where id=2602) a) b; +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | | | 3 | DERIVED | t1 | const | PRIMARY,idx_t1_id | PRIMARY | 4 | | 1 | | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
四、type
表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
ALL：Full Table Scan， 全表扫描
index: Full Index Scan，索引全扫描，index与ALL区别为index类型只遍历索引树
range: 索引范围扫描，只检索给定范围的行，使用一个索引来选择行，常用语<,<=,>=,between等操作
ref: 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
NULL: MySQL不访问任何表或索引，直接返回结果
五、possible_keys
该查询可以利用的索引. 如果没有任何索引可以使用，就会显示成null，这一项内容对于优化时候索引的调整非常重要；
六、Key
MySQL Query Optimizer 从possible_keys 中所选择的实际使用的索引；
如果没有选择索引，键是NULL。
要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
七、key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
不损失精确性的情况下，长度越短越好。
八、ref
如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func
九、rows
表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
十、Extra
十一、总结
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。
