mysql explain 详解

准备一条复杂一点的语句

字段描述

id

mysql查询序列号

可以认定为mysql语句的执行顺序

1:如果是同一组查询,id序号则一致,顺序从上到下

2:如果是子查询,则序号自增,执行顺序从小到大

select_type

查询的类型

mysql的查询方式,有以下几种类型:

SIMPLE                简单的查询,不适用union和子查询等
PRIMARY               子查询中最外层的查询                    
UNION                 union中第二个或后面的select语句                    
DEPENDENT UNION       union中后面的select语句,取决于外部的查询                   
UNION RESULT          联合查询结果              
SUBQUERY              子查询的第一个select(不依赖外部查询)             
DEPENDENT SUBQUERY    依赖于外部查询的子查询                
DERIVED               派生表的查询     
DEPENDENT DERIVED     依赖于另一个表的派生表查询          
MATERIALIZED          物化查询                
UNCACHEABLE SUBQUERY  不能缓存结果的子查询,必须重新被外层查询的每一行进行评估         
UNCACHEABLE UNION     不能缓存的联合查询

table

查询的表名,也可以是别名

partitions

分区

如果mysql表使用了分区,则会显示出具体需要查询的分区

type

访问类型

mysql在查询中找到数据的查询方式,从上往下,性能从最好到最差

system                该表只有一行数据(系统表),属于const的特例
const                 该表最多有一个匹配行，在查询开始时读取。因为只有一行，所以优化器的其余部分可以将此行中列的值视为常量。常量表非常快，因为它们只读取一次。在使用主键查询,或者额外的主键索引只匹配一条数据时会出现(select * from table where primary_key=1)
eq_ref                此连接类型表示使用了索引合并优化。 在这种情况下，输出行中的key列包含使用的索引列表，key_len包含所用索引的最长键部分列表.在多表合并查询时,通过table1与table2数据合并情况会出现(select * from table1,table2 where table1.column=table2.column)
ref                   连接不能基于关键字选择单个行，可能查找到多个符合条件的行。叫做 ref 是因为索引要跟某个参考值相比较。这个参考值或者是一个常数，或者是来自一个表里的多表查询的结果值在查询时候使用到索引并且有多个结果时会出现(select * from table where index=xx)
fulltext              使用全文索引时出现
ref_or_null           类似于ref,但是会额外搜索包含null的值(由于mysql null不会存储进索引,而是通过增加null的标识来实现查询)在字段允许null,并且查询需要包含null时会出现(select * from table where index=xx or index is null)
index_merge           此连接类型表示使用了索引合并优化。 在这种情况下，输出行中的key列包含使用的索引列表，key_len包含所用索引的最长键部分列表。由于mysql查询只能走一个索引查询,但是为了优化查询效果,在使用2个索引的情况,会分别查询出2个索引的数据,然后合并 (select * form table where index1=xx or index2=xx)
unique_subquery       此类型替换以下形式的某些IN子查询的eq_ref.类似于两表连接中被驱动表的 eq_ref 访问方法， unique_subquery 是针对在一些包含 IN 子查询的查询语句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 unique_subquery (value IN (SELECT primary_key FROM single_table WHERE some_expr))
index_subquery        此联接类型类似于unique_subquery。它替换子查询中的索引，但它适用于以下形式的子查询中的非唯一索引：(value IN (SELECT key_column FROM single_table WHERE some_expr))
range                 仅检索给定范围内的行，使用索引选择行。输出行中的键列指示使用哪个索引。在范围查询时会用到(select * from table where id>100)
index                 index和ALL都是全表查询,一个是扫描索引树,一个是扫描需要筛选的所有表字段每一行
ALL                   全表查询

possible_keys

可能可以找到记录的索引(可能多个)

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

key

mysql决定使用的索引,一定会在possible_keys中选择,也可以自己强制指定其他的

key_len

表示索引中使用的字节数，

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

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

ref

当使用索引列等值查询时，与索引列进行等值匹配的对象信息,可能是const(常量匹配),字段名匹配,方法匹配等

rows

估算结果行数

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

当估算行数超出总数的30%(默认情况)时,mysql将拒绝使用索引,转为全表查询 (select * from table where id<>0)

**filtered **

之前在分析连接查询的成本时提出过一个 condition filtering 的概念，就是 MySQL 在计算驱动表扇出时采用的 一个策略：

如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要估计出满足搜索条件的记录到底 有多少条的比值。

如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条的比值。

例如:

1:首先说明t2是全表搜索,并且执行计划预估有65条数据

2:其次说明t1表 ref依赖于t2表的purchase_id,预估每一条t2都能匹配一条数据,但是t1的filtered值是10%,说明需要查询匹配65*10%=6.5次才能得出t1的结果

Extra

用于显示解释器额外信息

该列包含MySQL解决查询的详细信息,有以下几种情况：

No tables used                           当查询语句的没有 FROM 子句时将会提示该额外信息.(select 1;)
Impossible WHERE                         查询语句的 WHERE 子句永远为 FALSE 时将会提示该额外信息( SELECT * FROM s1 WHERE 1 != 1)
No matching min/max row                  当查询列表处有 MIN 或者 MAX 聚集函数，但是并没有符合 WHERE 子句中的搜索条件的记录时，将会提示该额外信息( EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';)
Using index                              当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在Extra 列将会提示该额外信息。( SELECT key1 FROM s1 WHERE key1 = 'a';)
Using index condition                    有些搜索条件中虽然出现了索引列，但却不能使用到索引( SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';)
Using where                              当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件时，在Extra 列中会提示上述额外信息。( SELECT * FROM s1 WHERE common_field = 'a';)
Using join buffer (Block Nested Loop)    在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度， MySQL 一般会为其分配一块名叫
join buffer 的内存块来加快查询速度，也就是我们所讲的 基于块的嵌套循环算法( SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;)
Not exists                               当我们使用左（外）连接时，如果 WHERE 子句中包含要求被驱动表的某个列等于 NULL 值的搜索条件，而且那个列又是不允许存储 NULL 值的，那么在该表的执行计划的 Extra 列就会提示 Not exists 额外信息( EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NUL
L;)
Using intersect(...) 、 Using union(...) 和 Using sort_union(...)  如果执行计划的 Extra 列出现了 Using intersect(...) 提示，说明准备使用 Intersect 索引合并的方式执行查询，括号中的 ... 表示需要进行索引合并的索引名称；如果出现了 Using union(...) 提示，说明准备使用 Union 索引合并的方式执行查询；出现了 Using sort_union(...) 提示，说明准备使用 Sort-Union 索引合并的方式执行查询。
Zero limit                               当我们的 LIMIT 子句的参数为 0 时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息
Using filesort                           有一些情况下对结果集中的记录进行文件排序是可以使用到索引的
Using temporary                          在许多查询的执行过程中， MySQL 可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在 执行许多包含 DISTINCT 、 GROUP BY 、 UNION 等子句的查询过，如果不能有效利用索引来完成查询， MySQL 很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划 的 Extra 列将会显示 Using temporary 提示
Start temporary, End temporary           查询优化器会优先尝试将 IN 子查询转换成 semi-join ，而 semi-join 又 有好多种执行策略，当执行策略为 DuplicateWeedout 时，也就是通过建立临时表来实现为外层查中的记录进行去重操作时，驱动表查询执行计划的 Extra 列将显示 Start temporary 提示