8.2.1.3索引合并优化

8.2.1.3 Index Merge Optimization 8.2.1.3索引合并优化

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.

Index Merge 访问方法检索具有多个range扫描的行，并将其结果合并为一个行。这种访问方法只将来自单个表的索引扫描合并，而不是跨多个表进行扫描。合并可以产生它的基础扫描的并集、交集或交集。

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. 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 方法在类型列中显示为 index_merge。在这种情况下，键列包含使用的索引列表，键列包含这些索引的最长键部分的列表。

The Index Merge access method has several algorithms, which are displayed in the Extra field of EXPLAIN output:

Index Merge 访问方法有几个算法，它们显示在 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.

下面的小节将更详细地描述这些算法。优化器根据各种可用选项的成本估计，在不同的可能的 Index Merge 算法和其他访问方法之间进行选择。

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.

Index Merge 的使用取决于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

  索引合并交叉访问算法

• Index Merge Union Access Algorithm

  索引合并并行访问算法

• Index Merge Sort-Union Access Algorithm

  索引合并排序-联合访问算法

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.

Index Merge 交集算法对所有使用的索引执行同步扫描，并生成它从合并索引扫描接收到的行序列的交集。

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 输出包含在 Extra 字段中使用索引)。下面是一个例子:

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:

该算法的标准与 Index Merge 交集算法的标准相似。当表中的 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.

  适用于 Index Merge 交集算法的条件。

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 组合的多个范围条件时，此访问算法适用，但 Index Merge 联合算法不适用。

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.

排序联合算法和联合算法的区别在于，排序联合算法必须首先获取所有行的行 id，并在返回任何行之前对它们进行排序。

----------------------

原文链接： https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html