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

版本 8.4

9.0
8.0
5.7

8.0 日语

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

10.2.1.2 范围优化

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

单列索引的范围访问方法

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

单列索引的范围条件定义如下：

对于 BTREE 和 HASH 索引，当使用 =、<=>、IN()、IS NULL 或 IS NOT NULL 运算符时，将键部分与常量值进行比较是一个范围条件。
此外，对于 BTREE 索引，当使用 >、<、>=、<=、BETWEEN、!= 或 <> 运算符，或者如果 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 8.4 之前，除了使用 eq_range_index_dive_limit 系统变量之外，无法跳过使用索引探测来估计索引的有效性。在 MySQL 8.4 中，对于满足以下所有条件的查询，可以跳过索引探测

查询针对单个表，而不是针对多个表的联接。
存在单索引 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 输出不会更改。

在执行跳过索引探测的查询之后，信息架构 OPTIMIZER_TRACE 表中的对应行包含 index_dives_for_range_access 值 skipped_due_to_force_index。

跳跃扫描范围访问方法

考虑以下场景

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 上没有条件。优化器可以使用一种称为“跳跃扫描”（Skip Scan）的方法对 f1 的每个值执行多次范围扫描，该方法类似于“松散索引扫描”（Loose Index Scan）（请参阅第 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() 运算符。
查询必须是 conjunctive 查询；也就是说，是 AND 和 OR 条件的组合：(cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR ...) AND ...
C 上必须有一个范围条件。
允许对 D 列进行条件限制。D 上的条件必须与 C 上的范围条件结合使用。

EXPLAIN 输出中使用以下指示来表示“跳跃扫描”的使用：

Extra 列中的 Using index for skip scan 表示使用了松散索引“跳跃扫描”访问方法。
如果索引可用于“跳跃扫描”，则该索引应在 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" 元素。

“跳跃扫描”的使用取决于 optimizer_switch 系统变量的 skip_scan 标志的值。请参阅第 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。