MySQL explain详解

介绍

当使用EXPLAIN对一个可解释的语句进行解释时，MySQL会显示关于该语句执行计划的信息。也就是说，MySQL会解释该语句的执行过程，包括表的连接顺序和执行的顺序。

用法

EXPLAIN SELECT * FROM teach_user WHERE user_id = '123456'

生成结果

结果列解释

1. id相同，执行顺序从上之下
2. select_type：查询类型
3. table：输出的行所引用的表
4. partitions：表分区
5. type：表的联接类型
6. possible_keys：查询时可能使用的索引
7. key：实际使用索引
8. key_len：实际使用索引长度
9. ref：列与索引的比较
10. rows：扫描出的行数
11. filtered：通过条件过滤出的行数的百分比估计值
12. extra：执行情况描述和说明

1.id

select标识符, 可以理解为SQL执行的顺序, 从大到小执行

1. id相同，执行顺序从上之下
2. id不同，执行顺序从大到小
3. id相同不同，同时存在，遵守1、2规则.

2.select_type

查询类型

1. SIMPLE: 简单的select, 不适用union或子查询等. 例如: 
   
2. PRIMARY: 子查询中最外层查询, 查询中若包含任何复杂的子部分, 最外层的select被标记为primary. 例如: 
   
3. UNION: union中的第二个或后面的select语句. 例如: 
   
4. DEPENDENT UNION: UNION中的第二个或后面的SELECT语句,取决于外面的查询. 例如
   
5. UNION RESULT: union的结果集,例如：
   
6. SUBQUERY: 子查询中的第一个select. 例如:
   
7. DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询. 例如: 
   
8. DERIVED: 派生表的select(from子句的子查询). 例如: 
   版本：5.6
   
   
   
   版本：5.7（5.7以后估计被mysql优化了）
   

3.table

显示数据来自于哪个表，有时不是真实的表的名字（虚拟表），虚拟表最后一位是数字，代表id为多少的查询。

4.partitions

如果查询是基于分区表的话，显示查询将访问的分区。值为NULL表示表未被分区

5.type

重要且困难

这个字段尤为关键，直接反应我们SQL的性能是否高效，也是我们在优化SQL的时候重点关注的字段

性能排序-----system > const > eq_ref > ref > range > index > all-------

一般来说，得保证查询至少达到range级别，最好能达到ref。

1. system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计
2. const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const
3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
5. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引
6. index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）
7. all：Full Table Scan，遍历全表以找到匹配的行

6.possiblie_keys

不太重要

显示可能应用在表中的索引, 可能一个或多个. 查询涉及到的字段若存在索引, 则该索引将被列出, 但不一定被查询实际使用

7.key

简单且重要

实际中使用的索引, 如为NULL, 则表示未使用索引. 要想强制MySQL使用或忽视possible_keys列中的索引, 在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX.

查询中如果使用了覆盖索引，则该索引仅出现在key列表中

8.key_len

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

9.ref

重要

显示索引的那一列被使用了，如果可能，是一个常量const。

10.row

简单且重要

根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数. 当然该值越小越好.

11.filtered

这一列是MySQL5.1里新加的，它显示的是针对表里符合某个条件（where子句或联结条件）的记录数所做的一个悲观估算的百分比。 表示存储引擎返回的数据经过滤后, 剩下多少满足查询条件记录数量的比例.

12.Extra

重要

1. Using filesort ： 表明mysql会对数据使用一个外部的索引排序, 而不是按照表内的索引顺序进行读取. 在mysql中, 无法利用索引完成的排序操作称为"文件排序". 当出现using filesort时就非常危险了, 在数据量非常大的时候几乎"九死一生". 出现using filesort尽快优化sql语句.
   
   
   
   
   由于索引是先按nick_name排序、再按award_point排序，所以查询时如果直接去按award_point排序，索引就不能满足要求了，mysql内部必须再实现一次“文件排序”
    
2. Using temporary：使用了临时表保存中间结果, 常见于排序order by和分组查询group by. 非常危险, “十死无生”, 急需优化.
   
3. Using index：表明相应的select操作中使用了覆盖索引, 避免访问表的额外数据行, 效率不错. 如果同时出现了using where, 表明索引被用来执行索引键值的查找. 如果没有同时出现using where, 表明索引用来读取数据而非执行查找动作.
   
   
   覆盖索引（Covering Index）：也叫索引覆盖。就是select列表中的字段，只用从索引中就能获取，不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
   注意： 
     a、如需使用覆盖索引，select列表中的字段只取出需要的列，不要使用select * 
     b、如果将所有字段都建索引会导致索引文件过大，反而降低crud性能
    
4. Using where ： 不用读取表中所有信息, 仅通过索引就可以获取所需数据, 这发生在对表的全部的请求列都是同一个索引的部分的时候, 表示mysql服务器将在存储引擎检索行后再进行过滤.
    
5. Using join buffer ：使用了链接缓存
    
6. Impossible WHERE： where子句的值总是false，不能用来获取任何元素
   
7. select tables optimized away：在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即可完成优化
8. distinct： 优化distinct操作，在找到第一个匹配的元素后即停止找同样值得动作

总结

explain包含的信息十分的丰富, 主要关注以下几个字段信息.

1. id: select子句或表执行顺序, id相同, 从上到下执行, id不同, id值越大, 执行优先级越高.
2. type: type主要取值及其表示sql的好坏程度(由好到差排序)：system > const > eq_ref > ref > range > index > all. 保证range, 最好到ref.
3. key: 实际被使用的索引列.
4. ref: 关联的字段, 常量等值查询, 显示为const, 如果为连接查询, 显示关联的字段.
5. Extra: 额外信息, 使用优先级using index > using filesort > using temporary.