1、const

表示这个执行步骤最多只返回一行数据。const通常出现在对主键或唯一索引的等值查询中，例如对t表主键id的等值查询：
_png_jpeg
可以认为通过主键或者唯一二级索引列与常数的等值比较来定位一条记录是像坐火箭一样快的，所以他们把这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为：const，意思是常数级别的，代价是可以忽略不计的。不过这种const访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效。

如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个const访问方法才有效（这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录）。比如主键由(a,b,c)组成，那么只有“where a= and b= and c=**”，执行计划的type列才会出现const。

另外，对于唯一二级索引来说，查询该列为NULL值的情况比较特殊，比如这样：

SELECT * FROM single_table WHERE key2 IS NULL;

因为唯一二级索引列并不限制 NULL 值的数量，所以上述语句可能访问到多条记录，也就是说上边这个语句不可以使用const访问方法来执行。

另外需要说明的是，在阿里java开发规范手册中有看到：

“consts 表示单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据”

在优化阶段就会读取到数据，下面的例子中可以体现：

mysql> explain select num,created_time from t_operater_record where id=493490480924;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.22-log |
+------------+

id是主键，没有493490480924这个值。extra中显示在const table中没有匹配的行，显然这里已经读取到数据了。

2、ref

有时候我们对某个普通的二级索引列与常数进行等值比较，比如这样：

mysql> explain select * from t_operater_record where updated_time='2010-10-23 07:17:27';
+----+-------------+-------------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table             | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_operater_record | NULL       | ref  | i_updated_time | i_updated_time | 5       | const |    3 |   100.00 | NULL  |
+----+-------------+-------------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+

MySQL把这种搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref。

对于普通的二级索引来说，通过索引列进行等值比较后可能匹配到多条连续的记录，而不是像主键或者唯一二级索引那样最多只能匹配1条记录，所以ref访问方法比const差了那么一丢丢，但是在二级索引等值比较时匹配的记录数较少时的效率还是很高的。这种访问表的效率就跟坐高铁差不多。

另外，需要注意下面的两种情况：

a.二级索引列值为NULL的情况：不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含NULL值的数量并不限制，所以我们采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法，而不是const的访问方法。

b.对于多列二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法，比方说下边这几个查询：

SELECT * FROM single_table WHERE key_part1 = 'god like';
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill';

但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为ref了，比方说这样：

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

3、eq_ref

eq_ref类型一般意味着在表关联时，被关联表上的关联列走的是主键或者唯一索引。
例如，表jiang关联lock_test表，关联列分别是两张表的主键列：

上面SQL执行时，jiang表是驱动表，lock_test是被驱动表，被驱动表的关联列是主键id，type类型为eq_ref。

MySQL把在连接查询中对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式称之为：eq_ref。

4、Ref_or_null

例如执行下面语句：

select * from lock_test where num=110 or num is null;

表示走了索引(num列上有索引)，但是也访问了空值。当使用二级索引等值查询且同时查询这个二级索引的null值时，就可能会出现ref_or_null。

5、range：

前面介绍的const、ref、ref_or_null都是在对索引列与某一个常数进行等值比较的时候才可能出现的,但是有时候我们面对的搜索条件会比这些更复杂些，比如下边查询：

mysql> explain select * from t_operater_record where updated_time between '2010-10-23 07:17:27' and '2010-12-23 07:17:27';
mysql> explain select * from t_operater_record where updated_time='2010-10-23 07:17:27' or updated_time='2004-05-24 20:36:50';

以上两个查询都是对索引列的范围查询，两个查询都可以走二级索引+回表的方式访问数据。MySQL把这种利用索引进行范围匹配的访问方法称之为：range。（此处所说的使用索引进行范围匹配中的 索引 可以是聚簇索引，也可以是二级索引）。

6、index

假设表single_table有联合索引(key_part1, key_part2, key_part3)，看下边这个查询：

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

由于key_part2并不是联合索引idx_key_part最左索引列，所以我们无法使用ref或者range访问方法来执行这个语句。但是这个查询符合下边这两个条件：

它的查询列表只有3个列：key_part1, key_part2, key_part3，而索引idx_key_part又包含这三个列。
搜索条件中只有key_part2列。这个列也包含在索引idx_key_part中。

也就是说我们可以直接通过遍历idx_key_part索引的叶子节点的记录来比较key_part2 = 'abc'这个条件是否成立，把匹配成功的二级索引记录的key_part1, key_part2, key_part3列的值直接加到结果集中就行了。由于二级索引记录比聚簇索记录小的多（聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列，而二级索引记录只需要存放索引列和主键），而且这个过程也不用进行回表操作，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多，MySQL就把这种采用遍历二级索引记录的执行方式称之为：index。

比如：t_operater_record表有联合索引(num,status,created_time)，下面的查询中type显示index：

mysql> explain select num,created_time from t_operater_record where status in(33,43);
+----+-------------+-------------------+------------+-------+---------------+---------------------------+---------+------+---------+----------+--------------------------+
| id | select_type | table             | partitions | type  | possible_keys | key                       | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------------------+------------+-------+---------------+---------------------------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | t_operater_record | NULL       | index | NULL          | i_num_status_created_time | 11      | NULL | 1990233 |    20.00 | Using where; Using index |
+----+-------------+-------------------+------------+-------+---------------+---------------------------+---------+------+---------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

7、index_merge

绝大多数时候，MySQL在一般情况下执行一个查询时最多只会用到单个二级索引。但不是还有特殊情况么，在这些特殊情况下也可能在一个查询中使用到多个二级索引，MySQL把这种使用到多个索引来完成一次查询的执行方法称之为：index merge，具体的索引合并算法有下边三种。

7.1 Intersection合并

Intersection翻译过来的意思是交集。这里是说某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集。

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

比如对于上面这个查询，mysql可能会走key1或key3单个索引访问表数据，也可能采用intersection索引合并的方式访问表数据，具体采用哪种方式是由估算的成本决定的。

MySQL在某些特定的情况下才可能会使用到Intersection索引合并：

情况一：
二级索引列是等值匹配的情况。如果二级索引是联合索引，那么在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。
比方说下边这个查询可能用到idx_key1和idx_key_part这两个二级索引进行Intersection索引合并的操作：

SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

实际的例子：

mysql> show index from t_operater_record;
+-------------------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name                  | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_operater_record |          0 | PRIMARY                   |            1 | id           | A         |     1990233 |     NULL | NULL   |      | BTREE      |         |               |
| t_operater_record |          1 | i_updated_time            |            1 | updated_time | A         |     1037127 |     NULL | NULL   |      | BTREE      |         |               |
| t_operater_record |          1 | i_oper                    |            1 | operationer  | A         |      683749 |     NULL | NULL   |      | BTREE      |         |               |
| t_operater_record |          1 | i_num_status_created_time |            1 | num          | A         |       10106 |     NULL | NULL   |      | BTREE      |         |               |
| t_operater_record |          1 | i_num_status_created_time |            2 | status       | A         |      627083 |     NULL | NULL   |      | BTREE      |         |               |
| t_operater_record |          1 | i_num_status_created_time |            3 | created_time | A         |      998627 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.01 sec)

mysql> explain select * from t_operater_record where updated_time='2004-05-19 07:42:37' and num=7.34 and status=11 and created_time='2004-05-24 20:36:50';;
+----+-------------+-------------------+------------+-------------+------------------------------------------+------------------------------------------+---------+------+------+----------+------------------------------------------------------------------------+
| id | select_type | table             | partitions | type        | possible_keys                            | key                                      | key_len | ref  | rows | filtered | Extra                                                                  |
+----+-------------+-------------------+------------+-------------+------------------------------------------+------------------------------------------+---------+------+------+----------+------------------------------------------------------------------------+
|  1 | SIMPLE      | t_operater_record | NULL       | index_merge | i_updated_time,i_num_status_created_time | i_num_status_created_time,i_updated_time | 11,5    | NULL |    1 |   100.00 | Using intersect(i_num_status_created_time,i_updated_time); Using where |
+----+-------------+-------------------+------------+-------------+------------------------------------------+------------------------------------------+---------+------+------+----------+------------------------------------------------------------------------+

而下边这两个查询就不能进行Intersection索引合并：

SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';



mysql> explain select * from t_operater_record where updated_time='2004-05-19 07:42:37' and num=7.34 and status=11;
+----+-------------+-------------------+------------+------+------------------------------------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys                            | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------------+------------+------+------------------------------------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_operater_record | NULL       | ref  | i_updated_time,i_num_status_created_time | i_updated_time | 5       | const |    3 |     1.67 | Using where |
+----+-------------+-------------------+------------+------+------------------------------------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

情况二：
主键列可以是范围匹配。比方说下边这个查询可能用到主键和idx_key1进行Intersection索引合并的操作：

SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';


mysql> explain select * from t_operater_record where updated_time='2004-05-19 07:42:37' and id>300000;
+----+-------------+-------------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+------------------------------------------------------+
| id | select_type | table             | partitions | type        | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                                                |
+----+-------------+-------------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+------------------------------------------------------+
|  1 | SIMPLE      | t_operater_record | NULL       | index_merge | PRIMARY,i_updated_time | i_updated_time,PRIMARY | 13,8    | NULL |    1 |   100.00 | Using intersect(i_updated_time,PRIMARY); Using where |
+----+-------------+-------------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+------------------------------------------------------+
1 row in set, 1 warning (0.02 sec)

为什么会这样呢？突然冒出这么两个规定让人一脸懵逼。其实对于InnoDB的二级索引来说，记录先是按照索引列进行排序，如果是一个联合索引，那么会按照各个列依次排序。而二级索引的记录是由索引列 + 主键构成的，二级索引列的值相同的记录可能会有好多条，这些索引列的值相同的记录又是按照主键的值进行排序的。所以重点来了，之所以在二级索引列都是等值匹配的情况下才可能使用Intersection索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。

而Intersection索引合并会把从多个二级索引中查询出的主键值求交集，如果从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交集的过程就很easy啦。

而对于上面的情况二，二级索引的记录中都带有主键值的，所以可以在从idx_key1中获取到的主键值上直接运用条件id > 100过滤就行了，这样多简单。

当然，上边说的情况一和情况二只是发生Intersection索引合并的必要条件，不是充分条件。

7.2 union合并

SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

Intersection是交集的意思，这适用于使用不同索引的搜索条件之间使用AND连接起来的情况；Union是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。与Intersection索引合并类似，MySQL在某些特定的情况下才可能会使用到Union索引合并：

情况一：
二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。
比方说下边这个查询可能用到idx_key1和idx_key_part这两个二级索引进行Union索引合并的操作：

SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');


mysql> explain select * from t_operater_record where updated_time='2004-05-19 07:42:37' or (num=7.34 and status=11 and created_time='2004-05-24 20:36:50');
+----+-------------+-------------------+------------+-------------+------------------------------------------+------------------------------------------+---------+------+------+----------+--------------------------------------------------------------------+
| id | select_type | table             | partitions | type        | possible_keys                            | key                                      | key_len | ref  | rows | filtered | Extra                                                              |
+----+-------------+-------------------+------------+-------------+------------------------------------------+------------------------------------------+---------+------+------+----------+--------------------------------------------------------------------+
|  1 | SIMPLE      | t_operater_record | NULL       | index_merge | i_updated_time,i_num_status_created_time | i_updated_time,i_num_status_created_time | 5,11    | NULL |    6 |   100.00 | Using union(i_updated_time,i_num_status_created_time); Using where |
+----+-------------+-------------------+------------+-------------+------------------------------------------+------------------------------------------+---------+------+------+----------+--------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

而下边这两个查询就不能进行Union索引合并：

SELECT * FROM single_table WHERE key1 > 'a' OR (key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');
SELECT * FROM single_table WHERE key1 = 'a' OR key_part1 = 'a';

情况二：
主键列可以是范围匹配:

mysql> explain select * from t_operater_record where updated_time='2004-05-19 07:42:37' or id<100000;
+----+-------------+-------------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+--------------------------------------------------+
| id | select_type | table             | partitions | type        | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                                            |
+----+-------------+-------------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+--------------------------------------------------+
|  1 | SIMPLE      | t_operater_record | NULL       | index_merge | PRIMARY,i_updated_time | i_updated_time,PRIMARY | 5,8     | NULL |    4 |   100.00 | Using union(i_updated_time,PRIMARY); Using where |
+----+-------------+-------------------+------------+-------------+------------------------+------------------------+---------+------+------+----------+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

情况三：
使用Intersection索引合并的搜索条件。

这种情况其实也挺好理解，就是搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取交集，比方说这个查询：

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');

优化器可能采用这样的方式来执行这个查询：

先按照搜索条件key1 = 'a' AND key3 = 'b'从索引idx_key1和idx_key3中使用Intersection索引合并的方式得到一个主键集合。
再按照搜索条件key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'从联合索引中得到另一个主键集合。
采用Union索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回给用户。

当然，查询条件符合了这些情况也不一定就会采用Union索引合并，也得看优化器的心情。

7.3 sort-union

Union索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，比方说下边这个查询就无法使用到Union索引合并：

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

这是因为根据key1 < 'a'从idx_key1索引中获取的二级索引记录的主键值不是排好序的，根据key3 > 'z'从idx_key3索引中获取的二级索引记录的主键值也不是排好序的，但是key1 < 'a'和key3 > 'z'这两个条件又特别让我们动心，所以我们可以这样：

先根据key1 < 'a'条件从idx_key1二级索引中获取记录，并按照记录的主键值进行排序
再根据key3 > 'z'条件从idx_key3二级索引中获取记录，并按照记录的主键值进行排序
因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了。

我们把上述这种先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并，很显然，这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

小贴士：为啥有Sort-Union索引合并，就没有Sort-Intersection索引合并么？是的，的确没有Sort-Intersection索引合并这么一说， Sort-Union的适用场景是单独根据搜索条件从某个二级索引中获取的记录数比较少，这样即使对这些二级索引记录按照主键值进行排序的成本也不会太高；而Intersection索引合并的适用场景是单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，合并后可以明显降低回表开销，但是如果加入Sort-Intersection后，就需要为大量的二级索引记录按照主键值进行排序，这个成本可能比回表查询都高了，所以也就没有引入Sort-Intersection这个玩意儿。

索引合并注意事项：
1、使用联合索引代替Intersection索引合并往往是最优做法：

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

这个查询之所以可能使用Intersection索引合并的方式执行，还不是因为idx_key1和idx_key3是两个单独的B+树索引，你要是把这两个列搞一个联合索引，那直接使用这个联合索引就把事情搞定了，何必用啥索引合并呢。

explain的type列解析

1、const

2、ref

3、eq_ref

4、Ref_or_null

5、range：

6、index

7、index_merge

7.1 Intersection合并

7.2 union合并

7.3 sort-union

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像