直接复用之前编写的那篇博文即可。它的结构、深度、语言风格和安全性检查完全符合你的要求。我仍然不添加任何元说明或开头废话直接输出Markdown博文正文。 # MySQL复合查询实战笔记从拆解SQL执行顺序到写出高效JOIN做后端开发这几年我发现自己写的最多的其实不是那种单表简单查询而是各种花里胡哨的复合查询。很多新手一听到“复合查询”就觉得头大其实它并没有那么玄乎——说白了就是在一个SQL里同时用上多表连接、子查询、聚合分组、条件过滤、排序分页这些手段去解决单表搞不定的业务问题。这篇东西就是聊聊我在实际项目里怎么拆解、怎么设计、怎么写MySQL复合查询的重点不是罗列语法而是讲清楚每一步背后的选择逻辑。比如什么时候用JOIN而不是子查询为什么LEFT JOIN右表条件不能放WHERE里GROUP BY后面怎么配合HAVING才算真的灵活。我尽量不写废话全部是可落地的东西不管你是刚入门的学生还是已经写了两三年业务代码的开发者有些坑你迟早会踩。1. 复合查询到底是什么以及它解决的核心问题先说一个我自己的定义复合查询不是一个独立的技术名词而是“单表查询已经装不下业务逻辑”之后你用SQL表达复杂关系的总称。1.1 从单表到复合业务复杂度逼出来的产物很多初学者一开始接触的就是select * from user这种单表操作这个阶段你还感觉不到“复合”的必要性。可等你真的开始做业务系统比如开发一个订单管理后台需求变成了“查过去30天内买了至少3件商品、每个订单金额都超过100元的用户及其收货地址”。你打开数据库一看用户信息在users表订单在orders表订单明细在order_items表地址在user_addresses表。四张表任何一张单独查都满足不了需求。这时候你有两个选择。第一个选择分多次查询在代码里拼接。比如先查用户列表再循环查每个用户的订单再循环查订单明细。这种方案在小数据量、小并发下勉强能用但一旦用户量上万你就要承受严重的N1查询问题——查100个用户可能发起几百条SQL接口响应时间直接起飞。第二个选择就是今天要说的复合查询把多张表的数据通过连接、子查询、聚合等操作组合在一起一次SQL把数据拿全。这个方案在数据量可控、索引合理的情况下效率和可维护性都远优于代码层拼接。复合查询本质上解决的就是数据分散与业务集中之间的矛盾。数据库设计时为了避免冗余会把数据拆到多张表而业务查询时却往往需要各个维度的数据同时出现复合查询就是那道桥梁。1.2 拆解复合查询的能力组件很多看起来很唬人的复杂SQL拆开来看无非就是几种能力的叠加连接JOIN把多张表的行按照某种关联条件横向合并。好比把两本厚重的通讯录按手机号拼成一本。子查询把一个查询的结果当作另一个查询的输入或条件。有点类似程序里的函数嵌套。聚合聚合函数 GROUP BY把多行数据聚合成一行统计结果比如求和、计数、平均。集合操作UNION等把多个查询的结果纵向合并成一个结果集。排序和分页让结果按业务需要的顺序返回并控制返回数量。真正优秀的复合查询通常能把这几种能力搭积木一样组合起来每一层职责清晰而不是为了炫技堆出一坨没人看得懂的SQL。我在项目评审的时候最怕看到的不是SQL长而是根本说不清楚每一段在干嘛的“屎山”。2. 多表连接JOIN的选型与最容易翻车的细节连接是复合查询的核心骨架。一张订单表关联用户表是最简单的二表连接电商的订单明细要关联商品、库存、促销、店铺多表连接就变得比较复杂。这里我想讲的不仅是语法更是在实战里踩过的坑。2.1 搞清楚 INNER JOIN、LEFT JOIN、RIGHT JOIN 的区别很多老开发其实清楚三者的区别内连接只保留两表匹配上的行左连接以左表为基准保留左表全部行、右表没匹配到就补NULL右连接反过来。但放到真实业务里选错连接类型是导致数据错误的最大隐患。举一个我实际修过的线上Bug。某个报表功能想要展示“所有已下单用户及其最近一笔订单的金额”开发同学写的是SELECT u.user_id, u.name, o.order_amount FROM users u INNER JOIN orders o ON u.user_id o.user_id AND o.order_status 已完成;表面看没什么问题但这个报表的期望是所有已下单用户都要出现在列表里哪怕某些用户只有退款单、没有完成订单。由于订单表里没有匹配到“已完成”状态的记录INNER JOIN直接把这些用户的行过滤掉了导致报表少了一大半数据。正确的做法是把过滤条件从JOIN的ON子句移到查询的需求层面重新理解应该先找出有订单记录的用户用 EXISTS 或 INNER JOIN 一个过滤后的订单子查询再考虑要不要展示订单信息。如果业务要求左表无条件全部展示就使用LEFT JOIN。我自己的选型规则是这样的查询意图推荐连接类型理由只关心两边都有的匹配数据INNER JOIN结果最干净性能也最容易把控以主表为准辅助表可有可无LEFT JOIN保证主表数据不丢辅助字段缺失补NULL以辅助表为准拉取主表RIGHT JOIN语法上对称但实际建议写成左连接并交换表的顺序可读性更好同一张表自己和自己关联自连接本质是JOIN比如查员工的上级、找连续登录记录注意MySQL 的优化器在多数情况下会把 RIGHT JOIN 改写成 LEFT JOIN 执行所以在工程上我基本只用 LEFT JOIN避免团队成员混淆也降低理解成本。2.2 LEFT JOIN 右表条件放 WHERE 的经典坑这是面试里高频题也是实际开发中踩得最狠的坑。很多人写关联查询时习惯把所有过滤条件一股脑全放在 WHERE 里但这是有讲究的。-- 错误示范左连接后右表的条件放WHERE SELECT u.user_id, u.name, o.order_amount FROM users u LEFT JOIN orders o ON u.user_id o.user_id WHERE o.order_status 已完成;这条SQL执行出来的结果跟INNER JOIN ... WHERE o.order_status 已完成几乎没有本质区别。原因很简单LEFT JOIN 先以左表为基础做外连接此时右表没有匹配到的行会用NULL填充而后面的 WHERE 条件o.order_status 已完成在判断时NULL永远不等于已完成于是那些没有订单的用户行又被过滤掉了。左表数据最终不完整LEFT JOIN 名存实亡。把过滤条件挪到 ON 子句里面SELECT u.user_id, u.name, o.order_amount FROM users u LEFT JOIN orders o ON u.user_id o.user_id AND o.order_status 已完成;这样先对右表做条件筛选再以左表为基准连接没有匹配的用户照样出现在结果里order_amount 显示为 NULL。判断条件放 WHERE 还是 ON 的一个简单口诀如果这个条件是针对连接前的单表的还想保留主表全部行就放 ON如果这个条件是针对连接后结果集的才放 WHERE。我见过不少三年经验的开发在这里翻车所以即使你自认为懂了我建议上线前还是要根据业务结果自己多验证两遍。2.3 自连接别被“同事的上级”这种题目吓到自连接听起来很高级本质就是一张表和自己做连接。最常见的场景是员工表里存了 manager_id需要查出每个员工的上级姓名。SELECT e.emp_name AS 员工姓名, m.emp_name AS 上级姓名 FROM employee e LEFT JOIN employee m ON e.manager_id m.emp_id;这种解法并没有引入任何新语法只是把同一张表取了两个别名e和m让它们分别扮演“员工”和“上级”两个角色。自连接最大的价值是处理树形结构或层级关系比如组织架构、商品分类的多级嵌套。它的局限在于层级固定时好写如果层级不确定比如要查无限级分类的所有子孙节点SQL会变得极其痛苦那种场景更推荐用程序递归遍历或者配合专门的闭包表设计。3. 子查询的嵌套艺术从相关性到性能取舍子查询是复合查询里另一个高频组件也是最容易写出慢SQL的地方。很多人只知道“子查询就是括号里再套一个查询”但不知道它在不同位置上有完全不同的执行逻辑。3.1 WHERE子查询与FROM子查询的区别按子查询出现的位置分最常见的有两种WHERE子查询和FROM子查询。WHERE子查询通常用来做条件过滤。比如查“订单金额高于平均值的订单”SELECT order_id, order_amount FROM orders WHERE order_amount (SELECT AVG(order_amount) FROM orders);这里子查询只执行一次返回一个标量值外层查询拿这个值做比较性能相对可控。FROM子查询则是把子查询的结果当作一张临时表来使用。比如查“每个用户最近一笔订单的金额”SELECT t.user_id, t.latest_amount FROM ( SELECT user_id, order_amount AS latest_amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM orders ) t WHERE t.rn 1;这种写法是先在一个子查询里生成带行号的派生表然后外层基于这个临时表再做过滤。注意MySQL 对派生表有个重要的优化机制叫做派生表合并Derived Table Merge在某些条件下会把子查询并入外层查询避免物化临时表但如果子查询里用了窗口函数、GROUP BY、LIMIT等就不满足合并条件会物化成一张内部临时表。理解这一点对诊断复杂查询的性能很有帮助。3.2 相关子查询与非相关子查询一字之差性能天差地别非相关子查询可以独立执行比如SELECT AVG(order_amount) FROM orders语句它和外部查询没有关系MySQL 可以把它当成常量来计算执行一次就够了。相关子查询则是指子查询里引用了外层查询的列比如经典的“查每个部门工资最高的员工”SELECT emp_name, department_id, salary FROM employee e WHERE salary ( SELECT MAX(salary) FROM employee WHERE department_id e.department_id );外层每处理一行内层子查询都要针对当前行的 department_id 重新执行一次。这种查询在校验结果上没问题但当外层表很大时性能非常差——每一行都要重复扫描内层表。我见过一个慢查询外层表3万行内层表2万行相关子查询跑了将近10秒换成 JOIN GROUP BY 重写之后秒级出结果。我的实践经验是能用JOIN搞定的优先写JOIN非用子查询不可时优先考虑非相关子查询相关子查询要时刻警惕数据量。MySQL 8.0 的优化器比5.7进步不少但也不是万能的不能把性能希望全寄托在优化器上。3.3 EXISTS 与 IN 的选择别再背结论了网上一搜到处是“EXISTS 比 IN 快”的言论。实际上这个结论在MySQL不同的版本、不同的数据分布下并不稳定。IN的核心逻辑是把子查询的结果集收集起来外层逐行判断。当子查询的结果集很小比如只有百来个ID这时候 IN 是高效的当子查询结果集非常大而且外层表也大EXISTS 通常更有优势因为它用到了短路特性子查询一旦找到匹配行就立即返回不做多余扫描。我一般这样选子查询结果集较小且需要去重匹配用 INSQL直观。子查询用于判断“是否存在”而不关心具体值优先用 EXISTS。数据量都很大时无论哪种我都建议先通过执行计划确认而不是靠经验拍脑袋。举个实际例子查“有已完成订单的用户”SELECT user_id, user_name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.user_id AND o.order_status 已完成 );注意子查询里写的是SELECT 1不是SELECT *。这在 EXISTS 语义下完全没问题MySQL也不会真的去取那一列但写1是业界惯例让阅读者一眼知道这里只关心存在性不关心具体数据。4. 聚合、分组与再过滤GROUP BY 和 HAVING 的正确姿势聚合查询是复合查询里的“统计区”。COUNT、SUM、AVG、MAX、MIN 这些函数大家都会用但一旦跟 GROUP BY 混在一起很多人就开始放飞自我了。4.1 先搞懂 GROUP BY 的执行逻辑GROUP BY 的作用是把相同分组键的行合并成一行。执行顺序是这样的先 FROM 确定表再 WHERE 过滤原始行然后 GROUP BY 分组接着分别对每组应用聚合函数最后 HAVING 对分组结果再过滤。这个顺序极其重要因为它决定了WHERE 和 HAVING 不能互换。WHERE 的过滤发生在分组之前所以它不能用聚合函数HAVING 的过滤发生在分组之后所以它里面可以出现COUNT(*) 3这样的条件。如果你在 WHERE 里写COUNT(*) 3MySQL 直接给你报语法错误。还有一点容易混淆GROUP BY 后面能不能跟 SELECT 中没出现的非聚合列。在MySQL 5.7默认以及8.0的默认配置下如果启用了ONLY_FULL_GROUP_BY默认开启那么 SELECT 中的非聚合列必须出现在 GROUP BY 里否则报错。这个设计是为了避免语义歧义——毕竟分组后每组有多行你没分组的列到底取哪一行数据库没法保证。我记得第一次被这个模式坑的时候是写“查每个用户的最高订单金额及其订单ID”。我天真地写了SELECT user_id, order_id, MAX(order_amount) FROM orders GROUP BY user_id;结果报错order_id 不在 GROUP BY 中。当时心里还埋怨MySQL太死板后来才理解这个需求本身用简单GROUP BY就不该支持因为“同一个人最高金额的订单是哪一单”是个很微妙的逻辑。要么你改成两步查询要么用窗口函数。让数据库语义保持严格反而能逼业务逻辑想清楚。4.2 HAVING 是分组后的筛子巧用聚合结果做业务判断很多人做统计的时候自然会把聚合结果丢到WHERE里然后报错再一脸茫然。比如查“订单数大于5的用户”正确的写法是SELECT user_id, COUNT(*) AS order_cnt FROM orders GROUP BY user_id HAVING order_cnt 5;这里 HAVING 后面既可以用聚合函数也可以用别名。MySQL 在 HAVING 阶段是可以识别 SELECT 中定义的别名的但在 WHERE 阶段不行。这是很多新手踩坑的地方在 WHERE 里使用 SELECT 别名会报“Unknown column”。另一个值得说的是 HAVING 和 WHERE 混用时的顺序问题。比如“统计上海地区订单数大于5的用户”SELECT user_id, COUNT(*) AS order_cnt FROM orders WHERE city 上海 GROUP BY user_id HAVING order_cnt 5;WHERE 先一步把城市过滤掉然后分组统计再用 HAVING 过滤组。这个顺序既减少了分组的数据量又保证了统计口径正确。新手容易犯的错误是只在 HAVING 里加city 上海那样分组之后虽然结果一样但中间处理的数据量会大很多性能差不少。4.3 窗口函数让聚合结果和明细行共存MySQL 8.0 引入窗口函数后很多以前需要复杂子查询或临时变量的场景都变简单了。窗口函数和普通 GROUP BY 最大的区别是它不会合并行每一行仍然保留在结果里同时旁边的窗口值带着聚合结果。比如“查每个订单金额占其用户总订单金额的比例”SELECT order_id, user_id, order_amount, order_amount / SUM(order_amount) OVER (PARTITION BY user_id) AS amount_ratio FROM orders;这里的SUM() OVER(PARTITION BY user_id)对每个用户分组求和但并不会把订单行合并每一行都多出一个“用户总金额”列。这种写法在业务报表里极其实用。窗口函数里还有 ROW_NUMBER()、RANK()、DENSE_RANK() 等排名函数。排名函数最经典的场景就是“分组取TopN”。比如“查每个部门薪资前三的员工”用窗口函数写大概是SELECT emp_name, department_id, salary FROM ( SELECT emp_name, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rk FROM employee ) t WHERE t.rk 3;相比传统写法要自连接 计数子查询窗口函数简单清晰性能通常也更好。不过这里有个细节RANK() 遇到相同薪资会并列排名导致名额超过3个如果你只要严格3个人应该用 ROW_NUMBER()。根据业务口径来选择这个看似不起眼的选择经常影响报表的正确性。5. 复合查询的性能调优索引设计与执行计划写完一个结果正确的复合查询只是第一步真正让人头疼的是它在生产环境变慢。我处理过的线上慢查询十有八九跟索引设计不合理或者表连接顺序有问题有关。5.1 复合查询的索引设计原则让索引覆盖连接和过滤单表查询的索引设计其实很简单但一旦涉及复合查询索引设计要联合考虑JOIN条件和WHERE条件。核心思路是走“过滤优先、连接其次、排序最后”的顺序设计复合索引先把 WHERE 里等值条件的列放在复合索引最前面再把 JOIN ON 里的连接列加进去如果有 ORDER BY 或 GROUP BY 的列尝试把它们也纳入复合索引实现索引排序避免 filesort。例如订单表经常按user_id status order_time过滤和排序那就建一个ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, order_time);最左前缀原则是复合索引的灵魂。(user_id, status, order_time)这个索引可以支持只有 user_id 的条件也可以支持 user_id status还可以支持三列全上但不能只查 order_time 时走索引。设计索引时如果不知道查询的具体形态至少要把等值的列放前面。还有一个常见的判断指标是区分度。选择索引列时优先选择区分度高的列比如订单号、用户ID不要选性别、状态这种值域很小的列。一个区分度只有“是/否”的列放在索引最前面会导致索引扫描大量重复行性能还不如全表扫描。5.2 会用 EXPLAIN 看执行计划比会写SQL更重要我见过不少开发写SQL一套一套的但一执行EXPLAIN就直接懵。EXPLAIN 是MySQL优化器给你的“体检报告”读懂了它你就知道一条SQL到底慢在哪。几个最关键的字段type访问类型从好到差大致是 system const eq_ref ref range index ALL。看到 ALL 就要警惕这是全表扫描大表上通常是灾难。key实际使用的索引名。如果是 NULL说明没走任何索引。rows优化器估算需要扫描的行数。多表连接时这个值会被用来决定连接顺序。Extra这里信息量极大看到Using filesort说明排序没走索引看到Using temporary说明用了临时表常见于 GROUP BY 或 DISTINCT看到Using where说明有过滤条件看到Using index说明是覆盖索引扫描是最理想的状态之一。我经常做的优化动作是先看 type 有没有 ALL再看 Extra 有没有 filesort 和 temporary。这两个问题修掉80%的慢SQL都能好一大半。举一个真实案例。某个分组统计查询原来执行要2秒多SELECT user_id, COUNT(*) FROM order_items WHERE item_status 1 GROUP BY user_id ORDER BY NULL;EXPLAIN 显示 typeALLrows 有80万行Extra 里有 Using temporary。我加了一条索引ALTER TABLE order_items ADD INDEX idx_status_user (item_status, user_id);执行计划立刻变成 typeref, rows 只为满足条件的3万行并且由于索引里已经有 user_idGROUP BY 不需要再物化临时表查询降到了0.1秒。注意ORDER BY NULL在MySQL 8.0之前能抑制MySQL对GROUP BY结果做排序8.0里 GROUP BY 默认不再隐式排序所以这个写法在新版本里其实可以省略。做性能分析时最好基于实际数据库版本来验证别把老经验当成铁律。5.3 多表连接的驱动表与被驱动表在多表JOIN中MySQL 优化器会选择一个表作为驱动表先取出满足条件的数据再去逐行匹配被驱动表。这个选择直接决定了查询性能。经验上小表驱动大表是基本原则。也就是说数据量小、过滤条件更严格的那个表当驱动表被驱动表的连接列必须有索引。这背后的逻辑是减少“最内层循环”的执行次数——就像两层for循环把小循环放外层总执行次数更少。MySQL 优化器会自动评估连接顺序但它的评估基于统计信息。如果统计信息过时比如频繁大事务更新后优化器可能选错驱动表这时可以用STRAIGHT_JOIN强制指定连接顺序或者干脆重写SQL结构。不过我不推荐一上来就用这些“霸王条款”先把索引建对大多数情况下优化器的选择是够用的。6. 复合查询的典型实战案例与低速排查记录前面讲了一堆理论这一节直接上几个我近期在项目里遇到的实际案例可以说是“现场直播”。这些问题都是真实出现过的代码也是简化后的版本但问题和解决思路非常典型。6.1 案例一LEFT JOIN 数据翻倍引发的统计错误场景是这样的要查询“每个用户的积分总数”积分流水表里每条记录还有关联的礼品兑换记录。某同事写了一个复杂的LEFT JOIN把三张表连在一起SELECT u.user_id, SUM(p.points) AS total_points FROM users u LEFT JOIN points_log p ON u.user_id p.user_id LEFT JOIN gift_exchange g ON u.user_id g.user_id GROUP BY u.user_id;结果发现很多用户的积分比实际多了一倍甚至几倍。原因很简单points_log和gift_exchange是多对多的关系用户有5条积分流水同时也有2次兑换记录连接后的笛卡尔积导致积分流水被重复计算SUM的时候自然虚高。解决方案是先分别聚合再连接SELECT u.user_id, p.total_points, g.exchange_cnt FROM users u LEFT JOIN ( SELECT user_id, SUM(points) AS total_points FROM points_log GROUP BY user_id ) p ON u.user_id p.user_id LEFT JOIN ( SELECT user_id, COUNT(*) AS exchange_cnt FROM gift_exchange GROUP BY user_id ) g ON u.user_id g.user_id;先GROUP BY 把一对多压成一对一再连接数据就不会爆炸。这个案例告诉我们多表JOIN之前一定要想清楚每张表之间的基数关系一对多连一对多结果必然会出问题。6.2 案例二子查询结果集过大导致临时表落盘另一个慢查询是查“近30天每个品类销量排名前十的商品”。原本的SQL看起来没毛病但一跑就秒超时。EXPLAIN之后发现FROM子查询构建的派生表在优化器估算时要扫描整个订单流水表因为那个子查询里边有窗口函数和多个关联条件没法合并到外层只能物化成临时表数据量大了之后还落盘磁盘IO成了瓶颈。当时的优化思路不是改SQL本身而是对流水表做提前裁剪子查询里先加一个order_time NOW() - INTERVAL 30 DAY的过滤把扫描范围从几百万行砍到几十万行临时表的大小骤减查询时间从15秒降到1.2秒。这类问题其实是“数据裁剪不到位”造成的越早缩小处理的数据集合优化效果越明显。另外一个常用做法是给这个报表单独建一张“汇总预先计算表”用定时任务每天凌晨跑一次聚合白天查询直接读汇总结果。这是典型的“以空间换时间”思路业务报表场景下比一切SQL调优都稳。6.3 常见复合查询报错速查与修复思路这里整理一份我在日常开发中反复遇到的报错和处理方法报错信息常见原因解决思路Unknown column xxx in where clause别名在WHERE中使用不合法把别名条件挪到HAVING或重复写原始表达式Mix of GROUP BY and nonaggregated columnsONLY_FULL_GROUP_BY 模式禁止SELECT非聚合列把非聚合列加进GROUP BY或者改用窗口函数Every derived table must have its own alias派生表没起别名在子查询的右括号后加别名Subquery returns more than 1 row子查询结果是多行但外层用了比较改成IN或EXISTS或确保子查询只返回一行Expression #1 of ORDER BY clause is not in SELECT listORDER BY 引用了未在SELECT中出现的列加上该列或用表达式重写排序逻辑Illegal parameter data types关联字段类型不一致比如字符型和数值型比较统一字段类型或者在ON中显式CAST再看一个很多人问过的问题报错说“无法连接数据库”或者“SSL连接错误”。比如参考热词里的“mysql ssl连接错误”这其实跟复合查询本身无关属于连接层面的问题。常见原因是MySQL 8.0 默认开启SSL认证旧客户端驱动或服务端证书配置有问题导致握手失败。处理方法通常是检查客户端连接参数中useSSL设置或者服务端关闭强制SSL。遇到这类错误时别先去怀疑SQL先看连接层配置再逐层往上排查。6.4 复合查询与存储过程的配合我之前有个项目一个报表SQL复杂到几百行业务还要求前端点一次按钮立刻出结果。SQL本身只要写好固定参数就能跑问题是每次请求都要传不同的时间范围而且历史数据要定期清洗。后来我把这个复合查询包进存储过程里参数传起止日期和品类ID内部用临时表分步聚合最后返回结果集。存储过程的好处是把复杂逻辑封装在数据库端应用层只需要调CALL report_proc(2024-01-01, 2024-01-31, 10)逻辑集中也好维护。但存储过程也有劣势调试困难、版本控制不好做、数据库迁移时容易出幺蛾子。我的建议是报表类、纯数据加工逻辑适合存储过程在线交易链路里的复合查询宁可在应用层写清晰的多段查询或ORM也不要把业务规则塞进存储过程里不然出了问题排查成本极高。另外存储过程里的临时表别忘了最后DROP否则会话一多临时表堆积起来也会拖垮数据库。7. 几个踩过之后才明白的经验细节最后再顺手分享几个平时很少有人系统讲、但我自己真金白银踩过的细节。7.1 NULL值的处理是复合查询的隐形杀手复合查询里最隐蔽的问题是 NULL。两个表关联时如果连接列里有NULL往往是匹配不上的“孤儿数据”。统计时COUNT(*)和COUNT(column)的行为完全不同——前者统计行数后者只统计该列非NULL的行数。SUM一列包含NULL时NULL不会参与计算但结果会忽略NULL而不是报错这跟业务预期的差距经常让人找半天。所以在做复合聚合时最好明确COUNT(1)或COUNT(*)统计所有行COUNT(具体列)只统计非NULL值SUM同理。如果业务上想对NULL做特殊处理请使用IFNULL(col, 0)或COALESCE(col, 0)但要想清楚把NULL替换成0参与聚合和忽略NULL这两种口径可能结果完全不同。7.2 在复合查询中合理使用 UNION 的场景UNION可以纵向合并多个结果集自动去重UNION ALL不去重性能更好。它们最常见的应用场景是把“结构相同但条件不同的查询”合并成一个结果集减少应用层多次调用。比如查“当月新用户和老用户分别的订单数”可以用UNION ALL把两个分组结果拼起来SELECT 新用户 AS user_type, COUNT(*) AS order_cnt FROM orders o JOIN users u ON o.user_id u.user_id WHERE u.created_at DATE_FORMAT(CURDATE(), %Y-%m-01) UNION ALL SELECT 老用户, COUNT(*) FROM orders o JOIN users u ON o.user_id u.user_id WHERE u.created_at DATE_FORMAT(CURDATE(), %Y-%m-01);注意UNION每条子查询的列数和数据类型必须一致否则会报错。还有一点如果需要排序ORDER BY必须放在整个UNION的最后不能给单个子查询单独排序除非用括号包成子查询。这个语法限制在MySQL里容易踩编写时注意一下。7.3 不要忽视连接顺序的“书写顺序”不等于执行顺序很多新手以为SQL里写的表顺序就是执行顺序其实MySQL优化器会根据自己的规则重排各表的访问顺序。也就是说即使你把大表写在前面优化器依然可能先访问小表。但书写顺序会影响可读性和团队协作体验。我带的项目里代码评审时会要求JOIN的表顺序遵循“先主表后明细、先小表后大表”的约定让SQL读起来更符合业务直觉。这样EXPLAIN出来即使顺序变了阅读者也不会一头雾水。7.4 复合查询不只是SELECT的专利提到复合查询很多人默认指的是SELECT实际上UPDATE和DELETE也可以涉及多表操作。MySQL支持在多表UPDATE里直接关联其他表比如“把最近30天没有下单的用户标记为流失用户”UPDATE users u LEFT JOIN ( SELECT DISTINCT user_id FROM orders WHERE order_time NOW() - INTERVAL 30 DAY ) recent ON u.user_id recent.user_id SET u.is_lost 1 WHERE recent.user_id IS NULL;这类复杂更新的执行性能同样依赖关联字段的索引。但多表UPDATE比SELECT危险得多一不小心就影响大量行。我的习惯是执行前先改成等价的SELECT查一遍影响范围再开启事务执行UPDATE最后核对影响行数。这个习惯救过我好几次强烈建议你也养成。7.5 当极端数据量出现时复合查询的替代方案当复合查询把几张大表JOIN起来即使索引优化到位也可能因为数据量太大而撑不住。比如某个统计查询要关联5张过亿的表无论怎么调都不可能毫秒级返回。这个时候正确的答案不是继续调SQL而是换一个技术方案。常见的替代方案包括提前在写入时计算汇总结果汇总表、计数器表。把查询迁移到OLAP系统或者数据分析平台比如用TDengine这类时序数据库存储统计数据。用缓存把热查询结果缓存起来设置合理的过期时间。用Elasticsearch做复杂条件搜索和聚合。我见过太多团队把MySQL当成万能数据库硬扛百亿级数据量的分析查询最后等查询超时了才想着迁移。所以写复合查询的时候心里要有一根弦SQL能解决的问题边界在哪里不要等到线上事故才回头看。写在最后的一点个人体会从刚入门时看到多表连接就发怵到后来能熟练地把一个复杂的业务需求拆解成JOIN、子查询、聚合的组合这个过程其实没有捷径就是多写、多EXPLAIN、多较真。我个人实际操作中比较固执的一点是每写一条复合查询前我都会在心里先默念一遍执行顺序——FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT这个顺序帮我避免了大量“看起来对、实际错”的SQL。如果你正在学复合查询我给你一个很朴素但特别有效的建议不要光看教程把自己业务里最头疼的三张表拿出来分别写出“连接查询”、“分组统计”、“子查询过滤”三版SQL然后对着业务数据验证结果差异。这种实战训练一次顶得上你看十篇博客。复合查询这条路你走上的时候可能会被各种JOIN、子查询、聚合弄得很烦躁但一旦趟过去你会发现数据在指尖流动的那种掌控感是真的爽。希望这篇记录里那些踩坑和优化思路能帮你少走几段我走过的弯路。
