MySQL查询优化之三-索引合并优化(Index Merge Optimization)

#MySQL查询优化之三-索引合并优化(Index Merge Optimization)

如需转载请标明出处：http://blog.csdn.net/itas109
QQ技术交流群：129518033

环境：
MySQL版本：5.5.15
操作系统：windows

本文讨论索引合并优化(Index Merge Optimization) 。

索引合并访问方法可以在查询中对一个表使用多个索引,对它们同时扫描，并且合并结果(intersect/union)。 此访问方法合并来自单个表的索引扫描; 它不会将扫描合并到多个表中。

使用索引合并的示例查询：、

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

在EXPLAIN输出中，索引合并方法在类型列中显示为index_merge。 在这种情况下，键列包含使用的索引列表，而key_len包含这些索引的最长键部分列表。

---

注意事项：
索引合并优化算法有以下已知的缺陷：

• 如果您的查询具有深度AND或OR嵌套的复杂WHERE子句，并且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)不适应与全文索引(full-text indexes)
• 如果某个键上的范围扫描是可能的，则优化器将不考虑使用Index Merge Union或Index Merge Sort-Union算法。 例如，考虑这个查询：

SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

对于这个查询，有两种处理方案：

• 索引合并(Index Merge)使用条件(goodkey1 < 10 OR goodkey2 < 20)
• 范围扫描(range scan)使用条件 badkey < 30

但是，优化器只考虑第二种方案。

---

索引合并(Index Merge)访问方法有几种算法，这些算法显示在EXPLAIN输出的Extra 字段中：

• Using intersect(…)

• Using union(…)

• Using sort_union(…)

以下部分将更详细地介绍这些算法。 优化器根据各种可用选项的成本估计，在不同的可能的索引合并算法和其他访问方法之间进行选择。

索引合并(Index Merge)的使用取决于optimizer_switch系统变量的index_merge，index_merge_intersection，index_merge_union和index_merge_sort_union标志的值。默认情况下，所有这些标志都打开。 要仅启用特定算法，请将index_merge设置为关闭，并仅启用其他应允许的其他算法。

##1.索引合并Intersection访问算法(The Index Merge Intersection Access Algorithm)

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

索引合并Intersection访问算法对所有使用的索引执行同时扫描，并产生从合并索引扫描中接收到的行序列的交集。

如果查询中使用的所有列都被使用的索引覆盖，则不会检索完整的表行（EXPLAIN输出包含在这种情况下在Extra字段中 Using index）。 这是一个这样的查询的例子：

SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;

如果使用的索引不包括查询中使用的所有列，则只有满足所有使用的键的范围条件时才检索完整的行。

如果其中一个合并条件是InnoDB表的主键条件，则不用于行检索，而是用于过滤使用其他条件检索的行。

##2.索引合并Union访问算法(The Index Merge Union Access Algorithm)

该算法的标准与索引合并Intersection访问算法相似。 当将表的WHERE子句转换由OR组合的不同键上的几个范围条件时，该算法是适用的，并且每个条件是以下之一：

• 这种形式的N部分表达式，其索引正好具有N个部分（即所有索引部分都被覆盖）：

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

• InnoDB表通过主键的任何范围条件。
• 索引合并Intersection访问算法适用的条件。

例如：

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;

##3.索引合并Sort-Union访问算法(The Index Merge Sort-Union Access Algorithm)

当WHERE子句被转换为由OR组合的几个范围条件时，该访问算法是适用的，但索引合并Union访问算法不适用。

例如：

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;

索引合并Sort-Union访问算法和索引合并Union访问算法的区别在于，索引合并Sort-Union访问算法必须首先为所有行提取行ID，然后在返回任何行之前对其进行排序。

---

Reference：
https://dev.mysql.com/doc/refman/5.5/en/range-optimization.html
https://dev.mysql.com/doc/refman/5.5/en/explain-output.html

---

觉得文章对你有帮助，可以扫描二维码捐赠给博主，谢谢！

如需转载请标明出处：http://blog.csdn.net/itas109
QQ技术交流群：129518033