数据库查询优化中的查询结果合并（Query Result Union）优化原理解析

一、题目描述
在数据库查询优化中，查询结果合并（Query Result Union）是一种处理包含UNION、UNION ALL等集合操作的优化技术。当查询中包含多个SELECT子查询的合并操作时，数据库需要高效地合并这些子查询的结果集，同时消除重复行（对于UNION）或保留所有行（对于UNION ALL）。优化器会通过重写查询逻辑、调整执行顺序、利用索引或物化中间结果等方式，降低I/O和计算开销，提升集合操作的性能。本知识点将深入解析其工作原理、优化策略及实际应用。

二、解题过程循序渐进讲解

步骤1：理解UNION操作的基础语义与执行过程

• UNION：合并多个SELECT的结果集，自动去除重复行，默认按第一个SELECT的列排序。
• UNION ALL：合并结果集但保留所有行（包括重复行），不进行去重。
• 执行阶段：
  1. 依次执行每个SELECT子查询，生成中间结果集。
  2. 将中间结果集合并为临时结果。
  3. 对UNION（非UNION ALL）进行去重排序操作。
• 性能瓶颈：
  • 去重操作需排序或哈希，消耗CPU和内存。
  • 中间结果可能过大，导致临时表I/O开销。
  • 多个SELECT子查询可能重复扫描相同表。

步骤2：识别优化机会——基于查询重写的优化策略
优化器通过分析查询结构，应用以下重写规则：

1. 常量折叠与子查询合并：

   • 若多个SELECT子查询条件相同，可合并为单次扫描。
   • 示例：

     -- 原始查询
     SELECT * FROM t1 WHERE a=1 UNION ALL SELECT * FROM t1 WHERE a=1;
     -- 优化后等价于
     SELECT * FROM t1 WHERE a=1;
     

   • 优化器自动识别重复子查询，避免重复计算。

2. 条件下推至子查询：

   • 将WHERE条件分别下推至每个SELECT子查询，提前过滤数据，减少中间结果集大小。
   • 示例：

     -- 原始查询
     SELECT * FROM t1 UNION ALL SELECT * FROM t2 WHERE t2.id > 100;
     -- 条件下推后，第二个子查询先过滤数据再合并。
     

3. 利用索引避免排序去重：

   • 若UNION的每个SELECT子查询结果已按去重键有序，可合并时使用归并排序（Merge Union），避免全量排序。
   • 要求：每个子查询的ORDER BY列与UNION去重键一致，且数据库支持有序结果流式合并。

步骤3：深入优化技术——基于执行计划的优化

1. 哈希去重（Hash Union）：

   • 对UNION操作，使用哈希表去重：
     • 将每个结果行插入哈希表，重复行自动丢弃。
     • 适用于数据量大但内存充足场景，避免排序I/O。
   • 限制：哈希表需内存支持，可能溢出到磁盘。

2. 排序去重（Sort Union）：

   • 传统方法：对所有中间结果排序，然后扫描去重。
   • 优化变体：
     • 若子查询结果已部分有序，可多路归并减少排序量。
     • 示例：UNION多个索引扫描的结果，每个结果已按索引键有序。

3. 并行执行合并：

   • 将多个SELECT子查询分配到不同CPU核心并行执行，最后合并结果。
   • 优化器权衡子查询复杂度与并行开销，避免过度并行化。

4. 物化临时结果优化：

   • 对复杂子查询，将中间结果物化到临时表，避免重复计算。
   • 权衡：物化减少重复计算，但增加临时表I/O。

步骤4：实战优化技巧与案例分析
案例：查询两个时间段订单的合并去重。

-- 原始查询
SELECT order_id, customer_id FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
UNION
SELECT order_id, customer_id FROM orders WHERE order_date BETWEEN '2023-02-01' AND '2023-02-28';

优化过程：

1. 索引利用：在order_date上建立索引，使每个子查询通过索引范围扫描获取结果，且结果按order_id有序（若索引包含该列）。
2. 归并合并：由于两个子查询结果均有序，优化器采用归并合并，流式去重，避免全量排序。
3. 条件下推：各自子查询的日期条件提前过滤数据。
4. 结果：执行计划显示两个索引范围扫描后直接进行Merge Union操作，无需临时表排序。

步骤5：高级优化——基于统计信息的代价估算
优化器通过统计信息选择最优合并策略：

• 若子查询结果基数小，选择哈希去重（内存友好）。
• 若子查询结果已部分有序，选择归并合并。
• 若UNION ALL且无去重需求，直接拼接结果流，避免任何额外操作。
• 动态调整：现代数据库（如PostgreSQL、Oracle）会在执行中根据中间结果大小切换策略（如哈希溢出时切排序）。

三、总结与面试要点

• 核心思想：通过重写减少计算量，利用有序性避免排序，并行化提升吞吐。
• 面试回答要点：
  1. 区分UNION与UNION ALL的代价差异（去重开销）。
  2. 列举优化策略：条件下推、索引利用、哈希/排序选择、并行执行。
  3. 结合实例说明优化器如何选择合并算法。
• 延伸问题：
  • 如何判断UNION查询是否被优化？
    （答：查看执行计划，关注Union节点算法类型，如Merge Union、Hash Union。）
  • UNION与OR条件查询的性能对比？
    （答：UNION可能利用索引合并，OR可能导致全表扫描；需根据索引和过滤性具体分析。）