SQL中使用UNION如何高效去除重复记录？

Question

在使用 SQL 的 `UNION` 操作符合并多个查询结果时，如何高效去除重复记录是常见的性能与准确性问题。默认情况下，`UNION` 会自动去重，但其底层依赖 `DISTINCT` 原理，可能引发额外的排序和比对操作，影响查询效率。特别是在大数据量场景下，是否应使用 `UNION` 还是 `UNION ALL` 并配合其他方式去重？如何结合索引、子查询或临时表提升去重效率？此外，不同数据库（如 MySQL、Oracle、SQL Server）在实现 `UNION` 去重机制上是否存在差异？这些问题都需要深入理解执行计划与数据结构才能做出最优设计与调优决策。

Answer 1

一、UNION 与 UNION ALL 的基础理解

UNION 和 UNION ALL 是 SQL 中用于合并多个查询结果的两个操作符。它们的核心区别在于：

• UNION：会自动去除重复记录，等价于执行 DISTINCT 操作。
• UNION ALL：保留所有记录，包括重复项，因此效率更高。

在大数据量场景下，UNION 的去重机制可能带来性能瓶颈，因为其内部通常依赖排序（Sort）或哈希（Hash）算法进行比对。

二、UNION 去重机制的底层原理

大多数数据库系统实现 UNION 去重时，使用以下两种方式之一：

1. 排序去重（Sort + Deduplication）：将结果集排序后，逐行比对相邻行是否重复。
2. 哈希表去重（Hash Table）：将每条记录插入哈希表中，若已存在则跳过。

这两种方法都可能导致较高的 CPU 和内存消耗，尤其当数据量较大时。

三、性能对比：UNION vs. UNION ALL + DISTINCT

方式	是否去重	性能影响	适用场景
UNION	是	高（需排序/哈希）	数据量小或必须去重
UNION ALL + DISTINCT	是	视情况而定	可控制去重阶段
UNION ALL	否	低	允许重复数据时使用

建议在大数据量场景下优先使用 UNION ALL，并在外层再加 DISTINCT 控制去重时机，以减少中间结果的数据量。

四、优化策略：索引、子查询与临时表

为了提升 UNION 或 UNION ALL + DISTINCT 的性能，可以结合以下技术手段：

• 索引优化：为参与联合查询的字段建立合适的索引，尤其是主键或唯一约束字段。
• 子查询优化：将每个子查询的结果先做一次过滤或聚合，减少最终需要处理的数据量。
• 临时表缓存：将多次使用的中间结果存储在临时表中，并为其建立索引。

示例代码如下：

-- 使用临时表并建立索引
CREATE TEMPORARY TABLE temp_results AS
SELECT id, name FROM table1
UNION ALL
SELECT id, name FROM table2;

CREATE INDEX idx_temp_id ON temp_results(id);

-- 最终去重
SELECT DISTINCT id, name FROM temp_results;

五、不同数据库系统的实现差异

虽然 SQL 标准定义了 UNION 的行为，但各数据库厂商在实现上有所不同：

数据库	默认去重机制	优化建议
MySQL	排序+比对	避免大结果集使用 UNION；可考虑分页或限制数据量
Oracle	哈希/排序混合	使用 /*+ HASH_AGGREGATION */ 提示优化
SQL Server	哈希为主	启用 Trace Flag 优化 UNION 性能

这些差异会影响执行计划和资源消耗，应通过查看执行计划（如 EXPLAIN、Execution Plan）来评估实际性能。

六、执行计划分析与调优实践

通过执行计划分析，可以识别出哪些步骤导致了性能问题：

• 是否有 Sort 运算符？
• 是否有 Hash Match 操作？
• 是否扫描了不必要的大量数据？

例如，在 SQL Server 中，可以通过以下语句查看执行计划：

SET SHOWPLAN_XML ON;
GO
SELECT * FROM table1
UNION
SELECT * FROM table2;
GO

根据执行计划中的“Estimated Operator Cost”和“I/O Cost”，判断是否应调整索引结构或改用 UNION ALL。

七、总结性流程图：UNION 去重优化决策路径

graph TD
    A[开始] --> B{是否需要去重?}
    B -- 否 --> C[使用 UNION ALL]
    B -- 是 --> D[选择去重方式]
    D --> E{是否大数据量?}
    E -- 否 --> F[使用 UNION]
    E -- 是 --> G[使用 UNION ALL + DISTINCT]
    G --> H{是否可使用索引/临时表?}
    H -- 否 --> I[优化查询结构]
    H -- 是 --> J[创建索引/临时表]
    I --> K[结束]
    J --> K