8.2.1.3 索引合并优化
索引合并访问方法检索具有多个range次扫描的行,并将其结果合并为一个。此访问方法仅合并来自单个 table 的索引扫描,而不合并多个 table 的扫描。合并可以为其基础扫描产生并集,相交或相交。
可能使用索引合并的示例查询:
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
在EXPLAIN输出中,索引合并方法在type
列中显示为index_merge。在这种情况下,key
列包含使用的索引列 table,而key_len
列包含这些索引的最长键部分的列 table。
索引合并访问方法具有几种算法,这些算法显示在EXPLAIN输出的Extra
字段中:
-
Using intersect(...)
-
Using union(...)
-
Using sort_union(...)
以下各节将更详细地描述这些算法。优化程序基于各种可用选项的成本估算,在可能的索引合并算法和其他访问方法之间进行选择。
索引合并的使用取决于optimizer_switch系统变量的index_merge
,index_merge_intersection
,index_merge_union
和index_merge_sort_union
标志的值。参见第 8.9.2 节“可切换的优化”。默认情况下,所有这些标志均为on
。要仅启用某些算法,请将index_merge
设置为off
,并仅启用应允许的其他算法。
索引合并路口访问算法
当将WHERE
子句转换为与AND组合在一起的不同键上的多个范围条件,并且每个条件是以下之一时,此访问算法适用:
- 此形式的*
N
* -parttable 达式,其中索引正好具有*N
*部分(即,所有索引部分都包括在内):
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
InnoDB
table 的主键上的任何范围条件。
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;
索引合并交集算法对所有使用的索引执行同时扫描,并生成从合并索引扫描接收到的行序列的交集。
如果查询中使用的所有列都被使用的索引覆盖,则不会检索完整的 table 行(在这种情况下,EXPLAIN输出在Extra
字段中包含Using index
)。这是此类查询的示例:
SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;
如果使用的索引未覆盖查询中使用的所有列,则仅在满足所有使用的键的范围条件时才检索完整行。
如果合并的条件之一是InnoDB
table 的主键上的条件,则该条件不用于行检索,而是用于过滤出使用其他条件检索的行。
索引合并联合访问算法
此算法的标准类似于索引合并交集算法的标准。当 table 的WHERE
子句在与OR组合在一起的不同键上转换为多个范围条件,并且每个条件是以下条件之一时,该算法适用:
- 此形式的*
N
* -parttable 达式,其中索引正好具有*N
*部分(即,所有索引部分都包括在内):
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
-
InnoDB
table 的主键上的任何范围条件。 -
索引合并交集算法适用的条件。
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;
索引合并排序联合访问算法
当WHERE
子句转换为由OR组合的多个范围条件时,此访问算法适用,但索引合并并集算法不适用。
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,然后对它们进行排序,然后再返回任何行。