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

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(...)

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

索引合并交集访问算法

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

这种形式的N-part 表达式，其中索引具有精确的 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输出包含 Using index在Extra 字段中）。以下是此类查询的示例：

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

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

如果其中一个合并条件是对InnoDB表主键的条件，则它不用于行检索，而是用于过滤掉使用其他条件检索的行。

索引合并联合访问算法

该算法的标准类似于索引合并交集算法的标准。该算法适用于将表的WHERE 子句转换为不同键结合的多个范围条件OR，且每个条件为以下之一：

这种形式的N-part 表达式，其中索引具有精确的 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，但 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_intersection、 index_merge_union和 index_merge_sort_union flags 的值。optimizer_switch请参阅第 8.9.2 节，“可切换优化”。默认情况下，所有这些标志都是on. 要仅启用某些算法，请设置 index_merge为 off，并仅启用应允许的其他算法。

除了使用 optimizer_switch系统变量来控制优化器在会话范围内使用索引合并算法之外，MySQL 还支持优化器提示以在每个语句的基础上影响优化器。请参阅第 8.9.3 节，“优化器提示”。