数据库的查询执行计划中的索引合并优化技术（Index Merge Optimization）

一、知识点描述
索引合并优化是数据库查询优化器处理对同一表的多列条件查询时的一种重要技术。当WHERE子句包含多个条件，且每个条件都可以利用不同索引时，优化器会考虑将多个索引的扫描结果进行合并（交集、并集或组合操作），而不是选择单个索引或全表扫描。这种技术特别适用于无法通过单列索引有效覆盖所有查询条件的情况。

二、索引合并的工作原理

1. 适用场景分析

   • 查询包含多个范围条件或等值条件，例如：WHERE key1 = 10 AND key2 = 20
   • 每个条件都有可用的独立单列索引（如index_key1和index_key2）
   • 没有合适的多列复合索引覆盖所有条件

2. 合并算法类型

   • 交集合并（Index Merge Intersection）：对多个索引扫描的结果取交集

     -- 示例：两个索引扫描结果的交集
     WHERE key1 = 10 AND key2 = 20
     

     执行过程：
     a. 通过index_key1找到所有key1=10的记录位置（rowid集合A）
     b. 通过index_key2找到所有key2=20的记录位置（rowid集合B）
     c. 计算A ∩ B（取两个集合的交集）
     d. 根据交集中的rowid回表读取完整数据行

   • 并集合并（Index Merge Union）：对多个索引扫描的结果取并集

     -- 示例：两个索引扫描结果的并集
     WHERE key1 = 10 OR key2 = 20
     

     执行过程：
     a. 分别通过两个索引获取满足各自条件的rowid集合
     b. 合并两个集合并去除重复rowid
     c. 根据合并后的rowid回表读取数据

   • 排序并集合并（Index Merge Sort-Union）：当OR条件无法直接使用并集合并时的优化
     适用场景：WHERE key1 < 10 OR key2 < 20（范围查询）
     执行过程：
     a. 从每个索引中获取rowid并单独排序
     b. 对排序后的rowid列表执行归并操作，生成有序无重复的最终结果

三、具体执行步骤分解

1. 条件分析与索引选择

   • 优化器解析WHERE子句，识别可索引的条件
   • 评估每个可用索引的选择性（过滤效果）
   • 计算不同索引组合的代价估计

2. 索引扫描执行

   • 同时或顺序执行多个索引的扫描操作
   • 每个索引扫描返回有序的rowid列表
   • 记录扫描过程中的统计信息（命中行数等）

3. rowid合并操作

   • 交集合并：采用双指针法在有序rowid列表上求交集
   • 并集合并：采用归并排序的思想合并多个有序列表
   • 去除重复rowid，确保同一行不会被多次处理

4. 回表数据读取

   • 根据合并后的rowid列表访问主表数据
   • 可能涉及随机I/O，需要优化读取顺序
   • 应用剩余无法下推的过滤条件

5. 结果返回

   • 对最终结果进行必要的排序或分组操作
   • 将符合要求的数据返回给客户端

四、优化器决策考量因素

1. 代价模型分析

   • 索引扫描代价：取决于索引深度和数据分布
   • rowid合并代价：与结果集大小和索引数量相关
   • 回表代价：受合并后rowid数量和物理分布影响

2. 选择性评估

   • 当多个条件的选择性都很高时，索引合并效果最好
   • 如果某个条件过滤性很差，可能全表扫描更优
   • 复合索引与索引合并的代价比较

五、实际应用示例

-- 创建测试环境
CREATE TABLE users (
    id INT PRIMARY KEY,
    age INT,
    dept_id INT,
    INDEX idx_age(age),
    INDEX idx_dept(dept_id)
);

-- 触发索引合并的查询
EXPLAIN SELECT * FROM users 
WHERE age BETWEEN 20 AND 30 AND dept_id = 5;

-- 执行计划可能显示：
-- -> Index merge intersection on idx_age,idx_dept
--    -> Index range scan on idx_age
--    -> Index scan on idx_dept

六、性能优化建议

1. 索引设计策略

   • 优先考虑创建合适的复合索引
   • 单列索引应覆盖高选择性的查询条件
   • 避免创建过多冗余索引增加维护代价

2. 查询编写技巧

   • 避免在WHERE子句中使用过多的OR条件
   • 合理使用括号明确条件优先级
   • 考虑使用UNION替代复杂的OR条件

3. 监控与调优

   • 通过EXPLAIN分析执行计划选择
   • 监控索引合并操作的实际性能
   • 在特定场景下使用优化器提示引导索引选择

索引合并优化技术是数据库在复杂查询条件下的重要优化手段，通过智能组合多个索引的扫描结果，在避免全表扫描的同时弥补单列索引的局限性，显著提升查询性能。