一、问题背景

之前遇到过一个关于MySQL索引的面试题，问题如下：

一张数据库表t其中有3列为a、b、c，分别为每一个列单独建立一个二级索引，那么现在有一个查询条件select * from t where a=? and b=? and c=?
请问上述SQL语句，MySQL存储引擎在执行查询的时候，哪些索引将会被使用到？

当时一顿分析，还谈到了MySQL基于成本估算的优化，认为MySQL优化器先基于a、b、c三个列的索引区分度，选择索引区分度较高的索引进行扫描，根据第一个索引的扫描结果，如果第一个索引的扫描结果集高于一个固定量级，则继续选择第二个索引进行扫描，依次判断是否需要使用第三个索引，如果第一个索引的扫描结果集小于一个固定量级，则直接根据第一个索引的扫描结果集进行回表查询到列记录，再根据未使用到的2列进行过滤。

最后得出的结果是：a、b、c 3个索引都可能被使用到，具体使用到哪些索引，取决于3个索引的区分度以及表的数据量。

正确答案

上面的问题应该用索引合并优化的原理来分析，上述的SQL的where条件满足了索引合并优化中的Index Merge Intersection Access Algorithm,
存储引擎将会同时使用3个索引进行扫描，将扫描到的结果集取交集后返回给MySQL Server，然后再进行回表查询。

二、索引合并优化

索引合并访问方法检索具有多个范围扫描的行并将其结果合并为一个。此访问方法仅合并来自单个表的索引扫描，而不是跨多个表的扫描。合并可以生成其底层扫描的并集、交集或交集并集。

索引合并的查询案例

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
索引合并优化具有以下已知限制：
如果您的查询有一个带有深度 AND/OR 嵌套的复杂 WHERE 子句，并且 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 输出中，Index Merge 方法在 type 列中显示为 index_merge。在这种情况下，键列包含使用的索引列表，而 key_len 包含这些索引的最长键部分的列表。

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

Using intersect(…)
Using union(…)
Using sort_union(…)

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

索引合并的使用取决于MySQL优化器开关,分别是系统变量 index_merge、index_merge_intersection、index_merge_union 和 index_merge_sort_union 标志的值。
请参见第 8.9.2 节，“可切换的优化”。默认情况下，所有这些标志都打开。
要仅启用某些算法，请将 index_merge 设置为关闭，并仅启用应允许的其他算法。

Index Merge Intersection Access Algorithm
Index Merge Union Access Algorithm
Index Merge Sort-Union Access Algorithm

Index Merge Intersection Access Algorithm（索引合并交集访问算法）

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

这种形式的 N 部分表达式，其中索引正好有 N 部分（即，组合索引所有索引部分都被覆盖）：

-- 二级索引必须是等值查询，如果是组合索引，则组合索引的每一列都必须覆盖到
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN

InnoDB 表的主键上的任何范围条件。

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;

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

如果查询中使用的所有列都被使用的索引覆盖，则不会检索完整的表行（在这种情况下，EXPLAIN 输出包含在 Extra 字段中使用索引）。以下是此类查询的示例：
```
SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;
```
如果使用的索引未覆盖查询中使用的所有列，则仅当满足所有使用的键的范围条件时才检索完整的行。
如果其中一个合并条件是对 InnoDB 表的主键的条件，则它不用于行检索，而是用于过滤掉使用其他条件检索到的行。

Index Merge Union Access Algorithm(索引合并并集访问算法）

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

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

Examples

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;

Index Merge Sort-Union Access Algorithm(索引合并排序并集访问算法)

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

Examples

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 并在返回任何行之前对它们进行排序。

三、总结

索引合并交集访问算法

WHERE条件之间使用AND结合，而且每个条件必须满足以下两个条件：

二级索引必须是等值查询，如果是组合索引，组合索引的每一列都必须覆盖到
InnoDB表的主键范围条件查询

索引合并并集访问算法

与索引合并交集访问算法类似，不过使用了OR连接条件，且满足以下条件：

二级索引必须是等值查询，如果是组合索引，组合索引的每一列都必须覆盖到
InnoDB表的主键范围条件查询
符合索引合并交集访问算法的条件

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

二级索引不必等值查询，联合索引也不必匹配所有的索引项

参考文章

8.2.1.3 Index Merge Optimization

MySQL：索引合并

MySQL索引合并优化

一、问题背景

正确答案

二、索引合并优化

索引合并的查询案例

Note

Index Merge Intersection Access Algorithm（索引合并交集访问算法）

Examples

Index Merge Union Access Algorithm(索引合并并集访问算法）

Examples

Index Merge Sort-Union Access Algorithm(索引合并排序并集访问算法)

Examples

三、总结

索引合并交集访问算法

索引合并并集访问算法

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

参考文章