8.2.1.2 范围优化

最新推荐文章于 2024-06-02 14:54:06 发布

独上西楼影三人

最新推荐文章于 2024-06-02 14:54:06 发布

阅读量344

点赞数

分类专栏：《MySQL 8.0 参考手册》中文翻译 # 第 8 章优化文章标签：数据库 sql mysql MySQL 8.0 SQL 优化

本文链接：https://blog.csdn.net/wudi53433927/article/details/129033876

版权

《MySQL 8.0 参考手册》中文翻译同时被 2 个专栏收录

40 篇文章 8 订阅

订阅专栏

第 8 章优化

13 篇文章 0 订阅

订阅专栏

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

单列索引的范围访问方法
多列索引的范围访问方法
多值比较的等值范围优化
跳跃扫描范围访问方法
行构造函数表达式的范围优化
限制范围优化的内存使用

单列索引的范围访问方法

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

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

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

上述描述中的“常量值”是指以下值之一：

查询字符串中的常量
来自同一联接的常量（constant）或系统（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')
```
折叠永远为真或假的条件：
- (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 不支持为空间索引的范围访问方法合并多个范围。为了解决这个限制，除了将每个空间谓词放在不同的 SELECT 中，您可以使用 UNION 和相同的 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 部分索引的每个部分有一个条件。）例如，以下是三部分 HASH 索引的范围条件：
```
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
```
有关被视为常量的定义，请参阅单列索引的范围访问方法。

对于 BTREE 索引，间隔可能适用于与 AND 组合的条件，其中每个条件使用 = 、<=> 、 IS NULL 、> 、< 、>= 、<= 、!= 、<> 将关键部分与常量值进行比较，BETWEEN 或 LIKE 'pattern'（其中 'pattern' 不以通配符开头）。只要可以确定包含所有符合条件的行的单键元组，就可以使用间隔（如果使用 <> 或 != ，则使用两个间隔）。

只要比较运算符为 = 、<=> 或 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)
```
在本例中，第一行的间隔使用一个键列作为左边界，两个键列用于右边界。第二行的间隔仅使用一个键列。EXPLAIN 输出中的 key_len 列表示使用的键前缀的最大长度。

在某些情况下，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 索引提示（hint）。其想法是，如果强制使用索引，那么对索引执行下潜操作的额外开销将不会带来任何好处。
该索引不是唯一的，并且不是 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 表中的相应行包含 skipped_due_to_force_index 的 index_dives_for_range_access 值。

跳跃扫描范围访问方法

考虑如下场景：

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 可以选择索引扫描来获取所有行（索引包括要查询的所有列），然后从 WHERE 子句应用 f2>40 条件来生成最终结果集。

范围扫描比全索引扫描更有效，但在这种情况下不能使用，因为第一个索引列 f1 上没有条件。但是，从 MySQL 8.0.13 开始，优化器可以使用名为 跳跃扫描（ Skip Scan ） 的方法执行多个范围扫描，为每个 f1 值执行一次，该方法类似于 宽松索引扫描（Loose Index Scan）（参见第 8.2.1.17 节“ GROUP BY Optimization”）：

在第一个索引列 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 () 运算符。
查询必须是联合查询；即 OR 条件的 AND ：(cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR ...) AND ...
C 上必须有范围条件。
允许 D 列上的条件。D 上的条件必须与 C 上的范围条件相结合。

EXPLAIN 输出中指示使用跳过扫描，如下所示：

在 Extra 字段的 Using index for skip scan 表示使用了宽松索引跳跃扫描访问方法（access method）。
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”。如果选择跳跃扫描作为总体最佳访问方法，则会出现“best_access_path” 元素。

跳跃扫描的使用取决于 optimizer_switch 系统变量的 Skip_Scan 标志的值。参见第 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.
```
对于 UPDATE 和 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 。

独上西楼影三人

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
8.2.1.2 范围优化

范围访问方法使用单个索引来检索包含在一个或多个索引值间隔内的表行子集。它可以用于单个部分或多个部分索引。以下部分描述优化器使用范围访问的条件。
复制链接

扫一扫

专栏目录