范围优化

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')
```
崩溃条件始终为true或false：
- (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执行附加检查以过滤出满足范围条件但不包括full WHERE子句的行。

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

MySQL不支持range为空间索引的访问方法合并多个范围。要解决此限制，您可以UNION对相同的SELECT语句使用a ，除了将每个空间谓词放在不同的中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-part索引的每个部分都有一个条件。）例如，以下是三部分HASH索引的范围条件：
```
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
```
有关什么是常量的定义，请参见单部分索引的范围访问方法。
对于一个BTREE索引，以一定间隔可能是可用于条件组合AND，其中每个状态具有恒定值使用一个关键部分进行比较=，<=>，IS NULL，>，<，>=，<=，!=，<>，BETWEEN，或（其中LIKE 'pattern''pattern'不以通配符开头）。只要可以确定包含所有与条件匹配的行的单个键元组，就可以使用一个间隔（如果使用<>或，!=则使用两个间隔）。
只要比较运算符为，或=，优化器就会尝试使用其他关键部分来确定间隔。如果操作是，，，，，，，或者，优化器使用它，但认为没有更多的关键部分。对于以下表达式，优化器使用第一个比较中的值。它也使用<=>IS NULL><>=<=!=<>BETWEENLIKE=>=根据第二个比较，但不考虑其他关键部分，并且不将第三个比较用于区间构造：
```
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等于多个值中的任何一个，则每个表达式为true 。这些比较是相等范围比较（其中“范围”是单个值）。优化器估算读取相等行以进行相等范围比较的成本，如下所示：

如果在上有唯一索引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使用一种称为“松散扫描”的方法（类似于“通过优化进行分组”），执行多个范围扫描，每个扫描针对的一个值：

在第一个索引部分的不同值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在Extr 列表示所使用的松散索引跳跃扫描访问方法。
如果索引可用于跳过扫描，则该索引应在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标志的optimizer_switch值。请参见“可切换的优化”。默认情况下，此标志为on。要禁用它，请设置skip_scan为off。

除了在整个optimizer_switch会话范围内使用系统变量来控制优化程序的使用之外，MySQL还支持优化程序提示，以针对每个语句影响优化程序。请参见“优化器提示”。

行构造函数表达式的范围优化

优化程序可以将范围扫描访问方法应用于以下形式的查询：

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()谓词的右侧，有多个行构造器。

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

限制内存使用以进行范围优化

要控制范围优化器可用的内存，请使用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  =1 OR  =2 OR  =3 OR .. .  =N;
```
同样，对于以下查询，每个谓词组合AND使用大约125个字节：
```
SELECT COUNT(*) FROM t
WHERE  =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。