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

单部分索引的范围存取方法

对于单部分索引，索引值区间可以方便地用 WHERE 子句中的相应条件表示，表示为范围条件而不是「区间（intervals）」。

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

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

前面的描述中的 「常量值」是指以下的一种：

  • 来自查询字符串的一个常数
  • 来自同一连接的常数或系统表的一个列
  • 一个不相关的子查询的结果
  • 完全由前述类型的子表达式组成的任何表达式

下面是一些在 WHERE 子句中带有范围条件的查询的例子：

  1. SELECT * FROM t1
  2.   WHERE key_col > 1
  3.   AND key_col < 10;
  5. SELECT * FROM t1
  6.   WHERE key_col = 1
  7.   OR key_col IN (15,18,20);
  9. SELECT * FROM t1
  10.   WHERE key_col LIKE 'ab%'
  11.   OR key_col BETWEEN 'bar' AND 'foo';

在优化器常数传播阶段，一些非常数值可能被转换为常数。

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

考虑下面的语句，其中 key1 是一个有索引的列，non key 是没有索引的：

  1. SELECT * FROM t1 WHERE
  2.   (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  3.   (key1 < 'bar' AND nonkey = 4) OR
  4.   (key1 < 'uux' AND key1 > 'z');

key1 的提取过程如下：

  1. 从原始的WHERE子句开始。

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

  2. 删除 non key = 4 和 key1 LIKE '%b'，因为它们不能用于范围扫描。正确的方法是用 TRUE 替换它们，这样我们在进行范围扫描时就不会错过任何匹配的行。用 TRUE 替换它们的结果是：

    1. (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
    2. (key1 < 'bar' AND TRUE) OR
    3. (key1 < 'uux' AND key1 > 'z')

  3. 折叠始终为真或假的条件

    1. -- (key1 LIKE 'abcde%' OR TRUE) 始终为 true
    2. -- (key1 < 'uux' AND key1 > 'z') 始终为 false
    3. -- 用常数代替这些条件，可以得到:
    4. (key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
    5. -- 去掉不必要的 TRUE 和 FALSE 常数，得到的是：
    6. (key1 < 'abc') OR (key1 < 'bar')

  4. 将重叠的区间合并为一个区间，可得到用于范围扫描的最终条件：

    1. (key1 < 'bar')

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

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

对于空间索引的范围访问方法，MySQL 不支持合并多个范围。为了绕过这个限制，你可以使用一个具有相同 SELECT语句的 UNION，只是你把每个空间谓词放在一个不同的 SELECT 中。

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

多部分索引的范围条件是单部分索引的范围条件的延伸。多部分索引的范围条件限制 索引行位于一个或几个关键元组区间内。关键元组区间是在一组关键元组上定义的，使用索引的排序。

例如，考虑一个定义为 key1(key_part1, key_part2, key_part3)的多部分索引，以及下面一组按关键顺序排列的关键元组：

  1. key_part1  key_part2  key_part3
  2.   NULL       1          'abc'
  3.   NULL       1          'xyz'
  4.   NULL       2          'foo'
  5.    1         1          'abc'
  6.    1         1          'xyz'
  7.    1         2          'abc'
  8.    2         1          'aaa'

条件 key_part1 = 1定义了这个区间：

  1. (1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

这个区间涵盖了前面数据集中的第 4、5、6 个图元，可以被范围访问方法使用。

相比之下，条件 key_part3 = 'abc'并没有定义一个单一的区间，不能被范围访问方法使用。

下面的描述更详细地说明了范围条件对多部分索引的作用：

  • 对于 HASH 索引，可以使用包含相同值的每个区间。这意味着只能对以下形式的条件产生区间

    1.   key_part1 cmp const1
    2. AND key_part2 cmp const2
    3. AND ...
    4. AND key_partN cmp constN;

    这里，const1，const2，…是常数，cmp 是 =，<=>，或 IS NULL 比较运算符之一，条件涵盖所有索引部分。(也就是说，有 N 个条件，N 个部分的索引的每个部分都有一个。) 例如，下面是一个三部分 HASH 索引的范围条件：

    1. key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'

    关于什么是常数的定义，请参见单部分索引的范围访问方法。

  • 对于 BTREE 索引来说，一个区间可以用于与 AND 结合的条件，其中每个条件使用 =, <=>, IS NULL, >, <, >=, <=, !=, <>, BETWEEN, 或者 LIKE 'pattern'（其中 'pattern'不以通配符开始）将一个关键部分与一个常数进行比较。只要有可能确定一个包含所有符合条件的记录的单键元组（或者如果使用 <>或 !=，则使用两个区间），就可以使用一个区间。

只要比较运算符是 =，<=>，或 IS NULL，优化器就会尝试使用额外的关键部分来确定区间。如果运算符是 >, <, >=, <=, !=, <>, BETWEEN, 或 LIKE，优化器会使用它，但不考虑更多的关键部分。对于下面的表达式，优化器使用第一个比较中的 =。它还使用了第二次比较中的 >=，但是没有考虑更多的关键部分，并且没有使用第三次比较来构建区间：

  1. key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10

单一区间

  1. ('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)

有可能创建的区间包含比初始条件更多的行。例如，前面的区间包括值（'foo', 11, 0），这不满足最初的条件。

  • 如果把包含在区间内的行集的条件与 OR 结合起来，它们就形成了一个包含在其区间的结合处的行集的条件。如果这些条件与 AND 结合，它们就形成了一个条件，涵盖了包含在它们区间的交集内的一组行。例如，对于一个两部分索引的这个条件
    1. (key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
    区间是
    1. (1,-inf) < (key_part1,key_part2) < (1,2)
    2. (5,-inf) < (key_part1,key_part2)
    在这个例子中，第一行的区间在左边界使用了一个关键部分，在右边界使用了两个关键部分。第二行的区间只使用了一个 key 部分。EXPLAIN 输出中的key_len 列表示所使用的 key 前缀的最大长度。

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

  1. key_part1 >= 1 AND key_part2 < 2

但是，事实上，条件转换为这样。

  1. key_part1 >= 1 AND key_part2 IS NOT NULL

关于如何进行优化以合并或消除单部分索引上的范围条件的间隔的描述，请参阅单部分索引的范围访问方法。对于多部分索引上的范围条件，要执行类似的步骤。

多值比较的等值范围优化

考虑一下这些表达式，其中 col_name 是一个索引列：

  1. col_name IN(val1, ..., valN)
  2. 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](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_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](https://dev.mysql.com/doc/refman/8.0/en/explain.html)，如果跳过了索引的潜入，输出会发生如下变化：

  • 对于传统的输出，行和过滤的值都是 NULL。
  • 对于 JSON 输出，rows_examined_per_scan 和 rows_produced_per_join 不会出现，skip_index_dive_due_to_force 为真，而且成本计算也不准确。

如果没有 FOR CONNECTION，当索引潜水被跳过时，EXPLAIN 输出不会改变。

在执行一个跳过索引潜水的查询后，INFORMATION_SCHEMA.OPTIMIZER_TRACE表中的相应行包含一个 skipped_due_to_force_index的index_dives_for_range_access值。

跳过扫描范围的访问方法

请考虑以下情况：

  1. CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
  3. INSERT INTO t1 VALUES
  4.   (1,1), (1,2), (1,3), (1,4), (1,5),
  5.   (2,1), (2,2), (2,3), (2,4), (2,5);
  6. INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
  7. INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
  8. INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
  9. INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
  11. ANALYZE TABLE t1;
  13. EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

为了执行这个查询，MySQL 可以选择一个索引扫描来获取所有的记录（索引包括所有要选择的列），然后应用 WHERE 子句中的 f2 > 40条件来产生最终的结果集。

范围扫描比全索引扫描更有效，但在这种情况下不能使用，因为在第一个索引列 f1 上没有条件。然而，从 MySQL 8.0.13 开始，优化器可以进行多次范围扫描，对 f1 的每个值进行一次扫描，使用的方法称为跳过扫描，与松散索引扫描类似（见第 8.2.1.17 节，GROUP BY 优化）：

  1. 在第一个索引部分，f1（索引前缀）的不同值之间跳过。
  2. 对剩余索引部分的 f2>40条件的每个不同前缀值进行子范围扫描。

对于前面显示的数据集，该算法是这样操作的：

  1. 获取第一个关键部分的第一个不同值（f1=1）。
  2. 基于第一个和第二个关键部分构建范围（ f1 = 1 AND f2 > 40）。
  3. 执行一个范围扫描。
  4. 获取第一个关键部分的下一个不同值(f1 = 2)。
  5. 基于第一和第二关键部分构建范围（f1 = 2 AND f2 > 40）。
  6. 执行一个范围扫描。

使用这种策略可以减少访问行的数量，因为 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 ...) 和…。
  • C 上必须有一个范围条件。
  • D 列上的条件是允许的。D 上的条件必须与 C 上的范围条件结合在一起。

跳过扫描的使用在 EXPLAIN 输出中显示如下：

  • 在 Extra 列中使用索引进行跳过扫描表示使用了松散索引的跳过扫描访问方法。
  • 如果索引可以用于跳过扫描，那么该索引应该在 possible_keys 列中可见。

跳过扫描的使用在优化器的跟踪输出中由这种形式的 「skip scan」元素表示：

  1. "skip_scan_range": {
  2.   "type": "skip_scan",
  3.   "index": index_used_for_skip_scan,
  4.   "key_parts_used_for_access": [key_parts_used_for_access],
  5.   "range": [range]
  6. }

你也可能看到一个 「best_skip_scan_summary」元素。如果跳过扫描被选为最佳范围访问变体，会写一个 「selected_range_access_summary」。如果选择跳过扫描作为整体的最佳访问方法，则会出现一个 「best_access_path」元素。

跳过扫描 的使用受制于 optimizer_switch 系统变量的 skip_scan 标志的值。参见第8.9.2节，可切换的优化。默认情况下，这个标志是打开的。要禁用它，请将 skip_scan 设置为 off。

除了使用 optimizer_switch 系统变量来控制优化器在整个会话中使用跳过扫描外，MySQL 还支持优化器提示，以便在每个语句基础上影响优化器。见第 8.9.3节，优化器提示。

:::tips 这节有点难阅读，在执行 ANALYZE TABLE t1; 前后的对比图如下，执行之后确实跳过了 index 扫描
image.png :::

行构造函数表达式的范围优化

优化器能够对这种形式的查询应用范围扫描访问方法：

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

以前，如果要使用范围扫描，必须将查询写成：

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

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

  • 只使用 IN()谓词，不使用 NOT IN()。
  • 在 IN()谓词的左边，行构造器只包含列引用。
  • 在 IN()谓词的右边，行构造函数只包含运行时常量，这些常量要么是字面意思，要么是在执行过程中被绑定到常量的本地列引用。
  • 在IN()谓词的右侧，有一个以上的行构造器。

关于优化器和行构造函数的更多信息，请参阅第 8.2.1.22 节 行构造函数表达式优化

限制范围优化的内存使用

为了控制范围优化器可用的内存，使用 [range_optimizer_max_mem_size](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_range_optimizer_max_mem_size)系统变量：

  • 值为 0 意味着 无限制。

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

    1. Warning    3170    Memory capacity of N bytes for
    2.                  'range_optimizer_max_mem_size' exceeded. Range
    3.                  optimization was not done for this query.

  • 对于 UPDATE 和 DELETE 语句，如果优化器返回到全表扫描，并且启用了 sql_safe_updates 系统变量，就会发生错误而不是警告，因为实际上没有使用键来确定要修改哪些行。更多信息，请参阅使用 安全更新模式（-saf-updates）。

对于超出可用范围优化内存的个别查询，以及优化器退回到不太理想的计划，增加 range_optimizer_max_mem_size 值可能会提高性能。

要估计处理一个范围表达式所需的内存量，请使用这些指南：

对于像下面这样一个简单的查询，有一个候选键的范围访问方法，每个谓词结合 OR 使用大约 230 字节。

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

同样，对于像下面这样的查询，每个谓词与 AND 相结合，大约需要 125 字节：

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

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

  1. SELECT COUNT(*) FROM t
  2. WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);

IN()列表中的每个字面值都算作一个与 OR 结合的谓词。如果有两个 IN()列表，与 OR 结合的谓词的数量是每个列表中字面值的乘积。因此，在前面的情况下，与 OR 结合的谓词的数量是 M×N。