mysql explain join types

explain 命令可以很清晰的展现sql 语句的执行计划，对于优化SQL语句很有帮助。在我看来执行计划中最有用的两列莫过于type和rows。rows很好理解就是这个查询遍历的行数，当然是越少越好了。type列就是指join type，也就是本文要尝试阐述的。

mysql的使用文档中有对join type的解释：

http://dev.mysql.com/doc/refman/5.7/en/explain-output.html

mysql 的join type从好到坏依次是：

system, const, eq_ref, ref, fulltext, ref_or_null, index_merge,unique_subquery, index_subquery, range, index, ALL.

一共12种，最差的就是ALL，也就是全表遍历了，最好的是system/const,因为system只是const的特殊形式，所以可以说const是最好的。

mysql文档对于const的解释是，该表在查询种最多只有一行返回。因为只有一行所以被优化器当做常量。当把主键（primary key）或者唯一索引（unique index）与常量做比较时会使用这个join type。

这个很好理解，因为是主键或者唯一索引，所以只有一行，又是很常量比较，所以就相当于不需要遍历。就像去数组里面拿下标是3的元素，不需要遍历。

alter table user add primary key(id);

alter table user add unique(telphone);

select * from user where id=1;

select * from user where telphone='13688889999';

这两个查询都是const。

除了system和const之外最好的类型就是eq_ref. 这个类型还是很主键（primary key）或者唯一索引（unique index）有关。如果拿A表的列与B表的主键或者唯一索引去join，这个时候用到的就是eq_ref这个类型。mysql文档的解释是，对于前表的所有行的每个组合，当前表只有一行被读出。

create table user_score (

user_id INT NOT NULL,

score double NOT NULL

)

select * from user_score us inner join user u on us.user_id=u.id;

这个语句使用了eq_ref, 因为id是user表的主键，当user_score去join user的id列时，使用的是eq_ref.

比eq_ref差一点的是ref，唯一的区别就是，这个时候join的index不是唯一索引。拿A表的列和B表的index去join的时候，对于A表的每一行，B表匹配的不只有一行，这个时候用到的join type就是ref。

假如前面的例子中join的不是user的id，而是另外一个非unqiue的index列，那么用到的就是ref。

fulltext指使用全文索引时的优化。

ref_or_null，在创建了index的列上使用is NULL的一种优化。

select * from user where name is null; 这个时候的join type时ref

select * from user where name='nokiaisacat' or name is null; 这个时候是ref_or_null

index_merge, 这也是一种优化，是对多个range的结果的merge，可能是对交际的，并集的merge，在explain的extra列中有说明。

select * from user where name ='小明' or telphone='13633339999';

这个语句使用了index_merge, extra是Using union(idx_u_name,telphone); Using where

unique_subquery和index_subquery都是对IN 来说的，他们分别是eq_ref和ref在IN （subquery）中的替代。

对创建了index的列使用一个范围的查询时用到range 类型。

当显式的使用using index时，用到index类型。