8.2.1.2 范围优化_MySQL 8.0 参考手册

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

对于单项索引，索引取值区间可以方便地用 WHERE子句中对应的条件来表示，记为范围条件而不是“区间”。”

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

对于BTREE和索引，使用, , , , or运算符HASH时，关键部分与常量值的比较是范围条件。 =<=>IN()IS NULLIS NOT NULL
此外，对于索引，使用、、、、、或运算符BTREE时，关键部分与常量值的比较是范围条件，或者如果参数 to 是不以通配符开头的常量字符串则进行比较。 ><>=<=BETWEEN!=<>LIKELIKE
对于所有索引类型，多个范围条件组合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')
```
折叠始终为真或假的条件：
- (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为空间索引的访问方法合并多个范围。要解决此限制，您可以使用UNIONwith identical SELECTstatements，只是您将每个空间谓词放在不同的 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元组，可以被range access方法使用。

相比之下，该条件 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-part 索引的每个部分一个。）例如，以下是一个三部分 HASH索引的范围条件：
```
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
```
有关常量的定义，请参阅单部分索引的范围访问方法。
对于索引， BTREE区间可能可用于与组合的条件，其中 AND每个条件使用 =、、、、、、、、、、、、或（其中 <=>IS NULL><>=<=!=<>BETWEENLIKE 'pattern''pattern' 不以通配符开头）。只要可以确定包含与条件匹配的所有行的单个键元组（或者如果使用<> or!= 则为两个间隔），就可以使用间隔。
只要比较运算符是、或，优化器就会尝试使用额外的关键部分来 =确定 <=>区间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)
```
在这个例子中，第一行的间隔使用一个关键部分作为左边界，两个关键部分作为右边界。第二行的区间只用了一个关键部分。输出中的key_len列EXPLAIN指示使用的键前缀的最大长度。
在某些情况下，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.0 之前，没有办法跳过使用索引潜水来估计索引有用性，除非使用 eq_range_index_dive_limit 系统变量。在 MySQL 8.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 表中相应的行包含 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第一个索引列没有条件。然而，从 MySQL 8.0.13 开始，优化器可以执行多个范围扫描，一个用于每个值f1，使用一种称为 Skip Scan 的方法，该方法类似于松散索引扫描（请参阅第 8.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 BYor 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表示使用松散索引Skip Scan访问方法。
如果索引可以用于 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"则写入 a。如果选择跳过扫描作为整体最佳访问方法， "best_access_path"则存在一个元素。

Skip Scan 的使用取决于系统变量skip_scan标志的值。optimizer_switch请参阅第 8.9.2 节，“可切换优化”。默认情况下，此标志为on. 要禁用它，请设置skip_scan为 off。

除了使用 optimizer_switch系统变量来控制优化器在整个会话范围内使用跳过扫描之外，MySQL 还支持优化器提示以在每个语句的基础上影响优化器。请参阅第 8.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()，有多个行构造函数。

有关优化器和行构造函数的更多信息，请参阅第 8.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.
```
对于UPDATEand 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。