8.2.1.2 范围优化

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

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

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

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

对于BTREE和HASH索引，使用=，<=>，IN()，IS NULL或不为空运算符时，将关键部分与常量值进行比较是一个范围条件。
此外，对于BTREE索引，使用>，<，>=，<=，BETWEEN，!=或<>运算符时，将关键部分与常量值进行比较是一个范围条件；如果LIKE的参数为常量字符串，则进行LIKE比较不能以通配符开头。
对于所有索引类型，将多个范围条件与OR或AND组合在一起可形成范围条件。

前面的描述中的“常数”table 示以下之一：

查询字符串中的常量
来自同一联接的const或systemtable 中的列
不相关子查询的结果
任何完全由上述类型的子 table 达式组成的 table 达式

以下是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 执行附加检查以过滤出满足范围条件但不完整的WHERE子句的行。

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

MySQL 不支持为空间索引的range访问方法合并多个范围。要解决此限制，可以将UNION与相同的SELECT语句一起使用，除了将每个空间谓词放在不同的SELECT中。

Multipart 索引的范围访问方法

Multipart 索引的范围条件是单部分索引的范围条件的扩展。Multipart 索引上的范围条件将索引行限制在一个或几个键 Tuples 间隔内。使用从索引开始的 Sequences，在一组键 Tuples 上定义键 Tuples 间隔。

例如，考虑定义为key1(key_part1, key_part2, key_part3)的 Multipart 索引，并按键 Sequences 列出以下一组键 Tuples：

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 个 Tuples，并且可以由范围访问方法使用。

相反，条件key_part3 = 'abc'并未定义单个间隔，并且不能被范围访问方法使用。

以下描述更详细地说明了范围条件如何作用于 Multipart 索引。

对于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'不能以通配符开头)。只要可以确定包含所有与条件匹配的行的单个键 Tuples，就可以使用一个间隔(如果使用<>或!=，则可以使用两个间隔)。

只要比较运算符是=，<=>或IS NULL，优化器就会尝试使用其他关键部分来确定间隔。如果运算符是>，<，>=，<=，!=，<>，BETWEEN或LIKE，则优化器将使用它，但不再考虑其他关键部分。对于以下 table 达式，优化器使用第一个比较中的=。它还从第二次比较中使用>=，但不考虑其他关键部分，并且不将第三次比较用于间隔构造：

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列 table 示所使用的密钥前缀的最大长度。

在某些情况下，key_len可能 table 明已使用了关键部件，但这可能不是您期望的。假设* key_part1 和 key_part2 *可以是NULL。然后key_len列显示以下条件的两个关键 Component 长度：

key_part1 >= 1 AND key_part2 < 2

但是，实际上，条件已转换为：

key_part1 >= 1 AND key_part2 IS NOT NULL

有关如何执行优化以合并或消除单部分索引上范围条件的间隔的描述，请参见单部分索引的范围访问方法。针对 Multipart 索引上的范围条件执行类似的步骤。

多值比较的均等范围优化

考虑以下 table 达式，其中* col_name *是索引列：

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

如果* col_name *等于多个值中的任何一个，则每个 table 达式为 true。这些比较是相等范围比较(其中“范围”是单个值)。优化器估算读取相等行以进行相等范围比较的成本，如下所示：

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

使用索引潜水时，优化器在范围的每个末端进行潜水，并将范围中的行数用作估计值。例如，table 达式col_name IN (10, 20, 30)具有三个相等范围，并且优化程序对每个范围进行两次潜水以生成行估计。每对潜水都会得出具有给定值的行数的估计值。

索引潜水可提供准确的行估计，但是随着 table 达式中比较值的数量增加，优化器将花费更长的时间来生成行估计。使用索引统计信息的准确性不如使用索引统计法准确，但允许对大型值列 table 进行更快的行估计。

eq_range_index_dive_limit系统变量使您可以配置优化器从一种行估计策略切换到另一种行估计策略的值的数量。要允许索引潜水用于最多* N 相等范围的比较，请将eq_range_index_dive_limit设置为 N 1.要禁用统计信息的使用，并且无论 N *始终使用索引潜水，请将eq_range_index_dive_limit设置为 0.

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

即使在本应使用索引潜水的条件下，对于满足所有这些条件的查询也将跳过它们：

存在单索引FORCE INDEX索引提示。这样的想法是，如果强制使用索引，那么执行潜入索引的额外开销将无济于事。
索引不是唯一的，不是FULLTEXT索引。
没有子查询。
没有DISTINCT，GROUP BY或ORDER BY子句。

这些跳水条件仅适用于单 table 查询。对于多 table 查询(联接)，不会跳过索引潜水。

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

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

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()谓词的右侧，行构造函数仅包含运行时常量，这些常量是在执行期间绑定到常量的 Literals 或本地列引用。
在IN()谓词的右侧，有多个行构造器。

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

限制内存用于范围优化

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

值 0table 示“无限制”。
值大于 0 时，优化程序将在考虑范围访问方法时跟踪消耗的内存。如果将要超出指定的限制，则将放弃范围访问方法，而改为考虑其他方法，包括全 table 扫描。这可能不太理想。如果发生这种情况，则会发生以下警告(其中* 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语句，如果优化器退回到全 table 扫描并且启用了sql_safe_updates系统变量，则会发生错误而不是警告，因为实际上，没有键用于确定要修改的行。有关更多信息，请参见使用安全更新模式(-安全更新)。

对于超出可用范围优化内存的单个查询，并且对于该查询，优化器后退到次优计划，增大range_optimizer_max_mem_size值可以提高性能。

若要估计处理范围 table 达式所需的内存量，请使用以下准则：

对于如下所示的简单查询，其中存在一个用于范围访问方法的候选键，每个与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()列 table 中的每个 Literals 值都算作与OR组合的谓词。如果有两个IN()列 table，则谓词与OR组合的数量是每个列 table 中 Literals 值数量的乘积。因此，在前一种情况下与OR组合的谓词数为* M × * N *。

在 5.7.11 之前，每个谓词与OR相结合的字节数更高，大约为 700 个字节。

Name	MySql 中文文档
Version
Language
Badge
Last Updated	2021-07-03T12:24:35