Mysql SELECT语句范围优化

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

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

    对于单个部分索引，索引值间隔可以方便地用WHERE子句中的相应条件表示，表示为范围条件而不是“间隔”
    单部分索引的范围条件的定义如下：

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

    前面的描述中的“ 常量值 ”表示以下之一：

• 查询字符串中的常量
• 同一联接中的const或system表的列
• 不相关子查询的结果
• 完全由前面类型的子表达式组成的任何表达式

    以下是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是一个索引列，而nonkey没有索引：

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 和 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) 永远为真
• (key1 < ‘uux’ AND key1 > ‘z’) 永远为假

    用常量替换这些条件将产生：

(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中的情况。

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

    多部分索引上的范围条件是单个部分索引的范围条件的扩展。多部分索引上的范围条件限制索引行位于一个或多个键元组间隔内。键元组间隔是在一组键元组上定义的，使用索引中的排序。
    例如，考虑一个定义为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’ 不定义单个间隔，不能由范围访问方法使用。
    以下描述更详细地说明了范围条件如何作用于多部分索引。

• 对于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'

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

多值比较的等距范围优化

    考虑以下表达式，其中 col_name 是索引列：

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

    如果col_name等于多个值中的任何一个，则每个表达式为true 。这些比较是相等范围比较（其中“ 范围 ”是单个值）。优化器估算读取相等行以进行相等范围比较的成本，如下所示：

• 如果在 col_name 上有唯一索引，则每个范围的行估计为1，因为最多一行可以具有给定值。
• 否则，col_name上的任何索引都不是唯一的，优化器可以使用对索引或索引统计数据的深入估算来估计每个范围的行数。

    使用 index dives时，优化程序在范围的每个末端进行dive，并将范围中的行数用作估计值。例如，该表达式 col_name IN (10, 20, 30)具有三个相等范围，并且优化器对每个范围进行两次dive以生成行估计。每对dive都会得出具有给定值的行数的估计值。
    Index dives 可提供准确的行估计，但是随着表达式中比较值的数量增加，优化器将花费更长的时间来生成行估计。使用索引统计信息的准确性不及使用 Index dives 的准确性，但允许对大型值列表进行更快的行估计。
    使用 eq_range_index_dive_limit 系统变量，可以配置优化程序从一种行估计策略切换到另一种行估计策略时所用的值数。要允许使用 index dives 进行最多等于范围 N 的比较，请设置 eq_range_index_dive_limit 为N+1。若要禁用统计信息的使用并始终使用 index dives，而不考虑N，请将eq_range_index_dive_limit设置为0。
    要更新表索引统计信息以获得最佳估计值，请使用 ANALYZE TABLE。
    即使在本应使用 index dives 的条件下，对于满足所有这些条件的查询也将跳过它们：

• 存在单独索引 FORCE INDEX 索引提示。这样的想法是，如果强制使用索引，那么执行潜入索引的额外开销将无济于事
• 索引不统一，不是FULLTEXT索引。
• 不存在子查询
• 不存在 DISTINCT， GROUP BY，或 ORDER BY 子句。

    这些跳转条件仅适用于单表查询。对于多个表查询（联接），不跳过索引剥离。

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

    优化程序可以将范围扫描访问方法应用于以下形式的查询：

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

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

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

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

• 仅使用IN()谓词，不使用NOT IN()
• 在IN()谓词的左侧 ，行构造器仅包含列引用
• 在IN()谓词的右侧 ，行构造器仅包含运行时常量，这些常量是在执行期间绑定到常量的文字或本地列引用
• 在IN()谓词的右侧 ，有多个行构造器

限制内存使用以进行范围优化

    要控制范围优化器可用的内存，请使用 range_optimizer_max_mem_size 系统变量：

• 值0表示“无限制”。
• 值大于0时，优化程序将在考虑范围访问方法时跟踪消耗的内存。如果将要超出指定的限制，则将放弃范围访问方法，而改用其他方法，包括全表扫描。这可能不是最佳选择。如果发生这种情况，则会发生以下警告（当前 range_optimizer_max_mem_size 值为 N）：

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

• 对于UPDATE和 DELETE语句，如果优化器退回到全表扫描并且sql_safe_updates启用了系统变量，则会发生错误而不是警告，因为实际上，没有键用于确定要修改的行。
  对于超出可用范围优化内存的单个查询，并且对于该查询，优化器会退回至次优计划，增加 range_optimizer_max_mem_size 值可能会提高性能。
  若要估计处理范围表达式所需的内存量，请使用以下准则：
• 对于一个简单的查询，如以下，在范围访问方法中有一个候选关键字，每个谓词结合OR使用大约230个字节：

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

• 同样，对于以下查询，每个谓词组合 AND 使用大约125个字节：

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

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

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

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

参考文档

https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html