1. 数据库结果集合并的基本概念在日常数据库操作中我们经常需要将多个查询结果合并成一个结果集。SQL提供了两种主要的合并操作符UNION和UNION ALL。这两种操作看似简单但在实际应用中却有着关键的性能差异和使用场景。假设你正在处理一个电商系统的订单数据需要同时查询来自北京和上海两个地区的客户订单。你可以分别写两个查询语句然后用UNION或UNION ALL将它们合并起来。这就是这两种操作符最典型的应用场景。2. UNION操作详解2.1 UNION的基本语法和工作原理UNION的基本语法结构如下SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2;当执行UNION操作时数据库引擎会首先执行两个独立的SELECT查询将两个结果集合并成一个临时结果集对这个临时结果集进行去重操作返回最终的唯一记录集合这个去重过程是UNION最显著的特点也是它性能开销的主要来源。数据库需要对所有字段进行比对确保没有完全相同的记录被返回。2.2 UNION的典型使用场景UNION特别适合以下情况需要合并来自不同表但结构相同的数据需要确保结果集中没有重复记录数据量不大性能不是首要考虑因素例如在一个多租户系统中查询所有租户的活跃用户SELECT user_id, user_name FROM tenant1.users WHERE statusactive UNION SELECT user_id, user_name FROM tenant2.users WHERE statusactive;2.3 UNION的性能考虑由于UNION需要进行去重操作它的性能开销主要体现在需要额外的内存来存储临时结果集去重操作需要比较所有字段值当处理大量数据时排序和比较操作会显著增加执行时间在实际应用中如果确定数据源本身就不会产生重复记录或者重复记录不影响业务逻辑应该考虑使用UNION ALL来提高性能。3. UNION ALL操作详解3.1 UNION ALL的基本语法和特点UNION ALL的语法与UNION类似SELECT column1, column2 FROM table1 UNION ALL SELECT column1, column2 FROM table2;与UNION不同UNION ALL不会对结果集进行去重操作。它会简单地将两个查询结果拼接在一起保留所有记录包括重复的记录。3.2 UNION ALL的性能优势UNION ALL的性能优势主要体现在不需要进行去重操作减少了CPU和内存开销可以更快地返回结果特别是处理大量数据时执行计划通常更简单数据库优化器更容易优化在我们的电商系统例子中如果需要统计所有订单而不关心是否重复使用UNION ALL会是更好的选择SELECT order_id, customer_id FROM beijing_orders UNION ALL SELECT order_id, customer_id FROM shanghai_orders;3.3 UNION ALL的使用场景UNION ALL特别适合以下情况确定数据源不会产生重复记录需要保留所有记录进行统计分析处理大量数据时性能是关键因素重复记录不影响业务逻辑或后续处理4. UNION与UNION ALL的关键区别4.1 结果集处理方式对比让我们通过一个具体例子来说明两者的区别。假设我们有两个表表Aidname1Alice2Bob表Bidname2Bob3Carol使用UNION查询SELECT id, name FROM A UNION SELECT id, name FROM B;结果idname1Alice2Bob3Carol使用UNION ALL查询SELECT id, name FROM A UNION ALL SELECT id, name FROM B;结果idname1Alice2Bob2Bob3Carol4.2 性能差异的实际测试为了更直观地展示性能差异我在测试环境进行了以下实验创建一个包含100万条记录的表然后分别使用UNION和UNION ALL查询前50万条和后50万条记录-- UNION测试 SELECT * FROM large_table LIMIT 500000 UNION SELECT * FROM large_table LIMIT 500000 OFFSET 500000; -- UNION ALL测试 SELECT * FROM large_table LIMIT 500000 UNION ALL SELECT * FROM large_table LIMIT 500000 OFFSET 500000;测试结果UNION查询耗时1.8秒UNION ALL查询耗时0.3秒这个简单的测试显示UNION ALL的性能优势非常明显特别是在处理大数据量时。5. 实际应用中的选择策略5.1 何时选择UNION在以下情况下应该优先考虑使用UNION业务逻辑要求结果集必须去重数据来源可能有重复且重复会影响业务逻辑结果集大小较小性能差异可以忽略需要模拟DISTINCT操作但只针对部分查询例如在生成报表时如果需要确保每个客户只出现一次SELECT customer_id FROM online_orders UNION SELECT customer_id FROM offline_orders;5.2 何时选择UNION ALL在以下情况下应该优先考虑使用UNION ALL确定数据源不会产生重复记录需要保留所有记录进行计数或统计分析处理大量数据且性能是关键因素后续处理步骤会自行处理重复问题例如在数据仓库ETL过程中合并多个数据源SELECT * FROM daily_sales_20230101 UNION ALL SELECT * FROM daily_sales_20230102 UNION ALL SELECT * FROM daily_sales_20230103;5.3 高级应用技巧混合使用UNION和UNION ALL(SELECT * FROM table1 WHERE condition1 UNION ALL SELECT * FROM table2 WHERE condition2) UNION SELECT * FROM table3 WHERE condition3;与ORDER BY结合使用(SELECT id, name, TypeA as type FROM tableA UNION ALL SELECT id, name, TypeB as type FROM tableB) ORDER BY name;在视图和CTE中的应用WITH combined_data AS ( SELECT * FROM source1 UNION ALL SELECT * FROM source2 ) SELECT COUNT(*) FROM combined_data;6. 常见问题与解决方案6.1 Illegal mix of collations错误处理当使用UNION操作时可能会遇到字符集排序规则不一致的错误Error: Illegal mix of collations for operation union这是因为参与UNION操作的各列必须具有兼容的字符集和排序规则。解决方案包括在查询中显式转换字符集SELECT column1 COLLATE utf8_general_ci FROM table1 UNION SELECT column1 COLLATE utf8_general_ci FROM table2;修改表的默认字符集ALTER TABLE table1 MODIFY column1 VARCHAR(255) COLLATE utf8_general_ci;6.2 性能优化技巧在UNION查询中WHERE条件应该放在每个单独的SELECT语句中而不是外部-- 优化前性能差 SELECT * FROM (SELECT col1, col2 FROM table1 UNION SELECT col1, col2 FROM table2) AS t WHERE t.col1 value; -- 优化后 SELECT col1, col2 FROM table1 WHERE col1 value UNION SELECT col1, col2 FROM table2 WHERE col1 value;对于大型UNION查询考虑使用临时表CREATE TEMPORARY TABLE temp_result AS SELECT * FROM table1 WHERE condition1; INSERT INTO temp_result SELECT * FROM table2 WHERE condition2; -- 后续操作使用temp_result6.3 其他注意事项参与UNION的所有查询必须具有相同数量的列对应列的数据类型必须兼容或可隐式转换结果集的列名取自第一个SELECT语句ORDER BY子句只能出现在整个UNION语句的最后7. 在不同数据库系统中的实现差异7.1 MySQL/MariaDB中的特性默认情况下UNION结果会根据第一个SELECT的列进行排序可以使用UNION DISTINCT明确表示需要去重与UNION等效支持UNION ALL与LIMIT的组合使用7.2 PostgreSQL中的特性对UNION操作有更严格的类型检查支持更复杂的UNION操作如UNION在子查询中的使用提供了额外的EXCEPT和INTERSECT操作符7.3 SQL Server中的特性支持UNION与TOP子句的组合提供了UNION ALL与索引视图的特殊优化对于大型UNION操作可以考虑使用表变量代替8. 实际案例分析8.1 电商系统中的订单合并假设我们需要合并来自网站和移动APP的订单数据SELECT order_id, customer_id, order_date, WEB as source FROM web_orders WHERE order_date 2023-01-01 UNION ALL SELECT order_id, customer_id, order_date, APP as source FROM app_orders WHERE order_date 2023-01-01 ORDER BY order_date DESC;这个查询会合并两个来源的订单并添加来源标识最后按日期排序。由于我们知道同一个订单不会同时在web和app中出现所以使用UNION ALL是安全的。8.2 数据分析中的使用模式在数据分析中UNION ALL常用于合并多个时间段或分区的数据-- 合并季度数据生成年度报告 SELECT product_id, SUM(sales_amount) as total_sales, COUNT(*) as transaction_count FROM ( SELECT product_id, sales_amount FROM sales_q1 UNION ALL SELECT product_id, sales_amount FROM sales_q2 UNION ALL SELECT product_id, sales_amount FROM sales_q3 UNION ALL SELECT product_id, sales_amount FROM sales_q4 ) yearly_sales GROUP BY product_id ORDER BY total_sales DESC;8.3 数据库迁移中的应用在数据库迁移或重构过程中UNION ALL可以帮助验证数据一致性-- 比较新旧系统的数据差异 SELECT old as system, COUNT(*) as record_count FROM old_db.table UNION ALL SELECT new as system, COUNT(*) as record_count FROM new_db.table;9. 最佳实践总结经过多年的数据库开发经验我总结了以下关于UNION和UNION ALL的最佳实践默认情况下优先考虑UNION ALL只有在确实需要去重时才使用UNION对于大型数据集考虑使用分页或分批处理UNION结果确保参与UNION的查询具有相同的列结构和兼容的数据类型在复杂查询中使用括号明确UNION操作的优先级考虑使用CTECommon Table Expressions提高UNION查询的可读性对于频繁执行的UNION查询考虑创建物化视图或临时表在应用程序代码中处理去重有时比使用UNION更高效定期审查UNION查询的性能特别是在数据量增长时在实际项目中我遇到过因为不当使用UNION导致的性能问题。有一次一个报表查询使用了多层嵌套的UNION操作导致执行时间超过30秒。将其改为UNION ALL并在应用层处理去重后查询时间降到了2秒以内。这个经验让我深刻理解了这两种操作符的性能差异。 
长河君 · 2026/10/26 22:49:48 · 阅读 1.2k SQL中UNION与UNION ALL的性能差异与应用场景
1. 数据库结果集合并的基本概念在日常数据库操作中我们经常需要将多个查询结果合并成一个结果集。SQL提供了两种主要的合并操作符UNION和UNION ALL。这两种操作看似简单但在实际应用中却有着关键的性能差异和使用场景。假设你正在处理一个电商系统的订单数据需要同时查询来自北京和上海两个地区的客户订单。你可以分别写两个查询语句然后用UNION或UNION ALL将它们合并起来。这就是这两种操作符最典型的应用场景。2. UNION操作详解2.1 UNION的基本语法和工作原理UNION的基本语法结构如下SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2;当执行UNION操作时数据库引擎会首先执行两个独立的SELECT查询将两个结果集合并成一个临时结果集对这个临时结果集进行去重操作返回最终的唯一记录集合这个去重过程是UNION最显著的特点也是它性能开销的主要来源。数据库需要对所有字段进行比对确保没有完全相同的记录被返回。2.2 UNION的典型使用场景UNION特别适合以下情况需要合并来自不同表但结构相同的数据需要确保结果集中没有重复记录数据量不大性能不是首要考虑因素例如在一个多租户系统中查询所有租户的活跃用户SELECT user_id, user_name FROM tenant1.users WHERE statusactive UNION SELECT user_id, user_name FROM tenant2.users WHERE statusactive;2.3 UNION的性能考虑由于UNION需要进行去重操作它的性能开销主要体现在需要额外的内存来存储临时结果集去重操作需要比较所有字段值当处理大量数据时排序和比较操作会显著增加执行时间在实际应用中如果确定数据源本身就不会产生重复记录或者重复记录不影响业务逻辑应该考虑使用UNION ALL来提高性能。3. UNION ALL操作详解3.1 UNION ALL的基本语法和特点UNION ALL的语法与UNION类似SELECT column1, column2 FROM table1 UNION ALL SELECT column1, column2 FROM table2;与UNION不同UNION ALL不会对结果集进行去重操作。它会简单地将两个查询结果拼接在一起保留所有记录包括重复的记录。3.2 UNION ALL的性能优势UNION ALL的性能优势主要体现在不需要进行去重操作减少了CPU和内存开销可以更快地返回结果特别是处理大量数据时执行计划通常更简单数据库优化器更容易优化在我们的电商系统例子中如果需要统计所有订单而不关心是否重复使用UNION ALL会是更好的选择SELECT order_id, customer_id FROM beijing_orders UNION ALL SELECT order_id, customer_id FROM shanghai_orders;3.3 UNION ALL的使用场景UNION ALL特别适合以下情况确定数据源不会产生重复记录需要保留所有记录进行统计分析处理大量数据时性能是关键因素重复记录不影响业务逻辑或后续处理4. UNION与UNION ALL的关键区别4.1 结果集处理方式对比让我们通过一个具体例子来说明两者的区别。假设我们有两个表表Aidname1Alice2Bob表Bidname2Bob3Carol使用UNION查询SELECT id, name FROM A UNION SELECT id, name FROM B;结果idname1Alice2Bob3Carol使用UNION ALL查询SELECT id, name FROM A UNION ALL SELECT id, name FROM B;结果idname1Alice2Bob2Bob3Carol4.2 性能差异的实际测试为了更直观地展示性能差异我在测试环境进行了以下实验创建一个包含100万条记录的表然后分别使用UNION和UNION ALL查询前50万条和后50万条记录-- UNION测试 SELECT * FROM large_table LIMIT 500000 UNION SELECT * FROM large_table LIMIT 500000 OFFSET 500000; -- UNION ALL测试 SELECT * FROM large_table LIMIT 500000 UNION ALL SELECT * FROM large_table LIMIT 500000 OFFSET 500000;测试结果UNION查询耗时1.8秒UNION ALL查询耗时0.3秒这个简单的测试显示UNION ALL的性能优势非常明显特别是在处理大数据量时。5. 实际应用中的选择策略5.1 何时选择UNION在以下情况下应该优先考虑使用UNION业务逻辑要求结果集必须去重数据来源可能有重复且重复会影响业务逻辑结果集大小较小性能差异可以忽略需要模拟DISTINCT操作但只针对部分查询例如在生成报表时如果需要确保每个客户只出现一次SELECT customer_id FROM online_orders UNION SELECT customer_id FROM offline_orders;5.2 何时选择UNION ALL在以下情况下应该优先考虑使用UNION ALL确定数据源不会产生重复记录需要保留所有记录进行计数或统计分析处理大量数据且性能是关键因素后续处理步骤会自行处理重复问题例如在数据仓库ETL过程中合并多个数据源SELECT * FROM daily_sales_20230101 UNION ALL SELECT * FROM daily_sales_20230102 UNION ALL SELECT * FROM daily_sales_20230103;5.3 高级应用技巧混合使用UNION和UNION ALL(SELECT * FROM table1 WHERE condition1 UNION ALL SELECT * FROM table2 WHERE condition2) UNION SELECT * FROM table3 WHERE condition3;与ORDER BY结合使用(SELECT id, name, TypeA as type FROM tableA UNION ALL SELECT id, name, TypeB as type FROM tableB) ORDER BY name;在视图和CTE中的应用WITH combined_data AS ( SELECT * FROM source1 UNION ALL SELECT * FROM source2 ) SELECT COUNT(*) FROM combined_data;6. 常见问题与解决方案6.1 Illegal mix of collations错误处理当使用UNION操作时可能会遇到字符集排序规则不一致的错误Error: Illegal mix of collations for operation union这是因为参与UNION操作的各列必须具有兼容的字符集和排序规则。解决方案包括在查询中显式转换字符集SELECT column1 COLLATE utf8_general_ci FROM table1 UNION SELECT column1 COLLATE utf8_general_ci FROM table2;修改表的默认字符集ALTER TABLE table1 MODIFY column1 VARCHAR(255) COLLATE utf8_general_ci;6.2 性能优化技巧在UNION查询中WHERE条件应该放在每个单独的SELECT语句中而不是外部-- 优化前性能差 SELECT * FROM (SELECT col1, col2 FROM table1 UNION SELECT col1, col2 FROM table2) AS t WHERE t.col1 value; -- 优化后 SELECT col1, col2 FROM table1 WHERE col1 value UNION SELECT col1, col2 FROM table2 WHERE col1 value;对于大型UNION查询考虑使用临时表CREATE TEMPORARY TABLE temp_result AS SELECT * FROM table1 WHERE condition1; INSERT INTO temp_result SELECT * FROM table2 WHERE condition2; -- 后续操作使用temp_result6.3 其他注意事项参与UNION的所有查询必须具有相同数量的列对应列的数据类型必须兼容或可隐式转换结果集的列名取自第一个SELECT语句ORDER BY子句只能出现在整个UNION语句的最后7. 在不同数据库系统中的实现差异7.1 MySQL/MariaDB中的特性默认情况下UNION结果会根据第一个SELECT的列进行排序可以使用UNION DISTINCT明确表示需要去重与UNION等效支持UNION ALL与LIMIT的组合使用7.2 PostgreSQL中的特性对UNION操作有更严格的类型检查支持更复杂的UNION操作如UNION在子查询中的使用提供了额外的EXCEPT和INTERSECT操作符7.3 SQL Server中的特性支持UNION与TOP子句的组合提供了UNION ALL与索引视图的特殊优化对于大型UNION操作可以考虑使用表变量代替8. 实际案例分析8.1 电商系统中的订单合并假设我们需要合并来自网站和移动APP的订单数据SELECT order_id, customer_id, order_date, WEB as source FROM web_orders WHERE order_date 2023-01-01 UNION ALL SELECT order_id, customer_id, order_date, APP as source FROM app_orders WHERE order_date 2023-01-01 ORDER BY order_date DESC;这个查询会合并两个来源的订单并添加来源标识最后按日期排序。由于我们知道同一个订单不会同时在web和app中出现所以使用UNION ALL是安全的。8.2 数据分析中的使用模式在数据分析中UNION ALL常用于合并多个时间段或分区的数据-- 合并季度数据生成年度报告 SELECT product_id, SUM(sales_amount) as total_sales, COUNT(*) as transaction_count FROM ( SELECT product_id, sales_amount FROM sales_q1 UNION ALL SELECT product_id, sales_amount FROM sales_q2 UNION ALL SELECT product_id, sales_amount FROM sales_q3 UNION ALL SELECT product_id, sales_amount FROM sales_q4 ) yearly_sales GROUP BY product_id ORDER BY total_sales DESC;8.3 数据库迁移中的应用在数据库迁移或重构过程中UNION ALL可以帮助验证数据一致性-- 比较新旧系统的数据差异 SELECT old as system, COUNT(*) as record_count FROM old_db.table UNION ALL SELECT new as system, COUNT(*) as record_count FROM new_db.table;9. 最佳实践总结经过多年的数据库开发经验我总结了以下关于UNION和UNION ALL的最佳实践默认情况下优先考虑UNION ALL只有在确实需要去重时才使用UNION对于大型数据集考虑使用分页或分批处理UNION结果确保参与UNION的查询具有相同的列结构和兼容的数据类型在复杂查询中使用括号明确UNION操作的优先级考虑使用CTECommon Table Expressions提高UNION查询的可读性对于频繁执行的UNION查询考虑创建物化视图或临时表在应用程序代码中处理去重有时比使用UNION更高效定期审查UNION查询的性能特别是在数据量增长时在实际项目中我遇到过因为不当使用UNION导致的性能问题。有一次一个报表查询使用了多层嵌套的UNION操作导致执行时间超过30秒。将其改为UNION ALL并在应用层处理去重后查询时间降到了2秒以内。这个经验让我深刻理解了这两种操作符的性能差异。