8.2.1.3 Index Merge Optimization索引合并优化

The Index Merge access method retrieves rows with multiple range scans and merges their results into one. 索引合并访问方法检索具有多个range扫描的行，并将其结果合并到一个范围扫描中。This access method merges index scans from a single table only, not scans across multiple tables. 此访问方法仅合并来自单个表的索引扫描，而不合并跨多个表的扫描。The merge can produce unions, intersections, or unions-of-intersections of its underlying scans.合并可以生成其基础扫描的并集、交集或交集的并集。

Example queries for which Index Merge may be used:可以使用索引合并的查询示例：

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1 = 1
  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);
Note注意

The Index Merge optimization algorithm has the following known limitations:索引合并优化算法具有以下已知限制：

  • If your query has a complex WHERE clause with deep AND/OR nesting and MySQL does not choose the optimal plan, try distributing terms using the following identity transformations:如果您的查询有一个带有深度AND/OR嵌套的复杂WHERE子句，并且MySQL没有选择最佳方案，请尝试使用以下标识转换分发术语：

    (x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)

  • Index Merge is not applicable to full-text indexes.索引合并不适用于全文索引。

In EXPLAIN output, the Index Merge method appears as index_merge in the type column. 在EXPLAIN输出中，索引合并方法在type列中显示为index_merge。In this case, the key column contains a list of indexes used, and key_len contains a list of the longest key parts for those indexes.在本例中，key列包含所用索引的列表，key_len包含这些索引的最长键部分的列表。

The Index Merge access method has several algorithms, which are displayed in the Extra field of EXPLAIN output:索引合并访问方法有几个算法，这些算法显示在EXPLAIN输出的Extra字段中：

The following sections describe these algorithms in greater detail. 以下各节将更详细地描述这些算法。The optimizer chooses between different possible Index Merge algorithms and other access methods based on cost estimates of the various available options.优化器根据各种可用选项的成本估计，在不同的索引合并算法和其他访问方法之间进行选择。

Index Merge Intersection Access Algorithm索引合并交叉访问算法

This access algorithm is applicable when a WHERE clause is converted to several range conditions on different keys combined with AND, and each condition is one of the following:当WHERE子句被转换为与AND组合的不同键上的多个范围条件，并且每个条件是以下条件之一时，此访问算法适用：

  • An N-part expression of this form, where the index has exactly N parts (that is, all index parts are covered):这种形式的N部分表达式，其中索引正好有N部分（即，所有索引部分都包含在内）：

    key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN

  • Any range condition over the primary key of an InnoDB table.InnoDB表主键上的任何范围条件。

Examples:示例：

SELECT * FROM innodb_table
  WHERE primary_key < 10 AND key_col1 = 20;

SELECT * FROM tbl_name
  WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

The Index Merge intersection algorithm performs simultaneous scans on all used indexes and produces the intersection of row sequences that it receives from the merged index scans.索引合并交集算法对所有使用的索引执行同时扫描，并生成从合并索引扫描接收的行序列的交集。

If all columns used in the query are covered by the used indexes, full table rows are not retrieved (EXPLAIN output contains Using index in Extra field in this case). 如果查询中使用的所有列都被使用的索引覆盖，则不会检索完整的表行（在本例中，EXPLAIN输出包含在Extra字段中Using index）。Here is an example of such a query:下面是这样一个查询的示例：

SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;

If the used indexes do not cover all columns used in the query, full rows are retrieved only when the range conditions for all used keys are satisfied.如果使用的索引没有覆盖查询中使用的所有列，则仅当满足所有使用的键的范围条件时，才会检索整行。

If one of the merged conditions is a condition over the primary key of an InnoDB table, it is not used for row retrieval, but is used to filter out rows retrieved using other conditions.如果合并条件之一是InnoDB表主键上的条件，则不用于行检索，而是用于过滤使用其他条件检索的行。

Index Merge Union Access Algorithm索引合并联合访问算法

The criteria for this algorithm are similar to those for the Index Merge intersection algorithm. 此算法的准则与索引合并交集算法的准则相似。The algorithm is applicable when the table's WHERE clause is converted to several range conditions on different keys combined with OR, and each condition is one of the following:当表的WHERE子句转换为不同键上的多个范围条件并结合OR时，该算法适用，并且每个条件是以下条件之一：

  • An N-part expression of this form, where the index has exactly N parts (that is, all index parts are covered):这种形式的N部分表达式，其中索引正好有N部分（即，所有索引部分都包含在内）：

    key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN

  • Any range condition over a primary key of an InnoDB table.InnoDB表主键上的任何范围条件。

  • A condition for which the Index Merge intersection algorithm is applicable.索引合并交集算法适用的条件。

Examples:示例：

SELECT * FROM t1
  WHERE key1 = 1 OR key2 = 2 OR key3 = 3;

SELECT * FROM innodb_table
  WHERE (key1 = 1 AND key2 = 2)
     OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;
Index Merge Sort-Union Access Algorithm索引合并排序联合存取算法

This access algorithm is applicable when the WHERE clause is converted to several range conditions combined by OR, but the Index Merge union algorithm is not applicable.当WHERE子句转换为OR组合的多个范围条件时，此访问算法适用，但索引合并联合算法不适用。

Examples:示例：

SELECT * FROM tbl_name
  WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;

The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm must first fetch row IDs for all rows and sort them before returning any rows.sort-union算法和union算法的区别在于，sort-union算法必须首先获取所有行的行id，并在返回任何行之前对它们进行排序。

Influencing Index Merge Optimization影响指标合并优化

Use of Index Merge is subject to the value of the index_merge, index_merge_intersection, index_merge_union, and index_merge_sort_union flags of the optimizer_switch system variable. 索引合并的使用取决于优化器开关系统变量的index_merge、index_merge_intersection、index_merge_union和index_merge_sort_union标志的值。See Section 8.9.2, “Switchable Optimizations”. 参见第8.9.2节，“可切换优化”。By default, all those flags are on. 默认情况下，所有这些标志都处于on状态。To enable only certain algorithms, set index_merge to off, and enable only such of the others as should be permitted.若要仅启用某些算法，请将index_merge设置为off，并仅启用应允许的其他算法。

In addition to using the optimizer_switch system variable to control optimizer use of the Index Merge algorithms 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节，“优化器提示”。