MySQL优化(6)：SELECT 之 索引合并优化

索引合并访问方法检索具有多个范围扫描的行并将它们的结果合并为一个。

此访问方法仅合并来自单个表的索引扫描，而不是跨多个表的扫描。 合并可以产生其底层扫描的联合、交集或交集。

索引合并

索引合并访问方法检索具有多个范围扫描的行并将它们的结果合并为一个。

此访问方法仅合并来自单个表的索引扫描，而不是跨多个表的扫描。 合并可以产生其底层扫描的联合、交集或交集。

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

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 方法在 type 列中显示为 index_merge。 在这种情况下，key 列包含使用的索引列表，key_len 包含这些索引的最长 key parts 的列表。

索引合并访问方法有几个算法，这些算法显示在 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：影响索引合并优化

索引合并交集访问算法

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

• 这种形式的 N-part 表达式，其中索引正好有 N parts（即，所有索引部分都包含在内）：

  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;

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

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

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

如果使用的索引没有覆盖查询中使用的所有列，则只有在满足所有使用 Key 的范围条件时才会检索完整行。

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

索引合并联合访问算法

此算法的标准类似于索引合并交集算法的标准。当表的 WHERE 子句转换为不同 Key 上的多个范围条件并结合OR时，该算法适用，并且每个条件是以下条件之一：

• 这种形式的 N-part 表达式，其中索引正好有 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;

索引合并排序联合访问算法

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

sort-union 算法和 union 算法之间的区别在于，sort-union 算法必须首先获取所有行的行 ID 并在返回任何行之前对其进行排序。

影响索引合并优化

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

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