MySQL :: MySQL 8.4 参考手册 :: 10.2.1.3 索引合并优化

版本 8.4

8.0 日语

MySQL 8.4 参考手册 / ... / 索引合并优化

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

注意

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

如果您的查询具有一个复杂的 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)
```
索引合并不适用于全文索引。

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

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

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

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

索引合并交集访问算法

当 WHERE 子句转换为使用 AND 组合的不同键上的多个范围条件时，这种访问算法适用，并且每个条件都是以下之一

这种形式的 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;

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

如果查询中使用的所有列都由使用的索引覆盖，则不会检索完整表行（在这种情况下，EXPLAIN 输出的 Extra 字段包含 Using index）。以下是一个此类查询的示例

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

如果使用的索引没有覆盖查询中使用的所有列，则只有当所有使用的键的范围条件都满足时才会检索完整行。

如果合并的条件之一是在 InnoDB 表的主键上的条件，则它不会用于行检索，而是用于过滤掉使用其他条件检索到的行。

索引合并并集访问算法

此算法的标准与索引合并交集算法的标准类似。当表的 WHERE 子句转换为使用 OR 组合的不同键上的多个范围条件时，该算法适用，并且每个条件都是以下之一

这种形式的 N 部分表达式，其中索引正好有 N 部分（即，所有索引部分都已覆盖）
```
key_part1 = const1 OR key_part2 = const2 ... OR key_partN = constN
```
在 InnoDB 表的主键上的任何范围条件。
索引合并交集算法适用的条件。

示例

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 组合的多个范围条件时，这种访问算法适用，但索引合并并集算法不适用。

示例

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;

排序联合算法与联合算法之间的区别在于，排序联合算法必须首先获取所有行的行 ID 并对其进行排序，然后再返回任何行。

影响索引合并优化

索引合并的使用受 index_merge、index_merge_intersection、index_merge_union 和 index_merge_sort_union 这些标志的值控制，这些标志属于 optimizer_switch 系统变量。参见第 10.9.2 节，“可切换优化”。默认情况下，所有这些标志都为 on。要仅启用某些算法，请将 index_merge 设置为 off，并仅启用应允许的其他算法。

除了使用 optimizer_switch 系统变量在会话级别控制优化器对索引合并算法的使用外，MySQL 还支持优化器提示，以便在每个语句的基础上影响优化器。参见第 10.9.3 节，“优化器提示”。