6.MySQL优化索引合并优化

介绍

索引合并访问方法检索具有多个范围扫描的行并合并其结果合而为一。 此访问方法仅合并来自单个表的索引扫描，而不是跨多个扫描表。 合并可以生成其基础扫描的联合，交叉或交叉联合。

下面举个例子介绍一下如何使用:

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

索引合并优化算法具有以下已知限制：

• 如果您的查询具有带有深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)
  

• 索引合并不适用于全文索引。

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

Index Merge访问方法有几种算法，它们显示在EXPLAIN输出的Extra字段中：

• Using intersect(…)
• Using union(…)
• Using sort_union(…)

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

• Index Merge Intersection Access Algorithm
• Index Merge Union Access Algorithm
• Index Merge Sort-Union Access Algorithm
• Influencing Index Merge Optimization

1 Index Merge Intersection Access Algorithm

• 当WHERE子句在与AND组合的不同键上转换为多个范围条件时，此访问算法适用，并且每个条件都是以下之一：

    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;

Index Merge Intersection Access Algorithm的意思就是对所有使用的索引进行同时扫描(simultaneous scans)，并生成从合并索引扫描接收的行序列的交集。

其实就是在扫描索引的时候就对索引集合进行了合并减少了取数据的数量。

2 Index Merge Union Access Algorithm

该算法的标准与Index Merge Intersection Access Algorithm的标准类似。当表的WHERE子句在与OR结合的不同键上转换为多个范围条件时，该算法适用。

• 这种形式的N部分表达式，其中索引具有正好N个部分（即，所有索引部分都被覆盖），并且每个条件都是以下之一：

    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;

3 Index Merge Sort-Union Access Algorithm

当WHERE子句转换为OR组合的多个范围条件时，此访问算法适用，但是Index Merge union algorithm方法不适用

例如：

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，然后在返回任何行之前对它们进行排序。

4 Influencing Index Merge Optimization

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

除了使用optimizer_switch系统变量来控制会话范围内的索引合并算法的优化器使用之外，MySQL还支持优化器提示以在持久化基础上影响优化器。