mysql 组合索引 排序 mysql索引合并

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