MySQL复合查询实战：JOIN、子查询、UNION与聚合分组详解

1. 复合查询到底在解决什么问题先抛个场景。你手里有两张表一张是用户表一张是订单表。用户表里有用户ID、用户名、注册时间订单表里有订单号、下单用户ID、订单金额、下单时间。现在老板让你拉一份报表要求列出每个用户名下最近三个月的所有订单明细包括用户名、订单号、金额、下单时间。你一看这两张表单表查用户只能查出用户名单表查订单只能查出订单号两边都对不上号只能把数据导到Excel里手工VLOOKUP。这个场景就是复合查询最典型的应用场景。所谓复合查询本质上就是打破单张表的物理边界把多张表的数据按照某种逻辑关系拼在一起查询。MySQL里常见的实现方式就是JOIN、子查询、UNION这类手段。很多初学者在学完单表增删改查之后一碰到这种“数据分散在多张表”的需求就卡住本质上是因为还没有建立“关系”的思维方式——表与表之间靠什么字段关联关联之后数据怎么拼接拼接之后怎么过滤和聚合这篇文章不会讲那些教科书式的定义我会直接从我实际写SQL的经验出发把复合查询里最常用的JOIN、子查询、UNION、聚合分组这些操作捋一遍重点讲清楚每个操作背后的逻辑、踩过的坑以及哪些看起来能用但实际性能差到离谱的写法。不管你是刚学MySQL的初学者还是已经在业务里写了两年SQL但一直靠试错吃饭的开发这篇内容应该都能让你对复合查询有一个系统性的认知提升。2. 先搞懂JOIN的底层逻辑2.1 关联条件的本质是“行的配对规则”我第一次接触JOIN的时候觉得这东西特别玄乎什么内连接、左连接、右连接概念背了一堆真到写SQL的时候还是靠蒙。后来我换了个思路就通了JOIN的本质就是把两张表的数据按某种规则做行的配对。你可以把两张表想象成两副扑克牌左手一张牌右手一张牌挨个比大小。你规定“花色相同就配对”那就是内连接你规定“不管右边有没有左边的每张牌都要保留”那就是左连接。所谓关联条件ON就是你制定的配对规则字段相等只是最常用的一种实际还可以写大于、小于、区间匹配只不过业务里99%的场景都是等值关联。举一个我实际见过的问题。有同事写LEFT JOIN关联条件里除了等值还加了一个过滤条件结果发现数据莫名其妙地变少了。他百思不得其解最后发现是把过滤条件写在ON后面还是WHERE后面的问题。ON后面是“配对规则”的一部分WHERE是在配对完成后对结果集做最终裁剪这俩的执行顺序完全不同结果自然也不同。这是很多初学者甚至一些工作两三年的开发都会踩的坑后面我专门展开讲。2.2 五种JOIN类型逐个拆解MySQL里常用的JOIN类型我从实际使用的角度给你排个序按使用频率从高到低说。INNER JOIN内连接这是最常用的。它的语义是只保留两边“配对成功”的行。用集合论的话说就是两个集合的交集。我写SQL的时候如果明确知道业务上只需要两边都有的数据就直接用INNER JOIN语义最清晰。LEFT JOIN左连接使用频率跟INNER JOIN差不多。它的语义是以左表为主体左表的每一行都保留右表能配上的就配上配不上就补NULL。这个“补NULL”的行为特别重要很多人在写LEFT JOIN之后发现SUM求和的结果不对就是因为没意识到左表有行在右表里找不到对应记录被补了NULL。RIGHT JOIN右连接实际上是LEFT JOIN的反向写法。我个人的习惯是尽量不用RIGHT JOIN能用LEFT JOIN表达的就不写成RIGHT因为人的阅读习惯是从左往右扫LEFT JOIN读起来顺。当然这纯属个人风格MySQL也完全支持RIGHT JOIN。CROSS JOIN交叉连接这个要特别小心。它表示“每一行和每一行都配对”也就是笛卡尔积。如果左表有100行右表有200行结果就是20000行。我见过有的同事写SQL的时候忘了写ON条件MySQL不会报错而是直接给你一个CROSS JOIN的结果行数瞬间爆炸。更可怕的是在数据量大的表上这么干直接把数据库CPU打满。FULL OUTER JOIN在MySQL里没有直接的实现。如果业务上确实需要“两边都全保留”的语义可以用LEFT JOIN UNION RIGHT JOIN的方式模拟。下面给一张对照表方便你速查JOIN类型保留原则未匹配到的行实际使用频率INNER JOIN只保留两边都匹配的行直接丢弃最高LEFT JOIN保留左表所有行右表补NULL很高RIGHT JOIN保留右表所有行左表补NULL低CROSS JOIN所有行两两配对全部保留极低多用于测试数据生成FULL OUTER JOIN两边都保留另一侧补NULLMySQL不直接支持需用UNION模拟2.3 为什么说ON和WHERE的执行顺序直接决定结果这个点我必须单独拿出来说因为我见过太多人在这里翻车包括我自己早期也在这个问题上栽过跟头。有一个真实的业务例子。用户表user有1000条记录订单表order有2000条记录我要统计每个用户的订单情况。某个同事写了一条SQL目标是“查所有用户以及他们在2023年的订单数”SELECT u.user_id, u.username, COUNT(o.order_id) AS order_cnt FROM user u LEFT JOIN order o ON u.user_id o.user_id AND o.order_year 2023 GROUP BY u.user_id, u.username;这个写法是正确的语义。加了AND条件在ON后面意味着在配对的时候只有2023年的订单才参与配对其他年份的订单直接被过滤掉所以每个用户最多只会匹配到2023年的订单。如果某用户在2023年没有订单LEFT JOIN依然会保留这个用户COUNT的结果是0。但如果不小心把过滤条件写到了WHERE里SELECT u.user_id, u.username, COUNT(o.order_id) AS order_cnt FROM user u LEFT JOIN order o ON u.user_id o.user_id WHERE o.order_year 2023 GROUP BY u.user_id, u.username;结果就完全不一样了。WHERE是在JOIN完成之后对整个结果集做裁剪那些2023年没下单的用户LEFT JOIN补出来的NULL行会被WHERE条件直接滤掉最终结果里丢失了这部分用户。SQL的执行逻辑说白了就是先FROM再JOIN然后WHERE过滤这决定了ON和WHERE的语义差异。理解了执行顺序这类问题就不会再困惑了。3. 子查询把一张表变成查询的原料3.1 标量子查询与IN子查询的使用边界子查询是复合查询的另一大主力。它的核心思想很直白把一条SQL的查询结果当作另一条SQL的输入。比如我需要查“订单金额高于平均订单金额的订单有哪些”如果我不会子查询就得先把平均值查出来记在脑子里再写第二条SQL去查大于这个值的订单。有了子查询一条SQL搞定SELECT order_id, user_id, amount FROM order WHERE amount (SELECT AVG(amount) FROM order);括号里的部分就是标量子查询它的特点是返回值只有一个单值。因为只返回单值所以可以配合比较运算符使用性能也还说得过去。另一个高频场景是IN子查询。比如“查所有下过单的用户信息”直觉写法是SELECT * FROM user WHERE user_id IN (SELECT DISTINCT user_id FROM order);这种写法在数据量小的时候没问题但有一个隐藏的性能隐患如果子查询返回的数据量很大比如返回了几万个user_idIN的效率会明显下降。我的建议是能先用JOIN写清楚语义的就优先用JOININ子查询更适合那些“确实需要把一张表的查询结果当作过滤集合”的场景且确认返回集合不会特别大。3.2 EXISTS和相关子查询什么时候比IN快EXISTS子查询是另一种风格。它的语义是“是否存在满足条件的记录”不关心子查询返回什么字段只关心有没有行返回。从MySQL优化器的执行逻辑来看EXISTS通常走半连接遇到匹配行就可以提前停止扫描所以在右表数据量大的时候往往比IN更高效。看一个典型场景“查所有下过单的用户”用EXISTS写SELECT * FROM user u WHERE EXISTS ( SELECT 1 FROM order o WHERE o.user_id u.user_id );这里注意两点。第一子查询里SELECT 1就行不需要SELECT *EXISTS根本不关心返回的列是什么。第二这是一个相关子查询内层SQL引用了外层表的字段u.user_id意味着外层每处理一行内层都要执行一次判断。从执行方式上看它天然适合“外层小、内层大”的场景反过来如果外层表很大内层表很小性能就要打折扣。我把IN和EXISTS的取舍总结一下这个经验表可以直接拿去用对比维度IN子查询EXISTS子查询执行逻辑先执行子查询再对外层逐行判断外层逐行判断匹配即停适合场景子查询结果集小外层表小、内层表大结果集匹配值匹配存在性判断常见问题子查询结果过大会变慢子查询内引用外层字段过多会变慢3.3 派生表FROM子句里的临时结果集派生表也叫子查询放在FROM子句里。它的用法是先把需要的明细查出来包成一个临时结果集再跟别的表关联。这个写法在多层统计报表里特别常见。举个实际的例子。我要统计“每个用户平均每单金额超过500的用户名单”直接对原始表关联计算比较啰嗦可以先在FROM里把用户ID和平均金额算出来SELECT t.user_id, t.avg_amount FROM ( SELECT user_id, AVG(amount) AS avg_amount FROM order GROUP BY user_id ) t WHERE t.avg_amount 500;注意派生表有几个基本要求这也是踩坑重灾区。第一派生表必须有别名上面写的是t忘了写直接报语法错误。第二派生表本质上是MySQL在内存或磁盘上创建的一个临时结果集如果数据量特别大性能可能有问题。第三MySQL 8.0对派生表的优化做了改进新版本里会尝试把派生表合并到外层查询但并非所有情况都能合并所以复杂查询里不宜过度依赖派生表。3.4 子查询的嵌套深度能少一层就少一层我之前在代码评审里看到过一条SQL嵌套了五六层子查询每一层都是一层临时表整个SQL读下来花了十分钟才理清楚逻辑。这种写法在功能上可能没问题但对执行效率的影响通常非常大。每一层子查询都可能让优化器不容易选到最优执行计划而且对临时表的物化消耗也会层层叠加。我的个人原则是能用JOIN表达的优先JOIN确实需要子查询的控制在两层以内超过三层的先停下来想想有没有更简单的写法或者把中间结果拆成多步执行。SQL是给人读的首先得逻辑清晰其次才是性能优化。4. UNION与UNION ALL纵向合并结果集4.1 UNION的“去重”行为和你想象的不一样JOIN和子查询解决的是“横向拼接”问题UNION解决的则是“纵向合并”问题。比如两张表的数据结构一样一个是2023年的数据一个是2024年的数据你想把两张表的数据合并成一份全量数据UNION就是为此而生的。有个细节必须讲清楚UNION会做去重操作。所谓去重是对整个结果集的每一行做完整的值比较如果两行所有字段的值完全一样才认为是重复。这意味着UNION的实际执行过程是先把两边的结果集合并然后对全量结果做一次排序或哈希去重。这一步非常消耗资源和时间。而UNION ALL则什么都不管直接把所有行拼在一起有多少行就返回多少行。大部分业务场景其实去重是有意义的但如果你确定两边数据不会有重复或者重复了也没关系比如统计数据里每条明细本身就带唯一编号那就直接上UNION ALL。90%的情况下UNION ALL是更优的选择唯一的例外是你确实需要全局去重。4.2 UNION与JOIN的取舍什么时候用哪个UNION和JOIN经常被拿来做对比其实这俩解决的是完全不同维度的需求。我见过同事写一条SQL想实现“纵向合并”的效果结果用了一堆JOIN把一个查询搞成两条竖排拼在一起的假象不仅写法别扭性能也差。换到思路上来说如果你的目标是把A表字段和B表字段拼成一行中间有业务关联关系用JOIN如果你的目标是把A表的行和B表的行合并成一个更大的行集合用UNION。实际业务里对账场景常用UNION报表统计常用JOIN分表数据合并用UNION ALL这个区分可以帮你快速选型。4.3 UNION的排序与分页必须二次包装UNION的结果集如果你想ORDER BY或者LIMIT直接写在最后一段SQL后面是没用的。MySQL的语法规定UNION整体只允许一个ORDER BY和一个LIMIT而且它作用于整个UNION的结果。正确写法是把UNION包在子查询里再在外层排序和分页SELECT * FROM ( SELECT order_id, amount FROM order_2023 UNION ALL SELECT order_id, amount FROM order_2024 ) t ORDER BY amount DESC LIMIT 20;这个坑我实际踩过。当时想查今年和去年销售额前10的商品直接把ORDER BY写在了第二段SELECT后面结果返回的数据明显不对仔细一查发现只对第二个表排序了。后来养成了习惯凡是UNION的结果要做排序分页一律先包一层派生表。5. 聚合与分组复合查询里的统计利器5.1 GROUP BY和HAVING的正确姿势复合查询离不开统计统计离不开聚合函数。MAX、MIN、AVG、SUM、COUNT是五个最基础的聚合函数。但聚合函数只有在GROUP BY的配合下才能发挥真正价值。GROUP BY的分组逻辑是把相同值的行归为一组然后对每一组做聚合计算。这里必须养成一个习惯SELECT里除了聚合函数之外的普通字段都必须出现在GROUP BY里。比如SELECT user_id, username, COUNT(*)如果只GROUP BY user_idMySQL 8.0里有什么后果ONLY_FULL_GROUP_BY这个SQL模式默认是开启的会直接报错就算关掉了username在不同行里也可能不同查出来的值是不可预测的。所以别跟这个规则较劲该分组的分组不该SELECT的字段就别SELECT。HAVING和WHERE的区分也是一个经典考点。WHERE是在分组之前过滤原始行HAVING是在分组之后过滤组。比如“查订单数超过10个的用户”先按用户分组再统计每个组内的订单数再过滤订单数大于10的组这就是HAVING的工作SELECT user_id, COUNT(*) AS order_cnt FROM order GROUP BY user_id HAVING order_cnt 10;注意HAVING里可以用聚合函数也可以直接引用SELECT里的别名order_cnt但WHERE里不能出现聚合函数。这个区别在面试里经常被问到在实际写SQL时也容易搞混——我见过有人把HAVING写成WHERE COUNT(*) 10直接语法报错。5.2 聚合查询中的NULL陷阱聚合函数跟NULL值之间的恩怨值得一提。COUNT()和COUNT(字段)的区别就是最典型的例子COUNT()统计的是行数不管某个字段是不是NULLCOUNT(字段)统计的是该字段非NULL的行数。如果你期望统计行数却写了COUNT(amount)而amount字段恰好有很多NULL结果就会偏少。SUM函数遇到NULL就跳过AVG是除以非NULL行数而不是所有行数。如果某组里全是NULLSUM返回NULLAVG也返回NULL但COUNT(*)返回0。业务里如果这种NULL值会导致结果不对可以用IFNULL或者COALESCE兜底但前提是你得知道这里有NULL。排错的时候第一反应应该去查原始数据有没有NULL而不是怀疑SQL写错了。5.3 复合聚合与多表聚合的经典写法实际业务里经常要把多表聚合跟JOIN结合起来。最经典的场景查每个用户的订单总金额和订单数量。SELECT u.user_id, u.username, COUNT(o.order_id) AS order_cnt, IFNULL(SUM(o.amount), 0) AS total_amount FROM user u LEFT JOIN order o ON u.user_id o.user_id GROUP BY u.user_id, u.username;这里用LEFT JOIN而不是INNER JOIN是为了把没有下过单的用户也查出来然后通过IFNULL把SUM(NULL)兜底成0。如果写成INNER JOIN没下过单的用户直接消失了统计口径就不对。多表聚合的核心思维是先确定主表的范围再决定用哪种JOIN最后考虑NULL值的处理口径。6. 复合查询的性能优化与排查6.1 索引是如何影响JOIN的复合查询的性能问题90%都出在JOIN的关联字段有没有索引上。MySQL的JOIN执行典型的方式是嵌套循环连接外层表逐行取数内层表根据关联条件找匹配行。内层表的查找如果走了索引就像拿着身份证号去查档案库的目录效率极高如果没走索引就相当于把整个档案库翻一遍性能差距是数量级的。所以我的第一个排查动作就是看JOIN的关联字段有没有建索引。比如上面那个user和order的JOINorder表上的user_id字段一定要建索引。用EXPLAIN看执行计划时重点关注type列是不是ref或者eq_ref如果是ALL基本就是全表扫描了该加索引得加索引。关于索引我再多说一句。给JOIN关联字段建索引比在WHERE过滤字段上建索引往往更重要。因为WHERE过滤只影响读取范围而JOIN关联字段决定了内层表查找的效率一个是节省IO一个是节省CPU两者的权重不一样。6.2 用EXPLAIN检查执行计划EXPLAIN是排查SQL性能问题的第一工具。把EXPLAIN加在SELECT语句前MySQL会返回一张执行计划表里面有关键的几个字段type、key、rows、Extra。type字段从好到坏的顺序大概是这样system const eq_ref ref range index ALL。实际JOIN查询里eq_ref和ref是理想的关联扫描级别range表示走了索引做范围扫描index表示扫描了整个索引树ALL表示全表扫描。ALL在数据量小的表上问题不大在大表上出现就是灾难。rows字段表示优化器估算的需要扫描的行数是判断性能好坏的重要参考。Extra字段如果出现Using filesort或者Using temporary说明查询有排序或临时表的开销也值得关注。下面是我平时排查一张慢查询SQL的标准步骤直接照着做先用EXPLAIN看执行计划重点看type和key字段。确认JOIN关联字段是否命中索引没有的话评估建索引的方案。看Extra有没有filesort和temporary有的话评估排序字段是否能走索引。看rows变大是否为笛卡尔积检查有没有漏写ON条件。用实际数据量估算返回结果集的大小确认是否需要分页或拆分查询。6.3 从执行计划看子查询的性能问题子查询的性能问题比JOIN更隐蔽因为它可能在执行计划里物化成一张临时表。特别是IN子查询MySQL有时会把子查询的结果物化成一张临时索引表再跟外层表做半连接这个过程叫semi-join。物化本身需要时间和存储数据量越大代价越高。EXISTS相关子查询则不同它的开销取决于外层表的行数。外层每处理一行内层就要执行一次存在性判断如果内层表有索引这个判断非常快如果内层没有索引每次判断都是全表扫描性能会非常难看。所以写EXISTS的时候内层关联字段的索引比什么都重要。6.4 三种典型的慢查询现场我整理几个我实际排查过的慢查询案例给你做个参考。案例一漏写ON条件导致笛卡尔积。一条JOIN两张表的查询每张表五万行结果扫描了25亿行数据库直接卡死。排查时发现SQL里只有JOIN关键字没有任何ONMySQL直接做了CROSS JOIN。这种低级错误在新手身上特别常见代码评审时一定要重点检查JOIN语法完整性。案例二IN子查询的结果集巨大。一条IN子查询子查询返回了三万多个ID外层表有十万行直接就慢得离谱。后来改成EXISTS写法外层逐行判断加内层索引耗时从十几秒降到几十毫秒。案例三ORDER BY字段没有索引。一条查询JOIN完再排序排序字段没有索引MySQL只能用Using filesort。数据量小的时候看不出问题数据量一上来文件排序的时间远远超过查询本身。后来给排序字段加了联合索引问题直接消失。7. 一套可以直接抄的复合查询速查模板7.1 JOIN、子查询、UNION、聚合的选型逻辑写了这么多年SQL我形成了一套自己的选型逻辑写下来给你参考。遇到复合查询需求先不要急着写SQL先问三个问题。第一个问题要查的数据来自几张表如果只有一张表但条件特别复杂那不是复合查询是普通查询加条件如果数据分散在多张表先理清表之间的关系是一对一、一对多还是多对多。第二个问题数据的拼接方向是横向还是纵向横向拼字段用JOIN纵向拼行用UNION。第三个问题查出来之后要做什么要统计汇总就用GROUP BY聚合只要明细就直接查要排序分页就注意包装。把这套逻辑背下来应付绝大多数业务需求就够用了。我在给团队做SQL培训时也反复强调SQL首先要语义正确其次是性能达标最后才是写法优雅千万别为了秀操作把简单问题复杂化。7.2 一个从需求到SQL的完整案例我拿一个电商场景来完整走一遍流程。需求是查出每个商品分类下销售总额超过10000元的商品列出分类名、商品名、销售总额按销售总额倒序排列只要前5条。商品表product有product_id、product_name、category_id、price分类表category有category_id、category_name订单明细表order_item有item_id、order_id、product_id、quantity。第一步理清关系。product和category是一对多product和order_item是一对多。要拿分类名得JOIN category要算销售总额得JOIN order_item。第二步组装JOIN。先写from和JOINSELECT c.category_name, p.product_name, SUM(oi.quantity * p.price) AS total_sales FROM category c JOIN product p ON c.category_id p.category_id JOIN order_item oi ON p.product_id oi.product_id第三步加分组。统计销售总额按产品维度所以GROUP BY要带上产品和分类字段GROUP BY c.category_id, c.category_name, p.product_id, p.product_name第四步加HAVING过滤销售总额超过10000的产品HAVING total_sales 10000第五步排序和分页ORDER BY total_sales DESC LIMIT 5;完整SQLSELECT c.category_name, p.product_name, SUM(oi.quantity * p.price) AS total_sales FROM category c JOIN product p ON c.category_id p.category_id JOIN order_item oi ON p.product_id oi.product_id GROUP BY c.category_id, c.category_name, p.product_id, p.product_name HAVING total_sales 10000 ORDER BY total_sales DESC LIMIT 5;注意GROUP BY字段的顺序先走一遍流程再写SQL往往比直接开写更不容易出错。8. 写在最后的一些经验和话这些年在项目里写了大量复合查询SQL最大的体会是复合查询这东西思路比语法重要逻辑比技巧重要。很多人在学习的时候热衷于收藏各种“高级写法”但真正到了业务现场最常用的不过是INNER JOIN、LEFT JOIN、GROUP BY、HAVING这几板斧。把这几板斧磨利了再理解子查询和UNION的适用场景已经能覆盖95%以上的业务需求。最后再分享一个实用的小习惯。每写一条复合查询SQL我都会顺手EXPLAIN看一眼执行计划。这不是形式主义而是花十秒钟可能省下后面十个小时的排查时间。很多慢查询问题其实在执行计划里一眼就能看出来比如漏了索引、出现All、扫了太多行。养成这个习惯之后你的SQL质量会有一个肉眼可见的提升。还有一点面对特别复杂的嵌套查询别急着一步到位。先写子查询确认中间结果对不对再逐层拼装最后合并优化出错的概率会低很多。写SQL和写代码一样是一步一步调试出来的不是一口气憋出来的。