MySQL语句优化之Range优化（2）

大家好，我是Bingo，一枚IT从业者，通过翻译MySQL8.0文档来学习MySQL。今天是我的第五篇文稿，请大家验收

8.2.1.2 Range范围优化（2）

2、联合索引的范围访问

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

例如，考虑定义为 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' 没有定义单个区间，并且不能由范围访问方法使用。

以下描述更详细地说明了范围条件如何适用于多部分索引
1、对于 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'

有关被视为常量的定义，请参阅单部分索引的范围访问方法。

2、对于 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

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