一文带你了解MySQL之单表访问方法

前言

对于我们这些MySQL的使用者来说，MySQL其实就是一个软件，平时用的最多的就是查询功能。DBA时不时丢过来一些慢查询语句让优化，我们如果连查询是怎么执行的都不清楚还优化什么，所以是时候掌握真正的技术了。

一、数据准备

我们在之前的文章的中，知道MySQL Server有一个称为查询优化器的模块，一条查询语句进行语法解析之后就会被交给查询优化器来进行优化，优化的结果就是生成一个所谓的执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。不过查询优化这个主题有点儿大，在学会跑之前还得先学会用，所以本章先来瞅瞅MySQL怎么执行单表查询（就是from语句后边只有一个表，最简单的那种查询～）。不过需要强调的一点是，在学习本章前务必看过前边关于记录结构、数据页结构以及索引的部分，如果你不能保证这些东西已经完全掌握，那么本章不适合你

为了我们能顺利的学习，我们先创建一张表：

mysql> USE testdb;

mysql> create table demo8 (    
id int not null auto_increment,    
key1 varchar(100),    
key2 int,    
key3 varchar(100),    
key_part1 varchar(100),    
key_part2 varchar(100),    
key_part3 varchar(100),    
common_field varchar(100), 
primary key (id),
key idx_key1 (key1),    
unique key idx_key2 (key2),    
key idx_key3 (key3),    
key idx_key_part(key_part1, key_part2, key_part3));

demo8 表一共创建了1个聚簇（主键）索引和4个二级索引：

• 为id列创建的聚簇索引；
• 为key1列创建的二级索引；
• 为key2列创建的唯一二级索引；
• 为key3列创建的二级索引；
• 为key_part1、key_part2、key_part3列创建的复合（联合）二级索引。

然后我们需要为这个表插入20000条记录，除id列外其余的列都插入随机值就好了

mysql> delimiter //
create procedure demo8data()
begin    
	declare i int;    
	set i=0;    
	while i<20000 do        
		insert into demo8(key1,key2,key3,key_part1,key_part2,key_part3,common_field) values(substring(md5(rand()),1,2),i+1,substring(md5(rand()),1,3),substring(md5(rand()),1,4),substring(md5(rand()),1,5),substring(md5(rand()),1,6),substring(md5(rand()),1,7));        
		set i=i+1;    
	end while;
end;
//
delimiter ;

mysql> call demo8data();

二、单表访问方法

2.1 访问方法（access method）的概念

大家上小学时都有过查字典的经历，当我们词汇量还没有达到一定程度时，如果我们想知道某个没见过字的释义，就需要查字典。一般情况下，我们都会先去字典目录找对应的拼音或者汉字部首、定位所在字的页码，然后直接翻到对应页码查看对应释义即可。还有一种情况，闲的厉害，上百页的字典，可以从首页翻到尾页一页一页去查找，最终也能找到。两种方式，都可以得到我们想要的结果，但是花费的时间和精力却是天差地别。MySQL也一样，为了加快查询数据的速度，提出了B+树索引的概念。但是有的时候，我们要查询所有的数据，那么就不得不一行一行，一页一页的去遍历所有的数据页去获取结果。

对于单个表的查询，MySQL查询的执行方式大致分为下边两种：

2.1.1 使用全表扫描进行查询

这种方式就是上面说的，一行一行记录，一页一页记录去遍历，把符合搜索条件的记录加入到结果集就完了。这种方式虽然可以满足我们的需求，但效率无疑是最低的。

2.1.2 使用索引进行查询

直接使用全表扫描的方式执行查询要遍历好多记录，代价可能非常大。就像我们通过字典目录查找汉字一样，如果MySQL查询语句中的搜索条件可以使用到某个索引，那直接使用索引来执行查询可能会加快查询执行的时间。使用索引来执行查询的方式可以细分为许多种类：

• 针对主键或唯一二级索引的等值查询
• 针对普通二级索引的等值查询
• 通过索引列的范围查询
• 通过直接扫描整个索引

小结

MySQL执行查询语句的方式称之为访问方法或者访问类型。同一个查询语句可能使用多种不同的访问方法来执行，虽然最后的查询结果都是一样的，但是执行效率却千差万别。

2.2 const

通过主键列来定位一条记录：

mysql> select * from demo8 where id=9999;
+------+------+------+------+-----------+-----------+-----------+--------------+
| id   | key1 | key2 | key3 | key_part1 | key_part2 | key_part3 | common_field |
+------+------+------+------+-----------+-----------+-----------+--------------+
| 9999 | 34   | 9999 | 6c7  | 1823      | 24955     | 5deed4    | 3aebe82      |
+------+------+------+------+-----------+-----------+-----------+--------------+
1 row in set (0.00 sec)

还记得MySQL之B+树索引中聚簇索引B+树的结构吗，叶子（Leaf）节点存储的是完整的记录，B+树叶子（Leaf）节点中的记录是按照主键id列的值从小到大排序的。B+树本来就是一个矮胖子，加上唯一、有序、非空的属性，根据主键值定位一条记录的速度非常快。同样，根据唯一二级索引列来定位一条记录的速度也是非常快的：

mysql> select * from demo8 where key2=8888;
+------+------+------+------+-----------+-----------+-----------+--------------+
| id   | key1 | key2 | key3 | key_part1 | key_part2 | key_part3 | common_field |
+------+------+------+------+-----------+-----------+-----------+--------------+
| 8888 | 12   | 8888 | fc9  | 7810      | 1c7ed     | 5d6dbc    | adbea8c      |
+------+------+------+------+-----------+-----------+-----------+--------------+
1 row in set (0.00 sec)

根据之前文章的知识铺垫，这个查询的执行分两步：

• 第一步先从idx_key2对应的B+树索引中根据key2列与常数的等值比较条件定位到一条二级索引记录，
• 第二步再根据该记录的id值到聚簇索引中获取到完整的用户记录。

MySQL通过主键或者唯一二级索引列与常数的等值比较来定位一条记录是非常非常快的，所以把这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为：const，意思是常数级别，代价可以忽略不计。

不过这种const访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个const访问方法才有效（这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录）。

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

mysql> select * from demo8 where key2 is null;

唯一二级索引列并不限制NULL值的数量，所以上述语句可能访问到多条记录，上边这个语句不可以使用const访问方法来执行，至于什么访问方法我们下边马上说

2.3 ref

普通的二级索引列与常数进行等值比较：

mysql> select * from demo8 where key1 = 'd9';
+-------+------+-------+------+-----------+-----------+-----------+--------------+
| id    | key1 | key2  | key3 | key_part1 | key_part2 | key_part3 | common_field |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
|    59 | d9   |    59 | b7a  | 041b      | 5b1cf     | e342ac    | e103738      |
|   182 | d9   |   182 | 8bf  | 2b3c      | 08b7c     | a63ed0    | 40f2c52      |
|   401 | d9   |   401 | 137  | adbd      | dafba     | 581313    | ba72bf5      |
|  2114 | d9   |  2114 | d8b  | bf2a      | 117ae     | 69de3d    | f5467a5      |
|  2758 | d9   |  2758 | 2f7  | a159      | e3707     | f60f38    | 795ec06      |
|  3823 | d9   |  3823 | 682  | 347e      | f6195     | 6faa0d    | 5e55f78      |
|  4351 | d9   |  4351 | e92  | f3b4      | a159e     | d3e013    | e28ca48      |
|  5138 | d9   |  5138 | b41  | 1b10      | b9605     | cbe517    | b267144      |
|  5539 | d9   |  5539 | da1  | d30d      | c59b1     | 0c5d79    | ae57b7d      |
|  5604 | d9   |  5604 | ddc  | f0b0      | 00dbf     | f93c0e    | 3218cff      |
|  6050 | d9   |  6050 | 64e  | 22a4      | 69e69     | 7284ba    | 4a5b7d5      |
|  6147 | d9   |  6147 | 529  | cd38      | 71855     | 434168    | a426cbe      |
|  6428 | d9   |  6428 | a38  | c00f      | 9f710     | 9fb7c5    | a722b51      |
|  6456 | d9   |  6456 | eb7  | d208      | 30539     | ee3ae4    | a6c4870      |
|  6473 | d9   |  6473 | c15  | da29      | cc897     | 1e35c3    | 4b5f135      |
|  6860 | d9   |  6860 | b01  | bad8      | 4e3a9     | e83331    | 0cd3b9d      |
|  7257 | d9   |  7257 | fa6  | d537      | a4afe     | bbc5d8    | e11e937      |
|  7333 | d9   |  7333 | a97  | 5532      | 64097     | cb5d16    | 7e43077      |
|  7867 | d9   |  7867 | 3d7  | b341      | 0b0bb     | 7df721    | 8c64142      |
|  8265 | d9   |  8265 | a16  | 120b      | 9d372     | c17ce4    | c481ace      |
|  8371 | d9   |  8371 | 5be  | 7924      | 313f7     | 293487    | cb52072      |
|  8738 | d9   |  8738 | c05  | 7123      | b61b1     | c8d819    | e310cbf      |
|  9005 | d9   |  9005 | 44e  | e857      | 4075c     | 8460a0    | 409cb1d      |
|  9006 | d9   |  9006 | d8c  | 8c2b      | ed54f     | 3b8bfd    | 268fcce      |
|  9362 | d9   |  9362 | 5cb  | 9d70      | 05937     | 1b70d2    | a866a32      |
|  9449 | d9   |  9449 | ab8  | f9c6      | c1917     | 5ffe25    | ff88471      |
| 10146 | d9   | 10146 | 07f  | 31a7      | c30c4     | 7c2e48    | 6c5c562      |
| 10197 | d9   | 10197 | 85a  | 4796      | e5ff9     | d12af4    | 20be699      |
| 10223 | d9   | 10223 | 94b  | c57e      | adfb6     | b93c19    | a7c944b      |
| 10285 | d9   | 10285 | 9ab  | f33d      | 69e5c     | 35a651    | 0953db7      |
| 10621 | d9   | 10621 | a29  | a92b      | fbf80     | 83f1e2    | d167770      |
| 11133 | d9   | 11133 | 560  | 97af      | 35f38     | ceb1b9    | 6e89ca8      |
| 11265 | d9   | 11265 | fcc  | e7d7      | 0243e     | b52571    | 89ea417      |
| 11557 | d9   | 11557 | 1c0  | 7f66      | 0898f     | d41cfc    | e759975      |
| 11614 | d9   | 11614 | de5  | 00f7      | fb3b3     | 93dc1a    | bbe8993      |
| 11672 | d9   | 11672 | f3f  | 9fe4      | da2dd     | 82f711    | 436f3d4      |
| 12477 | d9   | 12477 | d58  | 0613      | 1df6d     | 40999a    | b748cd2      |
| 13789 | d9   | 13789 | 67b  | 5b30      | ab2f3     | 89f0ec    | 9e2d255      |
| 14050 | d9   | 14050 | 537  | bbdc      | 5e87e     | 4ac153    | 0346558      |
| 14363 | d9   | 14363 | 2ac  | 33f3      | e2b82     | 7e55c1    | 45ee579      |
| 14444 | d9   | 14444 | e47  | 6319      | 851b7     | 1d4c57    | e17a95b      |
| 14635 | d9   | 14635 | 16a  | 4d83      | 52b33     | 376017    | c853bc0      |
| 14646 | d9   | 14646 | 202  | 6fdd      | f2486     | 9900f3    | c29d0d6      |
| 15298 | d9   | 15298 | 074  | a7ee      | 6bc1d     | e96458    | 723b0f8      |
| 15489 | d9   | 15489 | 514  | 0bdc      | fb94c     | db5ce8    | 63797e8      |
| 16895 | d9   | 16895 | 4aa  | 921c      | 00b9e     | f07907    | bce779f      |
| 17587 | d9   | 17587 | 6aa  | 621b      | d521f     | a6c5ad    | 45fac89      |
| 18151 | d9   | 18151 | 87d  | cd74      | f7135     | 47d900    | 211303e      |
| 18255 | d9   | 18255 | 4dc  | b9e7      | 99bf2     | 55d0eb    | 3e6ce6c      |
| 18490 | d9   | 18490 | 6a2  | f0ff      | 85e86     | ed9bb8    | dca2cb4      |
| 18872 | d9   | 18872 | 404  | eeee      | 001c7     | 0e846d    | fae0876      |
| 19018 | d9   | 19018 | 142  | 80d7      | 2b9fd     | 77be32    | d6d8398      |
| 19228 | d9   | 19228 | a5b  | a125      | 795fa     | 108159    | 65acbf5      |
| 19537 | d9   | 19537 | 1ca  | 016a      | 3df13     | 3f5b9c    | 720de00      |
| 19940 | d9   | 19940 | 95c  | 6150      | 2696d     | 3f89b8    | d37d43a      |
| 19945 | d9   | 19945 | 538  | 6378      | a20a9     | 2b7b00    | 1865f1c      |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
56 rows in set (0.00 sec)

普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录（就像我们本例中共匹配出56行满足条件的记录），也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。如果匹配的记录较少，则回表的代价还是比较低的，所以MySQL可能选择使用索引而不是全表扫描的方式来执行查询。MySQL把这种搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref。
对于普通的二级索引来说，通过索引列进行等值比较后可能匹配到多条连续的记录，而不是像主键或者唯一二级索引那样最多只能匹配1条记录，所以这种ref访问方法比const差了一点，但是在二级索引等值比较时匹配的记录数较少时的效率还是很高的（如果匹配的二级索引记录太多那么回表的成本就太大了）。

需要注意下边两种情况：

• 二级索引列值为NULL的情况：不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含NULL值的数量并不限制，所以我们采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法，而不是const的访问方法。
• 包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法idx_key_part(key_part1, key_part2, key_part3)

mysql> select * from demo8 where key_part1='6378';
+-------+------+-------+------+-----------+-----------+-----------+--------------+
| id    | key1 | key2  | key3 | key_part1 | key_part2 | key_part3 | common_field |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
| 19945 | d9   | 19945 | 538  | 6378      | a20a9     | 2b7b00    | 1865f1c      |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
1 row in set (0.00 sec)

mysql> select * from demo8 where key_part1='6378' and key_part2='a20a9';
+-------+------+-------+------+-----------+-----------+-----------+--------------+
| id    | key1 | key2  | key3 | key_part1 | key_part2 | key_part3 | common_field |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
| 19945 | d9   | 19945 | 538  | 6378      | a20a9     | 2b7b00    | 1865f1c      |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
1 row in set (0.00 sec)

mysql> select * from demo8 where key_part1='6378' and key_part2='a20a9' and key_part3='2b7b00';
+-------+------+-------+------+-----------+-----------+-----------+--------------+
| id    | key1 | key2  | key3 | key_part1 | key_part2 | key_part3 | common_field |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
| 19945 | d9   | 19945 | 538  | 6378      | a20a9     | 2b7b00    | 1865f1c      |
+-------+------+-------+------+-----------+-----------+-----------+--------------+
1 row in set (0.00 sec)

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

mysql> select * from demo8 where key_part1='6378' and key_part2>'a20a9';
Empty set (0.00 sec)

2.4 ref_or_null

找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来

mysql> select * from demo8 where key1 = 'd9' or key is null;

当使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为ref_or_null。相当于先分别从idx_key1索引对应的B+树中找出key1 IS NULL和key1 = 'ef’的两个连续的记录范围，然后根据这些二级索引记录中的id值再回表查找完整的用户记录。

2.5 range

之前介绍的几种访问方法都是在对索引列与某一个常数进行等值比较的时候才可能使用到（ref_or_null比较奇特，还计算了值为NULL的情况），有时候我们面对的搜索条件更复杂：

mysql> select * from demo8 where key2 in (5555,6666) or (key2>=1234 and key2<=1235);
+------+------+------+------+-----------+-----------+-----------+--------------+
| id   | key1 | key2 | key3 | key_part1 | key_part2 | key_part3 | common_field |
+------+------+------+------+-----------+-----------+-----------+--------------+
| 1234 | 67   | 1234 | a2d  | 2779      | 18191     | 96a5b2    | 86c5afa      |
| 1235 | 6b   | 1235 | c48  | 43b4      | 0e1d1     | 27f9a0    | 1c17810      |
| 5555 | 0b   | 5555 | dc2  | 6b61      | dac52     | a0451f    | 187011e      |
| 6666 | 00   | 6666 | 9c3  | 99f8      | d22de     | 283e92    | 656f2f1      |
+------+------+------+------+-----------+-----------+-----------+--------------+
4 rows in set (0.00 sec)

可以使用全表扫描的方式来执行这个查询，不过也可以使用二级索引 + 回表的方式执行，如果采用二级索引 + 回表的方式来执行的话，那么此时的搜索条件就不只是要求索引列与常数的等值匹配了，而是索引列需要匹配某个或某些范围的值，在本查询中key2列的值只要匹配下列3个范围中的任何一个就算是匹配成功了：

• key2的值是5555
• key2的值是6666
• key2的值在1234和1235之间

MySQL把这种利用索引进行范围匹配的访问方法称之为：range

2.6 index

mysql> select key_part1,key_part2,key_part3 from demo8  where key_part2 = 'd22de';
+-----------+-----------+-----------+
| key_part1 | key_part2 | key_part3 |
+-----------+-----------+-----------+
| 99f8      | d22de     | 283e92    |
+-----------+-----------+-----------+
1 row in set (0.01 sec)

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 = 'd22de’这个条件是否成立，把匹配成功的二级索引记录的key_part1, key_part2, key_part3列的值直接加到结果集中就行了。由于二级索引记录比聚簇索记录小的多（聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列，而二级索引记录只需要存放索引列和主键），而且这个过程也不用进行回表操作，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多，把这种采用遍历二级索引记录的执行方式称之为：index

2.7 all

mysql> select * from demo8;

就像这个SQL语句，查询所有数据。这种查询执行方式就是全表扫描，对于InnoDB表来说也就是直接扫描聚簇索引，把这种使用全表扫描执行查询的方式称之为：all

三、注意事项

3.1 重温 二级索引+回表

一般情况下只能利用单个二级索引执行查询，比如下边的语句：

mysql> select * from demo8 where key1='00' and key2>15544;

查询优化器会识别到这个查询中两个搜索条件

• key1=‘00’
• key2>15544

优化器一般会根据demo8表的统计数据来判断到底使用哪个条件到对应的二级索引中查询扫描的行数会更少，选择那个扫描行数较少的条件到对应的二级索引中查询。然后将从该二级索引中查询到的结果经过回表得到完整的用户记录后再根据其余的where条件过滤记录。假设优化器决定使用idx_key1索引进行查询，那么整个查询过程可以分为两个步骤：

• 步骤一：使用二级索引定位记录：根据条件key1 = '00’从idx_key1索引代表的B+树中找到对应的二级索引记录
• 步骤二：根据上一步骤中找到的记录的主键值进行回表操作，也就是到聚簇索引中找到对应的完整的用户记录，再根据条件key2>15544到完整的用户记录继续过滤。将最终符合过滤条件的记录返回给用户

因为二级索引的节点中的记录只包含索引列和主键，所以在步骤1中使用idx_key1索引进行查询时只会用到与key1列有关的搜索条件，其余条件，比如key2 > 9988这个条件在步骤1中是用不到的，只有在步骤2完成回表操作后才能继续针对完整的用户记录中继续过滤。

小提示：
需要注意的是注意，这里我们所说的是一般情况，一般情况下执行一个查询只会用到单个二级索引

3.2 明确range访问方法使用的范围区间

对于B+树索引来说，只要索引列和常数使用=、<=>、in、not in、is null、is not null、>、<、>=、<=、between、!=（不等于也可以写成<>）或者like操作符连接起来，就可以产生一个所谓的区间。

小提示：

• like操作符比较特殊，只有在匹配完整字符串或者匹配字符串前缀时才可以利用索引。
• in操作符的效果和若干个等值匹配操作符=之间用or连接起来是一样的，也就是说会产生多个单点区间,比如下边这两个语句的效果是一样的：
  select * from demo8 where key2 in (1222, 1333);
  select * from demo8 where key2 = 1222 or key2 = 1333;

不过在经常工作中，一个查询的where子句可能有很多个小的搜索条件，这些搜索条件需要使用and或者or操作符连接起来：

• A and B，A和B都为true，整个表达式才为true
• A or B，A或B任意一个为true，整个表达式就为true

当我们想使用range访问方法来执行一个查询语句时，重点就是找出该查询可用的索引以及这些索引对应的范围区间。下边分两种情况看一下怎么从由and或or组成的复杂搜索条件中提取出正确的范围区间：

3.2.1 所有搜索条件都可以使用某个索引的情况

mysql> select * from demo8 where key2 > 2222 and key2 > 3333;

这个查询中的搜索条件都可以使用到key2，也就是说每个搜索条件都对应着一个idx_key2的范围区间。这两个小的搜索条件使用AND连接起来，也就是要取两个范围区间的交集。key2 > 2222和key2 > 3333交集当然就是key2 > 3333了，上边这个查询使用idx_key2的范围区间就是(3333, +∞)

如果是or

mysql> select * from demo8 where key2 > 2222 or key2 > 3333;

or意味着需要取各个范围区间的并集，key2 > 2222和key2 > 3333并集就是key2 > 2222，上边这个查询使用idx_key2的范围区间就是(2222， +∞)

3.2.2 部分搜索条件无法使用索引的情况

mysql> select * from demo8 where key2 > 2222 AND common_field = '039cb00';

这个查询语句中能利用的索引只有idx_key2一个，而idx_key2这个二级索引的记录中又不包含common_field这个字段，所以在使用二级索引idx_key2定位记录的阶段用不到common_field = '039cb00’这个条件，这个条件是在回表获取了完整的用户记录后才使用的，而范围区间是为了到索引中取记录中提出的概念，所以在确定范围区间的时候不需要考虑common_field = '039cb00’这个条件，我们在为某个索引确定范围区间的时候只需要把用不到相关索引的搜索条件替换为true（把用不到索引的搜索条件替换为true，是因为我们不打算使用这些条件进行在该索引上进行过滤，所以不管索引的记录满不满足这些条件，我们都把它们选取出来，待到之后回表的时候再使用它们过滤）。

mysql> select * from demo8 where key2 > 2222 AND true;

简化后

mysql> select * from demo8 where key2 > 2222;

由此可得，使用idx_key2的范围区间就是：(2222, +∞)。同理，可得使用or的情况：

mysql> select * from demo8 where key2 > 2222 or common_field = '039cb00';

mysql> select * from demo8 where key2 > 2222 or true;

mysql> select * from demo8 where true;

这也说明，如果强制使用idx_key2执行查询的话，对应的范围区间就是(-∞, +∞)，也就是需要将全部二级索引的记录进行回表，这个代价肯定比直接全表扫描都大了。也就是说一个使用到索引的搜索条件和没有使用该索引的搜索条件使用or连接起来后是无法使用该索引的。

3.2.3 复杂搜索条件下找出范围匹配的区间

mysql> select * from demo8 
	where (key1 > 'ed' and key2 = 66 ) 
	or (key1 < 'zc' and key1 > 'zz') 
	or (key1 like '%33' and key1 > 'fa' and (key2 < 7777 or common_field = '97d435e')) ;

看到这种复杂条件的SQL，不要惊慌，一起来慢慢分析一下

步骤一：首先查看where子句中的搜索条件都涉及到了哪些列，哪些列可能使用到索引

这个查询的搜索条件涉及到了key1、key2、common_field这3个列，然后key1列有普通的二级索引idx_key1，key2列有唯一二级索引idx_key2

步骤二：对于那些可能用到的索引，分析它们的范围区间

步骤三：假设我们使用idx_key1执行查询 ，还是用上面的方法，那些用不到该索引的搜索条件暂时移除掉

(key1 > 'ed' and true ) or(key1 < 'zc' and key1 > 'zz') or(true and key1 > 'fa' and (true or true))

继续简化

(key1 > 'ed') OR(key1 < 'zc' AND key1 > 'zz') OR(key1 > 'fa')

步骤四：替换掉永远为true或false的条件

mysql> select 'zc' > 'zz';
+-------------+
| 'zc' > 'zz' |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

结果为0，即为false，‘zc’小于’zz’。符合key1 < ‘zc’ AND key1 > 'zz’永远为false，所以上边的搜索条件可以被写成这样:
(key1 > 'ed') OR (key1 > 'fa')

继续化简区间

mysql> select 'ed' > 'fa';
+-------------+
| 'ed' > 'fa' |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

key1 > 'ed’和key1 > 'fa’之间使用or操作符连接起来的，意味着要取并集，所以最终的结果化简的到的区间就是：key1 > ‘ed’。也就是说：上述复杂搜索条件的查询语句如果使用 idx_key1索引执行查询的话，需要把满足key1 > 'ed’的二级索引记录都取出来，然后拿着这些记录的id再进行回表，得到完整的用户记录之后再使用其他的搜索条件进行过滤。

步骤五： 假设我们使用idx_key2执行查询，需要把那些用不到该索引的搜索条件暂时使用trueE条件替换掉，其中有关key1和common_field的搜索条件都需要被替换掉

(true and key2 = 66 ) or(true and true) or(true and true and (key2 < 7777 or true))

key2 < 7777 OR true 的结果是true ，继续简化

key2 = 66 or true

继续简化：true

这个结果意味着如果我们要使用idx_key2索引执行查询语句的话，需要扫描idx_key2二级索引的所有记录，然后再回表，和使用idx_key1二级索引相比，得不偿失，两种方法对比结果下不会使用idx_key2索引。

四、索引合并

在MySQL中，官方为我们内置了很多优化特性，是否需要启用需要我们手动设置：

mysql> show variables like 'optimizer_switch'\g
*************************** 1. row ***************************
variable_name: optimizer_switch
        value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.01 sec)

下面我们简单说明一下：

• index_merge=on（索引合并）
• index_merge_union=on（Union索引合并—非聚簇索引获取到的id值取并集）
• index_merge_sort_union=on（Sort-Union索引合并—非聚簇索引获取到的id值先排序-取并集）
• index_merge_intersection=on（Intersection索引合并—非聚簇索引获取到的id值取交集）
• engine_condition_pushdown=on（引擎条件下推—只用于NDB引擎，开启后时按照WHERE条件过滤后的数据发送到SQL节点来处理，不开启所有数据节点的数据都发送到SQL节点来处理。）
• index_condition_pushdown=on（索引条件下推—ICP）
• mrr=on（Multi-Range Read-MRR—这个优化的主要目的是尽量使用顺序读盘）
• mrr_cost_based=on（cost-based choice—是否计算基于使用MRR的成本计算/判断消耗）
• block_nested_loop=on（基于块的嵌套循环连接—BNL）
• batched_key_access=off（BKA—针对Index Nested-Loop Join(NLJ) 算法的优化）
• materialization=on（物化）
• subquery_materialization_cost_based=on（是否开启子查询物化的成本计算）
• semijoin=on（半连接）
• loosescan=on（半连接—松散扫描）
• firstmatch=on（半连接—首次匹配）
• duplicateweedout=on（半连接—重复值消除）
• use_index_extensions=on（使用索引扩展）
• condition_fanout_filter=on（条件(扇出)过滤）
• derived_merge=on（视图/派生表合并，需要配合好Auto_key）

这些优化特性会在后面的文章中慢慢铺开详解，回到今天的内容，我们前边说过MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但在某些特殊情况下也可能在一个查询中使用到多个二级索引，MySQL把这种使用到多个索引来完成一次查询的执行方法称之为：index merge，就是位置排在最前面的优化特性，具体的索引合并算法有下边三种

4.1 Intersection合并

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

mysql> select * from demo8 where key1='4a' AND key3='c84';

这个查询使用Intersection合并的方式执行的话，那这个过程就是这样的：

• 从idx_key1二级索引对应的B+树中取出key1 = '4a’的相关记录。
• 从idx_key3二级索引对应的B+树中取出key3 = 'c84’的相关记录。
• 二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个结果集中id值的交集。
• 按照上一步生成的id值列表进行回表操作，也就是从聚簇索引中把指定id值的完整用户记录取出来，返回给用户。

这里有同学会思考：为啥不直接使用idx_key1或者idx_key3只根据某个搜索条件去读取一个二级索引，然后回表后再过滤另外一个搜索条件呢？这里要分析一下两种查询执行方式之间需要的成本代价：

只读取一个二级索引的成本：

• 按照某个搜索条件读取一个二级索引；
• 根据从该二级索引得到的主键值进行回表操作，然后再过滤其他的搜索条件；

读取多个二级索引之后取交集成本：

• 按照不同的搜索条件分别读取不同的二级索引；
• 将从多个二级索引得到的主键值取交集，然后进行回表操作；

虽然读取多个二级索引比读取一个二级索引消耗性能，但是读取二级索引的操作是顺序I/O，而回表操作是随机I/O，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

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

情况一： 二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

比方说下边这个查询可能用到idx_key1和idx_key_part这两个二级索引进行Intersection索引合并的操作：

mysql> select * from demo8 where key1 = 'a' and key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c';

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

mysql> select * from demo8 where key1 > 'a' and key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c';
mysql> select * from demo8 where key1 = 'a' and key_part1 = 'a';

第一个查询是因为对key1进行了范围匹配，第二个查询是因为联合索引idx_key_part中的key_part2列并没有出现在搜索条件中，所以这两个查询不能进行Intersection索引合并。

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

比方说下边这个查询可能用到主键和idx_key1进行Intersection索引合并的操作：

mysql> select * from demo8 where id > 100 and key1 = 'a';

对于InnoDB的二级索引来说，记录先是按照索引列进行排序，如果该二级索引是一个联合索引，那么会按照联合索引中的各个列依次排序。而二级索引的用户记录是由索引列 + 主键构成的，二级索引列的值相同的记录可能会有好多条，这些索引列的值相同的记录又是按照主键的值进行排序的。在二级索引列都是等值匹配的情况下才可能使用Intersection索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。

so？还是没看懂根据二级索引查询出的结果集是按照主键值排序的对使用Intersection索引合并有啥好处？小伙子，别忘了Intersection索引合并会把从多个二级索引中查询出的主键值求交集，如果从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交集的过程就很easy啦。假设某个查询使用Intersection索引合并的方式从idx_key1和idx_key2这两个二级索引中获取到的主键值分别是：

• 从idx_key1中获取到已经排好序的主键值：1、3、5
• 从idx_key2中获取到已经排好序的主键值：2、3、4

那么求交集的过程就是这样：逐个取出这两个结果集中最小的主键值，如果两个值相等，则加入最后的交集结果中，否则丢弃当前较小的主键值，再取该丢弃的主键值所在结果集的后一个主键值来比较，直到某个结果集中的主键值用完了，如果还是觉得不太明白那继续往下看：

• 先取出这两个结果集中较小的主键值做比较，因为1 < 2，所以把idx_key1的结果集的主键值1丢弃，取出后边的3来比较。
• 因为3 > 2，所以把idx_key2的结果集的主键值2丢弃，取出后边的3来比较。
• 因为3 = 3，所以把3加入到最后的交集结果中，继续两个结果集后边的主键值来比较。
• 后边的主键值也不相等，所以最后的交集结果中只包含主键值3。

别看我们写的这么复杂，其实这个过程其实很快，时间复杂度是O(n)，但是如果从各个二级索引中查询出的结果集并不是按照主键排序的话，那就要先把结果集中的主键值排序完再来做上边的那个过程，就比较耗时了。

小提示：
按照有序的主键值去回表取记录有个专有名词，叫：Rowid Ordered Retrieval，简称ROR，以后⼤家在某些地方见到这个名词就眼熟了。

另外，不仅是多个二级索引之间可以采用Intersection索引合并，索引合并也可以有聚簇索引参加，也就是我们上边写的情况二：在搜索条件中有主键的范围匹配
的情况下也可以使⽤Intersection索引合并索引合并。为啥主键这就可以范围匹配了？还是得回到应用场景里，比如看下边这个查询：

mysql> select * from demo8 where key1 = 'a' and id > 100;

假设这个查询可以采用Intersection索引合并，我们理所当然的以为这个查询会分别按照id > 100这个条件从聚簇索引中获取一些记录，在通过key1 = 'a’这个条件从idx_key1二级索引中获取一些记录，然后再求交集，其实这样就把问题复杂化了，没必要从聚簇索引中获取一次记录。别忘了二级索引的记录中都带有主键值的，所以可以在从idx_key1中获取到的主键值上直接运行条件id > 100过滤就行了，这样多简单。所以涉及主键的搜索条件只不过是为了从别的二级索引得
到的结果集中过滤记录罢了，是不是等值匹配不重要。

当然，上边说的情况一和情况二只是发生Intersection索引合并的必要条件，不是充分条件。即使情况一、情况二成立，也不一定发生Intersection索引合并，最终还是要看优化器如何选择。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过Intersection索引合并后需要回表的记录数大大减少时才会使用Intersection索引合并。

4.2 Union合并

我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来，我们说这些不同的搜索条件之间是OR关系。有时候OR关系的不同搜索条件会使用到不同的索引，比如说这样：

mysql> select * from demo8 where key1 < 'a' or key3 > 'z';

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

情况一： 二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。

比如说下边这个查询可能用到idx_key1和idx_key_part这两个二级索引进行Union索引合并的操作：

mysql> select * from demo8 where key1 = 'a' or ( key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c');

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

mysql> select * from demo8 where key1 > 'a' or (key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c');
mysql> select * from demo8 where key1 = 'a' or key_part1 = 'a';

第一个查询是因为对key1进行了范围匹配，第二个查询是因为联合索引idx_key_part中的key_part2列并没有出现在搜索条件中，所以这两个查询不能进行Union索引合并。
情况二： 主键列可以是范围匹配。
情况三： 使用Intersection索引合并的搜索条件（这种情况其实也挺好理解，就是搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取交集）。

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

mysql> 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’从联合索引idx_key_part中得到另一个主键集合。
• 采⽤Union索引合并的⽅式把上述两个主键集合取并集，然后进行回表操作，将结果返回给用户

当然，查询条件符合了这些情况也不一定就会采用Union索引合并，最终也还是要看优化器如何选择。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并。

4.3 Sort-Union合并

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

mysql> select * from demo8 where key1 < 'a' and 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。

4.4 联合索引合并注意事项

联合索引替代Intersection索引合并

mysql> select * from demo8 where key1 = 'a' and key3 = 'b';

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

mysql> alter table demo8 drop index idx_key1, idx_key3, add index idx_key1_key3(key1, key3);

这样我们把没用的idx_key1、idx_key3都干掉，再添加一个联合索引idx_key1_key3，使用这个联合索引进行查询简直是又快又好，既不用多读一棵B+树，也不用合并结果，何乐而不为？

小提示：
不过小心有单独对key3列进行查询的业务场景，这样不得不再把key3列的单独索引给加上

总结

今天学写了MySQL针对单表的访问方式（access method）以及优化特性-索引合并（index merge）的知识点，下面我们来总结一下：

• 针对单表的访问方式（access method）：

  • const：通过主键或者唯一二级索引列来定位一条记录的访问方法定义为：const，意思是常数级别的，代价是可以忽略不计的。
  • ref：搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref。
  • ref_or_null：使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为：ref_or_null。
  • range：利用索引进行范围匹配的访问方法称之为：range。
  • index：遍历二级索引记录的执行方式称之为：index。
  • all：使用全表扫描执行查询的方式称之为：all。

• 索引合并（index merge）的三种算法：

  • Intersection合并（index_merge_intersection）。
  • Union合并（index_merge_union）。
  • Sort-Union合并（index_merge_sort_union）。

我们也学习了索引合并三种算法的触发条件都是必要且不充分的，最终是否会使用到相关算法，仍需通过优化器做出最终判断。同时还学习了range访问方法使用的范围区间，通过人为分析的方式预估出使用不同索引执行SQL时所花费的成本高低。今天的内容偏原理，但理解起来很简单，大家多结合前面MySQL之B+树索引知识来感受MySQL设计的精妙之处。

至此今天的学习就到此结束了，愿您成为坚不可摧的自己~~~
​

You can’t connect the dots looking forward; you can only connect them looking backwards. So you have to trust that the dots will somehow connect in your future.You have to trust in something - your gut, destiny, life, karma, whatever. This approach has never let me down, and it has made all the difference in my life

如果我的内容对你有帮助，请 点赞、评论、收藏，创作不易，大家的支持就是我坚持下去的动力

文章来源地址https://www.toymoban.com/news/detail-454361.html

到了这里，关于一文带你了解MySQL之单表访问方法的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！