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

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

8.2.1.2 Range范围优化

范围访问来检索包含在单列索引或复合索引值间隔内的行。它可用于单个或多个索引。以下描述了优化器使用范围访问的条件

1、单列索引的范围访问

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

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

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

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

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

以下是在 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 是索引列，非键没有索引：

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的提取过程如下：
1、从原始 WHERE 子句开始：
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')

2、移除 nonkey = 4 and key1 LIKE '%b' 因为它们不能用于范围扫描。移除它们的正确方法是将它们替换为 TRUE，这样我们在进行范围扫描时就不会错过任何匹配的行。用 TRUE 替换它们会产生：
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')

3、始终为真或假的折叠条件 
(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')

4、将重叠的间隔组合成一个产生用于范围扫描的最终条件：
(key1 < 'bar')

通常（如前面的示例所示），用于范围扫描的条件没有 WHERE 子句那么严格。 MySQL 执行额外的检查以过滤掉满足范围条件但不满足完整 WHERE 子句的行。

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

MySQL 不支持空间索引的合并多个范围。要解决此限制，你可以用相同的SELECT语句使用UNION，只是你把每个空间谓词放在不同的SELECT中。

如此详细的优化，不得不佩服