MySQL Access Method 访问方法简述

2023-10-23 13:10:34 浏览数 (1)

本文参考自《从根儿上理解MySQL》

访问方法（Access Method)的概念

对于单表查询来说，查询方式大致如下：

  • 全表查询 （每一个记录都跑一遍）
  • 索引查询

而MySQL执行查询语句的方式称为：访问方法（AM)或者访问类型

下面来一个建表

代码语言：sql复制
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),				## id为主键
 KEY idx_key1 (key1),			## key1索引
 UNIQUE KEY idx_key2 (key2),	## key2唯一 索引
 KEY idx_key3 (key3),			## key3索引
 KEY idx_key_part(key_part1, key_part2, key_part3)		## 二级索引，这里也是联合索引
) Engine=InnoDB CHARSET=utf8;

在此之前，我们来回顾一下联合索引的最左匹配原则：

MySQL 建立多列索引（联合索引）有最左匹配的原则，即最左优先：

如果有一个 2 列的索引 (a, b)，则已经对 (a)、(a, b) 上建立了索引；

如果有一个 3 列索引 (a, b, c)，则已经对 (a)、(a, b)、(a, b, c) 上建立了索引；

const

这里的const，是指常数级。

在细讲之前我们来说一下：聚簇索引和二级索引

聚簇索引：通常是指主键索引，也就是这个索引带有整条行记录

二级索引：通常该索引只带有部分记录，比如以k2为索引，携带一个主键k1

如果我们是这样的语句

代码语言：sql复制
SELECT * FROM single_table WHERE id = 1438;

那么查询就是这样的

image-20230906111317754image-20230906111317754

那么二级索引呢？

代码语言：sql复制
SELECT * FROM single_table WHERE key2 = 3841;

就是这样

image-20230906111756160image-20230906111756160

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

但是对于唯一二级索引来说有一种情况比较特殊

查空 NULL

代码语言：sql复制
SELECT * FROM single_table WHERE key2 IS NULL;

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

ref

如果对普通二级索引进行select呢？

代码语言：sql复制
SELECT * FROM single_table WHERE key1 = 'abc';

如果使用二级索引，那么查询的方式将会是如下

image-20230906142131044image-20230906142131044

普通二级索引并不限制索引列值的唯一 性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录 条数。如果匹配的记录较少，则回表的代价还是比较低的，所以 MySQL 可能选择使用索引而不是全表扫描的方式 来执行查询。

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

  • 二级索引 clomn value = null

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

  • 包含很多个索引列的二级索引

只要是最左边的连续索引列是与常数的等值比较就可能采用 ref 的访问方法，比方说下边这几个查询：

代码语言：sql复制

SELECT * FROM single_table WHERE key_part1 = 'god like';

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

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill';

代码语言：txt复制

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

代码语言：sql复制

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

代码语言：txt复制

ref_or_null

代码语言：sql复制
SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;

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

image-20230906162041020image-20230906162041020

range

当我们表示某一些范围的时候该使用什么访问方法呢？range

代码语言：sql复制
SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

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

此处所说的使用索引进行范围匹配中的 索引 可以是聚簇索引，也可以是二级索引。

image-20230906173814229image-20230906173814229

我们可以把那种索引列等值匹配的情况称之为 单点区间 ，上边所说的 范围1 和 范围2 都可以被称为单点区间， 像 范围3 这种的我们可以称为连续范围区间。

index

代码语言：sql复制
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 中。

可以直接通过遍历 idx_key_part 索引的叶子节点的记录来比较 key_part2 = 'abc' 这个条件是否 成立，把匹配成功的二级索引记录的 key_part1 , key_part2 , key_part3 列的值直接加到结果集。

而且这个过程也不用进行回表操作，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多

联合索引由于最左匹配原则，如果前列未被使用，那么后列的索引也会失效，因为联合索引会从最左索引开始进行比较

all

最直接的查询执行方式就是我们已经提了无数遍的全表扫描，对于 InnoDB 表来说也就是直接扫描聚簇索引

注意

二级索引 回表

对于有多个二级索引那么怎么做呢？

代码语言：sql复制
SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

优化器一般选择对应的二级索引中查询的行数会更少，选择那个扫描行数较少的条件到对应的二级索引中查询，然后将从该二级索引中查询到的结果经过回表得到完整的用户记录后再根据其余的 WHERE 条件过滤记录。（行数越少，回表次数越少）

一般来说，等值查找比范围查找需要扫描的行数更少（也就是 ref 的访问方法一般比 range 好，但这也不总是一 定的，也可能采用 ref 访问方法的那个索引列的值为特定值的行数特别多）

所以这里：

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

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

需要注意的是，我们说一般情况下执行一个查询只会用到单个二级索引，不过还是有特殊情况的。

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

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

LIKE操作符比较特殊，只有在匹配完整字符串或者匹配字符串前缀时才可以利用索引。 IN操作符的效果和若干个等值匹配操作符=之间用OR连接起来是一样的，也就是说会产生多个单点 区间，比如下边这两个语句的效果是一样的：

SELECT * FROM single_table WHERE key2 IN (1438, 6328);

SELECT * FROM single_table WHERE key2 = 1438 OR key2 = 6328;

但是通常情况下，我没还会结合WHERE子语句来继续进行范围的添加，比如：AND、OR

如下：

cond1 AND cond2 ：只有当 cond1 和 cond2 都为 TRUE 时整个表达式才为 TRUE 。 cond1 OR cond2 ：只要 cond1 或者 cond2 中有一个为 TRUE 整个表达式就为 TRUE 。

那么这个时候我们如何确定区间呢？那就是取交集和并集了。

所有搜索条件都可以使用某个索引的情况
代码语言：sql复制
SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;
image-20230906183311500image-20230906183311500
代码语言：sql复制
SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;
image-20230906183359256image-20230906183359256
有的搜索条件无法使用索引的情况
AND
代码语言：sql复制
SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';

但是要注意的是，idx_key2中没有common_field这个字段，所以这里会发生索引失效。

所以在确定 范围区间 的时候不需要考虑 common_field = 'abc' 这个条件，我们在为某个索引确定范围区间的时候只需要把用不到相关索引的搜索条件替换为 TRUE 就好了。

之所以把用不到索引的搜索条件替换为TRUE，是因为我们不打算使用这些条件进行在该索引上进行过滤，所以不管索引的记录满不满足这些条件，我们都把它们选取出来，待到之后回表的时候再使用它们 过滤。

最后的结果是这样：

代码语言：sql复制
SELECT * FROM single_table WHERE key2 > 100 AND TRUE;

简化以后就是这样

代码语言：sql复制
SELECT * FROM single_table WHERE key2 > 100;
OR
代码语言：sql复制
SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';

同理

优化如下

代码语言：sql复制
SELECT * FROM single_table WHERE key2 > 100 OR TRUE;

我们再来简化一下

代码语言：sql复制
SELECT * FROM single_table WHERE TRUE;

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

不如直接优化，减少二级索引回表

复杂搜索条件下找出范围匹配的区间
代码语言：sql复制
SELECT * FROM single_table WHERE 
 (key1 > 'xyz' AND key2 = 748 ) OR
 (key1 < 'abc' AND key1 > 'lmn') OR
 (key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc')) ;

下面我们来进行SQL优化

代码语言：sql复制
SELECT * FROM single_table WHERE 
 (key1 > 'xyz' AND key2 = 748 ) OR		## key1 > 'xyz' AND key2 = 748  OR
 (key1 < 'abc' AND key1 > 'lmn') OR		## false OR
 (key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc')) ; ## true AND key1 > 'zzz' AND true

去掉false和true

代码语言：sql复制
key1 > 'xyz' AND key2 = 748 OR key1>'zzz' ## xyz<zzz

优化后 key1>xyz

代码语言：sql复制
key1 > 'xyz' AND key2 = 748

下面就看选择idx_key1还是idx_key2进行回表了

其实如果我们从头分别分析的话，我们会发现选择key1

  • 针对idx_key1分析
代码语言：sql复制

key1 > 'xyz' AND true AND

false OR

true AND key1>'zzz ' AND true

代码语言：txt复制

最后的结果就是key1>'xyz'

  • 针对key2分析
代码语言：sql复制

true AND key2 = 748 OR

true AND ture OR

true AND true AND (key2 < 8000 OR true)

代码语言：txt复制

最后的结果是

代码语言：sql复制

key2=748 OR true

代码语言：txt复制

那么最后就是回归全表，得不偿失

索引合并

前面我们讲的都是基于单个二级索引进行查询，但真实情况也有多个的，下面来西索。

Intersection合并

取交集。

老规矩，直接上SQL

代码语言：sql复制
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

下面我们来看一下步骤：

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

来进行一下成本比对：

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

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

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

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

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

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

  • 二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。（联合索引当值确定的时候按照主键排序）

比如下面这个就可以：

代码语言：sql复制

SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b'

AND key_part3 = 'c';

代码语言：txt复制

但是下面这两个就不行：

代码语言：sql复制

SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b'

AND key_part3 = 'c'; ## 出现key1范围匹配

代码语言：txt复制
代码语言：sql复制

SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a'; ## key_part2、key_part3没有匹配

代码语言：txt复制
  • 主键列可以是范围匹配
代码语言：sql复制

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

代码语言：txt复制
Union合并

并集合并

代码语言：sql复制
SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b

Intersection 是交集的意思，这适用于使用不同索引的搜索条件之间使用 AND 连接起来的情况； Union 是并集 的意思，适用于使用不同索引的搜索条件之间使用 OR 连接起来的情况。与 Intersection 索引合并类似。

条件如下：

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

就是搜索条件的某些部分使用 Intersection 索引合并的方式得到的主键集合和其他方式得到的主键集合取交集

代码语言：sql复制

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 =

'c' OR (key1 = 'a' AND key3 = 'b');

代码语言：txt复制

优化器可能采用这样的方式来执行这个查询：

  • 先按照搜索条件 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 索引合并。

Sort-Union合并

Union 索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到。

比如：

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

0 人点赞