Index Merge
访问方法
通过多次扫描检索行
range
并将它们的结果合并为一个。此访问方法仅合并来自单个表的索引扫描，而不是跨多个表的扫描。合并可以产生其底层扫描的并集、交集或交集并集。
可以使用索引合并的示例查询：
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)
Index Merge 不适用于全文索引。
index_merge_intersection
、
index_merge_union
和
index_merge_sort_union
flags 的值。
optimizer_switch
请参阅
第 8.9.2 节，“可切换优化”
。默认情况下，所有这些标志都是
on
. 要仅启用某些算法，请设置
index_merge
为
off
，并仅启用应允许的其他算法。
This access algorithm is applicable when a
WHERE
clause is converted to several
range conditions on different keys combined with
AND
, and each condition is one
of the following:
An
N
-part expression of this
form, where the index has exactly
N
parts (that is, all index
parts are covered):
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
Any range condition over the primary key of an
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;
The Index Merge intersection algorithm performs simultaneous
scans on all used indexes and produces the intersection of
row sequences that it receives from the merged index scans.
If all columns used in the query are covered by the used
indexes, full table rows are not retrieved
(
EXPLAIN
output contains
Using index
in
Extra
field in this case). Here is an example of such a query:
SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;
If the used indexes do not cover all columns used in the
query, full rows are retrieved only when the range
conditions for all used keys are satisfied.
If one of the merged conditions is a condition over the
primary key of an
InnoDB
table, it is not
used for row retrieval, but is used to filter out rows
retrieved using other conditions.
The criteria for this algorithm are similar to those for the
Index Merge intersection algorithm. The algorithm is
applicable when the table's
WHERE
clause is converted to several range conditions on different
keys combined with
OR
, and each
condition is one of the following:
An
N
-part expression of this
form, where the index has exactly
N
parts (that is, all index
parts are covered):
key_part1 = const1 OR key_part2 = const2 ... OR key_partN = constN
Any range condition over a primary key of an
InnoDB
table.
A condition for which the Index Merge intersection
algorithm is applicable.
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;
