mysql中explain的type的解释

导语：

当一个sql查询语句被写出来之后，其实你的工作只完成了一小半，接下来更重要的工作是评估你自己写的sql的质量与效率。mysql为我们提供了很有用的辅助武器explain，它向我们展示了mysql接收到一条sql语句的执行计划。根据explain返回的结果我们便可以知道我们的sql写的怎么样，是否会造成查询瓶颈，同时根据结果不断的修改调整查询语句，从而完成sql优化的过程。

虽然 explain返回的结果项很多，这里我们只关注三种，分别是type，key，rows。其中key表明的是这次查找中所用到的索引，rows是指这次查找数据所扫描的行数（这里可以先这样理解，但实际上是内循环的次数）。而type则是本文要详细记录的连接类型，前两项重要而且简单，无需多说。

type — 连接类型

type意味着类型，这里的type官方全称是“join type”，意思是“连接类型”,这样很容易给人一种错觉觉得必须需要俩个表以上才有连接类型。事实上这里的连接类型并非字面那样的狭隘，它更确切的说是一种数据库引擎查找表的一种方式，在《高性能mysql》一书中作者更是觉得称呼它为访问类型更贴切一些。

mysql5.7中type的类型达到了14种之多，这里只记录和理解最重要且经常遇见的六种类型，它们分别是all,index,range,ref,eq_ref，const。从左到右，它们的效率依次是增强的。撇开sql的具体应用环境以及其他因素，你应当尽量优化你的sql语句，使它的type尽量靠右，但实际运用中还是要综合考虑各个方面的。

接下来，为了演示和重现这几种连接类型，我新建了一个数据测试表，以方面更好的理解这五种类型。

| employee | CREATE TABLE `employee` ( `rec_id` int(11) NOT NULL AUTO_INCREMENT, `no` varchar(10) NOT NULL, `name` varchar(20) NOT NULL, `position` varchar(20) NOT NULL, `age` varchar(2) NOT NULL, PRIMARY KEY (`rec_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |

all

mysql> explain select * from employee where `no` = ''; +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | employee | ALL | NULL | NULL | NULL | NULL | 5 | Using where | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+

这是因为no列既不是主键也不是索引，因此只能采用全表扫描来查找目标no。

index

mysql> explain select * from employee order by `no` ; +----+-------------+----------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | employee | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort | +----+-------------+----------+------+---------------+------+---------+------+------+----------------+ mysql> explain select * from employee order by rec_id ; +----+-------------+----------+-------+---------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+---------+---------+------+------+-------+ | 1 | SIMPLE | employee | index | NULL | PRIMARY | 4 | NULL | 5 | NULL | +----+-------------+----------+-------+---------------+---------+---------+------+------+-------+

上面可以看出，根据no列排序的连接类型是all型的，但是注意extra列是用到了排序（Using filesort），而根据rec_id列排序的连接类型是index，而且得到的结果自然是有序的，不许额外的排序。可能正是因为这个缘故，index的效率比all高，但注意这需要相同的条件才成立（既需要排序）。

如果连接类型为type，而且extra列中的值为‘Using index’，那么称这种情况为 索引覆盖；
索引覆盖意味着什么呢？想象这样一种场景，如果说一本新华字典是一张表，当然前面的索引部分（假设按照部首的索引）是这张表的索引，那么索引覆盖就相当于根据部首索引获取第一个字到最后一个字（新华字典的所有字）。我们获得了字典中所有的字，然而我们并没有查一次表，因为我们想要的都早索引中，即索引覆盖。

mysql> explain select rec_id from employee ; +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | employee | index | NULL | PRIMARY | 4 | NULL | 5 | Using index | +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+

上例获取的rec_id刚好为索引列，因此无需回表取数据。

range

range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及’>’,’<'外，in和or也是索引范围扫描。

ref

出现该连接类型的条件是： 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。下面为了演示这种情形，给employee表中的name列添加一个普通的key（值允许重复）

 alter table employee add key I_EMPLOYEE_NAME(`name`); 

接下来，在employee表中根据name查找数据的时候，mysql优化器便选择了ref的连接类型。

mysql> explain select * from employee where `name` = '张三'; +----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | employee | ref | I_EMPLOYEE_NAM | I_EMPLOYEE_NAM | 62 | const | 1 | Using index condition | +----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+ 

ref_eq

CREATE TABLE `score` ( `rec_id` INT(11) NOT NULL AUTO_INCREMENT, `stu_id` INT(11) NOT NULL, `mark` INT(11) NOT NULL DEFAULT '0', PRIMARY KEY (`rec_id`), UNIQUE KEY `UK_SCORE_STU_ID` (`stu_id`) ) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

employee表中有五条数据，score表中有对应的五条数据，其中employee的rec_id 和score的stu_id 是一一对应的。

mysql> explain select ep.name,sc.mark from employee ep,score sc where ep.rec_id = sc.stu_id; +----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+ | 1 | SIMPLE | sc | ALL | UK_SCORE_STU_ID | NULL | NULL | NULL | 5 | NULL | | 1 | SIMPLE | ep | eq_ref | PRIMARY | PRIMARY | 4 | my_db.sc.stu_id | 1 | NULL | +----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+

上面就可以看到score表是全表扫描的类型，rows=5代表外层表循环了五次（因为有五条数据），但是employee表的rows怎么是1，怎么可能？刚开始也是很疑惑，这与mysql的查询原理息息相关，rows实际反映的是查询的内循环数，针对外层的每一条数据匹配，employee的确一枪就可以命中，因此rows为1。

const

通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。

总结

explain 就像一面镜子，有事没事写完sql记得explain一下。同时，在写文章也发现，有很多东西和细节，想要明白清楚，也是没有那么简单的，需要对操作系统以及数据库的底层查询和运行原理要有一个清楚的理解。同时type的几种类型几乎都是基于索引之上的，因此需要对索引有个深入的了解，而且explain的结果可以指导我们什么时候加索引，什么时候不加索引，从而让我们更好的使用索引。