For a single-part index, index value intervals can be conveniently represented by corresponding conditions in the WHERE clause, denoted as range conditions rather than “intervals.”对于单个部分索引，索引值间隔可以方便地用WHERE子句中的相应条件表示，表示为“范围条件”而不是“间隔”

The definition of a range condition for a single-part index is as follows:单个零件索引的范围条件定义如下：

“Constant value” in the preceding descriptions means one of the following:上述说明中的“常量值”是指下列情况之一：

Here are some examples of queries with range conditions in the WHERE clause:以下是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';

Some nonconstant values may be converted to constants during the optimizer constant propagation phase.在优化器常量传播阶段，一些非常量值可能会转换为常量。

MySQL tries to extract range conditions from the WHERE clause for each of the possible indexes. MySQL尝试从WHERE子句中为每个可能的索引提取范围条件。During the extraction process, conditions that cannot be used for constructing the range condition are dropped, conditions that produce overlapping ranges are combined, and conditions that produce empty ranges are removed.在提取过程中，删除不能用于构造范围条件的条件，合并产生重叠范围的条件，并删除产生空范围的条件。

Consider the following statement, where key1 is an indexed column and nonkey is not indexed:考虑下面的语句，其中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');

The extraction process for key key1 is as follows:key1的提取过程如下：

In general (and as demonstrated by the preceding example), the condition used for a range scan is less restrictive than the WHERE clause. 一般来说（如前面的示例所示），用于范围扫描的条件比WHERE子句的限制性小。MySQL performs an additional check to filter out rows that satisfy the range condition but not the full WHERE clause.MySQL执行额外的检查来过滤出满足范围条件但不满足完整WHERE子句的行。

The range condition extraction algorithm can handle nested AND/OR constructs of arbitrary depth, and its output does not depend on the order in which conditions appear in WHERE clause.范围条件提取算法可以处理任意深度的嵌套AND/OR构造，其输出不依赖于WHERE子句中条件出现的顺序。

MySQL does not support merging multiple ranges for the range access method for spatial indexes. MySQL不支持为空间索引的range访问方法合并多个范围。To work around this limitation, you can use a UNION with identical SELECT statements, except that you put each spatial predicate in a different SELECT.要解决此限制，可以将UNION与相同的SELECT语句一起使用，只是将每个空间谓词放在不同的SELECT语句中。

Range conditions on a multiple-part index are an extension of range conditions for a single-part index. 多零件索引的范围条件是单个零件索引范围条件的扩展。A range condition on a multiple-part index restricts index rows to lie within one or several key tuple intervals. 多部分索引的范围条件限制索引行位于一个或多个键元组间隔内。Key tuple intervals are defined over a set of key tuples, using ordering from the index.密钥元组间隔是在一组密钥元组上定义的，使用索引的顺序。

For example, consider a multiple-part index defined as key1(key_part1, key_part2, key_part3), and the following set of key tuples listed in key order:例如，考虑一个定义为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'

The condition key_part1 = 1 defines this interval:条件key_part1 = 1定义了这个间隔：

(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

The interval covers the 4th, 5th, and 6th tuples in the preceding data set and can be used by the range access method.间隔覆盖了前面数据集中的第4、第5和第6个元组，可由范围访问方法使用。

By contrast, the condition key_part3 = 'abc' does not define a single interval and cannot be used by the range access method.相比之下，条件key_part3 = 'abc'没有定义单个间隔，不能由范围访问方法使用。

The following descriptions indicate how range conditions work for multiple-part indexes in greater detail.以下描述更详细地说明了范围条件如何适用于多个零件索引。

For a description of how optimizations are performed to combine or eliminate intervals for range conditions on a single-part index, see Range Access Method for Single-Part Indexes. 有关如何执行优化以组合或消除单个零件索引上范围条件的间隔的描述，请参见单个零件索引的范围访问方法。Analogous steps are performed for range conditions on multiple-part indexes.对多个零件索引的范围条件执行类似的步骤。

Consider these expressions, where col_name is an indexed column:考虑这些表达式，其中col_name是索引列：

col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN

Each expression is true if col_name is equal to any of several values. 如果col_name等于多个值中的任何一个，则每个表达式都为true。These comparisons are equality range comparisons (where the “range” is a single value). 这些比较是相等范围比较（其中“范围”是单个值）。The optimizer estimates the cost of reading qualifying rows for equality range comparisons as follows:优化器估计读取相等范围比较的合格行的成本，如下所示：

With index dives, the optimizer makes a dive at each end of a range and uses the number of rows in the range as the estimate. 使用索引跳转，优化器在范围的每一端跳转一次，并使用范围中的行数作为估计。For example, the expression col_name IN (10, 20, 30) has three equality ranges and the optimizer makes two dives per range to generate a row estimate. 例如，表达式col_name IN (10, 20, 30)有三个相等范围，优化器对每个范围进行两次下潜以生成行估计。Each pair of dives yields an estimate of the number of rows that have the given value.每对潜水产生一个具有给定值的行数估计值。

Index dives provide accurate row estimates, but as the number of comparison values in the expression increases, the optimizer takes longer to generate a row estimate. 索引潜水提供了精确的行估计，但是随着表达式中比较值的数量增加，优化器生成行估计所需的时间也会延长。Use of index statistics is less accurate than index dives but permits faster row estimation for large value lists.使用索引统计信息的准确性不如使用索引潜水，但可以更快地估计大值列表的行。

The eq_range_index_dive_limit system variable enables you to configure the number of values at which the optimizer switches from one row estimation strategy to the other. eq_range_index_dive_limit系统变量允许您配置优化器从一行估计策略切换到另一行估计策略的值的数量。To permit use of index dives for comparisons of up to N equality ranges, set eq_range_index_dive_limit to N + 1. 若要允许使用索引潜水来比较多达N个相等范围，请将eq_range_index_dive_limit设置为N+1。To disable use of statistics and always use index dives regardless of N, set eq_range_index_dive_limit to 0.若要禁用统计信息并始终使用索引潜水而不考虑N，请将eq_range_index_dive_limit设置为0。

To update table index statistics for best estimates, use ANALYZE TABLE.要更新表索引统计信息以获得最佳估计，请使用ANALYZE table。

Prior to MySQL 8.0, there is no way of skipping the use of index dives to estimate index usefulness, except by using the eq_range_index_dive_limit system variable. 在MySQL 8.0之前，除了使用eq_range_index_dive_limit系统变量外，没有任何方法可以跳过使用索引潜水来估计索引的有用性。In MySQL 8.0, index dive skipping is possible for queries that satisfy all these conditions:在MySQL 8.0中，对于满足所有这些条件的查询，索引跳转是可能的：

For EXPLAIN FOR CONNECTION, the output changes as follows if index dives are skipped:对于EXPLAIN For CONNECTION，如果跳过索引潜水，则输出更改如下：

Without FOR CONNECTION, EXPLAIN output does not change when index dives are skipped.如果没有FOR CONNECTION，当跳过索引潜水时，EXPLAIN输出不会更改。

After execution of a query for which index dives are skipped, the corresponding row in the INFORMATION_SCHEMA.OPTIMIZER_TRACE table contains an index_dives_for_range_access value of skipped_due_to_force_index.在执行跳过索引删除的查询后，INFORMATION_SCHEMA.OPTIMIZER_TRACE表中的相应行包含一个skipped_due_to_force_index的index_dives_for_range_access值。

Consider the following scenario:考虑下面的情景：

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

To execute this query, MySQL can choose an index scan to fetch all rows (the index includes all columns to be selected), then apply the f2 > 40 condition from the WHERE clause to produce the final result set.为了执行这个查询，MySQL可以选择一个索引扫描来获取所有的行（索引包括所有要选择的列），然后应用WHERE子句中的f2>40条件来生成最终结果集。

A range scan is more efficient than a full index scan, but cannot be used in this case because there is no condition on f1, the first index column. 范围扫描比全索引扫描效率更高，但在这种情况下不能使用，因为第一个索引列f1没有条件。However, as of MySQL 8.0.13, the optimizer can perform multiple range scans, one for each value of f1, using a method called Skip Scan that is similar to Loose Index Scan (see Section 8.2.1.17, “GROUP BY Optimization”):但是，从MySQL8.0.13开始，优化器可以使用类似于松散索引扫描的Skip-Scan方法（参见第8.2.1.17节，“按优化分组”）：

For the data set shown earlier, the algorithm operates like this:对于前面显示的数据集，算法的操作方式如下：

Using this strategy decreases the number of accessed rows because MySQL skips the rows that do not qualify for each constructed range. 使用这种策略可以减少被访问的行数，因为MySQL会跳过不符合每个构造范围的行。This Skip Scan access method is applicable under the following conditions:此跳过扫描访问方法适用于以下情况：

Use of Skip Scan is indicated in EXPLAIN output as follows:跳过扫描的使用在EXPLAIN输出中指示如下：

Use of Skip Scan is indicated in optimizer trace output by a "skip scan" element of this form:跳过扫描的使用在优化器跟踪输出中由以下形式的"skip scan"元素指示：

"skip_scan_range": {
  "type": "skip_scan",
  "index": index_used_for_skip_scan,
  "key_parts_used_for_access": [key_parts_used_for_access],
  "range": [range]
}

You may also see a "best_skip_scan_summary" element. 您还可以看到"best_skip_scan_summary"元素。If Skip Scan is chosen as the best range access variant, a "chosen_range_access_summary" is written. 如果选择跳过扫描作为最佳范围访问变量，则会写入"chosen_range_access_summary"。If Skip Scan is chosen as the overall best access method, a "best_access_path" element is present.如果选择跳过扫描作为总体最佳访问方法，则会出现best_access_path"元素。

Use of Skip Scan is subject to the value of the skip_scan flag of the optimizer_switch system variable. 跳过扫描的使用取决于optimizer_switch系统变量的skip_scan标志的值。See Section 8.9.2, “Switchable Optimizations”. 参见第8.9.2节，“可切换优化”。By default, this flag is on. 默认情况下，此标志处于on状态。To disable it, set skip_scan to off.要禁用它，请将skip_scan设置为off。

In addition to using the optimizer_switch system variable to control optimizer use of Skip Scan session-wide, MySQL supports optimizer hints to influence the optimizer on a per-statement basis. 除了使用optimizer_switch系统变量来控制优化器在整个跳过扫描会话范围内的使用之外，MySQL还支持优化器提示来影响每个语句的优化器。See Section 8.9.3, “Optimizer Hints”.请参阅第8.9.3节，“优化器提示”。

The optimizer is able to apply the range scan access method to queries of this form:优化器能够将范围扫描访问方法应用于以下形式的查询：

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

Previously, for range scans to be used, it was necessary to write the query as:以前，要使用范围扫描，必须将查询编写为：

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );

For the optimizer to use a range scan, queries must satisfy these conditions:要使优化器使用范围扫描，查询必须满足以下条件：

For more information about the optimizer and row constructors, see Section 8.2.1.22, “Row Constructor Expression Optimization”有关优化器和行构造函数的更多信息，请参阅第8.2.1.22节，“行构造函数表达式优化”

To control the memory available to the range optimizer, use the range_optimizer_max_mem_size system variable:要控制范围优化器可用的内存，请使用range_optimizer_max_mem_size系统变量：

For individual queries that exceed the available range optimization memory and for which the optimizer falls back to less optimal plans, increasing the range_optimizer_max_mem_size value may improve performance.对于超出可用范围优化内存且优化器退回到较低优化计划的单个查询，增加range_optimizer_max_mem_size值可能会提高性能。

To estimate the amount of memory needed to process a range expression, use these guidelines:要估计处理范围表达式所需的内存量，请使用以下准则：