MySQL explain讲解

使用方法

explain <查询SQL>

例如：

explain select * from user_table;

结果包含字段：

• id
• select_type
• table
• partitions
• type
• possible_keys
• key
• key_len ref
• rows
• filtered
• Extra

explain作用：用于分析SQL的执行计划。

id列

• id相同，执行顺序由上到下。
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
• id相同和不同的会同时存在。

select_type列

• simple: 检查的select查询，查询中不包含子查询或者union
• primary: 查询中若包含任何复杂的子部分，最外层被标记为primary，一般最后执行的。
• subquery: 在select或where列表中包含了子查询
• derived：在from列表中包含的子查询被标记为derived,MySQL会递归执行这些子查询，把结果存入临时表。
  注意：被标记为derived的表会带一个数字，表示对应的id列。
• 若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived.
• union result: 从union表获取结果的select

table列

显示这一行的数据是关于哪张表的

partitions列

匹配的分区，对于非分区表，值为NULL。

type列

访问类型，显示查询使用了何种类型，最好到最差依次为：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system: 表只有一行记录（等于系统表），这是const类型的特例，基本不会出现，可以忽略。

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

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

ref: 非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配单独值的行，会找到多个符合条件的值，所以比eq_ref性能差。

range: 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在where中出现了between、<、>、in等范围的查询语句。比全表扫描要好，因为只扫描了范围内的数据。

index: 在索引上扫描所有数据，因为索引的数据相比于全表数据要少很多，因此比全表扫描要快。

all：全表数据扫描（上百万数据要避免），性能最差。

possible_keys、key、key_len列

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

key：实际使用的索引，如果为null则没有使用索引。若查询中出现覆盖索引（查询字段与索引字段的个数和顺序刚好匹配），则该索引仅出现在key列表中。

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

ref列

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

常见的值：

• null：没有使用索引；
• const: 常量值用到索引；
• 库名.表名.字段名：该字段使用索引；

rows列

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

filtered列

返回结果的行占需要读到的行(rows列的值)的百分比，这个值的结果在where非索引列时结果往往不准确，参考意义不大。可以参考：为什么Mysql explain extended中的filtered列值总是100%

extra列

十分重要的额外信息

• using filesort: 使用了外部的索引排序，说明查询不是按照表内索引的顺序进行读取的。无法利用索引的排序的操作称为文件排序。如果可以尽快优化这个。

• using temporary: 使用了临时表保存中间结果，mysql对查询的结果排序时使用临时表。常见于order by和分组查询group by。
  注意：groupby使用的字段顺序最好与索引字段顺序保持一致，可以避免这个临时表问题。

• using index: 查询使用到了覆盖索引，值得恭喜。如果同时出现using where，表明索引被用来执行索引键值的查找，在索引上有查询行为；如果没用同时出现using where，表明索引用来读取数据而非执行查询，也就是直接在索引上直接可以获取数据，不需要再取数据了。

• using where: 使用了where条件；

• using join buffer: 使用了连接缓存；

• impossible where: where子句的值总是false，不能用来获取任何数据。例如where name = ‘a’ and name = ‘b’;

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

• distinct: （很少用） 优化distinct操作，在找到第一个匹配的数据后即停止找同样值的动作。