Mysql索引合并的实现示例_Mysql

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(...) (排序并集)