8.2.1.2 范围优化

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

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

对于单部分索引，索引值间隔可以方便地由WHERE子句中的相应条件 table 示，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的提取过程如下：

(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')

(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(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 索引。

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'

有关被认为是常量的定义，请参见单部分索引的范围访问方法。

只要比较运算符是=，<=>或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)。

(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。这些比较是相等范围比较(其中“范围”是单个值)。优化器估算读取相等行以进行相等范围比较的成本，如下所示：

使用索引潜水时，优化器在范围的每个末端进行潜水，并将范围中的行数用作估计值。例如，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。

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

这些跳水条件仅适用于单 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' );

为了使优化器使用范围扫描，查询必须满足以下条件：

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

限制内存用于范围优化

要控制范围优化器可用的内存，请使用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.

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

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

SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;

SELECT COUNT(*) FROM t
WHERE a=1 AND b=1 AND c=1 ... N;

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 个字节。

首页