MySQL中的Explain用法

一、Explain介绍
　　使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而就可以知道MySQL是如何处理你的SQL语句的，可以用来分析你的查询语句或者是表结构的性能瓶颈。
　　通过EXPLAIN我们可以分析出以下结果：
　　　　●表的读取顺序
　　　　●数据读取操作的操作类型
　　　　●哪些索引可以使用
　　　　●哪些索引被实际使用
　　　　●表之间的引用
　　　　●每张表有多少行被优化器查询

　　使用方式如下：

EXPALIN + SQL语句

二、Explain各列信息含义

列名	说明
id	执行的编号，标识select所属的行。如果在语句中没有子查询或者关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type	显示本行是简单查询或者是复杂查询，如果查询有任何复杂的子查询，则最外层标记为PRIMARY(DERIVED、UNION、UNION RESUIT)
table	数据的查询访问引用了哪个表
type	数据访问/读取的操作类型(ALL、index、range、ref、eq_ref、const、system、NULL)
possible_keys	显示哪些索引可能有利于高效的数据查询
key	显示优化器最终决定采用的索引
key_len	显示使用索引中所使用的字节数
ref	显示了之前的表在key列记录的索引中查找值所用的列或者常量
rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值想乘，可粗略估算整个查询会检查的行数
Extra	额外信息，如using index、filesort等

1）id
　　id是用来顺序标识整个查询中SELECT语句的编号，在嵌套查询中id越大的语句越先执行。该值可能为NULL，那么这一行用来说明的是其他行的联合结果。
　　
2）select_type
　　表示查询的类型

类型	说明
simple	简单子查询，查询中不包含子查询和union
primary	包含union或者子查询，最外层的部分标记为primary
subquery	在select或者where列表中包含了子查询
derived	派生表，该临时表是从子查询中派生出来的，位于form中的子查询
union	位于union中第二个及其以后的子查询被标记为union，第一个就被标记为primary，如果是union位于from中则标记为derived
union result	用来从匿名临时表里检索结果的select被标记为union result
dependent union	顾名思义，首先需要满足union的条件，及union中第二个以及后面的select语句，同时该语句依赖外部的查询
dependent subquery	和dependent union相对union一样

3）table
　　对应行正在访问哪一个表，表名或者别名。
　　●关联优化器会为查询选择关联顺序，左侧深度优先
　　●当form中有子查询的时候，表名是derivedN的形式，N指向子查询，也就是explain结果中的下一列
　　●当有union result的时候，表名是union 1,2等的形式，1,2表示参与union的query id
　　注意：MySQL对待这些表和普通表一样，但是这些”临时表”是没有任何索引的。

4）type
　　type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
　　system > const > eq_ref > ref > fulltext > req_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，一般来说，得保证查询至少达到range级别，最好能够达到ref。

类型	说明
system	表只有一行记录(等于系统表)，这是const类型的特例
const	当确定查询最多只会有一行匹配的时候，MySQL优化器会在查询前读取它并且只读取一次，即通过索引一次就找到了数据，因此非常快。当主键放入where子句时，mysql会把这个查询转换为一个常量(高效)
eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值得所有行，本质上也是一种索引访问，它返回所有匹配某个单独值得行，然而，它可能会找到多个符合条件的行，所以它属于查找和扫描的混合体
range	只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般是在where语句中出现between、<、>、in等的查询中，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点不用做扫描全部索引
index	index和all的区别为index类型只遍历索引树，这通常比all要快，因为索引文件通常比数据文件小，也就是说all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读取的
all	最坏的情况，将遍历全表以找到匹配的行

5）possible_keys
　　显示查询使用了哪些索引，表示该索引可以进行高效的查找，但是列出来的索引对于后续优化过程可能是没有用的。

6）key
　　key列显示了MySQL优化器实际上所决定使用的键（索引），如果没有选择索引，键是NULL。要想MySQL使用或者忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

7）key_len
　　表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示地值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

8）ref
　　显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或者常量被用于查找索引列上的值。

9）rows
　　rows列显示MySQL认为它执行查询时必须检查的行数，注意这是一个预估值。

10）Extra
　　Extra是Explain输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。

类型	说明
Using filesoft	MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesoft说明MySQL使用了后者，但注意虽然叫filesoft但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。使用文件完成排序操作，这是可能是order by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。
Using temporary	用临时表保存中间结果，常用于group by和order by操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。
Not exists	MySQL优化了left join，一旦它找到了匹配left join标准的行，就不再搜索了
Using index	说明查询是覆盖了索引的，不需要读取数据文件，从索引树(索引文件)中即可获得信息，如果同时出现using where，表明索引被用来执行索引键值的查找，没有using where，表明索引被用来执行索引键值的查找，没有using where，表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的，但无需再回表查询记录
Using index condition	这是MySQL 5.6出来的新特性，叫做”索引条件推送”。简单说就是MySQL原来在索引上是不能执行如like这样的模糊匹配操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。
Using where	使用了where从句来限制哪些行将于下一张表匹配或者返回给用户。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用where条件过滤
Using join buffer	使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接；Batched Key Access，连接算法是批量索引连接
impossible where	where子句的值总是false，不能用来获取任何元组
select tables optimized away	在每有group by子句的情况下，基于索引优化min/max操作，或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct	优化distinct操作，在找到第一个匹配的元组后即停止找同样值得动作