MySQL Explain详解

​ 在日常工作中，我们有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。（QEP：sql生成一个执行计划query Execution plan）

image-20200510010205376

expain出来的信息有12列，分别是id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra，下面对这些字段出现的可能进行解释。

1.id

我的理解是SQL执行的顺序的标识,SQL从大到小的执行

1. id相同时，执行顺序由上至下
2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

2.select_type

表示查询中每个select子句的类型

• SIMPLE: 简单的select查询，查询中不包含子查询或者UNION

• PRIMARY: 查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY

• UNION: 若第二个SELECT出现在UNION之后，则被标记为UNION;
  若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

• UNION RESULT: UNION的结果

• SUBQUERY: 在SELECT或者WHERE列表中包含了子查询

• DERIVED: 在FROM列表中包含的子查询被标记为DERIVED（衍生）
  MySQL会递归执行这些子查询，把结果放在临时表里。

3.table

显示这一行的数据是关于哪张表的，有时不是真实的表名字，看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

image-20200510011913656

4.partitions

版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

5.type

显示查询使用了何种访问类型
从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL

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

• const: 表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键至于where列表中，MySQL就能将该查询转换为一个常量

• eq_ref: 唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描,

  简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

• ref: 非唯一索引扫描，返回匹配某个单独值的所有行。
  本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，
  它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

• range: 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引
  一般就是在你的where语句中出现了between、<、>、in等的查询
  这种范围扫描索引扫描比全表扫描要好，因为他只需要开始索引的某一点，而结束语另一点，不用扫描全部索引

• index: Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

• ALL: FullTable Scan,将遍历全表以找到匹配的行

6.possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

7.key

key列显示MySQL实际决定使用的键（索引）

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

查询中若使用了覆盖索引，则索引和查询的select字段重叠

8.key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的)

不损失精确性的情况下，长度越短越好

9.ref

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

10.rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

11.filtered

使用explain extended时会出现这个列，5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

12.Extra

包含不适合在其他列中显示但十分重要的额外信息

• Using filesort: 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
  MySQL中无法利用索引完成排序操作成为“文件排序”

• Using temporary: 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。

  常见于排序order by 和分组查询 group by

  表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！
  如果同时出现using where，表明索引被用来执行索引键值的查找；
  如果没有同时出现using where，表面索引用来读取数据而非执行查找动作。

• Using index: 覆盖索引（Covering Index）,是否使用了索引

• Using where: 表面使用了where过滤, 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

• Using join buffer: 改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

• impossible where: where子句的值总是false，不能用来获取任何元素

• select tables optimized away: 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者
  对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，
  查询执行计划生成的阶段即完成优化。

• distinct: 优化distinct，在找到第一匹配的元组后即停止找同样值的工作

总结

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。