Mysql索引合并的实现示例

目录

• 一、索引合并的类型
• 1.1 Index Merge Intersection Access (Using intersect(...)):
• 1.2 Index Merge Union Access (Using union(...)):
• 1.3 Index Merge Sort-Union Access (Using sort_union(...)):
• 二、索引合并的优点
• 三、索引合并的缺点与注意事项
• 四、如何识别索引合并
• 五、总结

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&nb编程客栈sp;的一种变体，用于处理范围扫描。

工作原理：

• 优化器对每个可用的索引执行范围扫描。
• 获取每个索引扫描得到的主键值（或行指针）集合。
• 对每个集合中的主键值分别排序。
• 将排序后的多个主键值列表进行归并排序，并在归并过程中进行去重。
• 根据归并去重后的主键值，回表（如果需要）读取完整的行数据。

示例：

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。

二、索引合并的优点

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

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

通常不如复合索引高效：

• 额外开销： 索引合并需要进行多个独立的索引扫描、结果集的合并操作编程客栈（交集、并集、排序归并去重），这些操作本身就有开销。
• 多次回表： 合并操作是基于主键值进行的，最终得到主键集后，还需要根据这些主键值回表读取完整的行数据（如果查询需要的数据不在索引中）。而一个设计良好的复合索引可能直接覆盖查询（避免回表）或者按最有效的顺序定位数据。
• 优化器成本估算可能不准： 合并多个索引的成本估算比使用单个复合索引更复杂，优化器可能错误地选择了索引合并，而实际上全表扫描或强制使用某个单索引可能更快（反之亦然）。

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

• 只有特定的 AND/OR 结构且每个条件都能独立使用索引时才可能触发。
• 索引列类型、查询条件的具体形式（等值、范围、函数、隐式转换）都会影响优化器是否选择索引合并。
• 配置影响： 索引合并是否启用受系统变量 optimizwww.devze.comer_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(...) (排序并集)

五、总结

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

• intersect 处理 AND 条件。
• union / sort_union 处理 OR 条件（sort_union 处理范围扫描）。

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

到此这篇关于Mysql索引合并的实现示例的文章就介绍到这了,更多相关Mysql索引合并内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以编程后多多支持编程客栈(www.devze.com)！