MySQL单表查询访问方法详解-CSDN博客

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 uk_key2 (key2),
    key idx_key3 (key3),
    key idx_part (key_part1, key_part2, key_part3)
) ENGINE = INNODB
  CHARSET = utf8;

MySQL服务器程序中有一个优化器模块，MySQL服务器在对一条查询语句中进行语法解析后，就会将语句交给优化器进行优化，优化的结果就是生成了一个执行计划

单表查询的访问方法

更多访问方法

MySQL执行查询语句的方式被称为访问方法、访问类型
一般情况下，单表查询的访问方法
- const
- ref
- ref_or_null
- range
- index
- all
特殊情况——索引合并（index_merge）
- 索引合并——交集
- 索引合并——并集
- 索引合并——排序并集

const

通过主键或唯一索引进行与与常数的等值查询的访问方法是const，速度非常快
如果主键或唯一索引是由多个列组成的，那么只有索引列中的每一个列都进行与常数的等值比较，const访问方法才有效

explain
select *
from single_table
where id = 345;

如果使用唯一索引进行查询null值，不会使用const的访问方法，因为唯一索引不限制null值的数量

ref

通过普通索引进行与常数的等值查询时，使用的访问方法是ref

explain
select *
from single_table
where key1 = 'ab';

使用普通索引来执行查询，此时对应的扫描区间是[‘ab’, ‘ab’]（单点扫描区间），可以定位到key1=‘ab’，的第一条记录，然后沿着记录所在的单向链表向后扫描，直到记录不符合key1=‘ab’。

查询列表是*，针对每一条索引记录，都需要根据记录的Id进行回表操作，获取完整的用户记录。

采用二级索引来执行查询时，每获取到一条索引记录，就会立刻进行回表操作，不是将所有记录的主键值都收集后再统一执行回表操作

ref_or_null

不仅想查询索引等于某个值，还想查询出索引为null的记录时，采用的访问方法时ref_or_null

explain
select *
from single_table
where key1 = 'ab' or key1 is null;

null的记录在非null的记录前面

range

搜索条件较为复杂时，产生范围的查询的访问方法是range

explain
select *
from single_table
where key2 in (15, 60)
   or (key2 > 35 and key2 < 75);

这种情况下，对于的扫描区间是[15, 15]、[60, 60]、[35, 75]，为多个单点扫描区间或范围区间。

扫描区间是(-∞, +∞)的查询的访问方法不是range

index

explain
select key_part1, key_part2, key_part3
from single_table
where key_part2 > 'bc';

key_part2的列不是联合索引中最左边的列，只使用key_part2不能减少需要扫描的记录数量，此时不会使用ref、range的访问方法，但他符合2个条件

查询列表中只有key_part1,、key_part2,、key_part3，索引中包含了这三个列
查询条件只有key_part2，并且它在索引中

这将直接遍历索引记录，针对每一条所有记录，都判断key_part2 > ‘bc’，如果成立就将查询列表的内容返回给客户端，对应的扫描区间是(-∞, +∞)。

all

最直接的全表扫描

一般情况的查询

使用索引减少需要扫描的记录数量时，一般情况只会为单个索引生成扫描区间

select *
from single_table
where key1 = 'ab'
  and key2 > 1000;

查询优化器会识别到2个查询条件
- key1 = ‘ab’
- key2 > 1000

如果使用了key1对应的索引，对应的扫描区间是[‘ab’, ‘ab’]，如果使用key2对应的索引，扫描区[1000，+∞]。

优化器会通过访问表中的少量数据或直接根据事先生成的统计数据，计算[‘ab’, ‘ab’]扫描区间有多少条记录，再计算[1000，+∞]扫描区间有多少记录，再通过一定的算法计算出两个扫描区间查询成本，选择成本更小的扫描区间对应的索引进行查询。

一般来说，等值查询比范围查询需要扫描的记录少，但并不是总是成立（可能采用ref方法访问时，相应的索引为特定行的记录特别多）

选择key1索引查找的过程
1. 通过key1索引定位到[‘ab’, ‘ab’]扫描区间的第一条记录
2. 根据步骤1得到的索引记录中的主键值进行回表操作，得到完整的用户记录，再判断该记录是否满足key2>1000，如果满足就将其发送给客户端
3. 根据该记录所在的单向链表找到下一条索引记录，重复步骤2直到索引记录不满足key1=‘ab’

从上面的步骤来看，可以发现每次通过索引获取到索引记录后，就会根据主键值进行回表操作。在某个扫描区间内，二级索引中主键值时无序的，也就是说每次执行回表操作都是相当于要随机读取一个聚簇索引页面。随机IO带来了性能开销。MySQL中，有一个MRR（Disk-Sweep Multi-Range Read，多范围读取）的优化措施，即读取一部分的索引记录，将他们的主键值排好序后再统一执行回表操作，这会节省一些IO开销，但使用MRR的条件较为苛刻。

特殊情况的索引合并

交集
并集
排序并集

交集索引合并

explain
select *
from single_table
where key1 = 'a'
  and key3 = 'b';

执行的方案
- 全表扫描
- 使用一个索引
- 使用索引合并

使用单个索引

使用key1的索引执行查询，扫描区间是[‘a’, ‘a’]，根据获取索引记录的主键值，进行回表操作得到用户记录，再判断key3='b’的条件是否成立

当然也可以使用key3的索引进行上述的步骤

key1 = ‘a’、key3 = 'b’这两个查询条件是等值查询，对应的扫描区间都是单点扫描区间，那么这个区间内的索引记录中，主键值是排好序的

交集索引合并

即同时使用key1、key3的索引进行查询，获取key1索引查询扫描区间[‘a’, ‘a’]、key3索引查询扫描区间[‘b’, ‘b’]的索引记录，然后在两者的操作结果中取得相同的主键值，再根据主键值进行回表操作

使用交集索引合并执行方法要求索引记录是按照主键值排序的，这主要出于两方面的考虑

从2个有序集合里取交集更容易
获取的主键值是有序的，在进行回表操作时不再是进行随机IO，提高效率

交集索引合并的大致步骤：
1. 分别从两个扫描区间[‘a’, ‘a’]、[‘b’, ‘b’]取出符合条件的索引记录
2. 比较两个扫描区间获得的索引记录中的主键值，抛弃主键值较小的一条记录，并从对应的扫描区间内获取下一条记录
3. 如果从两个不同扫描区间分别获取的2个索引记录中的主键值相等，就意味着主键交集成功
4. 根据主键值进行回表操作获取用户记录

特殊情况

进行了范围查询

explain
select *
from single_table
where key1 > 'b'
  and key3 = 'b';

从对应的扫描区间获取的索引记录的主键值不是排序的，则不可以使用交集索引合并来执行查询

使用了联合索引

explain
select *
from single_table
where key1 = 'b'
  and key_part1 = 'b';

上方的查询语句不能使用交集索引合并，因为key_part1对应的索引是联合索引（key_part1、key_part2、key_part3），key_part1='a’的索引记录，是根据key_part2值进行排序，不是根据主键值进行排序

使用了聚簇索引

explain
select *
from single_table
where key1 = 'b'
  and id > 9000;

在上述查询中使用交集索引合并，但查询条件id>9000不会生成扫描区间(9000,+∞)（普通索引中包含了主键值），而是与查询条件key1='b’形成扫描区间((‘a, 9000’),(‘a’,+∞))，在使用key1的索引时，可以定位符合key1 = 'b' and id > 9000的第一条索引记录，并从该记录所在的单向链表中向后扫描直到索引记录不符合上述条件，针对哪些符合条件的索引记录，需要根据主键值进行回表操作

并集索引合并

explain
select *
from single_table
where key1 = 'b'
   or key3 = 'b';

如果只是用key1或key3的索引进行查询，那么扫描区间是(-∞, +∞)，针对每一条索引记录都要进行回表操作，这种情况下是单独使用某一索引的

可以同时使用key1、key3索引，从两个扫描区间中分别获取索引记录，并对两个结果进行去重，再根据去重后的主键值进行回表操作

使用并集索引合并也要求获得的索引记录是按照主键值排序的，原因同交集索引合并一样

特殊情况

进行了范围查询，不能使用并集联合索引
使用了联合索引

explain
select *
from single_table
where key1 = 'a'
   or key_part1 = 'a';

使用了联合索引的最左边的列，这将会使用到排序并集索引合并

explain
select *
from single_table
where (key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'b')
   or (key1 = 'a' and key2 = 'b');

对于key1 = 'a' and key2 = 'b'条件使用交接索引合并，对于or子句条件，再使用并集索引合并

使用了聚簇索引

explain
select *
from single_table
where key1 = 'a'
   or id > 345;

key1索引的扫描区间为[‘a’, ‘b’]，并且索引记录时按照主键值排序的，聚簇索引的扫描区间为[345, +∞]，这种情况可以使用并集索引合并

排序并集索引合并

并集索引合并的条件太苛刻，MySQL引入了排序并集索引合并

explain
select *
from single_table
where key1 < 'a'
   or key3 > 'z';

排序并集索引合并，分别根据查询条件key1 < 'a'、key3 > 'z'从对应的索引中获得索引记录，根据主键值对索引记录进行排序，完成并集索引合并

MySQL没有引入排序交集索引合并，图书作者认为这是因为交集索引合并针对的是“单独根据查询条件从索引中获得的索引记录太多”的使用场景，这时使用交集索引和合并将大大减少回表成本，如果使用排序交集索引合并，它造成的成本可能比使用单个索引更高

MySQL的单表访问方法

MySQL知识总结

10 单表访问方法

表的结构

单表查询的访问方法

const

ref

ref_or_null

range

index

all

一般情况的查询

特殊情况的索引合并

交集索引合并

使用单个索引

交集索引合并

特殊情况

并集索引合并

特殊情况

排序并集索引合并