（转）mysql explain详解

原文：http://www.cnblogs.com/xuanzhi201111/p/4175635.html

http://yutonger.com/18.html

http://www.jianshu.com/p/73f2c8448722

在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。（QEP：sql生成一个执行计划query Execution plan）

mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面对这些字段出现的可能进行解释：

一、 id

我的理解是SQL执行的顺序的标识,SQL从大到小的执行

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

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

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

二、select_type

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

(1) SIMPLE(简单SELECT,不使用UNION或子查询等)

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

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

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

(5) UNION RESULT(UNION的结果)

(6) SUBQUERY(子查询中的第一个SELECT)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)

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

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

三、table

显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

mysql> explain select * from (select * from ( select * from t1 where id=2602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t1         | const  | PRIMARY,idx_t1_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

四、type

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

常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

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

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

五、possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

六、Key

key列显示MySQL实际决定使用的键（索引）

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

七、key_len

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

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

八、ref

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

九、rows

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

十、Extra

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

Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

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

参考资料：http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html

http://www.cnblogs.com/gomysql/p/3720123.html

MySQL EXPLAIN详解

相关文章：

MySQL高性能表设计规范:http://www.jianshu.com/p/f797bbe11d76
MySQL EXPLAIN详解:http://www.jianshu.com/p/ea3fc71fdc45
MySQL 锁机制 常用知识点:http://www.jianshu.com/p/0d5b7cd592f9

MySQL EXPLAIN命令是查询性能优化不可缺少的一部分，该文主要讲解explain命令的使用及相关参数说明。

EXPLAIN Output Columns

列名	说明
id	执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type	显示本行是简单或复杂select。如果查询有任何复杂的子查询，则最外层标记为PRIMARY（DERIVED、UNION、UNION RESUlT）
table	访问引用哪个表（引用某个查询，如“derived3”）
type	数据访问/读取操作类型（ALL、index、range、ref、eq_ref、const/system、NULL）
possible_keys	揭示哪一些索引可能有利于高效的查找
key	显示mysql决定采用哪个索引来优化查询
key_len	显示mysql在索引里使用的字节数
ref	显示了之前的表在key列记录的索引中查找值所用的列或常量
rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值相乘，可粗略估算整个查询会检查的行数
Extra	额外信息，如using index、filesort等

id

id是用来顺序标识整个查询中SELELCT 语句的，在嵌套查询中id越大的语句越先执行。该值可能为NULL，如果这一行用来说明的是其他行的联合结果。

select_type

表示查询的类型

类型	说明
simple	简单子查询，不包含子查询和union
primary	包含union或者子查询，最外层的部分标记为primary
subquery	一般子查询中的子查询被标记为subquery，也就是位于select列表中的查询
derived	派生表——该临时表是从子查询派生出来的，位于form中的子查询
union	位于union中第二个及其以后的子查询被标记为union，第一个就被标记为primary如果是union位于from中则标记为derived
union result	用来从匿名临时表里检索结果的select被标记为union result
dependent union	顾名思义，首先需要满足UNION的条件，及UNION中第二个以及后面的SELECT语句，同时该语句依赖外部的查询
subquery	子查询中第一个SELECT语句
dependent subquery	和DEPENDENT UNION相对UNION一样

table

对应行正在访问哪一个表，表名或者别名

关联优化器会为查询选择关联顺序，左侧深度优先
当from中有子查询的时候，表名是derivedN的形式，N指向子查询，也就是explain结果中的下一列
当有union result的时候，表名是union 1,2等的形式，1,2表示参与union的query id

注意：MySQL对待这些表和普通表一样，但是这些“临时表”是没有任何索引的。

type

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

类型	说明
All	最坏的情况,全表扫描
index	和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多
range	范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range
ref	一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
eq_ref	最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生（高效）
const	当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效）
system	这是const连接类型的一种特例，表仅有一行满足条件。
Null	意味说mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）

possible_keys

显示查询使用了哪些索引，表示该索引可以进行高效地查找，但是列出来的索引对于后续优化过程可能是没有用的

key

key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好。

ref

ref列显示使用哪个列或常数与key一起从表中选择行。

rows

rows列显示MySQL认为它执行查询时必须检查的行数。注意这是一个预估值。

Extra

Extra是EXPLAIN输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。

类型	说明
Using filesort	MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesort 说明MySQL使用了后者，但注意虽然叫filesort但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。使用文件完成排序操作，这是可能是ordery by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。
Using temporary	用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。
Not exists	MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
Using index	说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。如果同时出现using where，表明索引被用来执行索引键值的查找，没有using where，表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的，但无需再回表查询记录。
Using index condition	这是MySQL 5.6出来的新特性，叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。
Using where	使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。
Using join buffer	使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接
impossible where	where子句的值总是false，不能用来获取任何元组
select tables optimized away	在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct	优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

因为一直对MySQL中explain的返回结果一知半解，所以今天花时间研究下，写成博客分享给大家；内容主要参考MySQL的官方手册，还有网上的一些文章。

因为Mysq不同版本对查询优化不同，所以不同版本explain的结果可能不一样，本文基于MySQL 5.7 版本，参考MySQL 5.7 官方手册，手册是英文的，本人英语水平不好。。。翻译有出入的地方大家多多提意见。

EXPLAIN 语法

{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}
explain_type: {
EXTENDED
| PARTITIONS
| FORMAT = format_name
}
format_name: {
TRADITIONAL
| JSON
}
explainable_stmt: {
SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}

本次实验使用到的表结构

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.10 Homebrew
mysql> show tables;
+----------------------+
| Tables_in_index_test |
+----------------------+
| index_one |
| index_relation |
| index_test |
+----------------------+
3 rows in set (0.00 sec)

index_one表
1. mysql> show create table index_one;
2. +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------+
3. | Table | Create Table |
4. +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------+
5. | index_one | CREATE TABLE `index_one` (
6. `id` int(11) NOT NULL AUTO_INCREMENT,
7. PRIMARY KEY (`id`)
8. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
9. +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------+
10. 1 row in set (0.00 sec)
index_relation 关系表
1. mysql> show create table index_relation;
2. +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3. | Table | Create Table |
4. +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5. | index_relation | CREATE TABLE `index_relation` (
6. `id` int(11) NOT NULL AUTO_INCREMENT,
7. `aid` int(11) DEFAULT NULL,
8. PRIMARY KEY (`id`),
9. KEY `index_aid` (`aid`)
10. ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 |
11. +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12. 1 row in set (0.00 sec)
index_test 表
1. mysql> show create table index_test;
2. +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3. | Table | Create Table |
4. +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5. | index_test | CREATE TABLE `index_test` (
6. `id` int(11) NOT NULL AUTO_INCREMENT,
7. `a` varchar(64) DEFAULT NULL,
8. `b` varchar(64) DEFAULT NULL,
9. `c` varchar(65) DEFAULT NULL,
10. `e` varchar(64) DEFAULT NULL,
11. PRIMARY KEY (`id`),
12. KEY `Index_a_b_c` (`a`,`b`,`c`),
13. KEY `index_b` (`b`)
14. ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
15. +------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
16. 1 row in set (0.00 sec)

EXPLAIN 介绍

EXPLAIN Output

mysql> explain select * from index_test ,index_relation where index_test.id=index_relation.aid;
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | index_test | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 100.00 | NULL |
| 1 | SIMPLE | index_relation | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

EXPLAIN Output Columns

Column	含义	Meaning
id	query中select的序列号	The SELECT identifier
select_type	select 类型	The SELECT type
table	输出的行所引用的表	The table for the output row
partitions		The matching partitions
type	联接类型	The join type
possible_keys	可以使用的索引	The possible indexes to choose
key	决定使用到的索引	The index actually chosen
key_len	使用到的的索引长度	The length of the chosen key
ref		The columns compared to the index
rows	扫描的行数	Estimate of rows to be examined
filtered		Percentage of rows filtered by table condition
Extra	查询的额外信息	Additional information

id

query中select查询的序列号，如果为union查询的话，最后一行Union结果的id为null。另外还被用于 table 字段中表示合并行的开始和结束行的id，例如table = <unionM,N>，表示合并id从M到N行的结果。

mysql> explain select * from index_test where a='asf' union select * from index_test where a='dfe';
+----+--------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------+
| 1 | PRIMARY | index_test | NULL | ref | Index_a_b_c | Index_a_b_c | 195 | const | 1 | 100.00 | NULL |
| 2 | UNION | index_test | NULL | ref | Index_a_b_c | Index_a_b_c | 195 | const | 1 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.01 sec)

select_type

可以理解为select查询类型，对于索引优化没有重要的含义，简单介绍一下，有以下结果：

Value	含义	Meaning
SIMPLE	简单的查询（没有使用Union 或子查询）	Simple SELECT (not using UNION or subqueries)
PRIMARY	最外层的查询	Outermost SELECT
UNION	Union查询中的地二个，或第三、第四...	Second or later SELECT statement in a UNION
DEPENDENT UNION	Union的结果被外层查询依赖	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	合并结果	Result of a UNION.
SUBQUERY	子查询	First SELECT in subquery
DEPENDENT SUBQUERY	子查询的结果被外层查询依赖	First SELECT in subquery, dependent on outer query
DERIVED		Derived table SELECT (subquery in FROM clause)
MATERIALIZED		Materialized subquery
UNCACHEABLE SUBQUERY		A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION		The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

SIMPLE 简单的查询，没有使用Union或子查询，例如：
1. mysql> explain select * from index_test where id=1;
2. +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
5. | 1 | SIMPLE | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
6. +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
PRIMARY Union查询中的最外层的查询，例如：
1. mysql> explain select * from index_test where id=1 union select * from index_test where id=2;
2. +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
5. | 1 | PRIMARY | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
6. | 2 | UNION | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
7. | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
8. +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
UNION Union查询中的第二个或之后的查询，例如：
1. mysql> explain select * from index_test where id=1 union select * from index_test where id=2 union select * from index_test where id=2;
2. +----+--------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+--------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
5. | 1 | PRIMARY | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
6. | 2 | UNION | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
7. | 3 | UNION | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
8. | NULL | UNION RESULT | <union1,2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
9. +----+--------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
DEPENDENT UNION 类似于UNION，只是它的结果被外层查询依赖，例如：
1. mysql> explain select * from index_test where id in (select aid from index_relation where id=1 union select aid from index_relation where id=2);
2. +----+--------------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+--------------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
5. | 1 | PRIMARY | index_test | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |
6. | 2 | DEPENDENT SUBQUERY | index_relation | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
7. | 3 | DEPENDENT UNION | index_relation | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
8. | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
9. +----+--------------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
UNION RESULT 合并多行的结果，例如 UNION 中的例子。
SUBQUERY 子查询，例如：
1. mysql> explain select * from index_test where id = (select aid from index_relation where id=(select 1));
2. +----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
5. | 1 | PRIMARY | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
6. | 2 | SUBQUERY | index_relation | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
7. +----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
DEPENDENT SUBQUERY 不同于SUBQUERY的是，查询结果被外层依赖。例如DEPENDENT UNION中的例子。
DERIVED 子查询的结果作为外层查询的来源，例如：
1. mysql> explain select * from (select 1) as a;
2. +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
5. | 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
6. | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
7. +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+

table

查询使用到的表名，有几种特殊情况：

<unionM,N> The row refers to the union of the rows with id values of M and N.
<derivedN> The row refers to the derived table result for the row with an id value of N. A derived table may result, for example, from a subquery in the FROM clause.
<subqueryN> The row refers to the result of a materialized subquery for the row with an id value of N

partitions

结果记录中跨了几个分区，如果数据表用到了分区，可以使用EXPLAIN PARTITIONS SELECT * FROM table查看结果集的分区情况。

type

链接类型，从最好到最差的连接类型为system、const、eq_ref、ref、range、indexhe和ALL

system 这是const联接类型的一个特例,很少出现，例如 DERIVED 中的例子。
const 表最多有一个匹配行，用于主键或者唯一索引字段的查询，速度非常快，例如：
1. mysql> explain select * from index_test where id = 1;
2. +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
5. | 1 | SIMPLE | index_test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
6. +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
eq_ref 我理解是两个表联接，联接的字段在两个表都是唯一的，即主键或唯一索引，例如：
1. mysql> explain select index_test.a from index_test,index_one where index_one.id=index_test.id;
2. +----+-------------+------------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+-------------+------------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+
5. | 1 | SIMPLE | index_one | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using index |
6. | 1 | SIMPLE | index_test | NULL | eq_ref | PRIMARY | PRIMARY | 4 | index_test.index_one.id | 1 | 100.00 | NULL |
7. +----+-------------+------------+------------+--------+---------------+---------+---------+-------------------------+------+----------+-------------+
ref 和eq_ref不同的是匹配多行，使用最多的类型，例如：
1. mysql> explain select * from index_test,index_relation where index_relation.aid=index_test.id;
2. +----+-------------+----------------+------------+------+---------------+-----------+---------+--------------------------+------+----------+-------------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+-------------+----------------+------------+------+---------------+-----------+---------+--------------------------+------+----------+-------------+
5. | 1 | SIMPLE | index_test | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 100.00 | NULL |
6. | 1 | SIMPLE | index_relation | NULL | ref | index_aid | index_aid | 5 | index_test.index_test.id | 5 | 100.00 | Using index |
7. +----+-------------+----------------+------------+------+---------------+-----------+---------+--------------------------+------+----------+-------------+
fulltext 全文索引
ref_or_null 和ref不同的是，sql中包含 OR key_column IS NULL。
1. mysql> explain select index_test.a from index_test where a='abc' or a is null;
2. +----+-------------+------------+------------+-------------+---------------+-------------+---------+-------+------+----------+--------------------------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+-------------+------------+------------+-------------+---------------+-------------+---------+-------+------+----------+--------------------------+
5. | 1 | SIMPLE | index_test | NULL | ref_or_null | Index_a_b_c | Index_a_b_c | 195 | const | 2 | 100.00 | Using where; Using index |
6. +----+-------------+------------+------------+-------------+---------------+-------------+---------+-------+------+----------+--------------------------+
index_merge 表示使用了索引合并优化方法，key列包含了使用的索引的清单，例如：
1. mysql> explain select * from index_test where a='abc' or id=1;
2. +----+-------------+------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+-------------+------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
5. | 1 | SIMPLE | index_test | NULL | index_merge | PRIMARY,Index_a_b_c | Index_a_b_c,PRIMARY | 195,4 | NULL | 2 | 100.00 | Using sort_union(Index_a_b_c,PRIMARY); Using where |
6. +----+-------------+------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
unique_subquery
index_subquery
range 只检索给定范围的行，key列显示使用了哪个索引，当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range
1. mysql> explain select * from index_test where id > 5;
2. +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
5. | 1 | SIMPLE | index_test | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using where |
6. +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
index 遇ALL不同的是，它只扫描了索引树。因为索引文件通常比数据文件小。所以比ALL快。例如：
1. mysql> explain select a from index_test where c = 'abc';
2. +----+-------------+------------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+-------------+------------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
5. | 1 | SIMPLE | index_test | NULL | index | NULL | Index_a_b_c | 588 | NULL | 6 | 16.67 | Using where; Using index |
6. +----+-------------+------------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
ALL 全表扫描
1. mysql> explain select e from index_test where c = 'abc';
2. +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
4. +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
5. | 1 | SIMPLE | index_test | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
6. +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

possible_keys

列出能够使用哪些索引来查询，最终会选择一个最优的。如果该列为NULL，则表示没有相关的索引。

key

列出决定使用的索引，如果为NULL表示没有使用索引，可以使用FORCE INDEX、USE INDEX或者IGNORE INDEX 强制MySQL使用或忽视possible_keys列出的索引，在查。

key_len

列出使用索引的长度，通过使用的索引长度可以猜测出联合索引中哪几个字段使用到了索引，例如：

mysql> explain select * from index_test where a='abc' and b='abc' and c>'abc';
+----+-------------+------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | index_test | NULL | range | Index_a_b_c,index_b | Index_a_b_c | 588 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from index_test where a='abc' and b>'abc' and c='abc';
+----+-------------+------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | index_test | NULL | range | Index_a_b_c,index_b | Index_a_b_c | 390 | NULL | 1 | 16.67 | Using index condition |
+----+-------------+------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+

ref

rows

MySQL认为必须检查的用来返回请求数据的行数，约小越快；InnoDB 存储引擎这个值是一个大约值。

filtered

在MySQL 5.7.3之前，如果使用EXPLAIN EXTENDED 将显示此列。从MySQL 5.7.3开始，默认情况下启用扩展输出，并且不需要EXTENDED关键字。

Extra

列出了MySQL查询的详细信息，有很多种信息，如下

const row not found
Deleting all rows
Distinct
FirstMatch(tbl_name)
Full scan on NULL key
Impossible HAVING
Impossible WHERE
Impossible WHERE noticed after reading const tables
Using filesort
Using index
Using index condition
Using index for group-by
Using temporary
Using where
Zero limit

posted @ 2017-10-07 21:36 liujiacai 阅读(439) 评论(0) 编辑收藏举报

刷新页面返回顶部

liujiacai

（转）mysql explain详解

MySQL EXPLAIN详解

EXPLAIN Output Columns

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

EXPLAIN 语法

本次实验使用到的表结构

EXPLAIN 介绍

EXPLAIN Output

EXPLAIN Output Columns

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

公告