索引合并优化

当你兴高采烈地在各个字段建立索引，并在这些索引上使用WHERE+OR的SELECT操作，以为MySQL不会聪明到使用各个列的索引时，神奇的事情发生了：你看到了从没见过的type和Extra：

这是因为MySQL使用了名为index merge的优化操作。

这种优化会在你用多种不同的type为range的select检索行，并且最后将它们合并成一个时出现。这个优化仅仅能够合并单个表里的索引扫描，并不能跨表。这些索引合并可以产生底层扫描出的结果后的并集、交集、交集的并集。

以下是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);

注意：索引合并算法拥有以下已知的限制：

• 如果你的查询拥有一个复杂的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)

• 全文索引无法使用索引合并

使用EXPLAIN之后，若发生了索引合并，则type中会显示index_merge，key会包含使用到的所有索引，key_len为使用到的所有索引的最长key长度的列表，而EXPLAIN中会有三种不同的选择，代表着三种不同的索引合并的算法，MySQL优化器会在预估查询成本之后做出它自己的选择，下面我们依次来讲一下：

索引交集算法

显示：Using intersect(...)

这个算法用于where子句（或转化后）是AND连接的几个针对不同属性的范围查询，每个属性符合以下至少一个条件：

• 符合以下表达式，且每个key_part都对应一个索引

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;

 

索引并集算法

显示：Using union(...)

这个算法用于where子句（或转化后）是OR连接的几个针对不同属性的范围查询，每个属性符合以下至少一个条件：

• 符合以下表达式，且每个key_part都对应一个索引

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN

• 若为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;

 

 索引并集排序算法

显示：Using sort_union(...)

这个算法用于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;