8.2.1.2 范围优化_MySQL 5.7 参考手册

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

对于单项索引，索引取值区间可以方便地用 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)具有三个相等范围，优化器对每个范围进行两次潜水以生成行估计。每对潜水都会产生具有给定值的行数的估计值。

索引潜水提供准确的行估计，但随着表达式中比较值数量的增加，优化器需要更长的时间来生成行估计。索引统计的使用不如索引潜水准确，但允许对大值列表进行更快的行估计。

The eq_range_index_dive_limit system variable enables you to configure the number of values at which the optimizer switches from one row estimation strategy to the other. To permit use of index dives for comparisons of up to N equality ranges, set eq_range_index_dive_limit to N + 1. To disable use of statistics and always use index dives regardless of N, set eq_range_index_dive_limit to 0.

To update table index statistics for best estimates, use ANALYZE TABLE.

Even under conditions when index dives would otherwise be used, they are skipped for queries that satisfy all these conditions:

A single-index FORCE INDEX index hint is present. The idea is that if index use is forced, there is nothing to be gained from the additional overhead of performing dives into the index.
The index is nonunique and not a FULLTEXT index.
No subquery is present.
No DISTINCT, GROUP BY, or ORDER BY clause is present.

Those dive-skipping conditions apply only for single-table queries. Index dives are not skipped for multiple-table queries (joins).

Range Optimization of Row Constructor Expressions

The optimizer is able to apply the range scan access method to queries of this form:

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

Previously, for range scans to be used, it was necessary to write the query as:

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );

For the optimizer to use a range scan, queries must satisfy these conditions:

Only IN() predicates are used, not NOT IN().
On the left side of the IN() predicate, the row constructor contains only column references.
On the right side of the IN() predicate, row constructors contain only runtime constants, which are either literals or local column references that are bound to constants during execution.
On the right side of the IN() predicate, there is more than one row constructor.

For more information about the optimizer and row constructors, see Section 8.2.1.19, “Row Constructor Expression Optimization”

Limiting Memory Use for Range Optimization

To control the memory available to the range optimizer, use the range_optimizer_max_mem_size system variable:

A value of 0 means “no limit.”
With a value greater than 0, the optimizer tracks the memory consumed when considering the range access method. If the specified limit is about to be exceeded, the range access method is abandoned and other methods, including a full table scan, are considered instead. This could be less optimal. If this happens, the following warning occurs (where N is the current range_optimizer_max_mem_size value):
```
Warning    3170    Memory capacity of N bytes for
                   'range_optimizer_max_mem_size' exceeded. Range
                   optimization was not done for this query.
```
For UPDATE and DELETE statements, if the optimizer falls back to a full table scan and the sql_safe_updates system variable is enabled, an error occurs rather than a warning because, in effect, no key is used to determine which rows to modify. For more information, see Using Safe-Updates Mode (--safe-updates).

For individual queries that exceed the available range optimization memory and for which the optimizer falls back to less optimal plans, increasing the range_optimizer_max_mem_size value may improve performance.

To estimate the amount of memory needed to process a range expression, use these guidelines:

For a simple query such as the following, where there is one candidate key for the range access method, each predicate combined with OR uses approximately 230 bytes:
```
SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
```
Similarly for a query such as the following, each predicate combined with AND uses approximately 125 bytes:
```
SELECT COUNT(*) FROM t
WHERE a=1 AND b=1 AND c=1 ... N;
```
For a query with IN() predicates:
```
SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);
```
列表中的每个字面值都 IN()算作一个与组合的谓词OR。如果有两个IN() 列表，则谓词 OR的数量是每个列表中文字值数量的乘积。OR因此，在前面的情况中结合的谓词数是M× N。

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