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时实际上只是将两个结果集简单拼接执行第一个SELECT查询执行第二个SELECT查询将两个结果集按顺序合并直接返回合并后的结果这个过程不需要临时存储整个结果集数据库可以流式处理数据内存消耗最小。2.2 UNION的执行流程UNION操作则复杂得多典型实现包括以下步骤执行第一个SELECT查询将结果存入临时表执行第二个SELECT查询将结果追加到同一临时表对临时表进行排序或使用哈希算法扫描排序后的临时表去除相邻的重复行返回最终结果这个过程中数据库需要足够的临时空间存储所有结果排序操作的时间复杂度为O(n log n)对于大表可能非常昂贵。3. 性能对比与使用场景3.1 性能基准测试假设我们有两个表employees_west50万条记录employees_east50万条记录两表间有10万条重复记录测试结果可能如下操作执行时间内存使用适合场景UNION ALL0.8秒50MB已知无重复或需要保留重复UNION3.2秒500MB必须去除重复记录3.2 何时使用UNION ALL以下情况优先考虑UNION ALL确定源表之间没有重复记录需要保留所有记录如日志分析处理大型数据集且性能敏感已经在应用层处理去重3.3 何时使用UNION以下情况适合使用UNION需要数学上的集合合并真正的集合运算源数据可能有重复且需要去重结果集较小或性能不是首要考虑无法在应用层有效去重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_sales4.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 name4.3 类型兼容性处理合并的列必须类型兼容必要时使用CASTSELECT 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 departments5.2 性能优化策略对于大型UNION操作先过滤再合并在各自SELECT中添加WHERE条件考虑使用临时表先存中间结果再处理对大表使用UNION ALL 外层DISTINCT5.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 ONLY6. 不同数据库的实现差异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_orders7.2 分布式数据汇总从多个分片数据库合并数据-- 从北京节点获取数据 SELECT user_id, region FROM beijing.users UNION ALL -- 从上海节点获取数据 SELECT user_id, region FROM shanghai.users7.3 历史数据归档查询查询当前和历史数据SELECT * FROM active_products UNION ALL SELECT * FROM archived_products WHERE archive_date 2023-01-018. 替代方案与进阶思考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.id8.2 物化视图与UNION对于频繁执行的UNION查询考虑创建物化视图CREATE MATERIALIZED VIEW combined_data AS SELECT * FROM recent_data UNION ALL SELECT * FROM historical_data8.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处理。