【Mysql Explain结果分析详解】

explain简介

 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句。分析你的查询语句或是表结构的性能瓶颈。 通过explain我们可以获得以下信息：
- id 读表的顺序
- select_type  表查询类型
- table 参加分析表名
- partitions 分区
- type 索引类型
- possible_keys 可能使用索引
- key 实际使用索引名称
- ken_len 索引长度
- ref 索引列列名
- rows 扫描行数
- filtered 回结果的行数占需读取行数的百分比
- extra 额外的不显示但比较重要的信息

id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，即执行操作表的优先级

• id相同，执行优先级顺序由上至下
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
• id既存在相同又存在不同。相同可以认为为一组，优先级由上至下；不同组，优先级由下至上

优先级值越小优先级先级越高，执行越优先
第一种：ID相同

ID	优先级
1	1
1	2

第二种：ID不同

ID	优先级
1	2
2	1

第三种：ID既存在相同又存在不同

ID	优先级
1	2
1	3
1	4
2	1

select_type

• SIMPLE
  简单的SELECT语句（不包括UNION操作或子查询操作）
• PRIMARY/UNION
  PRIMARY：查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION）
• DEPENDENT UNION/UNIOIN RESULT
  1. DEPENDENT UNION：UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句有依赖关系）
  2. UNION RESULT：UNION操作的结果，id值通常为NULL
• SUBQUERY/DEPENDENT SUBQUERY
  SUBQUERY：子查询中首个SELECT（如果有多个子查询存在）：
  DEPENDENT SUBQUERY：子查询中首个SELECT，但依赖于外层的表（如果有多个子查询存在）
• DERIVED/MATERIALIZED
  DERIVED：被驱动的SELECT子查询（子查询位于FROM子句）
• UNCACHEABLE SUBQUERY/UNCACHEABLE UNION
  UNCACHEABLE SUBQUERY：对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）
  UNCACHEABLE UNION：UNION操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）

*注意*
特别关注 DEPENDENT SUBQUERY 

  1  会严重消耗性能

  2  不会进行子查询，会先进行外部查询,生成结果集,再在内部进行关联查询

  3  子查询的执行效率受制于外层查询的记录数

  4  可以尝试改成join查询   

table

table 指的就是当前执行的表

partitions

partitions 指的就是当前执行的表所处的分区

type

type所显示的是查询使用了哪种类型

从最好到最差依次是：system > const > eq_ref > ref > range > index > all

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

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

possible_keys

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

key

• 实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）
• 查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中

key_len

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

ref

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

rows

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

filtered

• filtered表示返回结果的行数占需读取行数的百分比
• filtered列的值越大越好
• filtered列的值依赖于统计信息

extra

在其他列中显式但十分重要的额外信息

• Using index
  使用覆盖索引，防止回表
  表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作
• Using where
  表明使用了where过滤
• Using filesort
  说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
• Using temporary
  使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
• impossible where
  where子句的值总是false，不能用来获取任何元组

例： where id=1 and id=2 永远为false

• select tables optimized away
  在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
• distinct
  优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作