理解mysql - 索引合并 - 《mysql》

索引合并
不适用场景
显示
参考资料

索引合并

先看一段官方说明：

The Index Merge access method retrieves rows with multiple range scans and merges their results into one. This access method merges index scans from a single table only, not scans across multiple tables. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans.

根据官方文档中的说明，我们可以了解到：

索引合并是把几个索引的范围扫描合并成一个索引。
索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。
这些需要合并的索引只能是一个表的。不能对多表进行索引合并。

简单的说，索引合并，让一条 sql 可以使用多个索引。对这些索引取交集，并集，或者先取交集再取并集。从而减少从数据表中取数据的次数，提高查询效率。
索引合并可能适用于下列Sql语句：

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 语句嵌套过深，导致不能优化 SQL 语句。
显示
在 EXPLAIN 输出中，索引合并方法在 type 列中显示 index_merge。
在这种情况下，该 key 列包含使用的索引列表。
Index Merge 访问方法有几种算法，它们显示在 Extra 字段中：

Using intersect(…)
Using union(…)
Using sort_union(…)
intersect合并
Intersection 翻译过来的意思是交集。这里是说某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集，比方说下边这个查询：
```
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
```
假设这个查询使用 Intersection 合并的方式执行的话，那这个过程就是这样的：
从 idx_key1 二级索引对应的 B+ 树中取出 key1 = ‘a’ 的相关记录。
从 idx_key3 二级索引对应的 B+ 树中取出 key3 = ‘b’ 的相关记录。
二级索引的记录都是由 索引列 + 主键 构成的，所以我们可以计算出这两个结果集中 id 值的交集。
按照上一步生成的 id 值列表进行回表操作，也就是从聚簇索引中把指定 id 值的完整用户记录取出来，返回给用户。
联合索引适用，但所有的列都要在 where 从句中：
```
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;
```
Union合并
我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来，我们说这些不同的搜索条件之间是 OR 关系。有时候 OR 关系的不同搜索条件会使用到不同的索引，比方说这样：
```
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 从句中：
```
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
```
InnoDB 表主键上可以使用范围查询，参考 intersect 。
Sort-Union合并
Union 索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，比方说下边这个查询就无法使用到 Union 索引合并：
```
SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'
```
这是因为根据 key1 < 'a' 从 idx_key1 索引中获取的二级索引记录的主键值不是排好序的，根据 key3 > 'z' 从 idx_key3 索引中获取的二级索引记录的主键值也不是排好序的，但是 key1 < 'a' 和 key3 > 'z' 这两个条件又特别让我们动心，所以我们可以这样：

先根据 key1 < 'a' 条件从 idx_key1 二级索引总获取记录，并按照记录的主键值进行排序；
再根据 key3 > 'z' 条件从 idx_key3 二级索引总获取记录，并按照记录的主键值进行排序。

因为上述的两个二级索引主键值都是排好序的，剩下的操作和 Union 索引合并方式就一样了。
我们把上述这种先按照二级索引记录的主键值进行排序，之后按照 Union 索引合并方式执行的方式称之为 Sort-Union 索引合并，很显然，这种 Sort-Union 索引合并比单纯的 Union 索引合并多了一步对二级索引记录的主键值排序的过程。
Sort-Union 算法和 Union 算法之间的区别在于，Sort-Union 算法必须首先获取所有行的 row id，并在返回任何行之前对它们进行排序。

参考资料

Index Merge Optimization
mysql索引合并:一条sql可以使用多个索引
从根儿上理解 MySQL

索引合并

索引合并

不适用场景

显示

intersect合并

Union合并

Sort-Union合并

参考资料