mysql中explain的type的解释

SQL写完了，只写完一半，因为还有SQL性能测试，SQL优化，时间太长了怎么办？

MySQL的中有解释工具，根据解释返回的结果我们便可以知道我们的SQL写的怎么样，是否会造成查询瓶颈，同时根据结果不断的修改调整查询语句，从而完成SQL优化的过程。

 

只需要关注三种，分别是type，key，rows。其中key表明的是这次查找中所用到的索引，如果是null则表示查询中没有用到索引，rows是指这次查找数据所扫描的行数（这里可以先这样理解，但实际上是内循环的次数）

type - 连接类型

type意味着类型，这里的类型官方全称是“join type”，意思是“连接类型”，是一种数据库引擎查找表的一种方式，在“高性能mysql”一书中作者更是觉得称它它为访问类型更贴切一些。

常见的六种类型，它们分别是所有，索引，范围，REF eq_ref，常量。从左到右，它们的效率依次是增强的。撇开SQL的具体应用环境以及其他因素，你应当尽量优化你的SQL语句，使它的类型尽量靠右，但实际运用中还是要综合考虑各个方面的。

| 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 |

所有

全表扫描，如果是在一个查找数据项的SQL中出现了所有类型，那通常意味着你的SQL语句处于一种最原生的状态，有很大的优化空间。

以员工表为例，下面一种情形便是所有类型的查找：

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

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

指数

这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和所有的相比，他们都是取得了全表的数据，而且指数要先读索引而且要回表随机取数据，因此索引不可能会比所有快（取同一个表数据），但为什么官方的手册将它的效率说的比所有好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。这样一来，结果不同，也就没法比效率的问题了。
如果一定要比效率，只需要获取这个表的数据并且排序便可以看出来谁比谁效率高了：

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  |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+

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

如果连接类型为type，而且额外列中的值为'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刚好为索引列，因此无需回表取数据

范围

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

REF

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

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

接下来，在员工表中根据名称查找数据的时候，MySQL的优化器便选择了裁判的连接类型。

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

ref_eq与ref相比牛的地方是，它知道这种类型的查找结果集只有一个？使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。
在单个表中，曾尝试了很多方法想出现ref_eq的连接类型，然而很多时候出现的都是常量，因此不得不随手连接了一张表得到了想要的连接类型，该表的建表代买为（连接了两个没有关系的表，O（╯□╰）O）

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表中有五条数据，得分表中有对应的五条数据，其中员工的rec_id和得分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  |
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+

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

常量

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