单表访问方法

访问方法（access method）的概念

我们平时所写的查询语句本质上只是一种声明式的语法，只是告诉Mysql我们要获取的数据符合哪些规则。之所以把Mysql执行查询的方式称为访问方法或访问类型，同一个查询语句可能使用多种不同的访问方法来执行，芮然最后查询结果都是一样的。

CREATE TABLE single_table ( 
	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) ) Engine=InnoDB CHARSET=utf8;

• 为id列建立的聚簇索引。
• 为key1列建立的idx_key1二级索引。
• 为key2列建立的idx_key2二级索引，而且该索引是唯一二级索引。
• 为key3列建立的idx_key3二级索引。
• 为key_part1、key_part2、key_part3列建立的idx_key_part二级索引，这也是一个联合索引。

const

常数级别，代价可以忽略不计，不过这种const访问方法只能在主键列或者唯一二级索引和一个常数进行常数进行等值比较时才有效。

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

根据唯一二级索引来定位一条记录；

ref

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

• 二级索引为NULL的情况

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

• 对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引是与常数的等值比较就可能采用ref访问方法。但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为ref。

ref_or_null

有时我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为null的记录也找出来，就像下边这个查询。

SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;

range

之前介绍的几种方法都是在对索引列与某一个常数进行等值比较的时候才可能使用到，但有时候面对的搜索条件更为复杂：

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

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

• key2的值是1438；
• key2的值是6328；
• key2的值在38和79之间；

把这种利用索引（聚簇索引或二级索引）进行范围匹配的访问方法称为range；

index

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

• 它的查询列表只有三列，而这三列由包含在联合索引idx_key_part中；
• 搜索条件中的列也包含在联合索引idx_key_part中。

我们可以直接通过遍历idx_key_part索引的叶子结点的记录来比较key_part2 = 'abc’这个条件是否成立，把匹配成功的二级索引记录的三个列的值直接加到结果集中就行了，而不用进行回表操作。

all

重温二级索引 + 回表

SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

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

• 使用二级索引定位记录的阶段，也就是根据条件key1=‘abc’从idx_key1索引代表的B+树中找到对应的二级索引记录；
• 回表阶段，根据上一步找到的记录的主键值进行回表操作，也就是到聚簇索引中找到对应的完整的用户记录，再根据条件key2>1000到完整的用户记录继续过滤。

因为二级索引的节点中的记录质保函索引列和主键，所以在步骤1中使用idx_key1索引进行查询只会用到key1列有关的搜索条件，key2>1000这个条件在步骤1是用不到的。

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

对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!= 或者LIKE操作符连接起来，就可以产生一个所谓的区间：

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

SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;

使用idx_key2索引的范围区间的确定过程如下图：

idx_key2的范围区间就是（200，正无穷）

我们再看一下使用OR将多个搜索条件连接在一起的情况：

 SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;

取值范围（100，正无穷）

有的搜索条件无法使用索引的情况

SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';

在为某个范围确定区间的时候只需要把用不到相关索引的搜索条件替换为TRUE，待到之后回表的时候在使用它们过滤。

我们把上边的查询中用不到idx_key2的搜索条件替换后就是这样：

SELECT * FROM single_table WHERE key2 > 100 AND TRUE; 

化简之后就是这样：

SELECT * FROM single_table WHERE key2 > 100;

也就是说最上边那个查询使用idx_key2的范围区间就是：(100, +∞)。

再来看一下使用OR的情况：

SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';

同理，我们把使用不到idx_key2索引的搜索条件替换为TRUE：

SELECT * FROM single_table WHERE key2 > 100 OR TRUE; 

接着化简：

SELECT * FROM single_table WHERE TRUE;

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

索引合并

在特殊情况下可能会出现一个查询中用到多个二级索引，设计者把这种使用到多个索引来完成一次查询的执行方法称为：index merge，具体的方法有以下三种：

Intersection（交集）合并

从多个二级索引中查询到的结果取交集；

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

合并过程：

• 从idx_key1二级索引对应的B+树中取出key1=‘a’的相关记录；
• 从idx_key3二级索引对应的B+树中取出key3=‘b’的相关记录；
• 二级索引的记录都是由索引列+主键构成的，所以我们可以计算出这两个结果集中id的交集；
• 按照上一步生成的id表进行回表操作。

为什么不直接使用idx_key1或者idx_key3只根据某个搜索条件去读取一个二级索引，然后回表再过滤另外一个搜索条件呢？这里需要分析一下两种查询方式的成本：

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

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

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

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

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

顺序IO是指读写操作的访问地址连续，HDD所需磁道搜索时间显著减少；随机IO是指读写操作时间连续，但是访问地址不连续，随机分布在磁盘的地址空间中。

应用场景

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

比方说下边这个查询可能用到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';

二级索引的用户记录是由索引列+主键构成的，二级索引列的值相同的记录可能有好多条，所以只有联合索引全部等值匹配的情况下查询出来的结果集是按照主键值排序的；
这样idx_key_part索引查询出来的主键是排序好的，key1索引查询出来的主键值也是排序好的。 排序好的两个集合取交集会容易得多；

取交集算法：逐个取出这两个结果集中最小的主键值，如果两个值相等，则加入最后的交集结果中，否则丢弃当前较小的主键值，再取该丢弃主键值所在结果集的后一个主键值来比较，知道某个结果集中的主键值用完了。这个过程的时间复杂度为O(n)，省去了排序时间。这种按照有序的主键值去回表去记录被称为Rowid Ordered Retrieval 简称为ROR；

• 主键列可以是范围匹配；

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

在从idx_key1中获取到的主键值上直接运用条件id>100过滤就行了。

上述两种情况是发生交集索引的必要条件，但不是充分条件，是否进行交集索引查询还是在于优化器的分析，优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过Intersection索引合并后需要回表的记录数大大减少时才会使用Intersection索引合并。

Union（并集）合并

对于搜索条件用AND连接的情况可以使用Intersection合并，而对于使用OR连接的情况，则考虑使用Union合并。

• 二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。
• 主键列可以是范围匹配；
• 使用Intersection索引合并的搜索条件；

优化器只有在单独根据搜索条件从某个二级索引中获取的记录数较少，通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并。

Sort-Union合并

Union索引合并的使用条件太苛刻，下面的查询就是用不到：

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

这是因为两个条件取出的主键值不是排好序的，但我们可以先根据连个条件查询出主键值并排好序，然后剩下的操作就和Union索引合并方式一样。