Mysql 范围查询优化
Range查询：用单独的Index的一个或多个index值来检索表的子集行数据，当然包含多个index。
1:一个index （单一部分）的range access 方法：（eg : 指的这种key (column1 )）
单独的index，index值间隔可以方便的由对应的where子句的条件表示，所有我们称值为range条件而不是间隔；
单独index的range条件的定义：
1.1:对于btree和hash索引，index和一个常量值通过 =, <=>,in(),is null,或者 IS not null操作符做比较；
1.2：另外，对于Btree索引，index和一个常量值通过 <,>,<=,>=,between，!=,或者<>操作符做比较；
1.3： 对于所有类型的index,多范围条件通过 or and关键字组合形式；
'常量值'在之前的描述中意味着：
2.1： 查询字符串的常量形式；
2.2： const 或者system表的一列（也只有一列）的自连接（join）；
2.3： 不相关子查询的结果；
2.4: 有上面类型子表达式完全组成的任意表达式；
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子句中的任何可能的index提取range condition,在提取过程中，不能构造的范围条件被舍去，产生重叠的条件被组合，产生空范围的条件被舍去。
如下：
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为index, nonkey非index;
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：舍去nokey = 4和key1 like ‘%d’,因为他们不能用作范围scan,正确的舍去方法是用true代替她们，
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR (key1 < 'bar' AND TRUE) OR (key1 < 'uux' AND key1 > 'z')
3: 以下条件总是true or false;
1:(key1 LIKE 'abcde%' OR TRUE) is always true
2:(key1 < 'uux' AND key1 > 'z') is always false
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
4： 去掉不必要的true 和false条件：
(key1 < 'abc') OR (key1 < 'bar')
5：组合那些重叠的区间成一个：
(key1 < 'bar')
范围条件的提取算法可以处理内嵌的and /or任意深度的构造，他的数去不依赖与他们出现在where子句中的顺序；
2：多part index的范围查询：
index的多个部分的范围条件是上面的扩展，被一个或几个key元组来限制条件：
eg: 一个联合索引定义：key1(key_part1,key_part2,key_part3),key元组以key order显示如下：
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)
这区间包括第四，第五，第六元组。
相对，条件 key_part3 = 'abc'不能定义一个单独的区间并且不能被区间scan方法使用（最左前缀index）;
3:多值的区间优化：
以下col_name 是index 列：
col_name IN(val1, ..., valN) col_name = val1 OR ... OR col_name = valN
如果col_name 等于这些值中的任意一个返回true,
1:如果col_name 为unique index,则范围的行评估为1，因为最多一个值只能对应一行；
2：否则，优化器估计范围行数得使用index潜入和index的统计信息；
index潜入，优化器植入一个潜入在每个range的结尾并且用该范围的行数来估计，eg:col_name IN (10, 20, 30)
30727 rows in set (5.37 sec) mysql> explain select * from employees where first_name like 'm%' ; +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | employees | ALL | idx_fn_ln | NULL | NULL | NULL | 299290 | Using where | +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from employees where first_name like 'mart%' ; +----+-------------+-----------+-------+---------------+-----------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+-----------+---------+------+------+-----------------------+ | 1 | SIMPLE | employees | range | idx_fn_ln | idx_fn_ln | 16 | NULL | 1635 | Using index condition | +----+-------------+-----------+-------+---------------+-----------+---------+------+------+-----------------------+ 1 row in set (0.00 sec)
第一个条件查找从执行计划上看走的扫表（all），where条件得到的结果为所以数据行的1/10;第二条增加like条件的匹配精度，走的ICP range查找，得到的数据行大约是1/30，mysql
会根据查找的数据范围（多少）决定走index range查找还是直接扫表（all）
mysql> desc select * from employees where first_name like 'ma%' and last_name like 'he%'; +----+-------------+-----------+-------+---------------+-----------+---------+------+-------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+-----------+---------+------+-------+-----------------------+ | 1 | SIMPLE | employees | range | idx_fn_ln | idx_fn_ln | 34 | NULL | 38174 | Using index condition | +----+-------------+-----------+-------+---------------+-----------+---------+------+-------+-----------------------+ 1 row in set (0.04 sec)
mysql> desc select * from employees where first_name like 'ma%' or last_name like 'he%'; +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | employees | ALL | idx_fn_ln | NULL | NULL | NULL | 299290 | Using where | +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.02 sec)
mysql> desc select * from employees where first_name in('mart','mori','moon'); +----+-------------+-----------+-------+---------------+-----------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+-----------+---------+------+------+-----------------------+ | 1 | SIMPLE | employees | range | idx_fn_ln | idx_fn_ln | 16 | NULL | 710 | Using index condition | +----+-------------+-----------+-------+---------------+-----------+---------+------+------+-----------------------+ 1 row in set (0.02 sec)
mysql> explain select * from employees where emp_no = 11; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.01 sec)
Mysql发现where条件不可能成立 ，返回null
