8.2.1.3 Range 优化

8.2.1.3 Range 优化

range access method 方法使用单个索引去取回数据表的子集,它包含一个或多个索引值区间.Rang它能对单个部分索引或者多个部分索引使用.以下章节给出详细的描述
去解释怎么从where语句中解析区间。

1.The Range Access Method for Single-Part Indexes

对于单个部分索引，索引的值区间能够方便的从where 条件语句中解析出,表示为范围条件而不是区间.

对于单个部分索引的范围条件定义如下.
1.对于所有的BTREE和HASH索引,key部分与常量值比较就是范围条件,当使用 =,<=>,IN(),IS NULL,IS NOT NULL操作.
2.另外,对于BTREE索引,key部分与常量值比较就是范围查找,当树勇 >, <, >=, <=, BETWEEN, !=, <>,或者LIKE,当LIKE的参数是一个常量并且不是用宽范围(wildcard character)字符开始
3.对于所有的索引,由OR 和 AND组合的多个范围条件形成一个范围条件
常量值定义如下
1.来自查询字符串的一个常量
2.来自 same join 常量表(const table)或者系统表(system table)的列
3.无关联子查询的结果集
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语句中提取范围条件.在提取阶段,不能用于构造范围条件的条件将会被丢弃,
生成重叠范围的条件将会被组合,生成空范围的条件将会被移除.

考虑下面这个语句,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');

对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.移除nonkey =4 和 key1 LIKE '%b',因为他们不能用于范围扫描.移除他们的正确姿势是把他们替换成TRUE,这样我们在进行范围扫描的时候就不会漏数据.
把他们替换成TRUE,我们的语句将会变成如下这样.
(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) 总是 TRUE
(key1 < 'uux' AND key1 > 'z') 总是false
使用true或false替换他们,我们得到如下语句
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
移除不必要的TRUE和FALSE常量.
(key1 < 'abc') OR (key1 < 'bar')
4.组合重复覆盖的区间得到一个最终的条件用于范围扫描
(key1 < 'bar')

通常(比如前面展示的列子),用于范围扫描的条件比WHERE 语句更加宽松.MySQL 执行一个附加的检查来过滤行,过滤掉的行满足RANG条件但是不满足满足WHERE 语句.

range条件提取算法能够提取嵌套任意深度的AND/OR组合，并且输出不依赖AND/OR出现在where语句中的顺序.

MySQL 不支持合并多个范围对于空间索引.为了绕过这个限制, 你可以对相同的SELECT使用UNION,除非你每个空间SELECT语句不同.

 

2.The Range Access Method for Multiple-Part Indexes

对多部分索引使用Range conditions是对单部分索引的扩展.多个部分索引上的范围条件严格索引落在一个或多个关键字元组行的内部.
关键字元组范围定义在关键字集合上,根据索引顺序.

比如,考虑一个多部分索引 key1(key_part1,key_part2,key_part3), 以下的key元组集合以关键字顺序列出
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元组,并且能被range access method 使用.

通过对比,条件 key_part3 = ‘abc’ 没有定义一个单区间,也不能在range access method中使用.

以下的描述更加详细说明多部分索引范围条件工作原理.

对于Hash索引,包含同样值的每个区间都能够被使用.这个就意味着区间只能够被用于以下条件.
key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;

在这里,const1,const2,...都是常量,cmp 是=,<=>,or IS NULL操作符中的一个,条件覆盖索引的所有部分.
比如,以下范围条件是三个部分的hash索引
key_part1 =1 AND key_part2 IS NULL AND key_part3 = 'foo'

哪些定义会被视为常量,查看 The Range Access Method for Single-Part Indexes.

对于BTREE 索引,条件能使用的区间由一个或多个AND组成，每个条件使用常量值比较key的一部分,使用 =,<=>,IS NULL,>,<,>=,<=,!=,<>,
BETWEEN,或者 LIKE 'pattern'(pattern不能是通配符开头).每个区间同样可能用于决定单个包含所有行的key元组,key元组匹配条件
(或者俩个区间,如果使用if <> 或 != )

当比较操作符是=,<=>,或者 is NULL,优化器尽可能使用key parts的其他部分去决定区间.如果操作符是>,<,>=,<=,!=,<>,BETWEEN,或者LIKE,优化器不再考虑更多的
key parts。对于下面的表达式,优化器使用来自第一个比较操作的 =.也同样使用来自第二个比较的>=,但是不再考虑更多的key parts,也不会使用第三个比较符来构造
区间.

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),但是这个不包含在原始条件内.

 

如果覆盖行集合的条件包含或的区间,它们形成一个UNION所有区间的条件.如果是由AND组成,形成的条件就是所有区间的交集.比如.对于下面在俩部分索引上的条件.

(key_part1 =1 AND key_part < 2) OR (key_part1 > 5)

形成的区间是
(1,-inf) < (key_part1,key_part2) < (1,2)
(5,-inf) < (key_part1,key_part2)

在这个例子里,第一行的区间左边界使用key的一部分,右边界使用key的俩个部分.
第二行的区间仅使用key的一部分.EXPLAN语句输出的key_len表示使用的最大的key前缀.

在一些例子中,key_len可能表示使用key的部分，但是可能不是你所期望的那样.考虑如下列子,key_part1和key_part2可能是NULL.然后key_len列
展示俩个key部分
key_part1 >= 1 and key_part2 < 2

但是,实际上,条件被转成如下条件

key_part1 >= 1 AND key_part2 IS NOT NULL
The Range Access Method for Single-Part Indexes章节描述了优化器对范围条件单部分索引怎么组合和排除区间.对于多部分条件索引与其相似.

 

多值比较的等值范围优化
考虑如下表达式,col_name 是一个被索引的列
col_name IN (val1,...,vall1N)
col_name = val1 OR ... OR col_name =valN

如果col_name等于其中的一个或多个,那么这俩个表达式都是true.这种比较等价于范围比较(这里的范围表示单一值).优化器根据一下规则估计等价范围比较读取行的花费.

1.如果col_name 是unique 索引,对每个范围行估价为 1 ,因为最多只有一行包含给定的值.

2.要不然,col_name是非unique,优化器对每个范围估计行总数,使用索引统计信息或者(dives into the index) dives into the index(这个是什么???)

对于index dives,对每个结束的范围优化器都做一次dive,使用范围内行的数目做预估.比如,col_name IN (10,20,30)这个查询三个等价范围,优化器对每个范围做俩次
dive来生成行估价值.每对dive产出一个行数目来做为估价值.

索引Index dives 加速行估价,但是随着比较值的数目增加,优化器使用生成行估价花费长时间.使用索引统计信息虽然加速比index dives慢,但是可以更快生成
行估价对于大量的值.

你可以配置 eq_range_index_dive_limit 系统变量的值,以此控制优化器行估价策略。对于 N 个等价范围 为了允许使用 index dive,设置 eq_range_index_dive_limit
为 N +1. 为了关闭使用索引统计信息,总是使用index dives ,设置 eq_range_index_dive_limit 为 0.

eq_range_index_dive_limit 在5.6.5中可用,在5.6.5之前,优化器使用index dive,这就是等价于 eq_range_index_dive_limit =0.

为了更好的计算代价,使用 ANALYZE TABLE更新表索引统计信息,

posted on 2018-11-06 23:46 好吧,就是菜菜 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/shuiyonglewodezzzzz/p/9919762.html