当前位置：首页 > news >正文

详解Mysql索引合并

news 2025/7/21 6:47:43

MySQL 中的 索引合并 是一种查询优化技术，当单个表查询的 WHERE 子句中包含多个条件，并且这些条件分别可以用到不同的索引时，MySQL 优化器可能会尝试将这些索引扫描的结果合并起来，以更高效地获取最终满足所有条件的行。它本质上是优化器在无法找到最优的单个复合索引时的一种“折衷”策略。

核心思想： 利用多个索引分别筛选数据，然后将结果集合并（交集、并集或排序后并集）以得到最终结果，避免全表扫描。

一、索引合并的类型

MySQL 主要支持三种索引合并算法：

1.1 Index Merge Intersection Access (`Using intersect(...)`):

适用场景： WHERE 子句中的多个条件通过 AND 连接，并且每个条件都可以有效地使用一个单独的索引（这些索引通常是单列索引）。
工作原理：优化器对每个可用的索引执行范围扫描或等值查询扫描。
- 获取每个索引扫描得到的主键值（或行指针）集合。
- 计算这些主键值集合的交集（即同时出现在所有集合中的主键值）。
- 根据交集得到的主键值，回表（如果需要）读取完整的行数据。
示例：
```
CREATE TABLE `t` (`id` INT PRIMARY KEY,`a` INT,`b` INT,`c` VARCHAR(100),INDEX `idx_a` (`a`),INDEX `idx_b` (`b`)
);
-- 假设 idx_a 和 idx_b 都是 B-Tree 索引
SELECT * FROM t WHERE a = 10 AND b = 20;
```
- 优化器可能分别使用 idx_a 查找 a=10 的行（得到主键集合 S1）。
- 使用 idx_b 查找 b=20 的行（得到主键集合 S2）。
- 计算 S1 和 S2 的交集。
- 根据交集结果回表取数据。
EXPLAIN 输出： type 列显示 index_merge，Extra 列显示 Using intersect(idx_a, idx_b); Using where。

1.2 Index Merge Union Access (`Using union(...)`):

适用场景： WHERE 子句中的多个条件通过 OR 连接，并且每个条件都可以有效地使用一个单独的索引（这些索引通常是单列索引），并且查询是 SELECT（非 UPDATE/DELETE），并且没有使用 FOR UPDATE 或 LOCK IN SHARE MODE。
工作原理：
- 优化器对每个可用的索引执行范围扫描或等值查询扫描。
- 获取每个索引扫描得到的主键值（或行指针）集合。
- 计算这些主键值集合的并集（即出现在任意一个集合中的主键值）。
- 对并集结果进行去重。
- 根据去重后的主键值，回表（如果需要）读取完整的行数据。
示例：
```
SELECT * FROM t WHERE a = 10 OR b = 20;
```
- 优化器可能分别使用 idx_a 查找 a=10 的行（得到主键集合 S1）。
- 使用 idx_b 查找 b=20 的行（得到主键集合 S2）。
- 计算 S1 和 S2 的并集，并去重。
- 根据去重后的结果回表取数据。
EXPLAIN 输出： type 列显示 index_merge，Extra 列显示 Using union(idx_a, idx_b); Using where。

1.3 Index Merge Sort-Union Access (`Using sort_union(...)`):

适用场景： WHERE 子句中的多个条件通过 OR 连接，但是这些条件无法直接使用 Index Merge Union（通常是因为索引扫描返回的是范围结果，而不仅仅是点查询的等值结果）。它是 Union 的一种变体，用于处理范围扫描。
工作原理：
- 优化器对每个可用的索引执行范围扫描。
- 获取每个索引扫描得到的主键值（或行指针）集合。
- 对每个集合中的主键值分别排序。
- 将排序后的多个主键值列表进行归并排序，并在归并过程中进行去重。
- 根据归并去重后的主键值，回表（如果需要）读取完整的行数据。
示例：
```
SELECT * FROM t WHERE a < 10 OR b < 20;
-- 或者
SELECT * FROM t WHERE a < 10 OR b = 20; -- 一个范围，一个等值
```
- 优化器使用 idx_a 扫描 a < 10（得到主键集合 S1）。
- 使用 idx_b 扫描 b < 20（或 b = 20）（得到主键集合 S2）。
- 分别对 S1 和 S2 中的主键排序。
- 对两个有序列表进行归并排序并去重。
- 根据结果回表取数据。
EXPLAIN 输出： type 列显示 index_merge，Extra 列显示 Using sort_union(idx_a, idx_b); Using where。

二、索引合并的优点

避免全表扫描： 当没有单个复合索引可以覆盖所有查询条件时，索引合并提供了利用现有多个单列索引的可能性，避免代价高昂的全表扫描。
利用现有索引： 如果表上已经存在多个单列索引，优化器可以尝试利用它们，而不一定需要为特定查询创建新的复合索引（尽管复合索引通常更好）。
处理复杂 OR 条件： 对于 OR 连接的复杂条件，索引合并（特别是 sort_union）提供了一种优化的执行路径。

三、索引合并的缺点与注意事项

通常不如复合索引高效：
- 额外开销： 索引合并需要进行多个独立的索引扫描、结果集的合并操作（交集、并集、排序归并去重），这些操作本身就有开销。
- 多次回表： 合并操作是基于主键值进行的，最终得到主键集后，还需要根据这些主键值回表读取完整的行数据（如果查询需要的数据不在索引中）。而一个设计良好的复合索引可能直接覆盖查询（避免回表）或者按最有效的顺序定位数据。
- 优化器成本估算可能不准： 合并多个索引的成本估算比使用单个复合索引更复杂，优化器可能错误地选择了索引合并，而实际上全表扫描或强制使用某个单索引可能更快（反之亦然）。
不是所有条件组合都适用：
- 只有特定的 AND/OR 结构且每个条件都能独立使用索引时才可能触发。
- 索引列类型、查询条件的具体形式（等值、范围、函数、隐式转换）都会影响优化器是否选择索引合并。

配置影响： 索引合并是否启用受系统变量 optimizer_switch 控制。例如：

-- 查看当前设置
SELECT @@optimizer_switch;
-- 关闭所有索引合并优化
SET optimizer_switch = 'index_merge=off';
-- 关闭特定类型的索引合并 (e.g., intersection)
SET optimizer_switch = 'index_merge_intersection=off';

需要确认相关标志（index_merge, index_merge_intersection, index_merge_union, index_merge_sort_union）是开启的 (on)。

统计信息准确性： 优化器是否选择索引合并以及选择哪种合并算法，高度依赖于表的统计信息（如索引的基数 cardinality）。过时的统计信息可能导致优化器做出错误的选择。
替代方案 - 优先考虑复合索引：
- 最佳实践： 对于经常一起出现在 WHERE 子句中的列，尤其是通过 AND 连接的列，创建合适的复合索引通常是性能最优的选择。复合索引直接按索引顺序定位满足所有条件的行，避免了多索引扫描和合并的开销，也更容易避免回表（如果索引覆盖查询）。
- 示例： 对于 SELECT * FROM t WHERE a = 10 AND b = 20;，创建 INDEX idx_a_b (a, b) 或 INDEX idx_b_a (b, a) 通常会比依赖 idx_a 和 idx_b 的索引合并快得多。

四、如何识别索引合并

使用 EXPLAIN 或 EXPLAIN FORMAT=JSON 查看查询的执行计划：

type 列： 显示为 index_merge。
key 列： 列出实际使用的索引，多个索引用逗号分隔（如 idx_a, idx_b）。
Extra 列： 明确指出使用的合并算法：
- Using intersect(...) (交集)
- Using union(...) (并集)
- Using sort_union(...) (排序并集)