MySQL系列(20)： 范围查询优化

　　MySQL 对范围查询提供了 range access method(范围访问方法) 来优化查询。

　　范围访问方法使用单个索引来检索包含在一个或多个索引值区间内的表行的子集。这里的单个索引可以是单列索引或多列索引。

单列索引范围访问方法

对于单索引使用，索引值间隔可以通过 WHERE 子句中的条件表达式来表示范围条件。

单个索引的范围条件使用定义如下：

• 对于 B Tree 索引和 HASH 索引，当使用 =、<=>、in()、is null 或 is not null 时，索引列与常量值比较，是个范围条件。
• 对于 B Tree 索引，当使用 >、<、>=、<=，between，!= 、<> 或 LIKE 不以通配符(%)开头的索引列与常量值比较，是个范围条件。
• 对于所有索引类型，多个范围条件使用 OR 或 AND 组合而成。

常量值指的是以下情况的值：

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

在优化器将常量传递阶段，一些非常量值可以转换为常量值。

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

一般而言，用于范围扫描的条件比 WHERE 子句的限制更小。MySQL 执行额外的检查以过滤掉满足范围条件但不满足完整 WHERE 子句的行。

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

MySQL 不支持为空间索引合并多个范围，可以使用带有相同 SELECT 语句的 UNION，将每个空间词放在不同的 SELECT 语句。

多列索引范围访问方法

多列索引上的范围条件是单索引范围条件的扩展。多列索引上的范围条件将索引行限制在一个或多个索引间隔内。多列索引的范围条件限制索引行位于一个或多个索引元组区间内。

1. 对于 HASH 索引，可以使用包含相同值的每个区间。

   索引通过使用 =、<=>、IS NULL 比较运算与常量比较，并且多个条件覆盖所有索引。

2. 对于 B-Tree 索引，区间可能适用于与 AND 的条件组合，其中每个条件使用 =、<=>、IS NULL、>、>=、<、<=、!= 、<>、BETWEEN 或 LIEK 模糊匹配(不能通配符开头) 来比较索引和常量值。

   只要能够确定一个包含所有符合条件的行的单个索引(多列索引)，就可以使用区间(如果使用 <> 或 != ，则为两个区间)

多等值范围比较优化

思考如下表达式，col_name 是个索引列：

col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN

如果 col_name 等于多个值中的任意一个，则整个表达式都为 true 。这类比较是相等范围比较(这里的范围指提下单个值)。优化器评估读取限定行的成本以进行相等范围比较，如下所示：

• 如果 col_name 上有唯一索引，则每个范围的行数估算为 1，因为只能有一行可以具有给定的值。
• 如果 col_name 的索引不是唯一索引，则优化器会挖掘索引信息或使用索引统计信息来评估每个范围的行数。

对于索引数据的挖掘，优化器会在范围的每一端进行数据挖掘，并使用范围中的行数作为估算值。

例如，表达式 *col_name in (10, 20, 30)*，有三个相等的范围，优化器对每个范围进行两次索引信息挖掘以生成行估计值。

每对(两端)数据挖掘都会根据给定的值生成行数估计值。

索引数据挖掘提供了准确的行估算值，但随着表达式中比较值的数量增加，优化器生成生估算需要更长的进间。索引统计的使用不如索引数据挖掘准确，但允许对更多值的范围(多值大列表)的行进行估算。

MySQL 系统变量 eq_range_index_dive_limit 允许配置优化程序切换估算策略。若要允许使用索引挖掘来比较 N 个相等范围，将 eq_range_index_dive_limit 设置为 N + 1。若要禁用 索引数据统计 并始终使用 索引数据挖掘，而不考虑 N，将该变量值设置为 0。

使用 ANALYZE TABLE 更新表统计信息以便获取最好的评估。

即使使用 索引挖掘 的情况下，在满足以下所有条件的查询时，索引挖掘 也是无法使用的(忽略)：

1. 存在单索引 FORCE INDEX(强制索引) 提示时；强制使用索引，那么对索引挖掘和评估是无法获取任何好处的。
2. 索引是非唯一的，并且不是 FULLTEXT 索引。
3. 没有子查询存在。
4. 没有 DISTINCT, GROUP BY, ORDER BY 子句存在。

这些满足跳过 索引挖掘 的查询只适用于单表查询。对于多表连接查询，索引挖掘不会跳过。

行构造表达式范围优化

以前，使用范围扫描的查询必必如下写法：

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

将以将上面的语句改造如下，优化器可以使用范围扫描方法来查询如下表单：

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

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

• 只能使用 IN() 条件，不能使用 NOT IN()。
• IN() 左边只能是包含对列的引用。
• IN() 右边只能是包含运常量或本地列引用。
• IN() 右边，可以有多个常量或本地列引用组。

范围优化之内存限制

要控制范围优化器可用的内存，可设置范围优化器最大内存大小系统变量：range_optimizer_max_mem_size

1. 值等于 0 表示没有限制。
2. 值大于 0，优化器会跟踪评估范围访问方法消耗的内存。如果超过限制则放弃范围访问方法，而考虑其他方法，包括全表扫描。但会输出警告信息。
3. 对于 UPDATE 和 DELETE 语句，如果优化器返回全表扫描，并启用了 SQL 安全更新系统变量，则会发生错误，而不是警告，因为实际上没有键(指定条件)来确定要修改的行。

对于超出可用范围优化内存并且回退到不太理解的执行计划的单个查询，增加 range_optimizer_max_mem_size 值以提高可以范围优化内存来提高性能。

要估算处理范围表达式所需的内存量，可如下方式计算：

1. 对于如下简单查询，使用 OR 组合的每个条件大约占用 230 个字节

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

2. 如下类似查询，使用 AND 组合的每个条件大约占用 125 个字节。

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

3. 对就 IN() 条件

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

   IN() 列表中的每个元素都计为与 OR 的组合。如果有两个 IN() 列表，则是与OR 组合的每个列表元数个数的乘积。因此，例子中的相当于 M x N 个 OR 条件。
   在 5.7.11 之前，每个 OR 条件组合占用的更多的字节，大约 700 个字节。