8.2.1.3 Range Optimization

8.2.1.3 Range Optimization 范围访问方法使用一个单个的索引来检索表记录的自己,包含在一个或者索引值区间。它可以用于一个单独的部分或者多个部分的索引,下面章节给出了一个详细的描述关于如何从WHERE 子句中提取区间8.2.1.3.1  范围访问方法用于单部分索引对于一个单部分索引,索引值区间可以方便的体现通过相应的条件在WHERE子句,表示为范围条件,而不是"间隔"单个range 条件定义对于一个单部分索引如下:1. 对于BTREE和HASH 索引,key和恒定值比较是一个范围条件 当使用=,<=> in ，is NULL或者IS NOT NULL操作此外, 对于BTREE 索引, key和常值比较是一个条件当使用e >, <, >=, <=, BETWEEN, !=, or <>操作或者LIKE 比较如果参数to like 是一个常量 不是以一个通配符开始对于所有index type, 多个range 条件集合OR或者AND 为一个range 条件常量值在前面的描述意味着下面:1.从查询字符串中的常量2.一个常量或者系统表的列从相同关联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 子句对于每个可能的indexes.在提取过程中,条件不能被用于构造范围条件会被drop掉,条件产生重叠范围会被合并,条件产生空的范围会被删除考虑下面的语句,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. 删除nokey=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.折叠的条件总是真的或者假的:(key1 LIKE 'abcde%' OR TRUE) is always true(key1 < 'uux' AND key1 > 'z') is always false替换不必要的TRUE和FALSE常量,我们得到:(key1 < 'abc') OR (key1 < 'bar')通常(和前面的例子所证明的),条件用于一个范围扫描是很少限制的相比WHERE 子句MySQL 执行一个额外的检查来过滤出记录 来满足范围条件但是不能完整的WHERE 子句范围条件提取算法可以处理 netsted and/or 随意深度的结构, 它的输出不依赖条件出现在WHERE 子句的顺序MySQL 不支持 合并多个range 对于range 访问方法,要解决这个限制,你可以使用一个UNION 相同的SQL语句,除非你放置每个谓词在不同的SELECT 语句8.2.1.3.2 范围访问方法用于多部分索引:Range 条件在一个多部分多音是一个rang 条件的扩展,一个range 条件在多部分索引限制索引记录位于一个或者多个key tuple 区间。Key 区间是定义通过key 的集合例如,考虑一个多部分索引定义为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)对于B树索引,一个区间可能是用于条件集合AND,每个条件比较一个Key和一个常值 使用=, <=>, IS NULL, >, <, >=, <=, !=, <>, BETWEEN, or LIKE 'pattern' （模式不是以一个通配符开始的）一个区间可以用于确定一个单独的key 包含所有的记录,匹配条件(或者2个区间 如果 <> or != 被使用)优化尝试使用额外的索引部分来决定区间只要比较表达式 是 =, <=>, or IS NULL. I如果 操作符是>, <, >=, <=, !=, <>, BETWEEN, or LIKE,优化器使用它不考虑更多的key如果下面的表达式,优化器使用=从第一个比较,它也使用>=从第2个比较表达式 但是不在考虑索引部分key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10多只比较 平等范围优化:考虑表达式,col_names 是一个索引的列:col_name IN(val1, ..., valN)col_name = val1 OR ... OR col_name = valN如果col_name 是等于任何几个值中的一个 ,那么每个表达式是真的。那些表达式是等于范围比较(range 是一个单独的值)优化器评估 读取符合记录的成本对于平等范围比较如下：如果有一个唯一的索引在col_name,row 评估每个range 是1 因为最多一个记录可以有给定的值否则,任何Index 在col_name 是非唯一的,优化器可以评估记录数对于每个range 使用