8.2.1.3 Index Merge Optimization
The Index Merge access method retrieves rows with multiple range scans and merges their results into one. 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:
如果查询有一个复杂的WHERE子句, 查询包含带有深层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. 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.
在EXPLAIN输出中，索引合并方法在类型列中显示为index_merge。 在这种情况下，键列包含使用的索引列表，而key_len包含这些索引的最长键部分的列表。
The Index Merge access method has several algorithms, which are displayed in the
Extra field of EXPLAIN output:
索引合并访问方法具有几种算法，这些算法显示在EXPLAIN输出的Extra字段中：
-
Using intersect(...) -
Using union(...) -
Using sort_union(...)
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.
下面的部分将更详细地描述这些算法。优化器根据各种可用选项的成本估计，在不同可能的索引合并算法和其他访问方法之间进行选择。
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. See Section 8.9.2, “Switchable Optimizations”. By default, all those flags are on. To enable only certain algorithms, set index_merge to off, and enable only such of the others as should be permitted.
索引合并的使用取决于optimizer_switch系统变量的index_merge，index_merge_intersection，index_merge_union和index_merge_sort_union标志的值。 请参见第8.9.2节“可切换的优化”。 默认情况下，所有这些标志均处于启用状态。 要仅启用某些算法，请将index_merge设置为off，并仅启用应允许的其他算法。
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子句转换为与和组合的不同键上的多个范围条件时，此访问算法适用，并且每个条件都是以下条件之一：
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). Here is an example of such a query:
索引合并交集算法对所有使用的索引执行同步扫描，并生成从合并索引扫描接收的行序列的交集。
如果查询中使用的所有列都被使用的索引覆盖，则不会检索完整的表行（在本例中，EXPLAIN output contains Using index in Extra field）。下面是这样一个查询的示例：
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子句转换为不同键上的多个范围条件时，该算法适用，并且每个条件都是以下条件之一
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.
A condition for which the Index Merge intersection algorithm is applicable.
InnoDB表主键上的任何范围条件。 索引合并交集算法适用的条件。
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，并在返回任何行之前对它们进行排序。
