8.2.1 优化 SELECT 语句

8.2.1.2 范围优化

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

单列索引的范围访问方法

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

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

• 对于BTREE（B树）和哈希索引来说，当使用 运算符时，键部分与常量值的比较就是一个范围条件。

• 此外，对于BTREE索引而言，当使用运算符时，或者使用运算符，并且比较的参数不是以通配符开头的常量字符串时，键部分与常量值的比较也是一个范围条件。

• 对于所有索引类型来说，使用 或 连接的多个范围条件形成一个新的范围条件。

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

• 查询中字符串常量

• 常量列或相同表连接中的系统表

• 不关联子查询的结果

• 完全由上述类型的子表达式组成的任何表达式

以下是子句中带有范围条件的查询示例：

在优化器展开常量阶段，有些非常量值可能会转化为常量值。

MySQL尝试从子句的每个可能的索引中提取范围条件。在提取过程中，删除不能用于构造范围条件的条件，合并产生重叠范围的条件，并删除产生空范围的条件。

考虑以下语句，其中是索引列，而没有建立索引：

的提取过程如下：

1. 从原始子句开始：

2. 删除 和两个表达式，因为它们不能用于范围扫描。删除它们的正确方法是将它们替换为，这样在执行范围扫描时就不会丢失任何匹配的行。用代替它们：

3. 对结果总是或的条件进行合并计算：

   • 结果总是 true。
   • 结果总是 false。

   使用常量结果替换这些条件：

   删除不必要的 和 常量结果后：

4. 将重叠的间隔组合成一个会产生用于范围扫描的最终条件:

一般来说（如上例所示），用于范围扫描的条件比子句的限制要小。MySQL执行额外的检查以过滤出满足范围条件但不满足完整子句的行。

范围条件提取算法可以处理任意深度的嵌套结构，其输出不依赖于条件在子句中出现的顺序。

对于空间索引（spatial indexes）的范围访问方法，MySQL不支持合并多个范围。为了解决这个限制，可以将与相同的语句一起使用，只是要将每个空间谓词放在不同的中。

多列复合索引范围访问方法

多列复合索引上的范围条件是单列索引的范围条件的扩展。多列复合索引上的范围条件将索引行限制在一个或多个键元组间隔的行内。键元组间隔是在一组键元组上使用索引排序定义的。

例如，考虑一个定义为的多列索引，以下键元组集是按键顺序列出的：

条件定义了这个间隔：

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

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

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

• 对于哈希索引，可以使用包含相同值的每个间隔。这意味着只能针对以下形式的条件生成间隔：

  这里，是常量，cmp是比较运算符其中之一，条件涵盖了所有索引列。(也就是说，有N个条件，N列索引的每列都有一个条件。) 例如，下面是一个由三部分组成的哈希（HASH）索引的范围条件：

关于什么是常量的定义，请参见单列索引的上面的单列范围访问方法。

• 对于BTREE索引，间隔可用于与连接的条件，其中每个条件使用或（其中’pattern’模式不以通配符开头）将键部分与常量值进行比较。只要能确定包含与条件匹配的所有行的单键元组，就可以使用间隔（如果使用，则可以使用两个间隔）。

  只要比较运算符是 ，优化器就会尝试使用其他键部分来确定间隔。如果操作符是，优化器使用它当作间隔，就不考虑其他键部分。对于下面的表达式，在间隔构造中，优化器使用第一个比较运算符中的 ，它还使用了第二个比较中的 ，但没有考虑其他键部分，也没有使用第三个比较运算符 ：

  上面的条件转换成单间隔为：

  创建的间隔可能包含比初始条件更多的行。例如，上面的间隔包含不满足原始条件的值，例如 。

• 如果把各个包含在间隔内能覆盖行集合的多个条件使用进行连接，则这些条件就构成一个能覆盖它们多个间隔的并集所覆盖的行集合的条件。如果把这些条件使用进行连接，则这些条件就构成一个能覆盖它们多个间隔的交集所覆盖的行集合的条件。例如，一下条件是两列索引：

  这些间隔是：

  在本例中，第一行的间隔使用一个键部分作为左边界（译者：就是 1），两个键部分作为右边界（译者：就是 2）。第二行的间隔只使用一个键部分。EXPLAIN输出中的键长度（key_len）列提示所用key前缀的最大长度。

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

但是，事实上，条件被转换成这样：

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

多值比较的相等范围优化

考虑这些表达式，其中 （列名）是有索引的列：

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

• 如果上有唯一的索引，那么每个范围中的行估计是1，因为最多只能有一行符合给定的值。

• 否则，col_name上的任何索引都是非惟一的，优化器可以通过下潜索引（译者：通过沿着索引树向下查找），或从索引统计信息中来估计每个范围中的行数。

使用索引下潜（index dives 译者：沿着索引树向下查找操作），优化器在一个范围的每个末端都进行一次下潜，并使用范围中的行数作为估计值。例如，表达式有三个相等范围，优化器对每个范围进行两次下潜以生成行数的估计值。每一对下潜都会产生一个具体给定值的行数估计值。

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

您可以配置（译者：相同范围索引下潜次数限制）系统变量的值，使优化器从一个行数估计策略切换到另一行数估计策略。要允许使用索引下潜来比较最多 个相等范围，请将设置为 。要禁用统计信息并始终使用索引潜水而不考虑次限制，请将设置为 0。

若要更新表索引统计信息以获得最佳估计，请使用（译者：分析表命令）。

在MySQL 8.0之前，除了使用系统变量外，是没有办法跳过索引下潜的使用来估计索引的有用性。在MySQL 8.0中，对于满足所有这些条件的查询，索引下潜是可能的：

• 该查询针对单个表，而不是多个表上的连接。

• 出现了一个单索引（强制使用索引）索引提示。其思想是，如果强制使用索引，那么从执行下潜索引的额外开销中不会得到任何结果。

• 该索引是非唯一的，也不是索引。

• 没有子查询。

• 没有子句。

对于连接的解释（EXPLAIN For CONNECTION），如果跳过索引下潜，输出将发生如下变化：

• 对于传统的输出，行数和过滤值是。

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

如果没有表连接，当跳过索引下潜时，EXPLAIN 输出不会改变。

在执行了跳过了索引下潜的查询后，表中的相应的行会包含一个的的值。

跳跃扫描范围访问方法

考虑以下情形：

执行这个查询，MySQL可以选择一个索引进行扫描来获取所有行（该索引包括所有要选择的列），然后从子句中应用 条件来生成最终的结果集。

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

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

1. 在第一个索引列（索引前缀）的不同值之间跳跃。

2. 在其他的索引列上，根据条件，对每个不同的前缀值执行子范围扫描。

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

1. 得到第一个键部分（）（译者：即复合索引中的第一个列）的第一个不同的值。

2. 根据第一个和第二个键部分（），构建查询范围。

3. 执行范围扫描。

4. 得到第一个键部分的下一个不同的值。

5. 根据第一个和第二个键部分（），构建查询范围。

6. 执行范围扫描。

使用这种策略可以减少被访问的行数，因为MySQL会跳过不符合每个构造范围的行。此跳跃扫描访问方法适用于以下情况：

• 表T至少有一个复合索引，其键的组成形式为（）。其中键的部分A和D可以为空，但是和必须不能为空。

• 查询只引用一个表。

• 查询没有使用或 。

• 查询只引用索引中的列。

• 上的谓词必须是相等谓词，并且必须是常量。这包括运算符。

• 查询必须是表连接查询；也就是说，是多个条件连接后的连接：

• 在上必须有一个范围。

• 列上的条件是允许的。上的条件必须与上的范围条件相结合。

跳跃扫描的使用在 EXPLAIN 输出中提示如下：

• 在Extra列中出现（在跳跃扫描中使用了索引） 提示使用了松散跳跃扫描范围方法。

• 如果该索引能被用作跳跃扫描，该索引应该在（译者：可能的键）列中看到。

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

您还可以看到一个（译者：最佳扫描摘要）元素。如果跳跃扫描被选择为最佳范围访问变体，则会写入一个（译者：被选为范围访问摘要）。如果跳跃扫描被选为总体最佳访问方法，则会出现（译者：最佳访问路径）元素。

跳跃扫描的使用取决于optimizer_switch系统变量标志的值。请参见8.9.2节，“Switchable Optimizations（可切换优化）”。默认情况下，这个标志是开启的。要禁用它，请将设置为。

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

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

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

以前，要使用范围扫描，必须将查询编写为：

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

• 只有谓词可以使用，不可以。

• 在谓词的左侧，行构造器只包含列引用。

• 在谓词的右侧，行构造器只包含运行时的常量，这些常量要么是字面量，要么是在执行期间绑定到常量的本地列引用。

• 在谓词的右侧，可有多个行构造器。

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

限制内存使用范围优化

要控制范围优化器可用的内存，可以使用（译者：范围优化器最大内存大小）系统变量：

• 该系统变量值为0表示没有限制。

• 如果该系统变量的值大于0，则优化器就会发现使用范围访问方法时跟踪所消耗的内存。如果即将超过指定的限制值，则放弃范围访问方法，而考虑其他方法，包括全表扫描。这可能不是最理想的。如果发生这种情况，会出现以下警告（其中N是当前的值）：

• 对于和语句，如果优化器返回到全表扫描，并且在已经启用系统变量情况下，则会出现错误而不是警告，因为实际上没有使用键来确定要修改哪些行。有关更多信息，请参见使用安全更新模式()。

对于超出可用范围优化内存的单个查询，优化器会退回到不太优化的计划，这种情况下，增加值可能会提高性能。

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

• 对于以下这样的简单查询，其中有一个范围访问方法的候选键，每个谓词都与连接使用大约230字节：

• 类似地，对于下面这样的查询，每个谓词都与连接使用大约125字节：

• 对于带有IN()谓词的查询：

在IN()列表中的每个字面值都作为与连接的谓词。如果有两个列表，连接的谓词数量是每个列表中字面值数量的乘积。因此，在上述情况下，与连接的谓词数为。

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

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