MySQL :: MySQL 8.4 Reference Manual :: 10.2.1.3 Index Merge Optimization

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / Index Merge Optimization

10.2.1.3 索引合并优化

索引合并访问方法从多个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);

Note

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

如果您的查询有复杂的WHERE子句，包含深层AND/OR嵌套，并且MySQL没有选择最优plan，尝试使用以下身份变换来分布项：
```
(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)
```
索引合并不适用于全文索引。

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

索引合并访问方法具有多个算法，这些算法在EXPLAIN输出的Extra字段中显示：

Using intersect(...)
使用union(...)
使用sort_union(...)

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

当WHERE子句被转换为多个范围条件，分别应用于不同的键，并且每个条件都是以下形式之一时，这种访问算法适用：

一个由N部分组成的表达式，其中索引恰好有N部分（即所有索引部分都被覆盖）：
```
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
```
对InnoDB表的主键范围条件。

示例：

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;

Index Merge.intersection算法同时扫描所有使用的索引，并生成从合并索引扫描中接收到的行序列的交集。

如果查询中使用的所有列都被使用的索引覆盖，full table rows将不会被检索（在这种情况下，EXPLAIN输出中包含Using index在Extra字段）。以下是一个这样的查询示例：

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

如果使用的索引不覆盖查询中使用的所有列，full rows将只在满足所有用于键的范围条件时被检索。

如果合并条件之一是对InnoDB表的主键的条件，它将不会用于行检索，但用于过滤使用其他条件检索出的行。

这个算法的标准与Index Merge.intersection算法类似。该算法适用于将表的WHERE子句转换为不同的键上范围条件，使用OR组合，每个条件是以下之一：

一个形式为的N-part表达式，其中索引具有 exactly N 部分（即所有索引部分都被覆盖）：
```
key_part1 = const1 OR key_part2 = const2 ... OR key_partN = constN
```
InnoDB表的主键上任何范围条件。
Index Merge.intersection算法适用的条件。

示例：

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，并对它们进行排序，然后返回任何行。

影响Index Merge优化

Index Merge的使用受index_merge、index_merge_intersection、index_merge_union和index_merge_sort_union标志的影响，见第10.9.2节，“可切换优化”。默认情况下，这些标志都设置为on。要启用特定的算法，设置index_merge到off，并启用其他允许的标志。

除了使用optimizer_switch系统变量来控制会话范围内的索引合并算法使用外，MySQL还支持在语句级别上影响优化器的 hint。请参阅第10.9.3节，“Optimizer Hints”。

PREV HOME UP NEXT