数据库查询优化中的索引合并优化策略

题目描述
索引合并（Index Merge）是数据库查询优化中的一种技术，当查询条件包含多个不同列的过滤条件且这些列上存在独立索引时，优化器可能选择同时扫描多个索引，再对索引结果进行合并（如取交集、并集），最终回表获取数据。本题要求理解索引合并的触发条件、合并算法（Intersection、Union、Sort-Union）及其优缺点，并掌握如何通过索引设计避免低效的索引合并。

解题过程

1. 索引合并的触发场景

问题示例：

SELECT * FROM orders 
WHERE customer_id = 100 AND product_id = 200;

假设表中存在两个独立索引：

• idx_customer_id (customer_id)
• idx_product_id (product_id)

若优化器预测单独使用某个索引的过滤效果较差（例如customer_id=100有10万条记录，product_id=200有5万条记录），而同时满足两个条件的记录很少，则可能触发索引合并。

2. 索引合并的三种算法

2.1 Intersection Merge（交集合并）

• 步骤：
  1. 分别扫描idx_customer_id和idx_product_id，获取满足条件的主键集合。
  2. 对两个主键集合取交集（例如使用哈希或排序合并算法）。
  3. 根据交集结果回表查询数据。
• 适用场景：WHERE条件通过AND连接，且合并后结果集显著小于单个索引的结果。

2.2 Union Merge（并集合并）

• 步骤：
  1. 分别扫描多个索引（例如WHERE条件通过OR连接），获取主键集合。
  2. 对主键集合取并集并去重。
  3. 回表查询数据。
• 示例：

  SELECT * FROM orders 
  WHERE customer_id = 100 OR product_id = 200;
  

2.3 Sort-Union Merge

• 背景：当OR条件对应的主键集合无序时，直接并集合并成本高。
• 步骤：
  1. 分别获取索引结果的主键集合并单独排序。
  2. 对有序主键集合执行合并去重（类似归并排序）。
  3. 回表查询。
• 与Union Merge的区别：Union要求索引返回的主键有序，而Sort-Union通过额外排序保证合并效率。

3. 索引合并的代价与局限性

3.1 成本分析

• 优点：
  • 避免全表扫描。
  • 在组合过滤性高时，比单索引扫描更高效。
• 缺点：
  • 多次索引扫描可能增加随机I/O（尤其是回表时）。
  • 主键合并操作需要CPU和内存开销。

3.2 常见失效场景

• 索引返回的主键范围过大（例如合并后仍需回表大量数据）。
• 表中存在全文索引或空间索引时，优化器可能拒绝合并。
• 索引的统计信息过期，导致优化器误判合并效率。

4. 优化建议：何时避免索引合并？

4.1 创建复合索引

若查询频繁使用多个列组合条件，直接创建复合索引效率更高：

CREATE INDEX idx_customer_product ON orders(customer_id, product_id);

优势：

• 单次索引扫描即可定位数据，避免多次扫描和合并操作。
• 减少随机I/O，尤其适合高选择性查询。

4.2 调整查询结构

通过提示（Hint）或重写查询，引导优化器选择更优索引：

-- 例如在MySQL中强制使用复合索引
SELECT * FROM orders USE INDEX(idx_customer_product) 
WHERE customer_id = 100 AND product_id = 200;

5. 实战验证：如何识别索引合并？

以MySQL为例：

1. 使用EXPLAIN查看执行计划：

   EXPLAIN SELECT * FROM orders WHERE customer_id = 100 AND product_id = 200;
   

2. 若type字段为index_merge，且Extra字段出现Using intersect(...)，则表明触发交集合并。

总结
索引合并是优化器在缺乏合适复合索引时的补救策略，其效率取决于条件的选择性和数据分布。理想情况下，应通过复合索引直接覆盖查询条件，避免额外的合并开销。实际调优中需结合执行计划和统计信息，权衡索引合并与复合索引的设计。