1. 面试必答之外UNION与UNION ALL的差异到底藏在哪里很多数据库方向的开发者在面试前都会背一套标准答案UNION会去重UNION ALL不去重所以UNION ALL性能更好。这句话确实不算错但它只是结论的最外层。真正到了生产环境里因为写错UNION而导致的线上问题我见过太多次几乎都出在这个结论没有覆盖到的细节上。与其反复背诵结论不如先把UNION和UNION ALL在数据库里到底做了什么动作拆开看清楚。UNION和UNION ALL都属于集合运算中的“垂直合并”操作。SQL里最常见的JOIN是把一张表的列拼接到另一张表右侧结果集会变宽也就是水平方向的合并而UNION系关键字做的是另一件事——把两个SELECT查询各自返回的行从上到下拼接成一张结果集结果集会变高属于垂直方向的合并。举个例子表A查出两条记录表B查出三条记录用UNION合并后最多可能得到五条记录但前提是这三条记录里没有和那两条重复的而用UNION ALL合并后必然是五条记录哪怕有重复也照单全收。这就是两者在结果集形态层面的分歧。再把语法层面展开一点UNION和UNION ALL的写法几乎一模一样要求也很一致两个SELECT子句返回的列数必须相同对应列的数据类型必须兼容最终结果集的列名以第一个SELECT子句为准。但真正的分水岭在于UNION在执行时会自动对合并后的结果做一次去重相当于把UNION ALL的结果再套一层SELECT DISTINCT而UNION ALL不执行任何去重判断直接把两个子查询的行按顺序拼接返回。这个“差一个DISTINCT”听起来不起眼实际却会带来一连串你意想不到的连锁反应接下来我用一次真实压测来说明。1.1 垂直拼接很多人对UNION的第一步理解就是错的我在做技术评审时经常发现一些工作了好几年的开发对UNION的认知还停留在“它能把两个查询结果合起来”。这不够因为一旦遇到需要合并两个结构完全不同的查询他们就会把JOIN和UNION混着用。JOIN的逻辑单位是“行与行的横向配对”UNION的逻辑单位是“结果集与结果集的纵向拼接”。前者通常要指定连接条件后者则完全不需要条件只要求列数匹配。理解这个区别最好的方式是把两个查询结果表想象成货架上的两层货架板。JOIN是把两层的商品在同一个货架上按位置对齐摆放UNION则直接把两个货架板上下叠起来上面的商品有没有和下面的重复它不会主动去管除非你明确要求它管。这个类比在你后面写分页、排序、统计SQL时会特别有用。1.2 标准答案背后的三个隐藏副作用回到那个标准答案UNION会去重UNION ALL不去重。这句话背后至少藏着三个平时不一定会被提到、但关键时刻会决定结果的副作用。第一个副作用是性能去重必然涉及行与行之间的比较数据库要么把结果集排序后扫描相邻行去重要么通过哈希表记录哪些行已经出现过。无论哪种方式都需要额外的内存和计算资源。第二个副作用是顺序为了去重很多数据库会使用排序操作这会导致UNION的结果顺序看起来像是“被有序化”了但这个顺序是服务于去重的不是任何业务希望的排序。第三个副作用是临时空间当结果集大过内存阈值时去重过程会把中间结果写入临时表或临时文件磁盘I/O会让延时急剧上升。把这三个副作用放在一起你会发现UNION和UNION ALL的差异不仅仅是数学意义上的“是否包含重复行”而是一整套执行计划的差异。真正遇到大数据量时这种差异会从理论变成灾难。所以我一直觉得要想不被“背八股”工程化关键不是记住结论而是理解去重这个动作的底层行为。2. 一个真实压测案例为什么UNION在数据量面前会“失态”为了把性能差异讲得不那么抽象我模拟了一张订单流水表一共存储了200万行数据。这张表有三个关键字段order_id主键、amount金额、status状态。我用它构造了一个典型场景一个查询想取“已支付状态”的订单另一个查询想取“金额大于500”的订单这两个条件在数据上有大量重叠重叠量大概是三十万行。然后分别用UNION和UNION ALL写出来观察执行计划和执行时间。-- 方式一使用UNION自动去重 SELECT order_id, amount, status FROM order_records WHERE status PAID UNION SELECT order_id, amount, status FROM order_records WHERE amount 500; -- 方式二使用UNION ALL不去重 SELECT order_id, amount, status FROM order_records WHERE status PAID UNION ALL SELECT order_id, amount, status FROM order_records WHERE amount 500;两条SQL唯一的不同就是关键字。如果你只把“去重”当成一个简单的数学操作你会觉得UNION也就是多判断几次相等至于慢那么多吗实际测试结果却非常打脸在同样环境、同样数据量下UNION ALL执行耗时大约180毫秒UNION执行耗时超过3秒。十几倍的差距全部出在去重这个动作上。2.1 执行计划里的关键算子SORT还是HASH我用EXPLAIN打开了两种写法的执行计划差异非常直观。UNION ALL的执行计划非常简洁两个子查询的扫描算子树合并到一个Append或类似节点下然后直接返回结果中间没有任何去重算子。UNION的执行计划里多了一个去重节点在MySQL里叫Using temporary在Oracle里叫SORT UNIQUE在PostgreSQL里叫Unique或HashAggregate。就是这个节点把所有返回行都装进一个临时结构进行重复判断。去重节点的实现方式决定了数据量变大以后谁的胜出更明显。UNION核心要处理的问题等价于“找出一个集合中所有不重复的行”。如果数据库采用排序去重它会把结果集按照所有列的顺序排一遍然后只保留和前一列不同的行排序本身需要O(n log n)的比较数据量翻倍耗时远不止翻倍。如果采用哈希去重虽然比较的效率高一些但哈希表占用的内存很大一旦超过内存阈值数据库就必须把哈希表溢写回磁盘瞬间变成磁盘I/O密集操作。2.2 临时表与磁盘I/O性能断崖的根源在MySQL里UNION的去重过程会创建临时表。临时表默认优先使用内存但如果结果集大小超过了tmp_table_size或max_heap_table_size的配置值MySQL会把临时表从内存转为磁盘临时表。磁盘临时表的读写速度比内存慢一个数量级这是UNION在百万级数据量下性能断崖的根源之一。Oracle的情况类似SORT UNIQUE产生的排序数据需要写入临时表空间如果临时表空间不足整个SQL会直接报错。很多人在优化UNION时总想着去加索引却忽略了一个关键事实UNION的去重发生在两个子查询的结果集已经合并之后索引只能优化子查询里的过滤条件对去重这个环节几乎帮不上忙。所以当你的SQL写成了UNION性能瓶颈往往就锁定在排序或哈希上而不是扫描上。这也是为什么我倾向于在数据量大的场景中优先用UNION ALL把数据取回来再在应用层或外层SQL里做去重。2.3 UNION结果顺序的“薛定谔”效应除了性能UNION还藏着一个经常让开发在测试环境乐呵、生产环境崩溃的顺序问题。由于UNION的去重经常依赖排序实现它的结果顺序在大多数情况下表现为“所有列从小到大有序”。但这种“有序”并不是你指定的也不是业务需要的它纯粹是去重的副产品。更麻烦的是这个排序的稳定性取决于数据库版本、优化器选择和表数据分布同一套SQL在测试库跑出来的顺序在生产库不一定复现。UNION ALL则完全不存在这个困惑它的输出规则明确左侧子查询的所有行先返回接着返回右侧子查询的所有行。只要你不加ORDER BY这个顺序就是稳定的。所以凡是涉及合并后还要继续处理顺序的业务尤其分页和榜单类需求直接用UNION ALL然后再统一排序要比依赖UNION的“默认顺序”可靠得多。3. 合并结果集时的边界规则类型转换、NULL与列名性能差异只是UNION和UNION ALL最显眼的一面真正容易让线上数据出错的反而是那些看似细枝末节的合并规则。因为UNION家族要求两个结果集“兼容”但兼容不等于完全相同数据库会在这套兼容机制里做出很多你未必预期到的决定。3.1 列数与列顺序第一个隐藏红线无论UNION还是UNION ALL都要求两个SELECT子句返回的列数必须一致。这里的“一致”是严格一致多一点少一点都不行MySQL会直接报The used SELECT statements have a different number of columnsOracle会报ORA-01789。很多人以为只要保证列数相同就万事大吉却忽略了列顺序也必须对应。列顺序的问题特别隐蔽。举个例子第一个子查询返回的是user_id, user_name第二个子查询写成user_name, user_id。两个查询列数相同类型也恰好都是数值和字符串UNION能正常执行但结果集第一列对应的是user_id来自第一个查询和user_name来自第二个查询语义完全错位数据汇总之后会变成一团乱麻。好在这种错误通常可以在输出里侥幸看出来但如果两张表恰好都是字符串ID和字符串姓名的组合那就真的事故现场了。3.2 隐式类型转换数据库悄悄替你决定类型列数对齐之后数据库还要处理类型。UNION要求对应列的数据类型兼容而不是完全一致。兼容意味着数据库有时候会做隐式类型转换。在Oracle里数值和字符串拼接时通常有一个明确的优先级顺序在MySQL里不同排序规则和字符集下转换规则也会不同。我踩过一个非常典型的坑两个子查询分别从两张历史表中提取设备编码一张表是varchar类型存储另一张表因为建表时字段设计不当存储的是int类型。数据本身长得一样都是1024这种格式。用UNION合并后本应保留两条记录结果只返回了一条。原因就是数据库在比较时把varchar的1024转成了数字1024和int列的值相等于是判定为重复行静默吞掉了其中一条。这种“假重复”在逻辑检查时很难发现只有对最终行数做精确校验时才会暴露。排查这一类问题的通用方法是提前把两边对应列CAST成同样的类型比如CAST(device_code AS CHAR)让比较在可控前提下进行。3.3 NULL参与去重的特殊语义再来说NULL。SQL里的NULL不代表一个具体值它代表“未知”。在常规的等值比较里NULL NULL的结果是UNKNOWN不是TRUE。但去重这个操作有自己的一套逻辑数据库在做DISTINCT或UNIQUE判断时会把多个NULL看作是同一个值。换句话说两行数据其他列完全相同其中一列都是NULL那么在UNION去重时这两行会被判定为重复只保留一行。这个行为在业务上有时合理有时就是灾难。比如合并两个客户来源渠道时source_code这一列如果允许NULL那么所有source_code为NULL的客户都会被合并成一行即使他们在另一张表里是不同的潜在客户记录。处理方式是如果NULL对你的业务有实际含义进入UNION之前先用COALESCE把NULL替换成一个占位值比如COALESCE(source_code, UNKNOWN)再去合并。3.4 列名以第一个子查询为准容易被忽视的使用约束还有一个经常被误解的规则UNION结果集列名取第一个SELECT子句的列名第二个及以后子查询的列别名都会失效。比如第一个查询SELECT name FROM a第二个查询SELECT nickname AS name FROM b最终结果集列名是name不会出现nickname这一列。更麻烦的是如果你在最终结果集外层写ORDER BY nickname数据库会直接报错或提示找不到列。解决方式很简单要么在第一个子查询里给列起别名要么在UNION外层套一层SELECT明确定义最终要输出的列名。我这里更推荐后者尤其在结果集还要继续参与JOIN或者被下游应用解析时显式的列名定义能避免一堆隐式行为带来的混乱。4. 跨数据库实践Oracle、MySQL与PostgreSQL的UNION细节差异UNION和UNION ALL是SQL标准的一部分但每个数据库在实现时都会加入自己的性格。平时开发可能只接触一种数据库切换数据库后一些经验会失效这一点在UNION上体现得尤其明显。我把几个常见数据库实测后的差异点整理了一下。4.1 Oracle的SORT UNIQUE与临时表空间Oracle在相当长的一代版本里对UNION的实现方式是SORT UNIQUE也就是先对结果集排序再去重。这也解释了为什么很多从Oracle出来的人会觉得UNION的结果“默认是排好序的”。但正如前面所说排序是为了去重不是业务语义。如果我们依赖Oracle的这个默认行为把UNION结果当成order by后的结果来消费一旦优化器改为哈希去重或数据库版本升级结果顺序就可能变化。Oracle的另一个独特问题是临时表空间。SORT UNIQUE在数据量大的时候会把排序数据写入临时表空间。如果临时表空间是个小文件或者剩余空间不足SQL会直接报ORA-01652错误。所以我建议在Oracle里跑大数据量UNION前先确认临时表空间的配额避免半夜调度任务因为一个UNION告警。4.2 MySQL的临时表策略与子查询排序陷阱MySQL对UNION的处理有一个非常典型的特征结果集去重时会尽可能用内存临时表一旦超过tmp_table_size和max_heap_table_size的较小值就会把临时表转成磁盘临时表。MySQL的这两个参数默认值普遍不大Linux默认服务器上通常只有16MB到64MB所以稍微大一点的UNION查询都容易把临时表溢出到磁盘性能问题随之而来。MySQL还有个特别容易踩的语法坑如果要在UNION的单个子查询里使用ORDER BY并配合LIMIT必须用括号把子查询包裹起来否则语法会报错或者在某些版本里ORDER BY被直接忽略。正确写法是这样的(SELECT order_id, amount FROM order_records WHERE status PAID ORDER BY create_time DESC LIMIT 10) UNION ALL (SELECT order_id, amount FROM order_records WHERE amount 500 ORDER BY create_time DESC LIMIT 10);如果不加括号MySQL会认为ORDER BY作用于整个UNION结果最终和你的预期完全错位。这个坑在Oracle里没那么明显因为Oracle对UNION子查询的排序容忍度更高但在MySQL里几乎每次都能踩到必须养成加括号的习惯。4.3 PostgreSQL的Unique节点与哈希聚合PostgreSQL在计划阶段对UNION的处理同样值得一说。它通常会在Append节点之上叠加一个Unique节点用于去重。新版PostgreSQL还支持HashAggregate方式的去重这种方式不需要全局排序只需要维护一个哈希表内存命中时性能非常可观。不过一旦数据量超过work_memPostgreSQL同样会把哈希表溢写到磁盘只是它的溢出机制比MySQL更温和一些。PostgreSQL还有一个很有特色的特性它允许在UNION之后使用DISTINCT ON这类语法虽然使用场景比较小众但如果你在写复杂报表这会是一个非常有价值的工具。DISTINCT ON允许你指定“按某一列去重并保留这一组中的特定行”比如按用户ID去重同时保留每个用户最新一条订单。它和UNION的去重语义不同但在某些场景里能替代UNION加窗口函数的组合值得把它纳入工具箱。5. 名称相同含义完全不同数据库UNION与FastAPI的Union网络热词里出现了一个和UNION相关的条目——FastAPI的Union作用。很多人刚开始接触FastAPI看到Union这个单词以为它和数据库的UNION有什么血缘关系实际上这是两种完全不同领域里的术语只是因为英文单词相同造成了认知上的混淆。5.1 FastAPI里的Union其实是Python类型联合FastAPI里的Union来自Python的typing模块它用来描述一个变量可以拥有多种类型中的任意一种。比如一个接口的某个可选参数既允许传入字符串又允许不传就可以用Union[str, None]来标注。更常见的写法是直接用Optional它本质上是Union[X, None]的别名。from typing import Union from pydantic import BaseModel class SearchRequest(BaseModel): keyword: Union[str, None] None page: int 1 page_size: int 20这段代码里的Union[str, None]表示keyword字段可以是一个字符串也可以是None。它不会在执行时把多个结果合并也不会对数据做任何行集层面的运算。它的作用是让类型检查器、IDE和FastAPI的接口文档生成器知道这个字段接受哪些类型语义上属于静态类型标注的范畴。5.2 为什么这个概念混淆会造成实际困扰我在审查代码时遇到过同事把FastAPI的Union和数据库的UNION混为一谈试图在一个接口里返回多个模型并用Union来“拼接”结果结果导致响应结构完全不符合预期而且错误暴露得很晚等到前端同事对接时才发现问题。这个场景特别典型地说明了一个问题在跨技术栈协作时同一个术语在不同技术体系里可能完全是两个含义如果不搞清楚术语所在的技术域很容易写出逻辑错误但语法上却“看起来没错”的代码。区分这两个概念其实不需要多高深的方法论只要记住三条其一数据库UNION是SQL执行语句作用于数据行产生结果集合并其二Python的Union是类型标注作用于类型检查阶段不产生任何运行时数据变换其三前者写在SQL查询中后者写在Python类型声明中。三者具备其一就不会再把它们搞混了。面试里如果被问到FastAPI的Union直接回答类型联合即可不用硬往数据库方向扯。5.3 FastAPI里Union真正有用的两个场景FastAPI中用Union最多的两个场景一个是请求体字段的可选参数比如上面那个例子中的keyword另一个是接口响应的多态类型比如一个接口可能返回成功对象也可能返回错误对象此时可以用from typing import Union from fastapi import FastAPI from pydantic import BaseModel class SuccessResp(BaseModel): code: int 0 data: dict class ErrorResp(BaseModel): code: int 1 message: str app FastAPI() app.get(/search, response_modelUnion[SuccessResp, ErrorResp]) def search(): return SuccessResp(data{result: ok})这样写之后FastAPI生成的OpenAPI文档里会把该接口的响应类型标明为SuccessResp或ErrorResp的联合客户端和前端可以根据不同的返回结构做类型判断。理解了这个用法以后看到Union就不会再和SQL UNION联想到一起了。6. 写进代码里的取舍我的UNION选型规则与翻车记忆讲了那么多原理和机制最后落到工程里最实际的问题同一个需求什么时候用UNION什么时候用UNION ALL在不清楚数据重叠程度时是否只能靠猜我根据自己的经验总结了一套可以直接上手的决策规则也算是对这么多年踩坑经历的一次系统归纳。6.1 明确不会重复时放心用UNION ALL最理想的情况是从业务约束上就能确认两个查询结果不会重复。比如两个查询来自不同的表其中一张表有唯一索引保证业务主键唯一另一张表的数据范围在业务上不可能与第一张表重合又或者两张表来自不同业务域的独立数据源没有任何重叠的可能性。在这些前提下UNION ALL是唯一正确的选择既能保证结果行数精确又能避免去重带来的额外性能开销。我做过一个典型案例一个报表系统需要合并“线上订单表”和“线下手工录入订单表”两张表虽然结构大部分一致但业务上线下录入时不会插入线上渠道的订单号而且线上订单表对订单号有唯一索引。明确这个约束后我把原先的UNION改成了UNION ALL报表查询耗时从2秒降到300毫秒数据没有任何偏差。有时候性能优化并不需要高级技巧只是把不必要的操作移除掉。6.2 无法确认重叠且数据量不大时用UNION更安全如果两个查询的来源比较复杂比如多表关联后的结果集无法凭直觉判断是否会有重复而且数据量相对可控那我倾向于用UNION。原因很简单UNION可以保证结果集的行数不超过任一子查询的行数之和中“不重复部分”的最大值至少不会因为重复行导致下游统计翻倍。在数据量不大的场景下去重的性能开销可以接受选择正确性优先是合理的。6.3 数据量大且必须去重时把去重从UNION里拆出来最尴尬的场景是数据量大业务又必须去重。这个时候如果直接用UNION除了要忍受排序或哈希去重的高昂代价还要面临临时表空间爆掉的风险。我的处理思路是先分析重复的来源看看能否在子查询层面就排除掉重复如果无法在源头排除那就把UNION ALL的结果先落进一张临时表在临时表上建立适当的索引然后再做去重或聚合。比如这种写法CREATE TEMPORARY TABLE tmp_union_result AS SELECT user_id, user_name FROM vip_user UNION ALL SELECT user_id, user_name FROM normal_user; -- 在临时表上以user_id建索引再做按业务键去重的查询 SELECT user_id, MAX(user_name) AS user_name FROM tmp_union_result GROUP BY user_id;这样做的好处是把UNION的自动去重换成了明确的GROUP BY或窗口函数执行路径更可控也便于在临时表上建立索引加速。如果你用的是MySQL临时表的引擎、字符集也要注意如果用的是PostgreSQL还可以考虑把两个查询改成分区表的一部分用分区裁剪来避免全量合并。6.4 我的避坑检查清单以下几条是我每次写UNION相关SQL时都要在心里过一遍的检查点分享出来供你参考两个子查询的列数是否一致列顺序是否对应尤其注意别把同类型但不同含义的列放错位置。对应列的类型是否需要显式CAST避免数据库隐式转换导致假重复。业务上是否允许重复行不清除允许就上UNION ALL不允许再考虑UNION。结果集是否需要稳定的顺序如果需要别依赖UNION的默认行为自己写ORDER BY。子查询里使用ORDER BY/LIMIT时是否已经用括号包裹整个子查询。结果集列名以哪个子查询为准下游代码取字段名时必须与之对应。大数据量场景是否已经评估过临时表空间或内存参数考虑用临时表替代。6.5 最后再分享一个实战细节在我个人写过的大量报表SQL里UNION ALL的使用频次远高于UNION。原因并不是我总在允许重复的场景里工作而是在多数业务报表需求里“哪些数据可能重复”这件事完全可以提前分析清楚。一旦分析清楚要么用UNION ALL直接输出要么在子查询里用DISTINCT或GROUP BY把数据源清理干净。这样一来合并层就不需要背着去重重担执行计划和性能都更加可控。八股文告诉我们的结论是静态的但数据库的执行行为是动态的能理解这层动态关系比记住任何一个标准答案都重要。
