# 4.2 范围优化 `range`访问方法使用单个索引来检索包含一个或若干个索引值的时间间隔内表行的子集。它可以用于单部分或多部分索引。以下各节描述了优化器使用范围访问的条件。 #### 单部分索引的范围访问方法 对于单部分索引，索引值间隔可以方便地由条款中的相应条件 `WHERE`表示，称为 范围条件， 而不是“ 间隔”。” 单部分索引的范围条件的定义如下： * 对于这两种`BTREE`和 `HASH`索引，使用时具有恒定值的关键部分的比较是一个范围条件 `=`， `<=>`， `IN()`，`IS NULL`，或`IS NOT NULL`运营商。 * 另外，对于`BTREE`索引，当使用具有恒定值的关键部分的比较是一个范围条件 `>`， `<`， `>=`， `<=`， `BETWEEN`， `!=`，或 `<>`运营商，或者`LIKE`比较，如果参数 `LIKE`是一个常数字符串不与通配符开始。 * 对于所有索引类型，多个范围条件组合`OR`或 `AND`形成一个范围条件。 前面的描述中的“ 恒定值 ”表示以下之一： * 查询字符串中的常量 * 来自同一联接 的`const`或\[`system`表的 列 * 不相关子查询的结果 * 任何完全由上述类型的子表达式组成的表达式 以下是该`WHERE`子句中具有范围条件的查询示例： ```sql 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`而没有索引： ```sql 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`子句开始： ```sql (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`产量代替它们： ```sql (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)` 永远是真的 * `(key1 < 'uux' AND key1 > 'z')` 永远是假的 用常量替换这些条件将产生： ```clike (key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE) ``` 去除不必要的`TRUE`和 `FALSE`常数的产率： ```clike (key1 < 'abc') OR (key1 < 'bar') ``` 4. 将重叠的间隔合并为一个会产生用于范围扫描的最终条件： ```clike (key1 < 'bar') ``` 一般而言（如前面的示例所示），范围扫描所使用的条件比该`WHERE`子句的限制要少。MySQL执行附加检查以过滤出满足范围条件但不包括full `WHERE`子句的行。 范围条件提取算法可以处理 任意深度的嵌套 `AND`/ `OR`构造，并且其输出不取决于条件在`WHERE`子句中出现的顺序 。 MySQL不支持`range`为空间索引的访问方法合并多个范围 。要解决此限制，可以将a `UNION`与相同的`SELECT`语句一起 使用，只是将每个空间谓词放在不同的中 `SELECT`。 #### 多部分索引的范围访问方法 多部分索引的范围条件是单部分索引的范围条件的扩展。多部分索引上的范围条件将索引行限制在一个或几个键元组间隔内。使用从索引开始的顺序，在一组键元组上定义键元组间隔。 例如，考虑定义为的多部分索引 ，并按键顺序列出以下一组键元组： `key1(*`key\_part1`*, *`key\_part2`*, *`key\_part3`*)` ```clike 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`定义了此间隔： ```clike (1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf) ``` 该间隔覆盖了先前数据集中的第4，第5和第6个元组，并且可以由范围访问方法使用。 相反，该条件 `*`key\_part3`* = 'abc'`未定义单个间隔，并且不能被范围访问方法使用。 以下描述更详细地说明了范围条件如何作用于多部分索引。 * 对于`HASH`索引，可以使用包含相同值的每个间隔。这意味着只能针对以下形式的条件生成间隔： ```sql key_part1 cmp const1 AND key_part2 cmp const2 AND ... AND key_partN cmp constN; ``` 这里\*`const1`*， `const2`...是常数，*`cmp`*是一个 `=`， `<=>`或者`IS NULL`比较运营商，以及条件覆盖所有指数部分。（也就是说，*`N`\* 有条件，*`N`*-part索引的每个部分都有一个 条件。）例如，以下是三部分`HASH`索引的范围条件 ： ```sql key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo' ``` * 对于一个BTREE索引，以一定间隔可能是可用于条件组合 AND，其中每个状态具有恒定值使用一个关键部分进行比较 =， <=>， IS NULL， >， <， >=， <=， !=， <>， BETWEEN，或 （其中 LIKE 'pattern''pattern' 不以通配符开头）。只要可以确定包含所有与条件匹配的行的单个键元组，就可以使用一个间隔（如果使用<> 或，!= 则使用两个间隔 ）。 只要比较运算符为，或=， 优化器就会尝试使用其他关键部分来确定间隔 。如果操作是 ， ， ， ， ， ， ，或者 ，优化器使用它，但认为没有更多的关键部分。对于以下表达式，优化器使用 第一个比较中的值。它也使用 <=>IS NULL><>=<=!=<>BETWEENLIKE=>= 根据第二次比较，但不考虑其他关键部分，并且不将第三次比较用于区间构造： ```sql key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10 ``` * 单个间隔为： ```sql ``` ('foo',10,-inf) < (key\_part1,key\_part2,key\_part3) < ('foo',+inf,+inf) ```` 创建的间隔可能包含比初始条件更多的行。例如，前面的时间间隔包含`('foo', 11, 0)`不满足原始条件的值。 - 如果将覆盖间隔中包含的行集合的条件与组合`OR`，则它们将形成覆盖间隔中的并 集中包含的行集合的条件。如果条件与组合 `AND`，则它们将形成一个条件，该条件覆盖其间隔的交点内包含的一组行。例如，对于由两部分组成的索引的这种情况： ```sql (key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5) ```` 间隔为： ```sql (1,-inf) < (key_part1,key_part2) < (1,2) (5,-inf) < (key_part1,key_part2) ``` 在此示例中，第一行的间隔使用一个关键部分作为左边界，使用两个关键部分作为右边界。第二行的间隔仅使用一个关键部分。输出中的`key_len`列`EXPLAIN`表示所使用的密钥前缀的最大长度。 在某些情况下，`key_len`可能表明已使用了关键部件，但这可能不是您期望的。假设 \*`key_part1`\*和 \*`key_part2`\*可以是 `NULL`。然后，该 `key_len`列显示以下条件的两个关键零件长度： ```sql key_part1 >= 1 AND key_part2 < 2 ``` 但是，实际上，条件已转换为： ```sql key_part1 >= 1 AND key_part2 IS NOT NULL ``` #### 多值比较的等距范围优化 考虑以下表达式，其中 \*`col_name`\*是索引列： ```sql col_name IN(val1, ..., valN) col_name = val1 OR ... OR col_name = valN ``` 如果\*`col_name`\*等于多个值中的任何一个，则每个表达式为true 。这些比较是相等范围比较（其中“ range ”是单个值）。优化器估算相等范围比较的读取合格行的成本，如下所示： * 如果在上有唯一索引 *`col_name`*，则每个范围的行估计为1，因为最多一行可以具有给定值。 * 否则，任何索引 \*`col_name`\*都不是唯一的，优化器可以使用对索引或索引统计的深入估算来估计每个范围的行数。 使用索引潜水时，优化器在范围的每个末端进行潜水，并将范围中的行数用作估计值。例如，表达式 `*`col\_name`* IN (10, 20, 30)`具有三个相等范围，并且优化器对每个范围进行两次下潜以生成行估计。每对潜水都会得出具有给定值的行数的估计值。 索引潜水可提供准确的行估计，但是随着表达式中比较值数量的增加，优化器将花费更长的时间来生成行估计。使用索引统计数据的准确性不及使用索引潜水的准确性，但允许对大型值列表进行更快的行估计。 使用 `eq_range_index_dive_limit`系统变量，您可以配置优化程序从一种行估计策略切换到另一种行估计策略的值的数量。要允许使用索引潜水进行最多\*`N`\* 等于范围的比较，请设置 `eq_range_index_dive_limit` 为\*`N`\*+1。要禁用统计信息，并且始终使用索引潜水而不管 *`N`*，将其设置 `eq_range_index_dive_limit`为0。 要更新表索引统计信息以获得最佳估计值，请使用 `ANALYZE TABLE`。 即使在本应使用索引潜水的条件下，对于满足所有这些条件的查询也将跳过它们： * 存在单索引`FORCE INDEX`索引提示。这样的想法是，如果强制使用索引，那么执行潜入索引的额外开销将无济于事。 * 索引不是唯一索引，不是 `FULLTEXT`索引。 * 没有子查询。 * 没有`DISTINCT`，`GROUP BY`或`ORDER BY`子句存在。 这些跳水条件仅适用于单表查询。对于多表查询（联接），不会跳过索引潜水。 #### 行构造函数表达式的范围优化 优化器可以将范围扫描访问方法应用于以下形式的查询： ```sql SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' )); ``` 以前，要使用范围扫描，必须将查询编写为： ```sql SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' ) OR ( col_1 = 'c' AND col_2 = 'd' ); ``` 为了使优化器使用范围扫描，查询必须满足以下条件： * 仅使用IN()谓词，不使用NOT IN()。 * 在IN()谓词的左侧 ，行构造器仅包含列引用。 * 在IN()谓词的右侧，行构造器仅包含运行时常量，这些常量是在执行期间绑定到常量的文字或本地列引用。 * 在IN()谓词的右侧，有多个行构造函数。 #### 限制内存使用以进行范围优化 要控制范围优化器可用的内存，请使用 range\_optimizer\_max\_mem\_size 系统变量： * 值0表示“ 无限制”。” * 值大于0时，优化器将在考虑范围访问方法时跟踪消耗的内存。如果将要超过指定的限制，则将放弃范围访问方法，而应考虑其他方法，包括全表扫描。这可能不是最佳选择。如果发生这种情况，则会发生以下警告（ N当前 range\_optimizer\_max\_mem\_size 值为）： ``` Warning 3170 Memory capacity of N bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query. ``` * 对于UPDATE和 DELETE语句，如果优化器退回到全表扫描并且sql\_safe\_updates启用了 系统变量，则会发生错误而不是警告，因为实际上，没有键用于确定要修改的行 对于超出可用范围优化内存的单个查询，并且对于该查询，优化器将退回至次优计划，增加 range\_optimizer\_max\_mem\_size 值可以提高性能。 若要估计处理范围表达式所需的内存量，请使用以下准则： * 对于诸如以下的简单查询，其中有一个用于范围访问方法的候选关键字，与组合`OR` 使用的每个谓词大约使用230个字节： ```sql SELECT COUNT(*) FROM t WHERE a=1 OR a=2 OR a=3 OR .. . a=N; ``` * 类似地，对于以下查询，每个谓词组合`AND`使用大约125个字节： ```sql SELECT COUNT(*) FROM t WHERE a=1 AND b=1 AND c=1 ... N; ``` * 对于带有`IN()`谓词的查询： ```sql SELECT COUNT(*) FROM t WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N); ``` IN()列表 中的每个文字值都 与组合为谓词OR。如果有两个IN() 列表，则与组合的谓词 OR数量是每个列表中文字值数量的乘积。因此，OR在前一种情况下组合的谓词数 为 M× N。 --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://yanghaiji.gitbook.io/advanced-books/note/mysql/book/sql-you-hua-15/fan-wei-you-hua.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.