二（2）、MySQL语句优化—范围优化

范围优化

range的访问方法是使用单个索引来检索包含一个或若干个索引值的时间间隔内表行的子集。它可以用于单部分或多部分索引。以下描述优化器使用范围访问的条件。
- 单部分索引的范围访问方法
- 多部分索引的范围访问方法
- 多值比较的灯距范围优化
- 行构造函数表达式的范围优化
- 限制内存使用以进行范围优化

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

对于单部分索引，索引值间隔可以方便地由条款中的相应条件WHERE表示，称为范围条件，而不是间隔。
单部分索引的范围条件定义如下：

• 对于BTREE和HASH索引，使用时具有恒定值的关键部分的比较是一个范围条件如：=，<=，>=，IN，IS NULL或IS NOT NULL。
• 另外，对于BTREE索引，当使用具有恒定值的关键部分的比较是一个范围条件如：>，<，>=，<=，BETWEEN，！=，或LIKE比较，如果参数LIKE是一个常数字符串不是从通配符开始的。
• 对于所有索引类型，多个范围条件组合OR或AND形成一个范围条件。
  前面所指的"常量值"表示为以下之一：
• 查询字符串中的常量
• 来自同一链接的const或system表的列
• 不相关子查询的结果
• 热河完全由上述类型的子表达式组成的表达式
  以下是WHERE字句中具有范围条件的查询示例：

SELECT * FROM t1 WHERE key_co1>1 AND key_co1<10;
SELECT * FROM t1 WHERE key_co1=1 or key_co1 in (15,18,20);
SELECT * FROM t1 WHERE key_co1 LIKE "ab%" OR key_co1 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");

1. 从原始子句开始

(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"因为他们不能用于范围扫描

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

3. 崩溃条件始终为True或者False

• (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')

（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"未定义单个间隔，并且不能由范围访问方法使用。
以下更详细地描述范围条件如何作用于多部分索引：
- 对于HASH索引，可以使用包含相同值的每个间隔，这意味着只能针对以下形式的条件生成间隔。

  key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;

这里的const1，const2…是常数，cmp是一个=，<=，>=或者IS NULL比较运算符，以及条件覆盖所有指数部分。（也就是说，存在N 条件，N-part索引的每个部分都有一个 条件。）例如，以下是三部分HASH索引的范围条件 ：

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

- 对于一个BTREE索引，以一定间隔可能是可用于条件组合 AND，其中每个状态具有恒定值使用一个关键部分进行比较 =， <=>， IS NULL， >， <， >=， <=， !=， <>， BETWEEN，或 （其中 LIKE 'pattern''pattern' 不以通配符开头）。只要可以确定包含所有与条件匹配的行的单个键元组，就可以使用一个间隔（如果使用<> 或，!= 则使用两个间隔 ）。
只要比较运算符为，或=， 优化器就会尝试使用其他关键部分来确定间隔 。优化器使用它，但认为没有更多的关键部分。对于以下表达式，优化器使用 第一个比较中的值。它也使用 <=>IS NULL><>=<=!=<>BETWEENLIKE=>= 根据第二次比较，但不考虑其他关键部分，并且不将第三次比较用于区间构造：

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

单个间隔为：

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

（3）多值比较的等距范围优化

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

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

如果col_name等于多个值中的任何一个，则每个表达式为True，这些比较是相等范围比较(其中范围是单个值)。优化器估算读取相等行以进行相等范围比较的成本，如下：
- 如果在col_name有唯一索引，怎每个范围的行估计为1，因为最多一行可以具有给定值。
- 否则任何索引col_name都不是唯一的，并且优化程序可以使用对索引或索引同级的深入了解来估计每个范围的行数。

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

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

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谓词的右侧，有多个行构造函数

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

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

值0表示“ 无限制”。”

值大于0时，优化程序将在考虑范围访问方法时跟踪消耗的内存。如果将要超过指定的限制，则将放弃范围访问方法，而应考虑其他方法，包括全表扫描。这可能不是最佳选择。如果发生这种情况，则会发生以下警告：
Warning 3170 Memory capacity of N bytes for
‘range_optimizer_max_mem_size’ exceeded. Range
optimization was not done for this query.
若要估计处理表达式所需的内存量，可以参考以下准则：

• 对于类似以下的简单查询，其中有一个用于范围访问方法的候选键，与组合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数量是每个列表中文字值数量的乘积。因此，OR在前一种情况下组合的谓词数为MxN。
在5.7.11之前，OR的每个谓词组合的字节数更高，约为700字节。