MySQL :: MySQL 9.0 参考手册 :: 10.2.1.2 范围优化

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / 范围优化

10.2.1.2 范围优化

range 访问方法使用单个索引检索包含在一个或多个索引值间隔内的表行子集。它可用于单部分或多部分索引。以下部分描述了优化器使用范围访问的条件。

单部分索引的范围访问方法

对于单部分索引，索引值间隔可以方便地用 WHERE 子句中的相应条件表示，称为范围条件而不是 “间隔”。

单部分索引的范围条件定义如下

对于 BTREE 和 HASH 索引，当使用 =、<=>、IN()、IS NULL 或 IS NOT NULL 运算符时，键部分与常量值的比较是范围条件。
此外，对于 BTREE 索引，当使用 >、<、>=、<=、BETWEEN、!= 或 <> 运算符，或者使用 LIKE 比较时（如果 LIKE 的参数是开头没有通配符的常量字符串），键部分与常量值的比较是范围条件。
对于所有索引类型，使用 OR 或 AND 组合的多个范围条件形成一个范围条件。

“常量值” 在上述描述中指以下内容之一

来自查询字符串的常量
来自相同连接的 const 或 system 表的列
非关联子查询的结果
完全由上述类型的子表达式组成的任何表达式

以下是一些在 WHERE 子句中包含范围条件的查询示例

SELECT * FROM t1
  WHERE key_col > 1
  AND key_col < 10;

SELECT * FROM t1
  WHERE key_col = 1
  OR key_col IN (15,18,20);

SELECT * FROM t1
  WHERE key_col LIKE 'ab%'
  OR key_col BETWEEN 'bar' AND 'foo';

某些非常量值可能会在优化器常量传播阶段转换为常量。

MySQL 尝试从每个可能索引的 WHERE 子句中提取范围条件。在提取过程中，无法用于构建范围条件的条件将被删除，产生重叠范围的条件将被合并，产生空范围的条件将被删除。

考虑以下语句，其中 key1 是索引列，nonkey 不是索引列

SELECT * FROM t1 WHERE
  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR
  (key1 < 'uux' AND key1 > 'z');

对于键 key1 的提取过程如下

从原始 WHERE 子句开始

(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')

删除 nonkey = 4 和 key1 LIKE '%b'，因为它们不能用于范围扫描。删除它们的正确方法是将它们替换为 TRUE，这样我们就可以在进行范围扫描时不会漏掉任何匹配的行。用 TRUE 替换它们会产生
```
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')
```
折叠始终为真或假的条件
- (key1 LIKE 'abcde%' OR TRUE) 始终为真
- (key1 < 'uux' AND key1 > 'z') 始终为假
用常量替换这些条件会产生
```
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
```
移除不必要的 TRUE 和 FALSE 常量，得到
```
(key1 < 'abc') OR (key1 < 'bar')
```
将重叠的区间合并成一个，得到用于范围扫描的最终条件
```
(key1 < 'bar')
```

一般来说（如前面的例子所示），用于范围扫描的条件比 WHERE 子句的限制性更小。MySQL 会执行额外的检查，过滤掉满足范围条件但不满足完整 WHERE 子句的行。

范围条件提取算法可以处理任意深度的嵌套 AND / OR 结构，其输出不依赖于条件在 WHERE 子句中出现的顺序。

MySQL 不支持将多个范围合并到空间索引的 range 访问方法。为了解决这个问题，可以使用带有相同 SELECT 语句的 UNION，唯一的区别在于将每个空间谓词放在不同的 SELECT 中。

多部分索引的范围访问方法

多部分索引的范围条件是单部分索引范围条件的扩展。多部分索引上的范围条件限制索引行位于一个或几个键元组区间内。键元组区间是使用索引的排序，在键元组集合上定义的。

例如，考虑一个定义为 key1(key_part1, key_part2, key_part3) 的多部分索引，以及以下按键顺序排列的键元组集合

key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

条件 key_part1 = 1 定义了此区间

(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

该区间涵盖了前面数据集中第 4、5 和 6 个元组，可以被范围访问方法使用。

相比之下，条件 key_part3 = 'abc' 不会定义单个区间，无法被范围访问方法使用。

以下描述更详细地说明了多部分索引的范围条件的工作原理。

对于 HASH 索引，可以使用包含相同值的每个区间。这意味着只有当条件具有以下形式时才能产生区间
```
    key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;
```
这里，const1、const2、… 是常量，cmp 是 =、<=> 或 IS NULL 比较运算符之一，并且这些条件涵盖所有索引部分。（也就是说，有 N 个条件，每个条件对应 N 部分索引的每个部分。）例如，以下是针对三部分 HASH 索引的范围条件
```
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
```
关于什么是常量的定义，请参见单部分索引的范围访问方法。
对于 BTREE 索引，一个区间可能适用于与 AND 组合的条件，其中每个条件都使用 =、<=>、IS NULL、>、<、>=、<=、!=、<>、BETWEEN 或 LIKE 'pattern'（其中 'pattern' 不以通配符开头）将键部分与常数值进行比较。只要能够确定包含所有匹配条件行的单个键元组（或者如果使用 <> 或 !=，则为两个区间），就可以使用区间。
优化器尝试使用额外的键部分来确定区间，只要比较运算符是 =、<=> 或 IS NULL。如果运算符是 >、<、>=、<=、!=、<>、BETWEEN 或 LIKE，优化器会使用它，但不会再考虑更多键部分，也不会使用第三个比较来构造区间
```
key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
```
单个区间是
```
('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)
```
创建的区间可能包含比初始条件更多的行。例如，前面的区间包括值 ('foo', 11, 0)，它不满足原始条件。
如果涵盖包含在区间内的行集的条件与 OR 组合，它们将形成一个涵盖包含在其区间并集内的行集的条件。如果这些条件与 AND 组合，它们将形成一个涵盖包含在其区间交集内的行集的条件。例如，对于针对双部分索引的此条件
```
(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
```
区间是
```
(1,-inf) < (key_part1,key_part2) < (1,2)
(5,-inf) < (key_part1,key_part2)
```
在本例中，第一行的区间使用一个键部分作为左边界，使用两个键部分作为右边界。第二行的区间只使用一个键部分。 EXPLAIN 输出中的 key_len 列表示所使用的键前缀的最大长度。
在某些情况下，key_len 可能表明使用了键部分，但可能不是您期望的那样。假设 key_part1 和 key_part2 可以是 NULL。然后，key_len 列将显示以下条件的两个键部分长度
```
key_part1 >= 1 AND key_part2 < 2
```
但是，实际上，该条件被转换为
```
key_part1 >= 1 AND key_part2 IS NOT NULL
```

有关针对单部分索引的范围条件如何执行优化来组合或消除区间的说明，请参见单部分索引的范围访问方法。针对多部分索引的范围条件会执行类似的步骤。

多值比较的等值范围优化

考虑以下表达式，其中 col_name 是索引列

col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN

如果 col_name 等于几个值中的任何一个，则每个表达式都为真。这些比较是等值范围比较（其中“范围”是单个值）。优化器按如下方式估计针对等值范围比较读取合格行的成本

如果 col_name 上存在唯一索引，则每个范围的行估计值为 1，因为最多只有一行可以具有给定值。
否则，col_name 上的任何索引都是非唯一的，优化器可以使用索引深入或索引统计信息来估计每个范围的行计数。

使用索引深入，优化器会在每个范围的末尾进行深入，并使用范围中的行数作为估计值。例如，表达式 col_name IN (10, 20, 30) 具有三个等值范围，优化器会在每个范围上进行两次深入以生成行估计值。每次深入对都会产生一个估计值，该估计值表示具有给定值的行的数量。

索引深入提供了准确的行估计值，但随着表达式中比较值的数量增加，优化器生成行估计值所需的时间也随之增加。使用索引统计信息不如索引深入准确，但允许针对大型值列表进行更快的行估计。

系统变量 eq_range_index_dive_limit 使您能够配置优化器从一种行估计策略切换到另一种行估计策略的值数量。要允许使用索引深入进行最多 N 个等值范围的比较，请将 eq_range_index_dive_limit 设置为 N + 1。要禁用统计信息的用法并始终使用索引深入，而不管 N 的值如何，请将 eq_range_index_dive_limit 设置为 0。

要更新表索引统计信息以获得最佳估计值，请使用 ANALYZE TABLE。

在 MySQL 9.0 之前，除了使用系统变量 eq_range_index_dive_limit 之外，没有办法跳过使用索引深入来估计索引的实用性。在 MySQL 9.0 中，对于满足以下所有条件的查询，可以跳过索引深入

该查询针对单个表，而不是针对多个表的联接。
存在单个索引 FORCE INDEX 索引提示。其理念是，如果强制使用索引，则通过执行索引深入来获得额外开销没有任何好处。
该索引是非唯一的，也不是 FULLTEXT 索引。
不存在子查询。
不存在 DISTINCT、GROUP BY 或 ORDER BY 子句。

对于 EXPLAIN FOR CONNECTION，如果跳过索引深入，输出将发生以下变化

对于传统输出，rows 和 filtered 值为 NULL。
对于 JSON 输出，rows_examined_per_scan 和 rows_produced_per_join 不会出现，skip_index_dive_due_to_force 为 true，并且成本计算不准确。

如果没有 FOR CONNECTION，当跳过索引深入时，EXPLAIN 输出不会发生变化。

在执行跳过索引深入的查询后，Information Schema OPTIMIZER_TRACE 表中相应的行包含值为 skipped_due_to_force_index 的 index_dives_for_range_access。

跳过扫描范围访问方法

考虑以下场景

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

要执行此查询，MySQL 可以选择索引扫描来获取所有行（索引包含要选择的全部列），然后将 WHERE 子句中的 f2 > 40 条件应用于生成的最终结果集。

范围扫描比全索引扫描效率更高，但在这种情况下无法使用，因为第一个索引列 f1 没有条件。优化器可以使用称为跳跃扫描的方法执行多个范围扫描，每个 f1 值一个，这类似于松散索引扫描（参见第 10.2.1.17 节，“GROUP BY 优化”）。

在第一个索引部分 f1（索引前缀）的不同值之间跳跃。
对每个不同的前缀值执行子范围扫描，以满足剩余索引部分的 f2 > 40 条件。

对于前面显示的数据集，算法的操作方式如下。

获取第一个键部分的第一个不同值 (f1 = 1)。
基于第一个和第二个键部分构建范围 (f1 = 1 AND f2 > 40)。
执行范围扫描。
获取第一个键部分的下一个不同值 (f1 = 2)。
基于第一个和第二个键部分构建范围 (f1 = 2 AND f2 > 40)。
执行范围扫描。

使用这种策略可以减少访问的行数，因为 MySQL 会跳过不符合每个构建范围的那些行。此跳跃扫描访问方法在以下情况下适用。

表 T 至少包含一个复合索引，其键部分的形式为 ([A_1, ..., A_k,] B_1, ..., B_m, C [, D_1, ..., D_n])。键部分 A 和 D 可以为空，但 B 和 C 必须非空。
查询只引用一个表。
查询不使用 GROUP BY 或 DISTINCT。
查询只引用索引中的列。
对 A_1, ..., A_k 的谓词必须是等值谓词，并且必须是常量。这包括 IN() 运算符。
查询必须是连接查询；即，AND 的 OR 条件：(cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR ...) AND ...
必须存在对 C 的范围条件。
允许对 D 列进行条件。对 D 的条件必须与对 C 的范围条件结合使用。

在 EXPLAIN 输出中指示使用跳跃扫描的方式如下。

Using index for skip scan 在 Extra 列中表示使用松散索引跳跃扫描访问方法。
如果索引可用于跳跃扫描，则该索引应该在 possible_keys 列中可见。

在优化器跟踪输出中，通过此形式的 "skip scan" 元素指示使用跳跃扫描。

"skip_scan_range": {
  "type": "skip_scan",
  "index": index_used_for_skip_scan,
  "key_parts_used_for_access": [key_parts_used_for_access],
  "range": [range]
}

您也可能看到 "best_skip_scan_summary" 元素。如果跳跃扫描被选为最佳范围访问变体，则会写入 "chosen_range_access_summary"。如果跳跃扫描被选为总体最佳访问方法，则会存在 "best_access_path" 元素。

使用跳跃扫描受 skip_scan 标志的值控制，该标志是 optimizer_switch 系统变量的一部分。参见第 10.9.2 节，“可切换优化”。默认情况下，此标志为 on。要禁用它，请将 skip_scan 设置为 off。

除了使用 optimizer_switch 系统变量在会话范围内控制优化器对跳跃扫描的使用之外，MySQL 还支持优化器提示，以在每个语句的基础上影响优化器。参见第 10.9.3 节，“优化器提示”。

行构造表达式范围优化

优化器能够将范围扫描访问方法应用于此形式的查询。

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

以前，要使用范围扫描，必须将查询写成如下形式。

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );

为了让优化器使用范围扫描，查询必须满足以下条件。

只使用 IN() 谓词，不使用 NOT IN()。
在 IN() 谓词的左侧，行构造器只包含列引用。
在 IN() 谓词的右侧，行构造器只包含运行时常量，这些常量要么是字面量，要么是局部列引用，在执行期间被绑定到常量。
在 IN() 谓词的右侧，存在多个行构造器。

有关优化器和行构造器的更多信息，参见第 10.2.1.22 节，“行构造表达式优化”。

限制范围优化内存使用

要控制范围优化器可用的内存，请使用 range_optimizer_max_mem_size 系统变量。

值为 0 表示 “无限制。”
如果值大于 0，则优化器会在考虑范围访问方法时跟踪所使用的内存。如果即将超过指定的限制，则会放弃范围访问方法，而是考虑其他方法，包括全表扫描。这可能不太理想。如果发生这种情况，则会发出以下警告（其中 N 是当前的 range_optimizer_max_mem_size 值）。
```
Warning    3170    Memory capacity of N bytes for
                   'range_optimizer_max_mem_size' exceeded. Range
                   optimization was not done for this query.
```
对于 UPDATE 和 DELETE 语句，如果优化器回退到全表扫描，并且启用了 sql_safe_updates 系统变量，则会发生错误，而不是警告，因为实际上没有使用任何键来确定要修改哪些行。有关更多信息，参见使用安全更新模式 (--safe-updates)。

对于超出可用范围优化内存的单个查询，以及优化器回退到不太理想的计划的查询，增加 range_optimizer_max_mem_size 值可能会提高性能。

要估计处理范围表达式所需的内存量，请使用以下指南。

对于以下简单的查询，其中只有一个候选键用于范围访问方法，每个谓词与 OR 结合使用大约 230 字节。
```
SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
```
类似地，对于以下查询，每个谓词与 AND 结合使用大约 125 字节。
```
SELECT COUNT(*) FROM t
WHERE a=1 AND b=1 AND c=1 ... N;
```
对于包含 IN() 谓词的查询。
```
SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);
```
在 IN() 列表中的每个字面量值都算作一个与 OR 结合使用的谓词。如果有两个 IN() 列表，则与 OR 结合使用的谓词数量是每个列表中字面量值数量的乘积。因此，在上例中，与 OR 结合使用的谓词数量为 M × N。

PREV HOME UP NEXT