MySQL EXPLAIN详解

MySQL EXPLAIN详解:http://www.jianshu.com/p/ea3fc71fdc45

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操作，在找到第一匹配的元组后即停止找同样值的动作

explain的作用

我们使用explain命令来查看mysql语句的执行计划（execution plan）,解释mysql是如何执行一条sql语句的；解释的内容主要包括表的连接方式和顺序，以及索引的使用情况。使用explain，可以分析出需要在哪里加上索引，以及调整表的连接，以达到优化查询的目的；explain命令之后不仅可以跟select语句，也可以跟delete,insert,update,replace语句。

用法

只需要在sql语句前加上explain就可以了，比如：

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100310 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+

如何分析

我们看到explain命令的结果有很多列，通常情况下，我们最关心的指标是select_type和type。

概念

Column	JSON Name	Meaning
`id`	`select_id`	The `SELECT` identifier（选择标识符）
`select_type`	None	The `SELECT` type（查询类型）
`table`	`table_name`	The table for the output row（结果集使用的表）
`partitions`	`partitions`	The matching partitions（匹配的分区）
`type`	`access_type`	The join type（连接类型）
`possible_keys`	`possible_keys`	The possible indexes to choose（可能使用到的索引）
`key`	`key`	The index actually chosen（实际使用的索引）
`key_len`	`key_length`	The length of the chosen key （使用索引的长度）
`ref`	`ref`	The columns compared to the index（和索引比较的列）
`rows`	`rows`	Estimate of rows to be examined（扫描的行数）
`filtered`	`filtered`	Percentage of rows filtered by table condition（表条件过滤行数的百分比）
`Extra`	None	Additional information（附加的信息）

进一步解释

id

id的值越大，执行的优先级越高，id相同，则从上往下顺序执行。所以以下例子：

id	placeholder
1	A
1	B
2	C

执行的顺序是：C，A，B。

select_type

`select_type` Value	JSON Name	Meaning（含义）
`SIMPLE`	None	Simple `SELECT` (not using `UNION` or subqueries)（查询查询，不使用union或子查询）
`PRIMARY`	None	Outermost `SELECT`（最外层的select查询）
`UNION`	None	Second or later `SELECT` statement in a `UNION` （在union中排在第二位甚至更靠后的select语句）
`DEPENDENT UNION`	`dependent` (`true`)	Second or later `SELECT` statement in a `UNION`, dependent on outer query （在union中排在第二位甚至更靠后的select语句，取决于外面的查询）
`UNION RESULT`	`union_result`	Result of a `UNION`.（union结果集）
`SUBQUERY`	None	First `SELECT` in subquery（子查询中的第一个select）
`DEPENDENT SUBQUERY`	`dependent` (`true`)	First `SELECT` in subquery, dependent on outer query（子查询中的第一个select，取决于外面的查询）
`DERIVED`	None	Derived table（派生表）
`DEPENDENT DERIVED`	`dependent` (`true`)	Derived table dependent on another table （派生表，依赖其他表）
`MATERIALIZED`	`materialized_from_subquery`	Materialized subquery （实现子查询）
`UNCACHEABLE SUBQUERY`	`cacheable` (`false`)	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query（结果不能被缓存并且外部查询的每一行都必须被重新评估的子查询）
`UNCACHEABLE UNION`	`cacheable` (`false`)	The second or later select in a `UNION` that belongs to an uncacheable subquery (see `UNCACHEABLE SUBQUERY`)（在union中排位第二甚至更靠后属于不能缓存的子查询）

table

显示这行的数据是关于哪张表的，也可能是表的别名。

type

可能的取值

null > system > const > eq_ref > ref > range > index > all,从左到右，性能递减，null最好，all最差，一般的，最好能优化查询到const到range之间。

含义

ALL：Full Table Scan，遍历全表。

index: Full Index Scan，index与ALL区别为index类型只遍历索引树，不遍历数据行，所以比all的速度要快。

range: 只检索给定范围的行，这个范围必须应用在一个有索引的列上。

ref: 使用了非唯一索引作为where或join条件，是一个确定的值。

eq_ref: 同ref，但索引是唯一索引。

const、system: 将确定的值应用在索引（unique and not unique）上，type将会是const,当结果只有一行时，type是system。

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

例子

每一个type都给出了一个例子:

drop table t1, t2, t3;

create table t1 (
  id      int(11) auto_increment,
  name    varchar(50),
  address varchar(100),
  primary key (id),
  key index_name(name)
);
create table t2 (
  id      int(11) auto_increment,
  name    varchar(50),
  address varchar(100),
  primary key (id),
  key index_name(name)
);
create table t3 (
  id      int(11) auto_increment,
  name    varchar(50),
  address varchar(100),
  primary key (id),
  key index_name(name)
);

insert into t1 (name, address)
values ('tom', 'downtown'),
       ('jack', 'country yard');
insert into t2 (name, address)
values ('tom', 'downtown'),
       ('jack', 'country yard');
insert into t3 (name, address)
values ('tom', 'downtown'),
       ('jack', 'country yard');

explain select *
        from t1
        where id = 1; -- const
explain select *
        from t1
        limit 10; -- all
explain select *
        from t1
        where name = 'tom'; -- ref
explain select t1.id
        from t1,
             t2
        where t1.id = t2.id; -- index, eq_ref
explain select t1.*
        from t1,
             t2
        where t1.id = t2.id; -- index, all
explain select *
        from t1
        where id between 1 and 10; -- range
explain select t1.name
        from t1,
             t2
        where t1.name = t2.name; -- index, ref
explain select *
        from t2,
             (select id from t1 where t1.id = 1) t
        where t2.id = t.id;
explain select *
        from (select * from t1 where t1.address = 'downtown') t;
explain select *
        from t1
        where id = 1
        union
        select *
        from t2
        where id = 2; -- const,const,all
explain select *
        from (select * from t1 limit 1) a1; -- system, all

Extra

Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤。

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

Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”。

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

Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。

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

No tables used：Query语句中使用from dual 或不含任何from子句。

应用

在很多博客上，都有关于between,>,<,in,not in,like是否会使用索引，如果使用，那么type是什么的文章，其实这个问题的本质是对explain命令的使用，我们只需要写几个包含以上where条件的语句，就可以找到问题的答案了。我们新建一个叫user的表，并且插入10万条随机的英文姓名。测试的结果如下。

create table user (
  id   int(11) auto_increment,
  name varchar(100),
  age  int(11),
  primary key (id),
  key index_age(age),
  key index_name(name)
);

explain select * from user where name in ('tom'); -- use
explain select * from user where name = 'tom'; -- use
explain select * from user where name between 'tom' and 'jerry'; -- not use
explain select * from user where name <> 'tom'; -- not use
explain select * from user where name = 'tom' or name = 'jerry'; -- use
explain select * from user where name like 'om%'; -- use
explain select * from user where name like 'tom'; -- use, 这里的like相当于等号
explain select * from user where name like '%to'; -- not use

explain select * from user where age between 0 and 1; -- use
explain select * from user where age not between 0 and 1; -- not use
explain select * from user where age > 50; -- not use
explain select * from user where age < 50; -- not use
explain select * from user where age != 3; -- not use
explain select * from user where age in (1, 99); -- use
explain select * from user where age = 1 or age = 3; -- use
explain select * from user where age like '1%'; -- not use
explain select * from user where age like '%1'; -- not use

从以上的测试结果，我们可以得出结论：
between and, >,<,in,not in,or,like都是会使用索引的，但是between and, >,<必须用在数值类型的列上；in，not in,or可以用到数值和字符串的列上；而like只能用到字符串类型的列上，而且必须是左边不能以通配符开头。