索引合并-Index Merge 算法

本文问题

1. 什么是Index Merge？Index Merge的限制有哪些？
2. 如何查看语句是否使用了Index Merger？
3. Index Merge有哪几种？分别适用于那些情景？
4. 如何控制优化器是否使用Index Merge

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

Index Merge的已知缺陷

• 如果在WHERE语句中，存在多层嵌套的AND/OR，MySQL可能不会选择最优的方案，可以尝试通过拆分WHERE子句的条件来进行转换：

(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 Merger不能应用于全文索引（fulltext index）

Index Merge的EXPLAIN输出

• type列的值显示为index_merge
• key列显示使用的索引列表
• key_len列显示这些索引的最大长度（列表）。
• Extra列显示Index Merge的算法：
  • Using intersect(...)
  • Using union(...)
  • Using sort_union(...)

mysql> explain select * from test_merge where (col1<10 and col2>50) or col3=50;
+----+-------------+------------+------------+-------------+---------------+---------------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                        |
+----+-------------+------------+------------+-------------+---------------+---------------+---------+------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | test_merge | NULL       | index_merge | idx_1_2,idx_3 | idx_1_2,idx_3 | 5,5     | NULL |  214 |   100.00 | Using sort_union(idx_1_2,idx_3); Using where |
+----+-------------+------------+------------+-------------+---------------+---------------+---------+------+------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test_merge where (col1=10 and col2=50) or col3=50;
+----+-------------+------------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                   |
+----+-------------+------------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
|  1 | SIMPLE      | test_merge | NULL       | index_merge | idx_1_2,idx_3 | idx_1_2,idx_3 | 10,5    | NULL |   22 |   100.00 | Using union(idx_1_2,idx_3); Using where |
+----+-------------+------------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
1 row in set, 1 warning (0.00 sec)

 

Index Merge Intersection 索引合并交集

这种方法适用于WHERE子句中的条件是通过AND结合的不同索引的范围条件时，其中的每个条件都需要满足下列条件之一：

• 如果其中的索引是多列索引，条件中需要包括索引的所有列
  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表主键索引条件，主键并不用来检索数据，而是用来筛选使用其他条件检索出的行。 # 就是先通过其他的范围条件筛选出一部分数据，在从这部分数据中，通过主键来筛选出最终的结果

Index Merge Union 索引合并并集

这种方法适用于WHERE子句中的条件是通过OR结合的不同索引的范围条件时，其中的每个条件都需要满足下列条件之一：

• 如果其中的索引是多列索引，条件中需要包括索引的所有列
  key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
• 在Innodb表的主键上的范围条件
• 适用于Index Merger intersection算法的条件

示例：

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

这种方法适用于WHERE子句中的条件是通过OR结合的不同索引的范围条件，但是不能使用Index Merge Union算法的情景
示例：

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并对行ID进行排序。

禁用Index Merge

在optimizer_swith中有4个关于Index Merge的变量：
index_merge,index_merge_intersection,index_merge_union,index_merge_sort_union
默认情况下都是启用的。要单独启用某个算法，设置index_merge=off，并将相应的标志设置为on

问题答案

1. 什么是Index Merge？Index Merge的限制有哪些？
   如果查询中使用到了不同的索引，可以对不同索引的条件分别进行范围扫描，然后将扫描结果合并得到最终的结果，这就是Index Merge。
   限制：只能合并同一个表的索引扫描结果，不能跨表合并。此外，无法对fulltext索引进行合并
2. 如何查看语句是否使用了Index Merge？
   EXPLAIN中type列的值为index_merge表示使用了索引合并。根据索引合并算法的不同，会在Extra列中显示Using intersect/union/sort_union
3. Index Merge有哪几种？分别适用于那些情景？
   3种：Intersection,Union,Sort_union
Intersection：使用AND结合的关于不同索引的条件（普通索引的等值表达式或者主键索引的范围表达式）
   Union和Sort Union：使用OR结合的关于不同索引的范围条件
   区别：当条件为普通索引的等值表达式或者主键索引的范围表达式时，可以使用Union。其他不符合条件的只能使用Sort Union
   如果包括多列索引，在范围条件中需要包括索引中的所有列。
4. 如何控制优化器是否使用Index Merge
   在optimizer_swith中有4个关于Index Merge的变量：
   index_merge,index_merge_intersection,index_merge_union,index_merge_sort_union
   默认情况下都是启用的。要单独启用某个算法，设置index_merge=off，并将相应的标志设置为on

作者：月饮沙
链接：https://www.jianshu.com/p/34bd66629355
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。