SQL中UNION与UNION ALL的区别与性能优化

1. UNION与UNION ALL的本质区别

在SQL查询中，UNION和UNION ALL都是用于合并多个SELECT语句结果集的操作符，但它们的处理方式存在关键差异。理解这个差异对于编写高效查询至关重要。

UNION ALL是最基础的集合合并操作，它会简单地将两个查询结果叠加在一起，不做任何去重处理。比如：

SELECT product_id FROM current_products UNION ALL SELECT product_id FROM discontinued_products

这个查询会返回两个表中所有product_id的简单叠加，包括重复值。从性能角度看，UNION ALL是最经济的操作，因为它不需要额外的计算资源来处理重复数据。

而UNION则在合并结果集后会自动去除重复行，相当于在UNION ALL的基础上增加了DISTINCT操作。例如：

SELECT customer_id FROM online_orders UNION SELECT customer_id FROM in_store_orders

这个查询会返回在所有渠道下单的客户ID列表，但每个客户只会出现一次。去重过程需要数据库引擎对结果集进行排序和比较，这会消耗额外的CPU和内存资源。

关键区别：UNION ALL保留所有行（包括重复），UNION自动去重。UNION ALL性能更高，UNION结果更干净。

2. 内部工作机制深度解析

2.1 UNION ALL的执行流程

数据库引擎处理UNION ALL时，实际上只是将两个结果集简单拼接：

1. 执行第一个SELECT查询
2. 执行第二个SELECT查询
3. 将两个结果集按顺序合并
4. 直接返回合并后的结果

这个过程不需要临时存储整个结果集，数据库可以流式处理数据，内存消耗最小。

2.2 UNION的执行流程

UNION操作则复杂得多，典型实现包括以下步骤：

1. 执行第一个SELECT查询，将结果存入临时表
2. 执行第二个SELECT查询，将结果追加到同一临时表
3. 对临时表进行排序（或使用哈希算法）
4. 扫描排序后的临时表，去除相邻的重复行
5. 返回最终结果

这个过程中，数据库需要足够的临时空间存储所有结果，排序操作的时间复杂度为O(n log n)，对于大表可能非常昂贵。

3. 性能对比与使用场景

3.1 性能基准测试

假设我们有两个表：

• employees_west：50万条记录
• employees_east：50万条记录
• 两表间有10万条重复记录

测试结果可能如下：

3.2 何时使用UNION ALL

以下情况优先考虑UNION ALL：

1. 确定源表之间没有重复记录
2. 需要保留所有记录（如日志分析）
3. 处理大型数据集且性能敏感
4. 已经在应用层处理去重

3.3 何时使用UNION

以下情况适合使用UNION：

1. 需要数学上的集合合并（真正的集合运算）
2. 源数据可能有重复且需要去重
3. 结果集较小或性能不是首要考虑
4. 无法在应用层有效去重

4. 高级用法与实战技巧

4.1 多表联合查询

可以一次合并多个查询结果：

SELECT product_id FROM q1_sales UNION ALL SELECT product_id FROM q2_sales UNION ALL SELECT product_id FROM q3_sales UNION ALL SELECT product_id FROM q4_sales

4.2 与ORDER BY配合使用

排序子句的位置很重要：

-- 错误：单独排序每个查询 SELECT name FROM employees WHERE dept = 'IT' ORDER BY name UNION SELECT name FROM employees WHERE dept = 'HR' ORDER BY name -- 正确：整体排序最终结果 SELECT name FROM employees WHERE dept = 'IT' UNION SELECT name FROM employees WHERE dept = 'HR' ORDER BY name

4.3 类型兼容性处理

合并的列必须类型兼容，必要时使用CAST：

SELECT customer_id FROM customers -- 整数类型 UNION ALL SELECT CAST(guest_id AS INT) FROM guest_orders -- 字符串转整数

5. 常见问题与解决方案

5.1 列数不匹配错误

每个SELECT语句必须有相同数量的列：

-- 错误：列数不同 SELECT id, name FROM employees UNION SELECT id FROM departments -- 正确：补足列数 SELECT id, name FROM employees UNION SELECT id, NULL AS name FROM departments

5.2 性能优化策略

对于大型UNION操作：

1. 先过滤再合并：在各自SELECT中添加WHERE条件
2. 考虑使用临时表：先存中间结果再处理
3. 对大表使用UNION ALL + 外层DISTINCT

5.3 分页查询处理

UNION查询的分页需要特殊处理：

WITH combined AS ( SELECT id, name FROM table1 UNION ALL SELECT id, name FROM table2 ) SELECT * FROM combined ORDER BY name OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

6. 不同数据库的实现差异

6.1 MySQL/MariaDB的特殊情况

MySQL中：

• UNION默认使用临时表处理
• 5.7+版本支持UNION ALL的松散扫描优化
• 可以使用UNION DISTINCT明确表示去重

6.2 SQL Server的优化

SQL Server提供：

• 合并连接(Concatenation)运算符
• 对已排序数据有特殊优化
• 支持TOP与UNION结合使用

6.3 PostgreSQL的特性

PostgreSQL中：

• 支持UNION ALL的并行执行
• 对哈希去重有良好优化
• 可以结合LATERAL使用

7. 实际案例剖析

7.1 电商平台订单合并

合并不同渠道订单但保留渠道标记：

SELECT order_id, 'web' AS channel FROM web_orders UNION ALL SELECT order_id, 'mobile' FROM mobile_orders UNION ALL SELECT order_id, 'store' FROM in_store_orders

7.2 分布式数据汇总

从多个分片数据库合并数据：

-- 从北京节点获取数据 SELECT user_id, region FROM beijing.users UNION ALL -- 从上海节点获取数据 SELECT user_id, region FROM shanghai.users

7.3 历史数据归档查询

查询当前和历史数据：

SELECT * FROM active_products UNION ALL SELECT * FROM archived_products WHERE archive_date > '2023-01-01'

8. 替代方案与进阶思考

8.1 使用JOIN替代UNION的情况

当需要关联查询而非简单合并时：

-- 低效的UNION方式 SELECT a.id FROM table_a a WHERE NOT EXISTS (SELECT 1 FROM table_b b WHERE b.id = a.id) UNION SELECT b.id FROM table_b b -- 更高效的FULL OUTER JOIN方式 SELECT COALESCE(a.id, b.id) FROM table_a a FULL OUTER JOIN table_b b ON a.id = b.id

8.2 物化视图与UNION

对于频繁执行的UNION查询，考虑创建物化视图：

CREATE MATERIALIZED VIEW combined_data AS SELECT * FROM recent_data UNION ALL SELECT * FROM historical_data

8.3 使用UNION实现动态条件

实现灵活的条件查询：

SELECT * FROM products WHERE (@category IS NULL OR category = @category) UNION ALL SELECT * FROM featured_products WHERE (@show_featured = 1)

在实际项目中，我经常发现开发人员过度使用UNION而忽视UNION ALL的性能优势。特别是在ETL流程中，当确定数据源没有重复时，改用UNION ALL往往能使查询速度提升3-5倍。一个实用的技巧是：先使用UNION ALL快速获取数据，如果确实需要去重，再考虑在应用层或通过外层SELECT DISTINCT处理。