索引合并访问方法检索具有多个范围扫描的行,并将其结果合并为一个。此访问方法仅合并来自单个表的索引扫描,而不是跨多个表的扫描。合并可以生成其基础扫描的合并、交叉或交叉的合并。
可以使用索引合并的查询示例:
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
嵌套,并且 MySQL 没有选择最佳方案,请尝试使用以下标识转换来分散项目:(x AND y) OR z => (x OR z) AND (y OR z) (x OR y) AND z => (x AND z) OR (y AND z)
- 索引合并不适用于全文索引。
在 EXPLAIN
输出中,索引合并方法在 type
列中显示为 index_merge
。 在本例中,key
列包含使用的索引列表,key_len
包含这些索引的最长键部分列表。
索引合并访问方法有几种算法,显示在 EXPLAIN
输出的 Extra
字段中:
- 使用
intersect(...)
- 使用
union(...)
- 使用
sort_union(...)
以下各节将更详细地描述这些算法。优化器根据各种可用选项的成本估计,在不同的索引合并算法和其他访问方法之间进行选择。
索引合并交叉访问算法
当 WHERE
子句被转换为与 AND
组合的不同键上的多个范围条件时,该访问算法适用,并且每个条件都是以下条件之一:
- 此形式的 N 部分(一般为列或包含列的表达式)表达式,其中索引正好有 N 个部分(即覆盖所有索引部分):
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
字段中使用索引)。下面是这样一个查询的示例:
SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;
如果使用的索引未覆盖查询中使用的所有列,则仅当满足所有使用的键的范围条件时才检索完整的行。
如果合并条件之一是 InnoDB 表主键上的条件,则它不用于行检索,而是用于过滤使用其他条件检索的行。
索引合并合并访问算法
该算法的标准与索引合并交叉算法的标准相似。当将表的 WHERE
子句转换为不同键上的多个范围条件并结合 OR
时,该算法适用,并且每个条件都是以下条件之一:
- 此形式的 N 部分表达式,其中索引正好有 N 个部分(即覆盖所有索引部分):
- InnoDB 表主键上的任何范围条件。
- 索引合并交叉算法适用的条件。
示例:
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
组合的多个范围条件而索引合并合并算法不适用时,此访问算法适用。
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 ,并在返回任何行之前对它们进行排序。
影响索引合并优化
索引合并的使用取决于 optimizer_switch
系统变量的 index_merge
、index_merge_intersection
、index_merge_union
、和 index_merge_sort_union
标志的值。参见 第 8.9.2 节 “可切换优化” 。默认情况下,所有这些标志都处于启用状态。若要仅启用某些算法,请将 index_merge
设置为禁用,并仅启用应允许的其他算法。
除了使用 optimizer_switch
系统变量控制优化器在会话范围内使用索引合并算法之外,MySQL 还支持优化器提示,以按语句影响优化器。参见 第 8.9.3 节“优化器提示” 。