mysql+index组合索引_MySQL 优化之 index merge(索引合并)

标签：

MySQL5.0之前，一条语句中一个表只能使用一个索引，无法同时使用多个索引。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引。理解了 index merge 技术，我们才知道应该如何在表上建立索引。

相关文档：http://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html (注意该文档中说的有几处错误)

The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. This access method merges index scans from a single table; it does not merge scans across multiple tables.

In EXPLAIN output, the Index Merge method appears as index_merge in the type column. In this case, the key column contains a list of indexes used, and key_len contains a list of the longest key parts for those indexes.

index merge: 同一个表的多个索引的范围扫描可以对结果进行合并，合并方式分为三种：union, intersection, 以及它们的各种组合。

Examples:

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);

文档这里是有错误的：最后一个select语句， t2.key1=t1.some_col OR t2.key2=t1.some_col2，因为这里使用的是 OR，所以这里是无法使用组合索引的。

The Index Merge method has several access algorithms (seen in the Extra field of EXPLAIN output):

Using intersect(...)

Using union(...)

Using sort_union(...)

根据索引合并的方式不同，会在explain结果中显示使用了那种合并方法。

一般而且出现了 Index merge 并不一定是什么好事。比如一般出现了 Using intersect 的执行计划，预示着我们索引的建立不是最佳的，一般可以通过建立符合索引来进一步进行优化，可以参考文章：https://www.percona.com/blog/2009/09/19/multi-column-indexes-vs-index-merge/

标签：