explain 详解

表 xinhua_ciyu 数据条数 264434

字段 ciyu 无索引的情况

字段 ciyu 有索引(index)的情况

连表查询

• a 表数据：238
• b 表数据：88

字段简述

id

SELECT识别符。这是SELECT的查询序列号

• id相同时，执行顺序由上至下

• 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

• id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type

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

• SIMPLE(简单SELECT，不使用UNION或子查询等)

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

• UNION(UNION中的第二个或后面的SELECT语句)

• DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

• UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

• SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

• DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

• DERIVED(派生表的SELECT, FROM子句的子查询)

• UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

table

显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称

partitions

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

type，这个字段很重要

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有（性能从好到坏）：system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL

• system 表中只有一行数据或者是空表，这是 const 类型的一个特例。且只能用于myisam 和 memory 表。如果是 Innodb 引擎表，type 列在这个情况通常都是 all 或者 index

• const 最多只有一行记录匹配。当联合主键或唯一索引的所有字段跟常量值比较时，join类型为const。其他数据库也叫做唯一索引扫描

• eq_ref 多表 join 时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了 system 和 const 之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作 join 联接时会使用此类型。 eq_ref 可用于使用 = 操作符作比较的索引列。比较的值可以是常量，也可以是使用在此表之前读取的表的列的表达式。

注：相对于下面的 ref 区别就是它使用的唯一索引，即主键或唯一索引，而 ref 使用的是非唯一索引或者普通索引。eq_ref 只能找到一行，而 ref 能找到多行。

• ref 对于来自前面表的每一行，在此表的索引中可以匹配到多行。若联接只用到索引的最左前缀或索引不是主键或唯一索引时，使用 ref 类型（也就是说，此联接能够匹配多行记录）ref 可用于使用 = 或 <=> 操作符作比较的索引列。

• fulltext 使用全文索引的时候是这个类型。要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql 不管代价，优先选择 使用全文索引

• ref_or_null 跟 ref 类型类似，只是增加了 null 值的比较。实际用的不多。

• index_merge 表示查询使用了两个以上的索引，最后取交集或者并集，常见 and ，or 的条件使用了不同的索引，官方排序这个在 ref_or_null 之后，但是实际上由于要读取多个索引，性能可能大部分时间都 不如range

• unique_subquery 用于 where 中的 in 形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。该类型替换了下面形式的 IN 子查询的 ref：value IN (SELECT primary_key FROM single_table WHERE some_expr)

• index_subquery 该联接类型类似于 unique_subquery。适用于非唯一索引，可以返回重复值。

• range 索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 或者 like 等运算符的查询中。

• index: 索引全表扫描，把索引从头到尾扫一遍。这里包含两种情况：一种是查询使用了覆盖索引，那么它只需要扫描索引就可以获得数据，这个效率要比全表扫描要快，因为索引通常比数据表小，而且还能避免二次查询。在extra 中显示 Using index，反之，如果在索引上进行全表扫描，没有Using index 的提示。

# 此表只有一个 name 列索引。
# 因为查询的列 name 上建有索引，所以如果这样 type 走的是 index
mysql> explain select name from testa;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | testa | index | NULL          | idx_name | 33      | NULL |    2 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set

# 因为查询的列 cusno 没有建索引，或者查询的列包含没有索引的列，这样查询就会走 ALL 扫描，如下：
mysql> explain select cusno from testa;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | testa | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

# 包含有未见索引的列
mysql> explain select * from testa;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | testa | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

• all 全表扫描，性能最差。

possible_keys

查询可能使用到的索引都会在这里列出来

key

查询真正使用到的索引。

select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

key_len

查询用到的索引长度（字节数）。

如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，用多少算多少。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。

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

key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

ref

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows，这个最直观

MySQL 估计需要扫描的行数（估计值），越少越好，这个非常直观的显示SQL 的好坏

filtered

这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。这个字段不重要

Extra

EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:

• distinct：在select部分使用了distinc关键字

• Using filesort：当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.

• Using index："覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错

• Using temporary：查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.