MySQL :: MySQL 8.3 Reference Manual :: 10.2.1.2 Range Optimization

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

对于单部分索引，索引值间隔可以方便地表示为 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');

对 key 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')
```
折叠总是 true 或 false 的条件：
- (key1 LIKE 'abcde%' OR TRUE) 总是 true
- (key1 < 'uux' AND key1 > 'z') 总是 false
将这些条件替换为常量得到：
```
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
```
删除不必要的 TRUE 和 FALSE 常量得到：
```
(key1 < 'abc') OR (key1 < 'bar')
```
将重叠间隔合并为一个得到最终用于范围扫描的条件：
```
(key1 < 'bar')
```

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

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

MySQL 不支持合并多个范围以用于 range 访问方法的空间索引。要解决这个限制，可以使用 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'
```
有关常量定义的详细信息，请参阅 Range Access Method for Single-Part Indexes。
对于 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)，它不满足原始条件。
```
(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
```
如果条件涵盖了包含在间隔中的行集，并使用 OR 组合，它们形成了涵盖行集的条件，包含在它们的间隔的并集中。如果条件使用 AND 组合，它们形成了涵盖行集的条件，包含在它们的间隔的交集中。例如，对于两个部分索引的条件：
```
(1,-inf) < (key_part1,key_part2) < (1,2)
(5,-inf) < (key_part1,key_part2)
```
间隔是：

在这个示例中，第一行的间隔使用一个键部分作为左边界，两个键部分作为右边界。第二行的间隔只使用一个键部分。key_len 列在 EXPLAIN 输出中指示了使用的最大键前缀长度。
```
key_part1 >= 1 AND key_part2 < 2
```
在某些情况下，key_len 可能表明键部分被使用，但这可能不是您所期望的。假设 key_part1 和 key_part2 可以是 NULL。那么，key_len 列将显示两个键部分的长度，对于以下条件：
```
key_part1 >= 1 AND key_part2 IS NOT NULL
```

对于如何对单个分区索引上的范围条件进行优化以组合或消除间隔的描述，请参阅单个分区索引上的范围访问方法。类似的步骤也适用于多个分区索引上的范围条件。

多值比较的相等范围优化

考虑这些表达式，其中 col_name 是一个索引列：

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

每个表达式都是 true，如果 col_name 等于多个值中的任何一个。这些比较是相等范围比较（其中““范围””是一个单值）。优化器估算了读取合格行的成本，如下所示：

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

使用索引dives，优化器在每个范围的两端进行dives，并使用范围内的行数作为估算。例如，表达式 col_name IN (10, 20, 30) 有三个相等范围，优化器对每个范围进行两次dives，以生成行估算。每对dives 生成的估算都是给定值的行数。

索引dives 提供了准确的行估算，但是随着表达式中的比较值数量的增加，优化器生成行估算所需的时间也增加。使用索引统计信息可以更快地估算行数，但不如索引dives 准确。

系统变量 eq_range_index_dive_limit 允许您配置优化器在何时从一种行估算策略切换到另一种。要允许使用索引dives 估算最多 N 个相等范围的比较，设置 eq_range_index_dive_limit 为 N + 1。要禁用统计信息的使用并总是使用索引dives，不管 N 的值，设置 eq_range_index_dive_limit 为 0。

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

在 MySQL 8.3 之前，没有办法跳过索引dives 来估算索引的有用性，除了使用 eq_range_index_dive_limit 系统变量。在 MySQL 8.3 中，可以跳过索引dives，以满足以下所有条件：

查询是针对单个表的，而不是多个表的连接。
存在单个索引的 FORCE INDEX 索引提示。该想法是，如果强制使用索引，那么没有必要再进行索引dives 的额外开销。
索引是非唯一的且不是 FULLTEXT 索引。
没有子查询。
没有 DISTINCT、GROUP BY 或 ORDER BY 子句。

对于 EXPLAIN FOR CONNECTION，如果跳过索引dives，输出将如下所示：

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

没有 FOR CONNECTION，EXPLAIN 输出不变。

在执行查询时，如果跳过索引dives，相应的行在 Information Schema 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 可以选择索引扫描以获取所有行（索引包括所有要选择的列），然后应用 f2 > 40 条件来自 WHERE 子句以生成最终结果集。

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

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

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

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

使用这种策略可以减少访问的行数，因为 MySQL 跳过了每个构建范围不合格的行。该 Skip Scan 访问方法适用于以下条件：

表 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 输出中，Skip Scan 的使用将被指示如下：

Using index for skip scan 在 Extra 列中指示松散索引 Skip Scan 访问方法被使用。
如果索引可以用于 Skip Scan，索引应该在 possible_keys 列中可见。

在优化器跟踪输出中，Skip Scan 的使用将被指示为 "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" 元素。如果 Skip Scan 被选为最佳范围访问变体，则将写入 "chosen_range_access_summary"。如果 Skip Scan 被选为总体最佳访问方法，则将存在 "best_access_path" 元素。

Skip Scan 的使用取决于 skip_scan 标志的值，该标志是 optimizer_switch 系统变量的一部分。见第 10.9.2 节，“可切换优化”。默认情况下，该标志为 on。要禁用它，请将 skip_scan 设置为 off。

除了使用 optimizer_switch 系统变量来控制优化器的 Skip Scan 使用外，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。

10.2.1.2 范围优化