MySQL 8.0 官方文档第八章优化（三）—— 范围优化

最新推荐文章于 2023-10-31 11:41:16 发布

HZ_Samuel

最新推荐文章于 2023-10-31 11:41:16 发布

阅读量1k

点赞数

文章标签： mysql优化 MySQL 8.0

本文链接：https://blog.csdn.net/qq_39785418/article/details/116331534

版权

第八章优化（三）—— 范围优化

8.2 优化SQL语句

8.2.1 优化 SELECT 语句

8.2.1.2 范围优化

范围访问方法就是利用单索引检索包含在一个或多个索引值间隔范围内的表中行的子集。它可以用于单列索引或多列复合索引。以下章节讲述优化器使用范围访问的条件。

单列索引的范围访问方法

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

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

对于BTREE（B树）和哈希索引来说，当使用 =、<=>、IN()、IS NULL或 IS NOT NULL运算符时，键部分与常量值的比较就是一个范围条件。
此外，对于BTREE索引而言，当使用>、<、>=、<=、BETWEEN、!= 或 <>运算符时，或者使用LIKE运算符，并且比较的参数不是以通配符开头的常量字符串时，键部分与常量值的比较也是一个范围条件。
对于所有索引类型来说，使用 OR或 AND连接的多个范围条件形成一个新的范围条件。

以上说明中的“常量值”是指下列情况之一：

查询中字符串常量
常量列或相同表连接中的系统表
不关联子查询的结果
完全由上述类型的子表达式组成的任何表达式

以下是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)结果总是 true。
- (key1 < 'uux' AND key1 > 'z')结果总是 false。
使用常量结果替换这些条件：
```
(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子句中出现的顺序。

对于空间索引（spatial indexes）的范围访问方法，MySQL不支持合并多个范围。为了解决这个限制，可以将UNION与相同的SELECT语句一起使用，只是要将每个空间谓词放在不同的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定义了这个间隔：

# 译者：inf应该表示无穷，-inf负无穷，+inf正无穷
(1, -inf, -inf) <= (key_part1, key_part2, key_part3) < (1, +inf, +inf)

该区间涵盖了上述数据集中的第4、5、6个元组，可用于范围访问方法。

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

下面的描述更详细地说明了范围条件如何用于多列索引。

对于哈希索引，可以使用包含相同值的每个间隔。这意味着只能针对以下形式的条件生成间隔：
```
    key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;
```
这里，const1、const2、…、constN是常量，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)
```
在本例中，第一行的间隔使用一个键部分作为左边界（译者：就是 1），两个键部分作为右边界（译者：就是 2）。第二行的间隔只使用一个键部分。EXPLAIN输出中的键长度（key_len）列提示所用key前缀的最大长度。

在某些情况下，key_len可能提示使用了一个key部分，但这可能不是您所期望的。假设key_part1和 key_part2可以为 NULL，那么 key_len列显示以下条件下的两个key部分的长度：

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等于几个值中的任何一个，则每个表达式都为真。这些比较是相等范围比较（其中“range”（范围）是单个值）。优化器会评估为进行相等范围比较而读取符合条件的行的成本，具体如下：

如果col_name上有唯一的索引，那么每个范围中的行估计是1，因为最多只能有一行符合给定的值。
否则，col_name上的任何索引都是非惟一的，优化器可以通过下潜索引（译者：通过沿着索引树向下查找），或从索引统计信息中来估计每个范围中的行数。

使用索引下潜（index dives 译者：沿着索引树向下查找操作），优化器在一个范围的每个末端都进行一次下潜，并使用范围中的行数作为估计值。例如，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），如果跳过索引下潜，输出将发生如下变化：

对于传统的输出，行数和过滤值是NULL。
对于JSON输出，不显示rows_examined_per_scan（译者：每次扫描被检查过的行）和rows_produced_per_join（译者：每次连接产生的行）, skip_index_dive_due_to_force（译者：由于强制而跳过索引下潜）为真，成本计算不准确。

如果没有表连接，当跳过索引下潜时，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条件来生成最终的结果集。

在这里插入图片描述

备注：译者使用的版本是：MySQL 8.0.24.0。

范围扫描比完全索引扫描更有效，但在这种情况下不能使用，因为在第一个索引列 f1 上没有条件。但是，从MySQL 8.0.13开始，优化器可以执行多个范围扫描，即对 f1 的每个值扫描一次，使用一种叫做Skip Scan（译者：跳跃扫描）的方法，类似于松散索引扫描（见8.2.1.17节，“分组优化”）：

在第一个索引列f1（索引前缀）的不同值之间跳跃。
在其他的索引列上，根据条件f2 > 40，对每个不同的前缀值执行子范围扫描。

对于前面看到的数据集，算法操作方法如下：

得到第一个键部分（f1 = 1）（译者：即复合索引中的第一个列）的第一个不同的值。
根据第一个和第二个键部分（f1 = 1 AND f2 > 40），构建查询范围。
执行范围扫描。
得到第一个键部分的下一个不同的值。
根据第一个和第二个键部分（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（在跳跃扫描中使用了索引）提示使用了松散跳跃扫描范围方法。
如果该索引能被用作跳跃扫描，该索引应该在possible_keys（译者：可能的键）列中看到。

在优化器跟踪输出中，使用以下形式的“跳跃扫描”元素表示使用了跳跃扫描：

"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节，“Switchable Optimizations（可切换优化）”。默认情况下，这个标志是开启的。要禁用它，请将skip_scan设置为off。

除了使用optimizer_switch系统变量来控制整个会话范围内Skip Scan优化器的使用之外，MySQL还支持优化器的提示来影响作用在每条语句上的优化器。参见8.9.3节，“Optimizer Hints（优化提示）”。

行构造器表达式的范围优化

优化器能够将范围扫描访问方法应用于此表单的查询：

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 ... AND n = 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。

上一章 MySQL 8.0 官方文档第八章优化（二）——优化 SELECT 语句

下一章 MySQL 8.0 官方文档第八章优化（四）—— 索引合并优化

HZ_Samuel

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
MySQL 8.0 官方文档第八章优化（三）—— 范围优化

目录第八章优化（三）—— Range（范围）优化8.2 优化SQL语句8.2.1 优化 SELECT 语句8.2.1.2 Range（范围）优化单列索引的范围访问方法多列复合索引范围访问方法第八章优化（三）—— Range（范围）优化8.2 优化SQL语句8.2.1 优化 SELECT 语句8.2.1.2 Range（范围）优化范围（range）访问方法使用单个索引来检索出包含在一个或多个索引值间隔范围内的表中记录行的子集。它可以用于单列索引或多列复合索引。以下部分描述优化器使用范围访问
复制链接

扫一扫