Session 10 单表访问方法

本章的辅助表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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;

UNIQUE KEY idx_key2 (key2)索引要求索引列key2的值必须唯一，也就是说，不能有两个或多个数据行具有相同的key2值。如果尝试插入一个key2值已经存在的数据行，MySQL会报错并拒绝插入该行数据。需要注意的是，虽然唯一索引要求索引列的值唯一，但是若该列允许包含NULL值的话，则可以有多个NULL值，因为NULL不能与任何其他值相等，也不会与其他NULL值冲突。因此，唯一索引不会要求NULL值唯一。

设计MySQL的大佬把MySQL执行查询语句的方式称之为访问方法或者访问类型。同一个查询语句可能可以使用多种不同的访问方法来执行，虽然最后的查询结果都是一样的，但是执行的时间可能相差极大。下面将介绍各种访问方法的具体内容。

const

1
2
SELECT * FROM single_table WHERE id = 1438;
SELECT * FROM single_table WHERE key2 = 3841;

通过主键或者唯一二级索引列来定位一条记录的访问方法定义为：const，意思是常数级别的，代价是可以忽略不计的。这种const访问方法只能在【主键列】或者【唯一二级索引列】和一个【常数】进行【等值比较】时才有效。

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

image-20230727153735190

对于唯一二级索引来说，查询该列为NULL值的情况比较特殊。因为可能访问到多条记录，所有不可以使用const访问方法来执行。

ref

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

1
SELECT * FROM single_table WHERE key1 = 'abc';

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

image-20230727154209323

  • 二级索引列值为NULL的情况

    我们采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法，而不是const的访问方法。

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

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

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

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

ref_of_null

相比ref，额外多了对null的查询（IS NULL不算是等值查询），比如说：

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

当使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为ref_or_null。

range

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

可以使用全表扫描的方式来执行这个查询，也可以使用二级索引 + 回表的方式执行。这种利用索引（这里的索引可以是聚簇索引，也可以是二级索引）进行范围匹配的访问方法称之为：range。

image-20230727154941718

我们可以把那种索引列等值匹配的情况称之为单点区间，

index

1
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中。

因此可以直接遍历二级索引，而无需回表操作。二级索引记录比聚簇索记录小的多，遍历叶子时也是顺序I/O。这种采用遍历二级索引记录的执行方式称之为：index。

all

最直接的查询执行方式就是我们已经提了无数遍的全表扫描，对于InnoDB表来说也就是直接扫描聚簇索引，设计MySQL的大佬把这种使用全表扫描执行查询的方式称之为：all。

一般情况下只能利用单个二级索引执行查询，比方说下面的这个查询：

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

假设优化器决定使用idx_key1索引进行查询，那就会根据条件key1 = 'abc'从二级索引中得到主键进行回表操作，再根据条件key2 > 1000到完整的用户记录继续过滤。将最终符合过滤条件的记录返回给用户。

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

这个查询语句中能利用的索引只有idx_key2一个，而idx_key2这个二级索引的记录中又不包含common_field这个字段，所以在使用二级索引idx_key2定位记录的阶段用不到common_field = 'abc'这个条件，这个条件是在回表获取了完整的用户记录后才使用的。在确定范围区间的时候不需要考虑common_field = 'abc'这个条件，我们在为某个索引确定范围区间的时候只需要把用不到相关索引的搜索条件替换为TRUE就好了。

1
2
SELECT * FROM single_table WHERE key2 > 100 AND TRUE;
SELECT * FROM single_table WHERE key2 > 100;

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

1
2
3
SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';
SELECT * FROM single_table WHERE key2 > 100 OR TRUE;
SELECT * FROM single_table WHERE TRUE;

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

索引合并

我们前面说过MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但在这些特殊情况下也可能在一个查询中使用到多个二级索引，设计MySQL的大佬把这种使用到多个索引来完成一次查询的执行方法称之为：index merge，具体的索引合并算法有下面三种。

Intersection合并

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

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

二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个结果集中id值的交集。然后按照生成的id值列表进行回表操作。

显然，合并后涉及的回表操作次数，一定比【只读取一个二级索引再过滤】要少。MySQL在某些特定的情况下才可能会使用到Intersection索引合并：

情况一：二级索引列是等值匹配的情况

​ 因为只有这样，从二级索引得到的主键集合才是有序的，这样在做合并操作时，可以达到O(n)复杂度。

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

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

二级索引的记录中都带有主键值的，所以可以在从idx_key1中获取到的主键值上直接运用条件id > 100过滤就行了

Union合并

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

  • 情况一：二级索引列是等值匹配的情况
  • 情况二：主键列可以是范围匹配
  • 情况三：使用Intersection索引合并的搜索条件（这就类似集合嵌套）

Sort-Union合并

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

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

我们可以这样：

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

很显然，这种方式多了一步对二级索引记录的主键值排序的过程。

注意，没有所谓的Sort-Intersection索引合并。

联合索引替代Intersection索引合并

我们可以直接为key1, key3建立联合索引。不过小心有单独对key3列进行查询的业务场景。