MySQL UNION ALL 用法详解：结果集合并、去重与性能优化技巧

1. 先把UNION ALL的定位搞清楚纵向拼接不是横向拼接1.1 一句话说清它在做什么mysql里做结果合并大家最常用到的就是UNION ALL。它的作用可以用一句话概括把多个SELECT查询结果按行上下堆在一起拼成一个更大的结果集。听起来很像把两张表“合成一张表”但它和JOIN有本质区别。JOIN是横向拼接把表A的列和表B的列并排扩展UNION ALL是纵向拼接把表A的行和表B的行依次往下累加。用一个简单的例子说明SELECT 1 AS num UNION ALL SELECT 2 AS num UNION ALL SELECT 3 AS num;执行结果就是三行数据分别是1、2、3。如果再执行SELECT 1 AS num UNION ALL SELECT 1 AS num;结果会是两行1。这也是UNION ALL和UNION最核心的区别UNION ALL完全不关心重复来多少行就返回多少行。很多刚接触mysql的人会把UNION ALL当成“高级查询关键字”其实它就是结果集的拼接器。你不需要知道两个结果集之间有什么外键关系也不需要它们来自同一个表只要字段数量和类型能对上就能拼。这个特性决定了它尤其适合做“全量集合拼接”类需求把分表数据合并、把不同统计口径的结果拼成长表、把历史数据和增量数据汇总。标题里说的“mysql全量集合拼接”本质上就是这种场景。1.2 UNION ALL 与 UNION 的差异去重是有成本的很多文章会把UNION ALL和UNION放在一起比较因为它们语法几乎一样差别只在一个ALL。但就是这个ALL让它们的执行逻辑分道扬镳。UNION会默认对最终结果去重等价于对合并后的结果集做一次DISTINCT。UNION ALL不做任何去重直接拼接所有行。差异用一个例子能看得很明白-- 两个查询都返回 1, 2 SELECT id FROM t1 WHERE id IN (1, 2) UNION SELECT id FROM t2 WHERE id IN (2, 3);UNION的结果是1、2、3如果有多个重复行也会被合并成一行。而UNION ALL的结果可能是1、2、2、3具体顺序还不保证。从性能上说UNION需要额外做一次去重。mysql内部可能要创建临时表把数据放进去再通过排序或哈希去重。数据量小的时候感觉不出来数据量一上去比如两个百万级结果集合并UNION的耗时和临时表空间消耗都会明显上升。所以我的经验是如果业务上能明确接受重复行或者两个结果集本身就不可能重复一律用UNION ALL。比如合并两个不同月份的分表订单ID理论上不会重复这时完全没有必要让mysql去重。只有在“必须保证结果集里没有重复行”的时候才用UNION而且要仔细评估数据量。另外还有一点容易忽略UNION虽然去重但结果集的行序依然不保证。不要以为用了UNION就会自动稳定排序mysql不会因为你写了UNION就帮你排好序。2. 全量集合拼接最常见的三个使用场景2.1 分表/分区数据合并把月度订单表拼成全年视图我在实际项目中遇到最多的情况就是业务表按月分表。比如订单表拆成orders_202401、orders_202402、orders_202403这种结构到了月底要拉一份全年订单明细最快的方式就是用UNION ALL把这些表拼起来。假设现在要查2024年前三个月的订单SELECT order_id, user_name, amount, created_at FROM orders_202401 UNION ALL SELECT order_id, user_name, amount, created_at FROM orders_202402 UNION ALL SELECT order_id, user_name, amount, created_at FROM orders_202403;这样做的好处是每一段SELECT都能独立走索引。如果每张订单表在created_at或order_id上有索引单表扫描很快拼接成本也不高。如果你经常要查询这个合并结果可以把它建成视图CREATE OR REPLACE VIEW v_all_orders AS SELECT order_id, user_name, amount, created_at FROM orders_202401 UNION ALL SELECT order_id, user_name, amount, created_at FROM orders_202402 UNION ALL SELECT order_id, user_name, amount, created_at FROM orders_202403;之后就能直接SELECT * FROM v_all_orders WHERE ...。不过要注意这种视图不是可更新视图。如果你试图对视图执行UPDATE或DELETEmysql大概率会报错因为UNION ALL的结果不具备可更新性。所以它只适合做只读汇总。2.2 同一张表按条件拆段后做 TopN 汇总另一个典型场景是“把一张大表按条件拆成几段每段取前N条再合并成最终TopN”。比如用户表里有一个排行榜要分别取iOS渠道前5名和Android渠道前5名最后合并成一个总榜前10。最简单的SQL是(SELECT user_id, user_name, score FROM player_score WHERE channel ios ORDER BY score DESC LIMIT 5) UNION ALL (SELECT user_id, user_name, score FROM player_score WHERE channel android ORDER BY score DESC LIMIT 5) ORDER BY score DESC LIMIT 10;这里有两层排序先给每个渠道内部排序取前5再把两个结果合并后整体排序取前10。注意单个SELECT的ORDER BY和LIMIT必须写在括号里否则mysql会认为你是想对整个UNION ALL结果做排序和分页逻辑就变了。如果业务要求合并后去掉重复用户可以把UNION ALL换成UNION但这种情况一般少见因为渠道通常不会重叠。2.3 跨库跨业务做汇总长表避免多层嵌套还有一种场景是把不同业务库里的统计结果拼成一张“长表”。比如同时要查订单总数、退款总数、投诉总数三张表没有任何直接关联用JOIN反而要处理空值用UNION ALL却非常干净SELECT order AS biz_type, COUNT(*) AS cnt FROM order_db.t_order UNION ALL SELECT refund AS biz_type, COUNT(*) AS cnt FROM refund_db.t_refund UNION ALL SELECT complaint AS biz_type, COUNT(*) AS cnt FROM complaint_db.t_complaint;结果就是三行数据每一行代表一个业务类型和对应的数量。这种长表非常方便后续做报表、做饼图或者继续在外面套一层聚合。相比用CASE WHEN把多个统计结果横向展开UNION ALL生成的纵向结构更容易被BI工具消费。3. 语法和类型规则为什么字段数一致还会出问题3.1 字段数量、顺序和别名的真实约束UNION ALL最硬的规则是每个SELECT查询返回的列数必须完全一致。第一个SELECT返回3列第二个也必须返回3列否则mysql会直接报错The used SELECT statements have a different number of columns这个错误一般不会有人看不懂真正容易踩坑的是“列数相同但顺序不同”。mysql只看位置不看你列的字段名是否一样。第二个SELECT里的第1列会拼到第一个SELECT的第1列下面即使语义完全不对。比如SELECT user_id, amount FROM orders_202401 UNION ALL SELECT amount, user_id FROM orders_202402;如果amount和user_id都是数值类型mysql根本不会报错但结果会非常诡异本来是用户ID的列下面可能混进了订单金额。排查这种问题时特别费劲因为它不报错只有对数据的人才能发现。所以统一规范很重要每个分支都显式列字段不要用SELECT *并且保证字段顺序一致。如果某个分支需要补充常量列比如标记来源月份可以写成SELECT user_id, amount, 2024-01 AS month FROM orders_202401 UNION ALL SELECT user_id, amount, 2024-02 FROM orders_202402;这里的结果列名以第一个SELECT为准后续SELECT即使起了不同别名也会被忽略。3.2 数据类型的隐式转换看上去能跑结果却不对mysql对UNION ALL各分支的数据类型处理比较“宽容”它会根据所有分支的字段类型选一个兼容类型。但这种宽容有时候会给你挖坑。比如第一个分支的字段是INT第二个分支的字段是VARCHAR而且里面存的是“abc”最终的返回类型可能被统一成字符串也可能被转成数值。一旦发生隐式转换你看到的数据可能变成0甚至导致排序、比较全部错乱。我建议在写UNION ALL时对容易混淆的字段显式统一类型。最常见的做法是用CASTSELECT user_id, CAST(amount AS DECIMAL(12,2)) AS amount FROM orders_202401 UNION ALL SELECT user_id, CAST(refund_amount AS DECIMAL(12,2)) FROM refunds_202401;这样至少能保证合并后的列类型一致后续做SUM或排序时不会因为类型不统一出现奇怪结果。另外要小心字段名是关键字的情况。如果你要合并的某张表里有key、order、rank这类字段必须用反引号包裹SELECT key, value FROM config_2024 UNION ALL SELECT key, value FROM config_2025;不要因为单个表里能用就以为在UNION ALL里也能裸写关键字字段名在复杂SQL里很容易出问题。3.3 ORDER BY 和 LIMIT 的正确写法括号不是装饰很多人在UNION ALL里排序分页时会写出这种SQLSELECT id FROM table_a ORDER BY id LIMIT 5 UNION ALL SELECT id FROM table_b ORDER BY id LIMIT 5;这段SQL的实际执行结果很可能和你预期完全不同。mysql不会把它理解成“第一个查询排序后取5条第二个查询排序后取5条再把两者拼起来”而是会把它当成对整个UNION ALL结果的某段排序处理。正确的写法是把每个分支的ORDER BY和LIMIT放进括号中(SELECT id FROM table_a ORDER BY id LIMIT 5) UNION ALL (SELECT id FROM table_b ORDER BY id LIMIT 5) ORDER BY id LIMIT 10;外层ORDER BY和LIMIT是作用在整个合并结果上的。如果省略外层ORDER BY即使每个分支内排好序合并后的顺序也不保证因为mysql为了执行效率可能改变行的输出顺序。这就是为什么我会在项目规范里明确要求凡是UNION ALL中带排序和分页的必须写清括号并且最终结果如果需要一致性排序必须在外层再写一次ORDER BY。4. 性能较量UNION ALL 快在哪慢又慢在哪4.1 去重的成本UNION 和 UNION ALL 的执行差异UNION ALL比UNION快几乎是公认的因为UNION要去重。为了去重mysql可能需要把合并后的结果集放进临时表然后做排序或者哈希操作。数据量小的时候无所谓数据量一大临时表落盘就会拖慢整个查询。举个例子两个表各100万行UNION ALL可能只是把两个结果流式往外发而UNION要先把200万行放进临时表判断哪些是重复行再只返回不重复的行。这个过程中可能产生Using temporary和Using filesort性能和磁盘消耗都会上升。所以只要业务允许少量重复或者确实无重复UNION ALL永远是更优的选择。4.2 索引在每个分支里独立生效UNION ALL的另一个特点是每个分支的SELECT是独立优化的。也就是说每个分支都可以用自己的WHERE条件、JOIN关系和索引。比如想查两种来源的用户SELECT user_id, user_name FROM users WHERE source app UNION ALL SELECT user_id, user_name FROM users WHERE source h5;如果users表上有(source, user_id)的联合索引两个分支都能用索引快速定位。mysql不会因为它们是同一个表就自动做全局优化但也不会互相拖累。这其实是UNION ALL的一个很大优势你可以把复杂查询拆成多个“小型索引友好查询”再拼接起来。4.3 用 EXPLAIN 观察临时表和排序当你怀疑UNION ALL性能出问题时第一件事就是跑EXPLAIN。看执行计划里有没有Using temporary、Using filesort以及每个分支实际扫描的行数。有一个常见误解以为UNION ALL没有临时表。其实它不一定完全没有临时表。如果外层有ORDER BY、GROUP BY、DISTINCT或者字段类型需要转换mysql照样可能建临时表。只是相比UNION少了一次“去重”的这层强制临时表操作而已。如果你想确认某个分支是不是索引没走对可以单独把那个SELECT拿出来执行EXPLAIN不要被UNION ALL整体执行计划干扰。4.4 不要盲目用 UNION ALL 替代一切 OR 查询网上有一种说法WHERE a 1 OR b 2有时候会让mysql放弃索引改成UNION ALL更快。这个说法在特定条件下成立但不是银弹。比如SELECT id, name FROM users WHERE login_name abc OR phone 123;如果login_name和phone都有独立索引mysql的优化器有可能把OR转换成UNION来执行但某些版本或某些复杂条件下确实会选择全表扫描。这时改成SELECT id, name FROM users WHERE login_name abc UNION ALL SELECT id, name FROM users WHERE phone 123;可能让两个分支各自走索引。但要注意如果一个用户既匹配login_name又匹配phoneUNION ALL会产生重复行。要不要换成UNION取决于你的结果集是否允许重复。我的建议是先用EXPLAIN看原始OR查询的执行计划确定它确实扫了全表再用UNION ALL来拆。不要看到一个优化技巧就无脑套用否则可能拆出来的SQL更难维护。5. UNION ALL 的进阶玩法从拼行到拼结果集5.1 INSERT INTO ... SELECT ... UNION ALL ... 做批量归档UNION ALL不仅可以出现在查询里还可以直接跟在INSERT INTO ... SELECT后面。比如把多张历史订单表一次性归档到总表INSERT INTO orders_archive (order_id, user_name, amount, created_at) SELECT order_id, user_name, amount, created_at FROM orders_202401 UNION ALL SELECT order_id, user_name, amount, created_at FROM orders_202402 UNION ALL SELECT order_id, user_name, amount, created_at FROM orders_202403;执行逻辑就是先把多个SELECT结果拼接成一个完整集合再插入目标表。这个方法特别适合做一次性数据迁移、月结归档。需要注意的是UNION ALL不去重如果源数据里本身有重复订单归档表里也会插入重复行。如果目标表有唯一键建议先做一次数据清洗或者在插入时考虑INSERT IGNORE但INSERT IGNORE会忽略主键冲突以外的错误使用时要谨慎。5.2 把多个统计结果拼成一张长表方便报表和看板做报表时经常需要把多个指标放在同一张表里。比如统计今天、本月、累计的订单数最直观的写法就是SELECT today AS period, COUNT(*) AS cnt FROM orders WHERE created_at CURDATE() UNION ALL SELECT this_month, COUNT(*) FROM orders WHERE created_at DATE_FORMAT(CURDATE(), %Y-%m-01) UNION ALL SELECT total, COUNT(*) FROM orders;结果两列三行报表前端只要循环渲染就行。如果以后要加“本月退款数”“今日新增用户数”只需继续追加分支。这种写法比把十几个指标写在一个SELECT里做COUNT(DISTINCT CASE WHEN ...)要清晰得多。尤其当各个指标来自不同表时UNION ALL可以避免一次JOIN引发的笛卡尔积问题。不同的统计查询本来互不相干硬要用JOIN关联反而可能造成结果膨胀。5.3 用 UNION ALL 生成连续日期序列含递归CTE另一个我很常用的玩法是用UNION ALL生成连续日期或者连续数字序列。在mysql 8.0之前很多人会用多个UNION ALL SELECT拼出一个数字表SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9;然后在外面套各种计算生成连续日期。这种写法在报表填充缺末日期的场景里非常好用比如订单表某些日期没有数据但报表要求每天一行WITH RECURSIVE date_series AS ( SELECT DATE(2024-01-01) AS d UNION ALL SELECT d INTERVAL 1 DAY FROM date_series WHERE d DATE(2024-01-31) ) SELECT d, IFNULL(SUM(o.amount), 0) AS amount FROM date_series LEFT JOIN orders o ON DATE(o.created_at) d GROUP BY d;这里的WITH RECURSIVE本质上也依赖UNION ALL做递归拼接。如果你还在用mysql 5.7可以手动拼一个日期临时表思路是一样的。5.4 在子查询和视图中使用的注意事项UNION ALL经常被放在子查询里。这时候有一个必须记住的点派生表必须有自己的别名。SELECT * FROM ( SELECT user_id, amount FROM orders_202401 UNION ALL SELECT user_id, amount FROM orders_202402 ) t WHERE t.amount 100;少了末尾的tmysql会直接报错。别小看这个别名我见过很多新手在这个报错上卡很久。在视图中使用UNION ALL也是一个好思路但同样要注意包含UNION ALL的视图默认不可更新同时查询时也无法像普通表那样利用视图合并优化。如果视图分支特别多建议只在报表类只读场景中使用。6. 我踩过的坑和排查思路碰到怪现象先看这几处6.1 坑一列顺序错位数据全错却不出错有一段时间我接手过一个报表系统里面的月表字段是user_id, user_name, amount, created_at其中一版SQL在拼接时不小心把某个分支写成了user_id, amount, user_name, created_at。因为三个字段类型分别是int、decimal、varchar、datetime两个分支类型对不上时mysql也没有报错最后报表里的用户名和金额全部错位。排查时最有效的办法是把每个分支单独执行一遍对比列头。如果列头一致再看每行数据是否在语义上能对齐。从那次之后我在任何用到UNION ALL的地方都要求列顺序必须完全一致而且不允许用SELECT *。6.2 坑二ORDER BY 被外层排序“覆盖”有一次我写了一道统计SQL想先对第一个分支按时间倒序取最近10条再跟第二个分支合并。当时写成(SELECT id, title, created_at FROM articles WHERE type news ORDER BY created_at DESC LIMIT 10) UNION ALL (SELECT id, title, created_at FROM articles WHERE type notice) ORDER BY created_at DESC;看起来没问题但实际跑出来发现第一个分支的“最近10条”根本没有保留因为外层ORDER BY created_at DESC把整个合并结果重新排序了。想保留每个分支的取数逻辑就要在分支内部或外层统一处理。后来我习惯的做法是如果每个分支有自己的取数顺序先把结果放在派生表里再加一个辅助排序字段。比如SELECT id, title, created_at FROM ( SELECT id, title, created_at, 1 AS sort_group FROM articles WHERE type news ORDER BY created_at DESC LIMIT 10 UNION ALL SELECT id, title, created_at, 2 FROM articles WHERE type notice ) t ORDER BY t.sort_group, t.created_at DESC;当然实际执行时分支内ORDER BY LIMIT必须放到括号内上面的示例是思路示范。遇到顺序不对不要只盯着ORDER BY先确认它到底作用在哪个层级。6.3 坑三分页/排名时缺少稳定的排序键UNION ALL合并后的结果如果只用ORDER BY score DESC做分页很容易出现下一页和上一页重复或漏数据。原因不是UNION ALL本身不稳定而是当你只按一个非唯一字段排序时mysql无法保证相同分数的行顺序固定。解决方式很简单在最终ORDER BY里加上唯一字段做第二排序键(SELECT user_id, score FROM t1) UNION ALL (SELECT user_id, score FROM t2) ORDER BY score DESC, user_id ASC LIMIT 20 OFFSET 20;只有排序键是唯一的分页才是稳定的。6.4 坑四把所有小结果集都拼进 UNION ALLSQL 越来越慢UNION ALL虽然快但也是相对UNION而言。如果你写了几十个分支每个分支都扫一次大表mysql会依次执行所有分支然后把结果合并。分支越多解析成本、IO成本和网络传输成本都会叠加。我见过一个统计SQL为了查每个省份的指标直接拼了34个SELECT ... UNION ALL ...执行时间从原来的2秒涨到30秒。后来改成把省份维度放进一张维表用JOIN一次查出来速度反而快得多。UNION ALL适合“分支数量少、每个分支索引友好”的场景。如果分支数量膨胀到几十个你要考虑换个思路比如临时表、分区表或者在应用层先批量查询再拼接。6.5 坑五可更新视图遇到 UNION ALL 直接拒绝最后再说一个很多人容易忽略的点。mysql里通过UNION ALL创建的视图默认是不允许UPDATE和DELETE的。比如你想写UPDATE v_all_orders SET amount amount * 1.1 WHERE order_id 100;mysql会报错因为这个视图底层是多表拼接mysql不知道应该去修改底层哪张表。如果需要修改只能直接操作底层表或者在应用层判断数据属于哪个分表后再发对应表的UPDATE。这不是UNION ALL的缺陷而是语义限制。遇到这种情况别想着改视图老老实实去改对应的底层表。我在实际使用中养成的习惯是先问自己“这个结果集需要去重吗需要排序吗需要分页吗每个分支能走索引吗”这四问过了基本就能确定该用UNION ALL还是UNION以及排序分页该写在哪个位置。mysql的UNION ALL本身不复杂复杂的是在不同业务场景下如何正确使用它。把上面这些规则和坑记住至少能少走一半弯路。