mysql explain 输出详解

explain的输出

mysql> explain select dept_no,count(*) from dept_emp where dept_no='d001';+----+-------------+----------+------+---------------+---------+---------+-------+-------+--------------------------+| id | select_type | table    | type | possible_keys | key     | key_len | ref   | rows  | Extra                    |+----+-------------+----------+------+---------------+---------+---------+-------+-------+--------------------------+|  1 | SIMPLE      | dept_emp | ref  | dept_no       | dept_no | 4       | const | 41592 | Using where; Using index |+----+-------------+----------+------+---------------+---------+---------+-------+-------+--------------------------+1 row in set (0.04 sec)mysql> explain extended select dept_no,count(*) from dept_emp where dept_no='d001';+----+-------------+----------+------+---------------+---------+---------+-------+-------+----------+--------------------------+| id | select_type | table    | type | possible_keys | key     | key_len | ref   | rows  | filtered | Extra                    |+----+-------------+----------+------+---------------+---------+---------+-------+-------+----------+--------------------------+|  1 | SIMPLE      | dept_emp | ref  | dept_no       | dept_no | 4       | const | 41592 |   100.00 | Using where; Using index |+----+-------------+----------+------+---------------+---------+---------+-------+-------+----------+--------------------------+1 row in set, 1 warning (0.57 sec)

---

由此可以看出：
当使用explain时，输出中包含的列为：
id，select_type，table，type，possible_keys，key，key_len，ref，rows，Extra

当使用explain extended时，输出中包含的列为：
比使用explain增加了filtered列

---

各个属性的含义：

id

select查询的序列号，当引用其他查询结果做union时，该值为null，且table列的值为union（m,n），意思是把id为m和n的查询结果做union。

select_type

select查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询。

• simple：简单查询，查询中不包含子查询或者union。
• primary：查询中若包含复杂的子部分，则最外层查询为primary。
• union：若第二个select出现在union之后，则被标记为union，如果有多个union，则除第一个select之外，后续的都是union。
• dependent union：在union中的第二个及以后的查询语句，依赖于外层查询。（关联查询？）
• union result：union的结果，id列为null，table列显示了是由哪几个查询的结果做的union。
• subquery：子查询中的第一个查询。（第二个及以后的呢？）
• dependent query：子查询中的第一个查询，依赖于外部查询。（关联子查询？）
• derived：衍生查询（from子句中的子查询）？？？
• materialized：物化子查询？什么意思？
• uncacheable subquery：查询结果不能缓存的子查询，对于外部查询中的每一行都必须重新计算。

• uncacheable union：该查询是union查询中的第二个及以后的查询语句，且整个union查询语句是一个uncacheable subquery。

  关于dependent subquery和uncacheable subquery，官方文档是这样解释的：

DEPENDENT SUBQUERY evaluation differs from UNCACHEABLE SUBQUERY evaluation. For DEPENDENT SUBQUERY, the subquery is re-evaluated only once for each set of different values of the variables from its outer context. For UNCACHEABLE SUBQUERY, the subquery is re-evaluated for each row of the outer context.

翻译如下：
dependent subquery评估和uncacheable subquery评估不同。dependent subquery对于外部查询中的不同的值只计算一次。而uncacheable subquery对于外部查询中的每一行都重新评估一次。
（由此可见两者的执行效率完全不同，极端情况下能差好几个数量级）

table

查询结果出自哪张表。可以是具体的表名，也可以是以下的值：

• union M，N：id为M和N的查询结果做union
• derivedN：参考id为N的查询的衍生查询
• subqueryN：参考id为N的物化子查询

type

表连接类型，访问方式。

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。

以下按照从好到坏的顺序介绍访问类型：

• system：表中只有一行数据（=system table），是const类型的特殊情况。
• const：表中最多只有一行符合条件的行，改行在查询一开始就会被读取。因为之后一行，所以优化器可以把这一值看做常数。const连接类型速度非常快，因为值读取一次。经常用在在主键或者唯一索引上做等值查询。在如下的查询中，tb1_name可以当做const表。

SELECT * FROM tbl_name WHERE primary_key=1;SELECT * FROM tbl_name  WHERE primary_key_part1=1 AND primary_key_part2=2;

• eq_ref：可以理解为利用主键或者唯一非空索引做等值唯一连接。从表里取出一行来，与从之前的表里取出的行做连接。不同于system和const，这个是最常用的连接方式。这是用在索引的全部都用来做连接，并且该索引为主键或唯一非空索引时。eq_ref用在索引列做等值连接。与之相比较的值可以是常数，或者与之前表的列有关的表达式。在如下的查询中，mysql使用eq_ref连接来处理ref_table：

SELECT * FROM ref_table,other_table  WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table  WHERE ref_table.key_column_part1=other_table.column  AND ref_table.key_column_part2=1;

• ref：可以理解为非唯一性索引扫描。从索引中将对应值的行取出来，跟之前表中取出的数据做连接。如果只使用了索引的左前缀，或者非主键非唯一索引做链接时，用ref。（换句话说，对于给定的值，返回的行数不止一行。）如果给定的值只返回很少的行，这是个很好的连接方式。
  ref可以用于在索引列上做=或>=,<=操作时。在下面的例子中，mysql使用ref连接方式处理ref_table：

SELECT * FROM ref_table WHERE key_column=expr;SELECT * FROM ref_table,other_table  WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table  WHERE ref_table.key_column_part1=other_table.column  AND ref_table.key_column_part2=1;

• fulltext：全文索引。
• ref_or_null：连接方式跟ref相似，但是增加了一个额外的搜索条件，包含null值。这种连接方式优化大多用于解决子查询问题。在下面的例子中，mysql使用ref_or_null连接方式处理ref_table：

SELECT * FROM ref_table  WHERE key_column=expr OR key_column IS NULL;

• index merge：这种连接方式表明使用了index merge优化。在这种情况下，explain输出的列中，key列包含用到的索引的列表，key_len列包含用到的列的最大长度的列表（什么意思？）。see Section 8.2.1.4, “Index Merge Optimization”.
• unique_subquery：这种方式用于在使用in关键字进行子查询时，代替ref连接方式。
  unique_subquery是一个索引查询方法，用来代替子查询以提供更好的效率。
  用于类似如下查询方式：

...... value IN (SELECT primary_key FROM single_table WHERE some_expr)

• index_subquery：这种连接方式类似于unique_subquery，代替了IN方式的子查询，但是用于非唯一索引的子查询。用于类似如下查询方式：

......value IN (SELECT key_column FROM single_table WHERE some_expr)

• range：索引范围查询。利用索引查询，返回给定范围内的行。在explain的输出中，key列显示使用了哪个索引，key_len列显示使用到的列的最长部分（什么意思？），在这种情况下，ref列为null。
  range用于key列与常数作比较，操作符可以是 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, 或者 IN()，例如：

SELECT * FROM tbl_name  WHERE key_column = 10;SELECT * FROM tbl_name  WHERE key_column BETWEEN 10 and 20;SELECT * FROM tbl_name  WHERE key_column IN (10,20,30);SELECT * FROM tbl_name  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

• index：index: 连接类型跟 all 一样，不同的是它只扫描索引树。
  有两种情况：
  （1）如果索引是覆盖索引，所有需要的数据都可以从索引中获得，此时只扫描索引。在这种情况下，explain命令的输出中，Extra列显示 Using index。它通常会比 all快点，因为索引文件通常比数据文件小。
  （2）通过查询索引，以索引的排列顺序做全表扫描。在这种情况下，Extra列不会显示using index。
• all：全表扫描。

possible_keys

指 mysql在搜索表记录时可能使用哪个索引。
possible_keys里面所包含的索引可能在实际的使用中没用到。如果这个字段的值是null，就表示没有索引被用到。
这种情况下，就可以检查 where子句中哪些字段那些字段适合增加索引以提高查询的性能。

key

key字段显示了mysql实际上要用的索引。
当没有任何索引被用到的时候，这个字段的值就是null。
想要让mysql强行使用或者忽略在 possible_keys字段中的索引列表，可以在查询语句中使用关键字force index, use index,或 ignore index。
如果是 myisam 和 bdb 类型表，可以使用 analyzetable 来帮助分析使用使用哪个索引更好。
如果是 myisam类型表，运行命令 myisamchk –analyze也是一样的效果。
详细的可以查看章节”14.5.2.1 analyze tablesyntax”和”5.7.2 table maintenance and crash recovery”。

key_len

显示MySQL决定使用的键长度。表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。如果键是NULL，长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。可以告诉你在联合索引中mysql会真正使用了哪些索引。
注：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

ref

ref 字段显示了哪些字段或者常量被用来和 key配合从表中查询记录出来。

rows

rows 字段显示了mysql认为在查询中应该检索的记录数。 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，在innodb上可能是不准确的。

Extra

extra列包含了mysql处理sql的一些附加信息。
如果你想让查询速度尽可能的快，那么就要注意Extra列中值为using filesort和using temporary。

• Child of ‘table’ pushed join@1
  仅适用于mysql cluster。在NDB内核中，该表被当做子表，查询条件下推（push down）到各个节点处理，避免节点间不符合条件的数据的交互，减少网络和系统负载。（我这么认为的）
• const row not found
  在类似于SELECT … FROM tbl_name 这样的查询中，被查询的表是空表。
• Deleting all rows
  某些引擎（例如myisam）在删除全部数据的时候，支持handle method，简单快速的删除所有数据。如果引擎使用了这种方式优化，则extra列显示此信息。
• distinct
  mysql当找到当前记录的匹配联合结果的第一条记录之后，就不再搜索其他记录了。
• FirstMatch(tbl_name)
• Full scan on NULL key
• Impossible HAVING
• Impossible WHERE
• Impossible WHERE noticed after reading const tables
• LooseScan(m..n)
• Materialize, Scan
• No matching min/max row
• no matching row in const table
• No matching rows after partition pruning
• No tables used
• Not exists
  mysql在查询时做一个 left join优化时，当它在当前表中找到了和前一条记录符合 left join条件后，就不再搜索更多的记录了。下面是一个这种类型的查询例子：
  select * from t1 left join t2 on t1.id=t2.id where t2.id isnull;
  假使 t2.id 定义为 not null。这种情况下，mysql将会扫描表 t1并且用 t1.id 的值在 t2 中查找记录。当在 t2中找到一条匹配的记录时，这就意味着 t2.id 肯定不会都是null，就不会再在 t2 中查找相同 id值的其他记录了。也可以这么说，对于 t1 中的每个记录，mysql只需要在t2 中做一次查找，而不管在 t2 中实际有多少匹配的记录。
• Range checked for each record (index map: N)
  mysql没找到合适的可用的索引。取代的办法是，对于前一个表的每一个行连接，它会做一个检验以决定该使用哪个索引（如果有的话），并且使用这个索引来从表里取得记录。这个过程不会很快，但总比没有任何索引时做表连接来得快。
• Scanned N databases
• Select tables optimized away
• Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table
• Start materialize, End materialize, Scan
• Start temporary, End temporary

• unique row not found
  类似于SELECT … FROM tbl_name这样的查询，该表上没有满足条件的唯一索引或者主键的行。（不好翻译，具体看官档吧）

• Using filesort
  mysql需要额外的做一遍从而以排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录，并且将所有符合 where条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了，对应的记录也会按照排好的顺序取出来。See Section 8.2.1.15, “ORDER BY Optimization”.

• Using index
  字段的信息直接从索引树中的信息取得，而不再去扫描实际的记录。这种策略用于查询时的字段是一个独立索引的一部分。
  如果extra列中同时还有using where，意思是这个索引时用来查找键值，然后回表。
  如果没有using where，优化器可能是为了避免回表。
  例如，如果索引时覆盖索引，优化器扫描索引，而不是用索引来回表。
  对于有聚集索引的innodb表，即使extra列中没有using index，也可以使用索引。这适用于type列是index，key列是primary的情况。

• Using index condition
  ICP优化相关，将数据过滤条件从server层面下推到存储引擎层，在存储引擎层根据索引元祖过滤数据，避免不符合条件的数据传给server。（非翻译，整理自网络。）
• Using index for group-by
  与using index类似，using index for group-by表明mysql找到一个索引，该索引可以返回所有group-by或distinct查询所需的列，不需要回表。
• Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)
  做连接时，当前表之前的表被分多次读入join buffer中，然后join buffer中的表再跟当前表做join。(Block Nested Loop) 表明使用BNL算法。 (Batched Key Access)表明使用BKA算法。explain中，上一行的keys会被读入buffer，然后当前表的相应的行会被分批获取。（具体什么意思，再查！）
• Using MRR
  MRR优化方式，具体再查。
• Using sort_union(…), Using union(…), Using intersect(…)
• Using temporary
  mysql需要创建临时表存储结果以完成查询。这种情况通常发生在查询时包含了groupby 和 order by 子句，它以不同的方式列出了各个字段。
• Using where
  过滤符合条件的行。
  当extra列中没有using where并且表连接类型为all或者index时，你可能执行了错误的查询，除非你要获取或检查所有的行。
• Using where with pushed condition
  仅适用于NDB。从各个节点过滤数据。后面的省略翻译。

举例

mysql> explain extended    -> select count(emp_no) from dept_emp where dept_no =     -> (select dept_no from departments where dept_name='Sales');+----+-------------+-------------+-------+---------------+-----------+---------+-------+-------+----------+--------------------------+| id | select_type | table       | type  | possible_keys | key       | key_len | ref   | rows  | filtered | Extra                    |+----+-------------+-------------+-------+---------------+-----------+---------+-------+-------+----------+--------------------------+|  1 | PRIMARY     | dept_emp    | ref   | dept_no       | dept_no   | 4       | const | 91566 |   100.00 | Using where; Using index ||  2 | SUBQUERY    | departments | const | dept_name     | dept_name | 42      | const |     1 |   100.00 | Using index              |+----+-------------+-------------+-------+---------------+-----------+---------+-------+-------+----------+--------------------------+2 rows in set, 1 warning (0.00 sec)

mysql> explain extended    -> select count(first_name) from employees    -> union    -> select count(distinct(first_name)) from employees    -> union    -> select count(last_name) from employees;+----+--------------+--------------+------+---------------+------+---------+------+--------+----------+-----------------+| id | select_type  | table        | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra           |+----+--------------+--------------+------+---------------+------+---------+------+--------+----------+-----------------+|  1 | PRIMARY      | employees    | ALL  | NULL          | NULL | NULL    | NULL | 299290 |   100.00 | NULL            ||  2 | UNION        | employees    | ALL  | NULL          | NULL | NULL    | NULL | 299290 |   100.00 | NULL            ||  3 | UNION        | employees    | ALL  | NULL          | NULL | NULL    | NULL | 299290 |   100.00 | NULL            || NULL | UNION RESULT | <union1,2,3> | ALL  | NULL          | NULL | NULL    | NULL |   NULL |     NULL | Using temporary |+----+--------------+--------------+------+---------------+------+---------+------+--------+----------+-----------------+4 rows in set, 1 warning (0.00 sec)