mysql rang 查询优化(5.7)
range访问方法使用单个索引来检索包含在一个或多个索引值区间内的表行的子集。它可以用于单列索引以及复合索引。
单个索引范围访问的方法优化
多个索引列范围访问的方法优化
多值比较的等距离优化
行构造函数表达式的范围优化
限制内存用于范围优化
单个索引
对于单部分索引，索引值区间可以方便地用WHERE子句中的相应条件表示，表示为范围条件而不是区间。
定义如下：
对于btree和hash索引，当使用=、<=>、in()、is null或is not null运算符时，索引键与常量值进行比较是一个范围条件。
此外，对于B-tree索引，当使用>、=、<=、between、!=或<>这些操作符或者参数是常量字符串且不以通配符开头的like关键字时，索引键与常量的比较是一个范围条件。
对于所有索引类型，多个范围条件与或组合在一起，形成一个范围条件。
上述说明中的"常量值"是指下列值之一：
查询字符串中的常量
来自同一个Join连接的常量或系统表的列
不关联子查询的结果
完全由上述类型的子表达式组成的任何表达式
举个栗子：
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是索引列，nonkey没有索引。
合并的过程如下：
提取where条件：
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')
移除掉没有索引的列nonkey并且，like查询以%开头的(因为用不上索引，通配符开头定位不到索引page页)，然后用True代替它，这样可以保证数据查询不会出错。(因为提取后还得把这些去掉的条件加上再进行过滤，只是为了合并范围，最大化的利用索引)
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')
有些条件的结果不是真就是假这种的就可以移除掉用True和False代替
(key1 LIKE 'abcde%' OR TRUE) is always true
(key1 < 'uux' AND key1 > 'z') is always false
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
去掉没必要的True和False
(key1 < 'abc') OR (key1 < 'bar')
将重叠的区间合并
(key1 < 'bar')
一般而言(如前例所示)，用于范围扫描的条件比WHERE子句的限制要小。MySQL执行额外的检查以筛选出满足范围条件而不是完整的WHERE子句的行。
范围条件提取算法可以处理任意深度的嵌套和/或构造，其输出不取决于条件在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 这个条件定义了如下区间：(inf表示∞这个符号)
(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)
该区间覆盖了前面数据集中的第4、第5和第6个元组，并且可以被范围访问方法使用。
相比之下，条件*key_part3* = 'abc'义单个区间，并且不能被范围访问方法使用。
以下描述更详细地说明了复合索引的范围条件如何工作。
对于哈希索引，可以使用包含相同值的每个区间。这意味着只能在以下形式的条件下生成区间：
key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;
这里，const1、const2，…是常量，cmp是=、<=>或是空比较运算符之一，条件涵盖了所有索引部分。(也就是说，有n个条件，n部分索引的每个部分对应一个条件。)例如，下面是由三部分组成的哈希索引的范围条件：
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
对于btree索引，区间可能适用于与和组合的条件，其中每个条件使用=、<=>、为空、>、=、<=、<=、！=、<>、between或like"pattern"(其中"pattern"不以通配符开头)。只要能够确定包含所有符合条件的行的单个索引key元组，就可以使用区间(如果<>或/!=，则为两个区间)。
只要是使用了=，<=>，或者is null操作符，MySQL优化器就尝试使用额外的索引键来确定区间。如果操作符是>，=，<=，!=，<>，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)
在这个栗子中，第一行的区间使用复合索引中的单列作为左边边界，使用复合索引中的两列作为右边边界。第二行的区间只使用了复合索引中的一列。expalin输出中的key_len列表示所使用的索引键前缀的最大长度。
在一些情况下，key_len列表明了有一个索引键被使用，但这可能不是你期望的。假设key_part1和key_part2可以为空。然后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。这些比较是相等范围比较(其中"范围"是单个值)。优化器估计为相等范围比较读取限定行的成本，如下所示：
如果列名称上有唯一索引，则每个范围的行估计值为1，因为最多只能有一行具有给定值。
否则，col_name上的任何索引都是非唯一的，优化器可以使用dive进入索引或index statistics信息来估计每个范围的行数。
对于索引下潜dive，优化器在范围的每一端进行数据挖掘，并使用范围中的行数作为估计值。例如，表达式col_name in(10，20，30)有三个相等的范围，优化器对每个范围进行两次dive以生成行估计。每对数据挖掘都会生成具有给定值的行数的估计值。
两者区别如下：
索引数据dive提供了准确的行估计，但是随着表达式中比较值的数量增加，优化器生成行估计需要更长的时间。
索引index statistics的使用不如索引数据挖掘准确，但允许对大值列表进行更快的行估计。
区分这2种方式主要是因为：
查询优化器使用代价估算模型计算每个计划的代价,选择其中代价最小的
单表扫描时，需要计算代价，所以单表的索引扫描也需要计算代价
单表的计算公式通常是：代价=元组数*IO平均值
所以不管是哪种扫描方式，都需要计算元组数
eq_range_index_dive_limit 系统变量使您能够配置优化程序从一个行估计策略切换到另一个行估计策略的值数量。若要允许使用指数下潜来比较n个相等范围，请将eq-range-index-dive-limit设置为n+1。若要禁用统计数据的使用并始终使用索引dive(就是潜入到索引中，利用索引完成元组数的估算)，而不考虑n，请将eq-range-index-dive-limit设置为0。
要更新表索引统计信息以获得最佳估计，请使用分析表。
即使在使用索引dive的情况下，也会跳过满足所有这些条件的查询：
存在单个索引强制索引提示。其想法是，如果强制使用指数，那么在指数中进行跳水的额外开销就没有什么好处。
索引不唯一，不是全文索引。
不存在子查询。
不存在distinct、group by或order by子句。
这些dive-skipping只适用于单表查询。对于多个表查询(联接)，不会跳过索引dive。可以使用optimizer_trace打印出来优化器执行的过程，查看具体的选择。
行构造器表达式的范围优化
优化器能够将范围扫描访问方法应用到如下所示查询：
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()谓词，而不使用In()。
在in()谓词的左侧，行构造函数只包含列引用。
在in()谓词的右侧，行构造函数只包含运行时常量，这些常量是在执行期间绑定到常量的文本或本地列引用。
在in()谓词的右侧，有多个行构造函数。
限制内存用于范围优化
值为0表示"no limit"没有限制。
如果值大于0，优化器将跟踪考虑范围访问方法时消耗的内存。如果即将超过指定的限制，则放弃范围访问方法，而考虑其他方法，包括全表扫描。这可能不太理想。如果发生这种情况，将出现以下警告(其中n是当前范围"range_optimizer_max_mem_size "值)：
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系统变量，则会发生错误而不是警告，因为实际上没有使用键来确定要修改的行。参考Using Safe-Updates Mode (--safe-updates)
4 . 对于超出可用范围优化内存且优化器返回到非最佳计划的单个查询，增加 range_optimizer_max_mem_size可能会提高性能。要估计处理范围表达式所需的内存量，请使用以下准则：
对于如下简单查询，如果range access方法有一个候选键，则每个谓词与大约230个字节组合或使用大约230个字节：
SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
类似地，对于如下查询，每个谓词与大约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()列表，则谓词的数目与每个列表中的文字值的数目相结合，或是两者的乘积。因此，与前一种情况结合的谓词的数目是m×n。
在5.7.11之前，每个谓词的字节数加上或更高，大约700字节。
