我们经常会使用Explain去查看执行计划，这个众所周知。今天我们就来说一说Explain中的“Type”和“Extra”。

Explain中的 Type

MySQL的官网解释为：连接类型(the join type)。它描述了找到所需数据使用的扫描方式。

Explain中的“Type”

最为常见的扫描方式有：

• system：系统表，少量数据，往往不需要进行磁盘IO；
• const：常量连接；
• eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描；
• ref：非主键非唯一索引等值扫描；
• range：范围扫描；
• index：索引树扫描；
• ALL：全表扫描(full table scan)；

上面各类扫描方式由快到慢：

system > const > eq_ref > ref > range > index > ALL

system

扫码类型为system，说明数据已经加载到内存里，不需要进行磁盘IO。

这类扫描是速度最快的。表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计

const

表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const。

explain select id from account_user_base where id =1;

const扫描的条件为：

1. 命中主键(primary key)或者唯一(unique)索引；
2. 被连接的部分是一个常量(const)值；

eq_ref

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

eq_ref扫描的条件为：对于前表的每一行(row)，后表只有一行被扫描。

注意：ALL全表扫描的表记录最少的表如t1表

ref

非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体

explain select * from account_user_base t1,account_user_security t2 where t1.id = t2.user_id;

对于前表的每一行(row)，后表可能有多于一行的数据被扫描。

range

range类型，它是索引上的范围查询，它会在索引上扫码特定范围内的值。

只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。

一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引。

explain select * from account_user_base where id > 4;

index

index类型，需要扫描索引上的全部数据。

explain select id from account_user_base;

Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）

all

Full Table Scan，全表扫描。遍历全表以找到匹配的行。

explain select * from account_user_base;

index merge

我们的 where 中可能有多个条件(或者join)涉及到多个字段，它们之间进行 AND 或者 OR，那么此时就有可能会使用到 index merge 技术。index merge 技术如果简单的说，其实就是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。

MySQL5.0之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。

index merge 算法根据合并算法的不同分成了三种：intersect, union, sort_union.

index merge 之 intersect

简单而言，index intersect merge就是多个索引条件扫描得到的结果进行交集运算。显然在多个索引提交之间是 AND 运算时，才会出现 index intersect merge。

下面两种where条件或者它们的组合时会进行 index intersect merge:

1. 条件使用到复合索引中的所有字段或者左前缀字段(对单字段索引也适用)

key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

1. 主键上的任何范围条件

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;
SELECT * FROM tbl_name WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

上面只说到复合索引，但是其实单字段索引显然也是一样的。比如 select * from tab where key1=xx and key2 =xxx;也是有可能进行index intersect merge的。另外上面两种情况的 AND 组合也一样可能会进行 index intersect merge.

index merge 之 union

简单而言，index uion merge就是多个索引条件扫描，对得到的结果进行并集运算，显然是多个条件之间进行的是 OR 运算。

下面几种类型的 where 条件，以及他们的组合可能会使用到 index union merge算法：

1. 条件使用到复合索引中的所有字段或者左前缀字段(对单字段索引也适用)
2. 主键上的任何范围条件
3. 任何符合 index intersect merge 的where条件；

例子：

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;
SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR (key3='foo' AND key4='bar') AND key5=5;

第一个例子，就是三个 单字段索引 进行 OR 运算，所以他们可能会使用 index union merge算法。

第二个例子，复杂一点。(key1=1 AND key2=2) 是符合 index intersect merge;(key3='foo' AND key4='bar') AND key5=5也是符合index intersect merge，所以 二者之间进行 OR 运算，自然可能会使用 index union merge算法。

index merge 之 sort_union

This access algorithm is employed when the WHERE clause was converted to several range conditions combined by [OR](http://dev.mysql.com/doc/refman/5.6/en/logical-operators.html#operator_or), but for which the Index Merge method union algorithm is not applicable.

多个条件扫描进行 OR 运算，但是不符合 index union merge算法的，此时可能会使用 sort_union算法

对 index merge 的进一步优化

index merge使得我们可以使用到多个索引同时进行扫描，然后将结果进行合并。听起来好像是很好的功能，但是如果出现了 **index intersect merge**，那么一般同时也意味着我们的索引建立得不太合理，因为 **index intersect merge** 是可以通过建立 复合索引进行更一步优化的。

比如下面的select:

SELECT * FROM t1 WHERE key1=1 AND key2=2 AND key3=3;

显然我们是可以在这三个字段上建立一个复合索引来进行优化的，这样就只需要扫描一个索引一次，而不是对三个所以分别扫描一次。

总结

• system最快：不进行磁盘IO
• const：PK或者unique上的等值查询
• eq_ref：PK或者unique上的join查询，等值匹配，对于前表的每一行(row)，后表只有一行命中
• ref：非唯一索引，等值匹配，可能有多行命中
• range：索引上的范围扫描，例如：between/in/>
• index：索引上的全集扫描
• ALL最慢：全表扫描(full table scan)

Explain中的 Extra

不适合在其他字段中显示，但是十分重要的额外信息

Explain中的“Extra”

从上图我们得知，Extra的值有 NULL、Using index、Using where、Using index condition、Using filesort、Using temporary

Using filesort

Extra为Using filesort说明，得到所需结果集，需要对所有记录进行文件排序。

典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。

explain select id from account_user_base order by nick_name;

mysql对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序”。

由于索引是先按email排序、再按address排序，所以查询时如果直接按address排序，索引就不能满足要求了，mysql内部必须再实现一次“文件排序”

Using temporary

Extra为Using temporary说明，需要建立临时表(temporary table)来暂存中间结果。

这类SQL语句性能较低，往往也需要进行优化。典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

explain select nick_name, COUNT(*) from account_user_base GROUP BY nick_name order by nick_name;

使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于order by 和 group by

Using index

覆盖索引（Covering Index)：也叫索引覆盖。就是select列表中的字段，只用从索引中就能获取，不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。

Extra为Using index 表示相应的select操作中使用了覆盖索引（Covering Index），SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录，效率高。

explain select id from account_user_base;

如果同时出现Using where，表明索引被用来执行索引键值的查找（参考上图）。如果没用同时出现Using where，表明索引用来读取数据而非执行查找动作。

注意：

1. 如需使用覆盖索引，select列表中的字段只取出需要的列，不要使用select *
2. 如果将所有字段都建索引会导致索引文件过大，反而降低crud性能

Using index condition

Extra为Using index condition说明，确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。

explain select * from account_user_security t1, account_user_base t2 where t1.user_id = t2.id;

Using where

explain select * from account_user_base where id > 4;

Extra为Using where说明，SQL使用了where条件过滤数据。

Using join buffer

使用了链接缓存

Impossible WHERE

where子句的值总是false，不能用来获取任何元组。

select tables optimized away

在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即可完成优化

distinct

优化distinct操作，在找到第一个匹配的元祖后即停止找同样值得动作