索引合并

本文翻译自MySQL 8.0 Reference Manual 8.2.1.3 Index Merge Optimization

Index Merge访问方法(Access method)通过合并多个范围扫描并对结果进行合并，然后获取最终需要返回的行数据。合并多个索引的扫描方法仅仅支持单表访问，不可用于多表关联查询。目前支持的合并方式有并集(unions)，交集(intersections)以及交集的并集(unions of intersections)。

如下的查询可能会采用Index Merge优化

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key = 20;

SELECT * FROM t1, t2
WHERE (t1.keys IN(1, 2) OR t1.key2 LIKE 'value%')
AND t2.key2 = t1.some_col;

SELECT * FROM t1, t2
WHERE t1.key1 = 1
AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

限制

注意，Index Merge 算法具有如下限制：

1、如果查询语句具有复杂的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);

2、Index Merge不支持全文索引。

在EXPLAIN输出中，Index Merge优化会在type列标识为index merge。

在这种场景中，key列则列出了使用了哪些索引， key_len列表示了使用这些索引中的最长key值。

Index Merge优化支持多种算法，在EXPLAIN输出的Extra列中分别为

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

下面将分别详细介绍这几种算法，优化器通过对多个变量的代价估算来选择Index Merge算法或者其他访问方法。

Index Merge Intersection Access Algorithm 交集访问算法

Index Merge Union Access Algorithm 并集访问算法

Index Merge Sort-Union Access Algorithm 排序-并集访问算法

配置 Index Merge 优化

交集访问算法（Index Merge Intersection Access Algorithm）

当查询语句的WHERE子句为多个不同索引的范围条件，并且通过AND连接时，可以使用交集访问算法。

另外，要求AND连接的每个条件必须是如下形式之一：

1、当索引包含多个列时，则每个列都必须被如下等值条件覆盖：

key_par1 = const1 AND key_par2 = const2 ... AND key_partN = constN

2、InnoDB表主键的范围查询条件。

SELECT * FROM innodb_table
WHERE primary_key  10 OR key_col2 = 20) AND nonkey_col = 30;
--译者注：上面两个语句查询条件中没有满足多列索引的每个列都是等值条件

排序-并集访问算法和并集访问算法的不同在于排序-并集访问算法必须先获取所有满足条件的Row ID进行排序，然后再返回或者访问表数据。

配置Index Merge优化

可以通过optimizer_switch系统参数中的index_merge、index_merge_intersection、index_merge_union以及index_merge_sort_union标识控制Index Merge的使用。

默认配置下，这些标识都是启用状态-on。

如果只想启用特定的某种算法，则可以设置index_merge为off，然后将相应启用算法的标识设置为on即可。

个人收获

索引合并实际上是一种比较好的方式，避免创建联合索引。

从而使单个字段的查询都可以命中索引，又可以在某种程度降低索引的体积。

拓展阅读

mongodb 索引合并

参考资料

Index Merge Optimization

Index Merge 优化