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;

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;

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; -- 一个范围，一个等值

二、索引合并的优点

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

通常不如复合索引高效：

不是所有条件组合都适用：

-- 查看当前设置
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）。过时的统计信息可能导致优化器做出错误的选择。

替代方案 – 优先考虑复合索引：

四、如何识别索引合并

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

五、总结

MySQL 的索引合并（Index Merge）是一种在特定查询条件下（涉及多个索引列且条件由 AND 或 OR 连接），优化器利用多个独立索引分别扫描数据，然后对结果集进行交集、并集或排序后并集操作，最终定位目标行的优化策略。

虽然索引合并提供了一种避免全表扫描的途径，但它通常伴随着额外的扫描、合并和回表开销。创建合适的复合索引（Composite Index）通常是解决这类查询性能问题的首选和更优方案，因为它能更直接、高效地定位数据。

到此这篇关于Mysql索引合并的实现示例的文章就介绍到这了,