Mysql是怎样运行的：第十章笔记

笔记正文

前置准备

MySQL Server 有一个称为查询优化器的模块，一条查询语句进行语法解析之后就会被交给查询优化器来进行优化，优化的结果就是生成一个执行计划。执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是什么样的，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。

本章讲解的是 MySQL 的单表访问方法，即 MySQL 怎么执行单表查询。所谓单表查询，就是查询的 FROM 子句后只有一个表。

在正式开始讲解前，我们需要一个表：

CREATE TABLE single_table (
    -- 为 id 列建立的聚簇索引
    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),
    -- 为 key1 列建立的 idx_key1 二级索引
    KEY idx_key1 (key1),
    -- 为 key2 列建立的 idx_key2 唯一二级索引
    UNIQUE KEY idx_key2 (key2),
    -- 为 key3 列建立的 idx_key3 二级索引
    KEY idx_key3 (key3),
    -- 为 key_part1、key_part2、key_part3 列建立的 idx_key_part 联合索引。
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

表中记录自行插入，使表拥有 10000 行记录即可。

访问方法（Access Method）的概念

查询语句的本质上只是一种声明式的语法，用于告知 MySQL 我们要获取的数据应该符合我们书写的查询语句的规则，具体 MySQL 如何执行查询，是MySQL自己决定的。

对于单个表而言，查询的执行方式可以大致分为两种：

使用全表扫描进行查询。
使用索引进行查询（这种方式又可以细分为很多种类，如下）。
- 针对主键或唯一二级索引的等值查询。
- 针对普通二级索引的等值查询。
- 针对索引列的范围查询。
- 直接扫描整个索引。

MySQL 执行查询语句的方式称之为访问方法或者访问类型。同一个查询语句使用不同的访问方法执行，结果一致但速度不同。下面，我们来介绍各种访问方法。

const

B+ 树叶子节点中的记录是按照索引列排序的，对于聚簇索引来说，它对应的 B+ 树叶子节点中的记录就是按照 id 列排序的。聚簇索引对应的 B+ 树根据主键值定位一条记录的速度相当快，类似的，我们根据唯一二级索引列来定位一条记录的速度也是相当快的。例如下面这两个查询：

-- 根据主键值定位一条记录
SELECT * FROM single_table WHERE id = 1438;

-- 根据唯一二级索引列来定位一条记录的速度
SELECT * FROM single_table WHERE key2 = 3841;

可以看到，这两个查询是主键或唯一二级索引列与常数的等值比较，这种通过主键或者唯一二级索引列来定位一条记录的访问方法被定义为：const ，即常数级别的访问方法（也可以称之为 const 访问方法）。

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

此处有个例外。我们知道，主键非空（NULL），但是唯一二级索引列并不限制 NULL 值的数量。也就是说，下面这条SQL可能访问到多条记录。

SELECT * FROM single_table WHERE key2 IS NULL;

这时候 const 访问方法就不会被用在这条SQL语句了，而是会使用 ref 访问方法。

ref

搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法被定义为：ref（也可以称之为 ref 访问方法）。例如如下SQL：

-- 搜索条件为 key1 = 'abc' ，是等值比较，并且 key1 列对应的索引是普通二级索引
-- 此时若满足搜索条件的记录较少，也就意味着回表的代价不大，MySQL可能选择使用索引而不是全表扫描的方式来执行查询
-- 这时执行查询的访问方法，是采用二级索引来执行查询的访问方法，即 ref 访问方法
SELECT * FROM single_table WHERE key1 = 'abc';

对于普通的二级索引来说，通过索引列进行等值比较后可能匹配到多条连续的记录，而对于主键或者唯一二级索引而言，最多只能匹配1条记录。所以，ref 访问方法比 const 访问方法慢。

对于 ref 访问方法，注意以下两种情况：

无论是普通还是唯一二级索引，它们的索引列对包含 NULL 值的数量并不限制。所以当搜索条件为key IS NULL时，对应的 SQL 最多使用 ref 访问方法，而不是 const 访问方法。

对于某个包含多个索引列的二级索引，只要最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法。例如：

-- 联合索引 idx_key_part(key_part1, key_part2, key_part3)

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';

但最左边的连续索引列并不全部是等值比较的情况，它的访问方法就不能称为 ref 了。例如：

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

ref_or_null

找出某个二级索引列的值为某个常数并且该列的值为 NULL 的记录的查询，若使用的是二级索引而不是全表扫描的方式执行该查询，那么此时 MySQL 执行查询使用的访问方法被称为：ref_or_null（也可以称之为 ref_or_null 访问方法）。例如：

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

range

利用索引进行范围匹配的访问方法称之为：range（也可以称之为 range 访问方法）。例如：

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

若将这个 SQL 搜索条件对应 key2 列的值需要满足的范围在数轴上体现，可以划分出三个区间：

区间 1 ：key2 = 1438
区间 2 ：key2 = 6328
区间 3 ：key2 ∈ [38, 79]

明显的，区间 1 和 2 对应着索引列等值匹配的情况，这种区间我们称为单点区间。而像区间 3 这种，我们可以称之为连续范围区间。

index

若一个查询满足以下条件：

查询列表中的所有列都是某个二级索引中的列（假设这个索引为索引 A）。
搜索条件中的列也都是索引 A 中的列。

那么我们就可以直接通过遍历索引 A 对应的 B+ 树的叶子节点的记录，来进行查询，只要将匹配成功二级索引记录的对应查询列表的列值直接加到结果集中即可，不用进行回表操作。

这个过程可比直接遍历聚簇索引的成本要小很多，同时这种采用遍历二级索引记录的执行方式称之为：index（也可以称之为 index 访问方法）。例如：

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

可以注意到，由于 key_part2 并不是联合索引 idx_key_part 最左索引列，所以我们无法使用 ref 或者 range 访问方法来执行这个语句。

注意：聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列，而二级索引记录只需要存放索引列和主键。

all

使用全表扫描执行查询的方式称之为：all（也可以称之为 all 访问方法）。

注意事项

重温二级索引+回表

一般情况下执行一个查询只会用到单个二级索引。例如：

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

优化器一般会根据 single_table 表的统计数据来判断到底使用哪个条件到对应的二级索引中查询扫描的行数会更少，然后到少的那个条件对应的二级索引中查询，最后从该二级索引中查询到的结果经过回表得到完整的用户记录后再根据其余的 WHERE 条件过滤记录。

注意：假设优化器决定使用 idx_key1 索引进行查询，这里再次重申，只有在完成回表操作后，才会使用查询条件key2 > 1000对结果进行过滤，而使用 idx_key1 索引进行查询时只会用到与 key1 列有关的搜索条件。

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

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

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

注意：LIKE操作符只有在匹配完整字符串或者匹配字符串前缀时才可以利用索引，而IN操作符的效果和若干个等值匹配操作符=之间用OR连接起来。

当我们想使用 range 访问方法来执行一个查询语句时，重点就是找出该查询可用的索引以及这些索引对应的范围区间，下面我们分两种情况进行讲解。

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

现在有如下 SQL 语句：

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

这个查询语句的所有搜索条件都使用到 key2 这个字段，每个搜索条件都对应着 idx_key2 唯一二级索引的一个范围区间。可以注意到，搜索条件之间用 AND 关键字连接。最终，我们可以通过这些搜索条件，确定出（搜索条件确定的范围区间取交集）使用的 idx_key2 索引的范围区间为(200, +∞)。

而对于另一个 SQL 语句：

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

这个查询语句的所有搜索条件也都使用到 key2 这个字段，每个搜索条件也都对应着 idx_key2 唯一二级索引的一个范围区间。只不过搜索条件之间用 OR 关键字连接，最终确定出（搜索条件确定的范围区间取并集）使用的 idx_key2 索引的范围区间为(100, +∞)。

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

例如查询语句如下：

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

该查询语句能利用的索引只有 idx_key2 。

因为范围区间是针对于索引提出的概念，所以在使用二级索引 idx_key2 定位记录的阶段用不到条件common_field = 'abc'。我们可以将用不到相关索引的搜索条件替换成 TRUE 。来进一步分析查询语句的范围区间。即：

SELECT * FROM single_table WHERE key2 > 100 AND TRUE;

经过分析，上述查询语句使用的索引 idx_key2 ，它的范围区间是 (100, +∞) 。

那么用不到相关索引的搜索条件在什么时候会被用到呢？

以搜索条件common_field = 'abc'为例，它将在回表获取了完整的用户记录后之后被使用。

现在我们将上述查询语句的 AND 关键字换成 OR ，得到如下 sql ：

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

同样的，我们将用不到相关索引的搜索条件置为 TRUE，可以得出使用 idx_key2 索引执行查询的范围区间为 (-∞, +∞) 。

哇，这样的话使用索引还不如直接全表扫描。

这个例子说明了，一个使用到索引的搜索条件和没有使用该索引的搜索条件使用 OR 连接起来后是无法使用该索引进行查询的。

复杂搜索条件下找出范围匹配的区间

现在有复杂搜索条件如下：

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')) ;

我们尝试来化简它。

确定可能会使用到的索引。即查看 WHERE 子句中的搜索条件都涉及到了哪些列，哪些列可能使用到索引。

明显的，可能会使用到的索引有 key1 列的普通二级索引 idx_key1 和 key2 列的唯一二级索引 idx_key2 。
对于那些可能用到的索引，分析它们的范围区间。
- 若使用索引 idx_key1 执行查询。我们把那些用不到该索引的搜索条件均置为 TRUE ，化简如下：
```
(key1 > 'xyz' AND TRUE ) OR
FALSE OR
(TRUE AND key1 > 'zzz' AND (TRUE OR TRUE))
```
  注意： key1 LIKE '%suf' 使用不到索引 idx_key1 。因为它是查找以字符串 suf 结尾的字符串，而不是以 suf 开头的。同时，key1 < 'abc' AND key1 > 'lmn'永远为 FALSE 。
  
  再次化简如下：
```
(key1 > 'xyz') OR (key1 > 'zzz')
```
  取并集，最终化简如下：
```
key1 > xyz
```
  根据最终化简结果，我们可以得出：查询语句如果使用 idx_key1 索引执行查询，需要把满足key1 > xyz的二级索引记录都取出来，然后拿着这些记录的 id 进行回表，得到完整的用户记录之后再使用其他的搜索条件进行过滤。
- 若使用索引 idx_key2 执行查询。同理，初步化简为：
```
(TRUE AND key2 = 748 ) OR
(TRUE AND TRUE) OR
(TRUE AND TRUE AND (key2 < 8000 OR TRUE))
```
  最终化简为：
```
TRUE
```
  根据最终化简结果，我们可以得出：如果查询语句使用 idx_key2 索引执行查询，则需要扫描 idx_key2 二级索引的所有记录，然后再回表。这样做相当损耗性能，所以这种情况下是不会使用 idx_key2 索引的。

索引合并

特殊情况下可能在一个查询中使用到多个二级索引，这种使用到多个索引来完成一次查询的执行方法称之为：index merge 。

具体的索引合并算法有以下三种。

Intersection 合并

Intersection 意为交集。Intersection 合并即对于某个查询，若其可以使用多个二级索引，则将会对这多个二级索引所查询到的结果取交集。

Intersection 合并适用于使用不同索引的搜索条件之间使用AND连接起来的情况，例如：

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

这个取交集是对于主键而言的。二级索引记录均由索引列+主键构成，所以对于某个查询的多个二级索引所查询到的结果我们会对他们的主键值取交集，而后根据取交集后的主键值回表取出完整的用户记录。

那么如何在Intersection 合并和只根据某个搜索条件去读取一个二级索引，然后回表后再过滤另外一个搜索条件的查询方式之间抉择呢？

我们都知道回表消耗的性能是巨大的，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高，读取多个二级索引后取交集比只读取一个二级索引的成本更低时。我们选择谁，就十分明确了。

注意：读取二级索引的操作是顺序I/O ，而回表操作是随机I/O 。

MySQL只会在两种特定的情况下使用 Intersection 索引合并：

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

特殊的，对于联合索引而言，联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

主键列可以是范围匹配。例如：

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

在解释为什么只有这两种情况可能使用 Intersection 索引合并之前，我们来看一个简单的算法。

假设有两个有序列表，如下：

list01: 1 3 5
list02: 2 3 4

我们对这两个列表求交集，过程如下：

逐个取出这两个结果集中最小的值，如果两个值相等，则加入最后的交集结果中，否则丢弃当前较小的主键值，再取该丢弃的值所在结果集的后一个值来比较，直到某个结果集中的值用完。

根据这个算法，我们可以得出对 list01 和 list02 取交集的结果集 result ，里面只包含了值 3。

重点不在这个算法的过程，而是在于它的时间复杂度，是 O(n) ，相当的快。

这个算法的要点在哪里呢？是的，在使用这个算法之前必须满足的条件是取交集的列表有序。

现在我们再看看使用 Intersection 索引合并的两种特定情况。

若二级索引列不是等值匹配而是范围匹配。那么我们通过对应的搜索条件从二级索引中取出的记录，其顺序就不会是按照主键值排序的。

我们知道，二级索引由索引列+主键构成，在二级索引列的值相同的记录中，是按照主键的值进行排序的。也就是说，等值匹配的情况下，我们从二级索引中取出的记录，是按照主键的值进行排序的。但是按照范围匹配，这一条规则就失灵了！从二级索引中取出的记录不是按照主键值进行排序的了。这种情况下，对多个二级索引的结果集取交集，若其中一个或多个二级索引的结果集是范围匹配出的，就无法使用上述的算法了，因为满足不了取交集的列表有序这一算法的要点。

同样的，对于联合索引而言，它的每个列都必须等值匹配，这是取决于它的排序规则的，即依次按照被选中的非主键列的顺序进行排序，最后按照主键值进行排序。也就是说，联合索引只有在它的非主键列都相同的时候，才会使用主键排序，即它的每个列都必须等值匹配。
主键值为何可以使用范围匹配？举个例子：
```
SELECT * FROM single_table WHERE key1 = 'a' AND id > 100;
```
假设这个查询使用了 Intersection 索引合并。

我们分别对 idx_key1 二级索引和 id 对应的聚簇索引求结果集，然后取并集，吗？

二级索引的构成就包含了主键值呀，我们可以直接把条件id > 100用在对 idx_key1 二级索引求结果集之上！

也就是说涉及主键的搜索条件只是为了从别的二级索引得到的结果集中过滤记录！那么，主键列是否是等值或范围匹配，还重要吗？不重要了。

最后，我们旧事重提一下。我们说过，上述两种情况可能使用 Intersection 索引合并，只是可能。也就是说，就算满足了上述两种情况，同时满足也好，只满足一个也好，都不一定会使用 Intersection 索引合并。这是取决于查询优化器的，查询优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过 Intersection 索引合并后需要回表的记录数大大减少时才会使用 Intersection 索引合并。

Union 合并

Union 意为并集，它适用于使用不同索引的搜索条件之间使用 OR 连接起来的情况，例如：

SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

Union 合并在三种特定情况下可能会被使用到。

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

使用 Intersection 索引合并的搜索条件。

这里我们着重说明一下，举个例子：

SELECT * FROM single_table 
-- key_part1、key_part2 和 key_part3 使用联合索引 idx_key_part 得到主键结果集01
WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' 
-- key1 和 key3 使用 Intersection 索引合并得到另一个主键结果集02
OR (key1 = 'a' AND key3 = 'b');

对主键结果集01 和主键结果集02 取并集后回表，最后将结果返回给查询的用户。

注意到，Union 索引合并也是可能会被使用到。也就是说，就算满足了上述三种情况，同时满足也好，只满足一个也好，都不一定会使用 Union 索引合并。查询优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少（因为要取并集），通过 Union 索引合并后进行访问的代价比全表扫描更小时才会使用 Union 索引合并。

Sort-Union 合并

以下查询是无法使用Union 索引合并的，即

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

因为两个搜索条件对应的二级索引查出来的二级索引记录不按主键排序。但是如果我们进行以下步骤：

先根据key1 < 'a'条件从 idx_key1 二级索引总获取记录，并按照记录的主键值进行排序。
再根据key3 > 'z'条件从 idx_key3 二级索引总获取记录，并按照记录的主键值进行排序。
进行 Union 索引合并。

这种执行方式，就是 Sort-Union 合并，即 Sort-Union 索引合并比单纯的 Union 索引合并多了一步对二级索引记录的主键值排序的过程。

Sort-Union 合并适用于单独根据搜索条件从某个二级索引中获取的记录数比较少的场景，这样按照记录的主键值进行排序成本不会太高。

那是否存在 Sort-Intersection 索引合并呢？答案是否定的，因为查询优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过 Intersection 索引合并后需要回表的记录数大大减少时才会使用 Intersection 索引合并。为大量的二级索引记录按照主键值进行排序的成本可能比回表查询成本都高，所以也就没有 Sort-Intersection 索引合并了。

索引合并注意事项

联合索引替代 Intersection 索引合并

举个例子：

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

这个查询语句可能会使用到 Intersection 索引合并，但是，为什么？我们明明用联合索引更加省事和方便，为什么要去多读一棵 B+ 树和合并结果呢？

于是，我们可以这么做。

ALTER TABLE single_table drop index idx_key1, idx_key3, add index idx_key1_key3(key1, key3);

使用联合索引 idx_key1_key3 进行上述的查询想必又快又好。

注意：一切以业务为主。若存在单独对 key3 列进行查询的业务场景，最好还是不要删除 key3 列对应的二级索引。

posted @ 2024-07-09 19:03 苏沐问阅读(22) 评论(0) 收藏举报

刷新页面返回顶部

Sumuwen

Mysql是怎样运行的：第十章笔记

Mysql是怎样运行的：第十章笔记

笔记正文

前置准备

访问方法（Access Method）的概念

const

ref

ref_or_null

range

index

all

注意事项

重温二级索引+回表

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

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

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

复杂搜索条件下找出范围匹配的区间

索引合并

Intersection 合并

Union 合并

Sort-Union 合并

索引合并注意事项

联合索引替代 Intersection 索引合并

公告

Sumuwen

Mysql是怎样运行的：第十章笔记

Mysql是怎样运行的：第十章笔记

笔记正文

前置准备

访问方法（Access Method）的概念

const

ref

ref_or_null

range

index

all

注意事项

重温 二级索引+回表

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

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

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

复杂搜索条件下找出范围匹配的区间

索引合并

Intersection 合并

Union 合并

Sort-Union 合并

索引合并注意事项

联合索引替代 Intersection 索引合并

公告

重温二级索引+回表