8.2.1.3 Range Optimization 范围优化
范围访问方法使用索引来检索表记录的子集,有一个或者多个索引值间隔。
它可以用于一个单部分或者多部分的索引,下面的章节给出了一个详细的描述说明如何从WHERE 子句中提取间隙:
8.2.1.3.1 单部分索引的范围访问方法:
对于一个单部分索引, 索引值的时间间隔可以方便的代表通过相应的条件在WHERE 子句中,
因此我们讲的范围条件,而不是时间间隔。
范围条件的定义对于一个单部分索引如下:
1.对于BTREE和HASH indexes, 比较一个key part 和一个恒定值是范围条件当使用=,<=>,in(),is null 或者not null操作。
- 此外, 对于BTREE 索引,比较一个key part 和一个常量是一个范围条件当使用>,<,>=，<=,
BETWEEN,!=,<>操作符,或者like 比较符 如果蚕食LIKE 是一个常量字符串,不是以通配符开始。
多余所有的类型的索引,多个范围条件组合成OR 或者AND 形成一个范围条件。
在前面的描述中的常量值指下列之一:
1.查询字符串常量
- 常量表的列或者系统表
3.一个非关联子查询的结果
这里有一些查询的例子,使用range 条件在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’);
对于key 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’)
删除nonkey = 4 和key1 LIKE ‘%b’ 因为它们不能用于一个范围扫描,
正确的方式是删除它们:
(key1 < ‘abc’ AND (key1 LIKE ‘abcde%’ OR TRUE)) OR
(key1 < ‘bar’ AND TRUE) OR
(key1 < ‘uux’ AND key1 > ‘z’)
折叠的条件,总是真或者假:
(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)
在一般情况下（如前面的例子所证明),用于范围扫描的条件比在WHERE 子句中的限制的更少。
MySQL 执行一个额外的检查来规律满足range 条件的记录,但不是整个where 子句。
范围条件提取算法依靠处理 nested AND/OR 结构,其处处不依赖条件表达式出现在WHERE 的顺序。
目前, MySQL 不支持merge多个range 对于rang 访问方法,
为了解决这个限制,你可以使用一个UNION 连接单个SELECT 语句
8.2.1.3.2 The Range Access Method for Multiple-Part Indexes 范围访问方法 用于部分索引（组合索引)
Multiple-Part Indexes ：组合索引
范围条件在一个多部分的索引是一个扩展的范围条件,
一个范围条件在一个多部分索引限制索引记录位于一个或者多个索引。
例如,考虑一个多部分索引定位为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)
区间在第四,第5，第6 元组在前面的数据库,可以通过范围访问。
相比之下, 条件 key_part3 = ‘abc’ 不定义一个单位的区间 不能用于范围扫描
下面的描述包括范围条件如何工作对于组合索引:
对于HASH indexes, 每个间隔包含相同的值被使用,这意味着 间隔可以产生仅在下面的形式的条件:
key_part1 cmp const1
AND key_part2 cmp const2
AND …
AND key_partN cmp constN;
在这里,const1，const2 …是常数,cmp是=,<=>或者is NULL 比较表达式中的一个,
条件覆盖所有的index 部分：
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = ‘foo’
对于B树索引,一个区间可以是有用的对于提交结合(and）,其中每个条件比较:
一个索引 部分 是一个常量值使用=,<=>，IS NULL ,>,< >= != <>
BETWEEN,或者LIKE 模式(模式不是通配符开始).
优化器尝试 使用额外的key 部分来确定间隔只要比较运算符是=,<=>或者IS NULL。
如果操作符是>,<,>=,<=,!=,<> 优化器使用它,但是认为没有更多的key 部分。
对于下面的表达式,优化器使用=从第一个表达式。
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 组合,
(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
The intervals are:
(1,-inf) < (key_part1,key_part2) < (1,2)
(5,-inf) < (key_part1,key_part2)
在这个例子中,第一行的间隔使用一个索引 对于左边边界,两个索引用于后边边界。
第2行间隔使用一个索引,key_len列在EXPLAIN中的输出表示索引前缀使用的最大长度:
在某系情况下, key_len 可能表示一个索引被使用, 但是这个可能不是你期望的,