第10章条条大道通罗马——单表访问方法

本章示例表：

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;

知识回顾 #

MySQL Server在对一条查询语句进行语法分析之后，就会将其交给优化器来优化，优化的结果就是生成一个执行计划。执行计划表明了应该使用哪些索引进行查询、表之间的连续顺序等等。
最后，会按照执行计划中的步骤调用存储引擎提供的接口来真正地执行查询，并将查询结果返给客户端。

访问方法的概念 #

MySQ把执行查询语句的方式称为访问方法（access method）或者访问类型。

同一个查询语句可以使用多种不同的访问方法来执行。

不同的执行方式花费的时间成本可能差距很大。

const #

通过主键或者唯一二级索引来定位一条记录的访问方法称为const。意思是常数级别的，代价可以忽略不计。注意事项如下：

只能用于与常数进行等值比较。
如果主键或者唯一二级索引由多个列组成，则只有在索引列中的每一个列都与常数进行等值比较时，const访问法才有效。
对于唯一二级索引列来说，由于其并不限制null列的数量，所以如果查询列时null值，不可以使用const方法来执行。

ref #

搜索条件为二级索引列与常数进行等值比较，形成的扫描区间为单点扫描区间，采用二级索引来执行查询的访问方法称为ref。

采用二级索引来执行查询时，如果需要回表，每获取到一条二级索引记录，就会立即对其执行回表操作，而不是将所有二级索引记录的主键值都收集起来后再统一执行回表操作。

ref访问方法比const差一些。

需要注意的两种情况：

在二级索引列允许存储null值时，无论是普通的二级索引，啊还是唯一二级索引，它们的索引列并不限制null列的数量，所以在执行包含“key is null”形式的搜索条件的查询时，最多只能使用ref访问方法，而不能使用const访问方法。
对于索引列中包含多个列的二级索引来说，只要最左边连续的列是与常数进行等值比较，就可以使用ref访问方法。
如果索引列中最左边连续的列不全部是等值比较的话，它的访问方法就不能是ref。

ref_or_null #

这种访问方式是：查找二级索引列等于某个常数的记录+值为null的记录。

值为null的记录会被放在索引的最左边。

range #

扫描区间为若干个单点扫描区间或者范围扫描区间的访问方法称为range。

index #

扫描全部二级索引记录的访问方法称为index访问方法。

此外，当通过全表扫描对使用InnoDB存储引擎的表执行查询时，如果添加了"ORDER BY 主键"的语句，在执行时也会被认定为使用的是index访问方法。

all #

全表扫描。即扫描全部的聚簇索引记录。

注意事项 #

重温二级索引+回表 #

在使用索引来较少需要扫描的记录数量时，一般情况下只会为单个索引生成扫描区间。如下查询：

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

如果使用idx_key1执行查询，对应的扫描区间就是[‘abc’,‘abc’]；如果使用uk_key2执行查询，对应的扫描区间就是(1000, +∞)。

假设优化器决定使用idx_key1索引来执行查询，那么整个查询的执行过程如下：

先通过idx_key1对应的B+树定位到扫描区间[‘abc’,‘abc’]中的第一条二级索引记录。
根据步骤 1 中得到的二级索引记录的主键值执行回表操作，得到完整的用户记录，再检测该记录是否满足key2 > 1000的条件。如果满足，就将其发送给客户端，否则将其忽略。
再根据该记录所在的单向链表找到下一条二级索引记录，重复步骤2中的操作，直到某条二级索引记录不满足key1=‘abc’的条件为止。

MRR:
每次执行回表操作时都相当于要随机读取一个聚簇索引页面，而这些随机I/O带来的性能开销比较大。于是MySQL提出了MRR（多范围读取）的优化措施，即先读取一部分二级索引记录，将它们的主键值排好序后再统一执行回表操作。

索引合并 #

在特殊情况下，MySQL可能为多个索引生成扫描区间。MySQL把这种使用多个索引来完成一次查询的执行方法称为index merge(索引合并)。具体的索引合并方法有下面3种：

1. Intersection 索引合并 #

2. Union 索引合并 #

先分别扫描不同二级索引中的记录，再根据二级索引记录中的id值对结果进行去重，再根据去重后的id值执行回表操作，这样重复的id值就只需回表一次。

如果在使用某个二级索引执行查询时，从对应的扫描区间中读取出的二级索引记录不是按照主键值排序的，则不可以使用Union索引合并的方式执行查询。比如下面的查询：
select * from single_table where key1 > 'a' or key3 = 'b';

3. Sort-Union 索引合并 #

先将从各个索引中扫描到的记录的主键值进行排序，再按照执行Union索引合并的方式执行查询。