MySQL - RANGE优化篇
RangeAccess使用单个索引的方式来检索包含在一个或多个索引值区间内的表行的子集。它也适用于单列或复合（组合）索引...
单列索引
对于单列索引，索引值区间可以方便地用WHERE语句中的相应范围条件表示。优化器在常量传播阶段，会将一些非常量值转换为常量。
定义
- 对于BTREE索引和HASH索引来说，索引的范围优化基本上只适用于等值查询。譬如=, <=>, IN(), IS NULL, IS NOT NULL操作符。
- 对于HASH索引，BTREE索引同样支持非等值查询，譬如>, <, >=, <=, BETWEEN, !=, <>和LIKE（注意，like的常量值不能以通配符开头）
- 对于所有索引类型，多个范围条件与
OR或AND在一起会形成一个范围条件
代码示例
以下是在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会尽可能从索引中提取范围条件，在提取过程中，利用索引将不能提取范围条件的过滤掉，然后对剩下的数据进行额外的筛选
代码示例
参考以下句子，其中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.原始的 WHERE语句
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')2.使用 TRUE 来替换不能进行范围扫描的 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.折叠始终为真或者假的条件
(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子句中的顺序。
复合索引
复合索引的范围条件是单个索引范围条件提取的扩展
限制内存
使用系统变量 range_optimizer_max_mem_size可以控制优化器对范围优化时使用的内存数
- 0 意味着不做任何限制
- 大于0，则优化器在操作时发现超出指定限制后将会改变策略（如：全表扫描），同时还会给出以下警告，所以增加
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.范围表达式内存估算准则
1.多个 OR组合，每个 OR大概占230字节，在 5.7.11之前约占 700字节，所以慎用
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，恐怖如斯，在 IN中每个内容就会视为一个 OR，如果有多个IN，那么该占用的指数是乘积（ M×N）
SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);行构造器
MYSQL写法优化的一种，简化了书写，不过这种方式只支持 IN，目前为止 NOT IN是不被支持的
优雅写法
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' );总结
最好的优化方案，跟着新版本走 推陈出新，新版中不仅扩展更多功能，同时会加强优化力度。虽然 MySQL优化器为我们做了很多事情，但开发过程中该主意还得注意。