sql优化教程之RangeOptimization实例

1、The Range Access Method for Single-Part Indexes

single-part索引的范围条件定义如下：

不管是BTREE还是HASH索引，使用=，<=>，IN()，IS NULL或IS NOT NULL运算符将索引字段与常量值的比较都是一个range conditions(范围条件)。另外，对于BTREE索引，当使用>，<，> =，<=，BETWEEN，!=或<>运算符或LIKE比较符(LIKE的参数是一个不以通配符开头的常量字符串)时，索引与常数值的比较是一个范围条件。对于所有索引类型，多个以OR或AND连接的范围条件会结合形成一个范围条件。

前面描述中的“Constant value”表示以下之一：

A constant from the query string

A column of aconstorsystemtable from the same join

The result of an uncorrelated subquery

Any expression composed entirely from subexpressions of the preceding types

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的范围条件的提取过程如下：

通常，用于范围扫描的条件比WHERE子句的条件更宽松。 MySQL会执行额外的检查来过滤满足范围条件但不满足完整WHERE子句的rows。

范围条件提取算法可以处理任意深度的嵌套AND / OR构造，其输出不依赖于条件在WHERE子句中出现的顺序。

2、The Range Access Method for Multiple-Part Indexes

Multiple-Part索引的范围条件是Single-Part索引的范围条件的扩展。多部分索引的范围条件将索引行限制在一个或多个key tuple intervals(元组间隔)内。关键元组间隔定义在一组key tuple上，使用索引中的排序。

例如，考虑定义为key1(key_part1，key_part2，key_part3)的Multiple-Part索引并且按key的顺序列出的以下一组key tuple：

条件key_part1 = 1定义了这个间隔：

该区间覆盖了前述数据集中的第4,5和6个元组，并且可以由range access method(范围访问方法)使用。

相比之下，条件key_part3 ='abc'没有定义单个区间，所以范围访问方法不能使用这个条件范围。

以下描述更详细地指出了范围条件如何对Multiple-Part起作用。

对于BTREE索引，区间适用于用AND组合的条件，其中每个条件都使用=，<=>，IS NULL，>，<，> =，<=，!=或者<>，BETWEEN或LIKE'pattern'(其中'pattern'不以通配符开头)来比较索引与常量值 。只要可以确定包含了条件匹配的所有rows的单个key tuple，就可以使用单个区间，或者使用两个区间，如果条件使用了<>或!=。如果覆盖区间内包含的行集合的条件是使用OR组合的，则它们会结合一个条件，该条件涵盖了包含在区间并集中的rows。如果条件使用AND连接的，则它们形成一个条件，这个条件涵盖了包含在它们的区间交集内的rows。例如，查询条件使用two-part索引的这种情况：

If there is a unique index oncol_name, the row estimate for each range is 1 because at most one row can have the given value.

Otherwise, any index oncol_nameis nonunique and the optimizer can estimate the row count for each range using dives into the index or index statistics.

4、Range Optimization of Row Constructor Expressions

优化器能够将范围扫描访问方法应用于下面这种形式的查询：

以前，为了使用范围扫描，有必要将查询写为：

为了优化器使用范围扫描，查询必须满足以下条件：

OnlyIN()predicates are used, notNOT IN().

On the left side of theIN()predicate, the row constructor contains only column references.

On the right side of theIN()predicate, row constructors contain only runtime constants, which are either literals or local column references that are bound to constants during execution.

On the right side of theIN()predicate, there is more than one row constructor.

For more information about the optimizer and row constructors, seeSection8.2.1.19, “Row Constructor Expression Optimization”