MySQL explain关键字详解

1、explain输出列

mysql> explain select tm.* from ((select * from t1) union (select * from t2) union (select * from t3
)) tm;
+------+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id   | select_type  | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+------+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|    1 | PRIMARY      | <derived2>   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |      100 | NULL            |
|    2 | DERIVED      | t1           | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |      100 | NULL            |
|    3 | UNION        | t2           | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |      100 | NULL            |
|    4 | UNION        | t3           | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |      100 | NULL            |
| NULL | UNION RESULT | <union2,3,4> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL     | Using temporary |
+------+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

id

包含一组数字，表示查询中执行select子句的或者操作表的顺序，
id相同，执行顺序由上至下顺序执行
id不同，id值越大优先级越高，越先执行
id相同或者不同，如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
id值可能为null，表示最后的结果集来源于union的结果，在这种情况下，使用一个类似<unionM,N>来表示这些行指向的是id为M和N的union结果集

select_type

select的子句类型，一般为以下几种

select_type值	json 命名	含义
SIMPLE	None	简单select类型，不使用union和子查询的
PRIMARY	None	查询中包任何复杂的子部分的最外层查询
UNION	None	union子句中的第二个或者往后的select
DEPENDENT UNION	dependent(true)	依赖于外部查询的第二个或者往后的union select
UNION RESULT	union_result	union子句的结果
SUBQUERY	None	子查询中的第一个select
DEPENDENT SUBQUERY	dependent(true)	依赖于外部查询的子查询中的第一个select
DERIVED	None	衍生表
DEPENDENT DERIVED	dependent(true)	表示依赖于另外表的衍生表
MATERIALIZED	materialized_from_subquery	物化子查询
UNCACHEABLE SUBQUERY	cacheable(false)	表示该子查询的结果集将不会被缓存并且一定会被外部查询重新计算
UNCACHEABLE UNION	cacheable(false)	属于uncacheable subquery的第二个或者往后的union select

table

表示结果集中的行指向的表，一般有以下几种

• <unionM,N>:id为M和N的结果集的union
• :结果集的行来源于衍生表的结果集，N表示来源于select标识符为N的select
• :物化子查询N的结果集
• 表名

partitions

表示将会被该查询匹配的分区，其值为空表示表不可分区

type

表示mysql在表中找到所需行的方式，一般有以下几种
system
表只有一行数据，const的特例
const
表示该表只有一行匹配的数据，比如主键和唯一键的查询方式，速度仅次于system
eq_ref
对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
ref
表示所有行都匹配索引值，表中有多行记录匹配
ref
使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行
fulltext
全文索引
ref_or_null
在ref的基础上，但是使用了一个额外的空值匹配

SELECT  \*  FROM  *ref\_table*  WHERE  *key\_column*\=*expr*  OR  *key\_column*  IS  NULL;

index_merge
表示索引合并优化。In this case, thekeycolumn in the output row contains a list of indexes used, andkey_lencontains a list of the longest key parts for the indexes used
unique_subquery
表示下面使用在子查询中的eq_ref,比如下面形式：

*value*  IN  (SELECT  *primary\_key*  FROM  *single\_table*  WHERE  *some\_expr*)

unique_subquery仅仅是一种完全为了更好查询效率的一种索引查找功能
range
索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有查询。当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。
index
和ALL一样，只是这里扫描的是索引树，而不是物理表

• 当查询结果集只需要扫描索引树就可满足要求，则Extra列出现Using Index。其实就是索引覆盖，这种事比ALL快很多的，毕竟索引很快
• 没有使用到索引覆盖，而是按照索引顺序来扫描全表，Using index不会出现在Extra列
  ALL
  全表扫描

possible_keys

表示mysql可以用来查找该表中记录的索引，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

key

表示真正被使用于查询的索引key
ForInnoDB, a secondary index might cover the selected columns even if the query also selects the primary key becauseInnoDBstores the primary key value with each secondary index. IfkeyisNULL, MySQL found no index to use for executing the query more efficiently.

To force MySQL to use or ignore an index listed in thepossible_keyscolumn, useFORCE INDEX,USE INDEX, orIGNORE INDEXin your query. SeeSection 8.9.4, “Index Hints”.

ForMyISAMtables, runningANALYZE TABLEhelps the optimizer choose better indexes. ForMyISAMtables,myisamchk --analyzedoes the same. SeeSection 13.7.3.1, “ANALYZE TABLE Syntax”, andSection 7.6, “MyISAM Table Maintenance and Crash Recovery”.

key_len

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

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

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

filtered

表示预计通过表条件过滤掉的表行百分比，rowsfiltered表示将要被用于接下来的连接的行数，比如，rows=1000,filtered=50%，那么用户连接另一个表的行数为1000filtered=500

Extra

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

• Using index:该值表示相应的select操作中使用了覆盖(Covering Index）
• Using where:表示mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where字句的查询都会显示"Using where"。有时"Using where"的出现就是一个暗示：查询可受益与不同的索引。
• Using temporary:表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。这个值表示使用了内部临时(基于内存的)表。一个查询可能用到多个临时表。有很多原因都会导致MySQL在执行查询期间创建临时表。两个常见的原因是在来自不同表的上使用了DISTINCT,或者使用了不同的ORDER BY和GROUP BY列。可以强制指定一个临时表使用基于磁盘的MyISAM存储引擎。这样做的原因主要有两个
  1)内部临时表占用的空间超过min(tmp_table_size，max_heap_table_size)系统变量的限制
  2)使用了TEXT/BLOB 列
• Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
• Using join buffer：强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
• Impossible where：这个值强调了where语句会导致没有符合条件的行
• Select tables optimized a way：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行.
• Index merges：当MySQL 决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型。Using sort_union(…)，Using union(…)，Using intersect(…)

总结：

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