标签:
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/
标签: