本文问题
- 什么是
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
网友评论