8.2.1.3 索引合并优化_MySQL 5.7 参考手册

MySQL 8.0 参考手册 / 第8章优化 / 8.2 优化SQL语句 / 8.2.1 优化 SELECT 语句 / 8.2.1.3 索引合并优化

8.2.1.3 索引合并优化

Index Merge访问方法通过多次扫描检索行 range并将它们的结果合并为一个。此访问方法仅合并来自单个表的索引扫描，而不是跨多个表的扫描。合并可以产生其底层扫描的并集、交集或交集并集。

可以使用索引合并的示例查询：

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);

笔记

索引合并优化算法具有以下已知限制：

如果您的查询有一个复杂的WHERE 子句，带有深度 AND/OR 嵌套，而 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 不适用于全文索引。

在EXPLAIN输出中，Index Merge 方法显示 index_merge在 type列中。在这种情况下，该 key列包含使用的索引列表，并key_len包含这些索引的最长键部分的列表。

Index Merge 访问方法有几种算法，显示在输出Extra字段中 EXPLAIN：

Using intersect(...)
Using union(...)
Using sort_union(...)

以下部分更详细地描述了这些算法。优化器根据各种可用选项的成本估算，在不同的可能索引合并算法和其他访问方法之间进行选择。

Index Merge 的使用受制于系统变量的index_merge、 index_merge_intersection、 index_merge_union和 index_merge_sort_unionflags 的值。optimizer_switch请参阅第 8.9.2 节，“可切换优化”。默认情况下，所有这些标志都是on. 要仅启用某些算法，请设置index_merge 为off，并仅启用应允许的其他算法。

索引合并交集访问算法

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:

An N-part expression of this form, where the index has exactly N parts (that is, all index parts are covered):
```
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
```
Any range condition over the primary key of an InnoDB table.

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:

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.

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:

An N-part expression of this form, where the index has exactly N parts (that is, all index parts are covered):
```
key_part1 = const1 OR key_part2 = const2 ... OR 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.

例子：

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;

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

该访问算法适用于 WHERE子句转换为由组合的多个范围条件 OR，但 Index Merge 并集算法不适用。

例子：

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;

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