★ FEATURED ARTICLE 做数据查询的时候碰上“两张表一张一对多又要 LEFT JOIN 又要 GROUP BY”的组合十有八九会翻车。我见过不少同学写出来的 SQL 在测试环境跑没问题数据量一上来要么结果翻倍到离谱要么 GROUP BY 之后统计数字怎么都对不上最气人的是还得不到报错只能拿计算器一笔一笔对数。这个问题的本质特别简单LEFT JOIN 会把主表的一行放大成多行而 GROUP BY 又想把它们“按主表维度”缩回去。一个放大一个缩小中间的度量字段到底算谁的值很多坑都从这里长出来。这篇文章我不打算讲理论直接拿一个订单和订单明细的场景把每一种错误翻车的原理拆开看再给出几种不同场景下真正实用的写法。不管你是写报表、做数据看板还是单纯在接口里拼数据应该都能找到能直接抄的答案。1. LEFT JOIN 与 GROUP BY 是怎么“打架”的1.1 先还原现场一对多关联时行数发生了什么变化先说一个最基础的事实也是很多人会忽略的LEFT JOIN 的语义是“保留左表所有行右表匹配不上就补 NULL”。听起来简单但“匹配得上”时到底匹配几行取决于右表里有多少行满足连接条件。假设左表是订单表一个订单一条记录右表是明细表一个订单可能有多条商品明细。你用订单 ID 做 LEFT JOIN那么订单表里的这一行会跟着明细表里所有对应明细逐行复制。一个订单有 3 条明细结果集里就有 3 行同样的订单信息。订单有 10 条明细就是 10 行。这个行为本身没有错是 SQL 标准早就规定好的。问题在于很多人 JOIN 完了下意识就写 GROUP BY心里想的是“按订单分组”但 SQL 并不知道你心里的“订单”是哪一个层级。它只看到结果集里每一行都带有订单信息分组时如果把订单 ID、客户名这些字段一股脑放进 GROUP BY那确实是“按订单分组”没错可如果聚合字段设计得不好比如对订单 ID 做 COUNT那 COUNT 到的就是复制后的行数不是真实的订单数。把这个过程想成复印你没有对明细做任何加工先把订单主表内容复印了 N 份N 等于明细行数。后面不管你怎么 GROUP BY复印出来这堆纸上的“订单数量”字段早就已经不是原件的数量了。1.2 GROUP BY 的语义很多坑其实源于理解偏差不少人把 GROUP BY 理解成“去重”这其实有偏差。GROUP BY 的真正语义是按照指定的字段维度把数据划分成若干个组然后对每一组内的行执行聚合计算。它不会“变出”一个原本不存在的行也不会自动帮你把前面 JOIN 造成的重复消除。打个比方。你面前有一堆价格标签每一张标签上面都写了“苹果”和价格。GROUP BY 做的事情是把写着“苹果”的标签全部归拢到一个格子里然后你在格子里计算总价、数量。如果这堆标签里有 3 张一模一样的“苹果”那不是 GROUP BY 的责任去判断这 3 张是不是同一箱苹果它只会老老实实按 3 张来算。这就能解释很多所谓“冲突”你期待 GROUP BY 帮你把 JOIN 膨胀的行数还原回主表原来的样子但 GROUP BY 根本不知道原始行数是几。它只会对你丢给它的最终结果集做分组和聚合。1.3 冲突的根源聚合列来自主表行却因为从表翻倍把前两点放在一起冲突的根源就很清晰了LEFT JOIN 造成主表行数膨胀聚合函数如果作用在主表字段上比如 COUNT(订单表.ID)、SUM(订单表.金额)那么统计时会把这些膨胀后的重复行也一并算进去分组维度如果只选主表部分字段又可能出现一部分字段不属于任何“逻辑组”的情况于是各种报错和奇怪结果接踵而来。典型翻车场景就是订单表里存了订单金额明细表里存了商品数量。你 LEFT JOIN 后想按客户分组统计每个客户下单总额、购买商品总件数。看起来很简单但你写SUM(orders.total_amount)的时候这个金额已经在每一条明细行里被复制了一遍。比如一个 300 元的订单带了 2 条明细结果集里就有两行“300 元”。SUM 之后这笔订单贡献了 600 元。你说这叫不叫“冲突”其实 SQL 的执行逻辑从头到尾都是自洽的真正冲突的是你的业务期望和表的数据特征你希望统计主表的指标又不想先处理从表的多行明细。两个目的直接硬凑在一个 GROUP BY 里必然出事。2. 用订单明细表复现一遍错误写法和结果分析2.1 样例表结构与数据准备为了把问题说透我建两张简单的表模拟真实业务里订单和明细的关系。-- 订单主表 CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_name VARCHAR(50), total_amount DECIMAL(10,2), order_date DATE ); -- 订单明细表 CREATE TABLE order_items ( item_id INT PRIMARY KEY, order_id INT, product_name VARCHAR(50), quantity INT, price DECIMAL(10,2) );插入几个有代表性的数据。为了便于心算金额设得简单一点订单表order_idcustomer_nametotal_amountorder_date1客户A3002025-01-012客户A5002025-01-023客户B2002025-01-03明细表item_idorder_idproduct_namequantityprice1011苹果2501021香蕉4502012橙子51002022葡萄303013西瓜1200两个客户A 有 2 个订单B 有 1 个订单。订单 1 有 2 条明细订单 2 有 2 条明细订单 3 有 1 条明细。业务目标统计每个客户的下单次数、下单总金额、购买商品总件数。2.2 常见的三种错误写法很多人的第一反应是直接 LEFT JOIN 再 GROUP BY 客户名SELECT o.customer_name, COUNT(o.order_id) AS order_cnt, SUM(o.total_amount) AS total_amount, SUM(oi.quantity) AS total_qty FROM orders o LEFT JOIN order_items oi ON o.order_id oi.order_id GROUP BY o.customer_name;这个 SQL 在很多数据库里都能跑通不会报错但实际上返回的前两列是错的。计算结果客户Aorder_cnt 4真实是 2total_amount 1600真实是 800客户Border_cnt 1真实是 1total_amount 200这个碰巧对了为什么客户 A 是 4 和 1600因为订单 1 有 2 条明细订单 2 有 2 条明细JOIN 后客户 A 总共有 4 行COUNT 数了 4 个 order_idSUM 加了 4 次金额。第二种错误写法更隐蔽把非聚合字段直接 SELECT 出来只 GROUP BY 主键SELECT o.order_id, o.customer_name, COUNT(oi.item_id) AS item_cnt FROM orders o LEFT JOIN order_items oi ON o.order_id oi.order_id GROUP BY o.order_id;在 MySQL 的 ONLY_FULL_GROUP_BY 模式下这个直接报错customer_name不在 GROUP BY 子句中。原因是分组后每个组内部有多个 customer_name数据库不知道取哪一个。解决方案要么把 customer_name 加进 GROUP BY要么用 ANY_VALUE但很多人加进 GROUP BY 后又发现统计结果依然不对因为分组粒度从“订单”变成了“客户订单”层级开始混乱。第三种错误写法是想要订单数但没有仔细想清楚 COUNT 到底应该放在 JOIN 前还是 JOIN 后SELECT o.customer_name, COUNT(DISTINCT o.order_id) AS order_cnt FROM orders o LEFT JOIN order_items oi ON o.order_id oi.order_id GROUP BY o.customer_name;这个写法能救回订单数因为 DISTINCT 把重复的 order_id 去掉了。但如果你同时还要算总金额问题又回来了SUM(o.total_amount)还是会重复计算除非你写SUM(DISTINCT ...)——可用金额做 DISTINCT 相当危险因为两笔订单金额相同会被合并成一笔照样错。2.3 逐步推导错误数字是怎么来的以客户 A 为例把 JOIN 后的结果集展开JOIN 后结果集里订单 1 出现了两行订单 2 出现了两行总共 4 行order_idcustomer_nametotal_amountitem_idquantity1客户A30010121客户A30010242客户A50020152客户A5002023然后 GROUP BY customer_name 之后这个组内部有 4 行。COUNT(order_id)统计的是这 4 行里非空 order_id 的数量得 4。SUM(total_amount)把这 4 行的 300、300、500、500 加起来得 1600。至于SUM(oi.quantity)得到 14这个其实是对的订单 1 的 246订单 2 的 538合计 14。这是巧合吗不是因为 quantity 本身就在明细表这一层JOIN 没有让它膨胀——实际上我们对明细表自身的统计只要聚合条件正确结果就是对的。所以你现在应该能看出一个规律**主表字段在 JOIN 后被复制了所以一切针对主表字段的聚合都会受到行数膨胀的影响而明细表字段本来就是多行状态聚合结果反而不受影响。**这就是为什么一个查询里既算“总金额”又算“总件数”时前者错得离谱、后者却一直稳定。很多人在排查时只盯着错的字段找原因完全没想到问题根源是主表字段被行膨胀污染了。3. 正解一先聚合从表再 JOIN 主表3.1 核心思路在明细表内部先 GROUP BY 外键既然问题根源是“主表字段被 JOIN 复制了”那最直接的解法就是不让主表字段被复制。怎么做先单独把从表按外键做聚合把一对多压成“一对一”再去 JOIN 主表。这话说出来很多人觉得是废话但真正动手时会有个心理障碍总觉得“先 GROUP BY 子查询再 JOIN”会比直接 JOIN 慢。实际上对于大多数场景恰恰相反后面我会专门聊性能。这里先说思路。操作层面你需要把原来的逻辑拆成两个片段对明细表执行GROUP BY order_id计算出每个订单的件数、商品种类数等用订单主表去 LEFT JOIN 这个已经“压扁”的聚合结果。因为子查询返回的每个 order_id 只有一行主表行数不再膨胀主表上的 COUNT、SUM 怎么算都对。3.2 实战 SQL子查询预聚合同样算客户下单次数、总金额、商品总件数正确写法如下SELECT o.customer_name, COUNT(o.order_id) AS order_cnt, SUM(o.total_amount) AS total_amount, COALESCE(SUM(t.total_qty), 0) AS total_qty FROM orders o LEFT JOIN ( SELECT order_id, SUM(quantity) AS total_qty, COUNT(*) AS item_cnt FROM order_items GROUP BY order_id ) t ON o.order_id t.order_id GROUP BY o.customer_name;执行结果客户Aorder_cnt 2total_amount 800total_qty 14客户Border_cnt 1total_amount 200total_qty 1这个结果才是业务上真正想要的数据。这段 SQL 里有两个细节值得展开。第一个子查询里COUNT(*)统计的是每个订单的明细行数最终外层并没有直接用这个字段但子查询中它有用。第二个外层用了COALESCE(SUM(t.total_qty), 0)因为订单 3 的没有明细时LEFT JOIN 后 t.total_qty 是 NULLSUM 会把 NULL 忽略掉导致结果变成 NULL。COALESCE 把 NULL 转成 0这样没有明细的订单也能正确显示“0 件”。3.3 为什么这个方案最稳含执行计划角度先聚合再 JOIN 的好处不只是“结果对了”还在于它把两层语义彻底分开了明细表的聚合在它自己那一层完成不受主表行数影响主表的聚合在主表行数上完成不受明细表干扰两个层次之间只有外键这个唯一关联不会有任何中间状态。从执行计划来说MySQL、PostgreSQL 这类数据库处理这个 SQL 时通常会先扫描 order_items 表做GROUP BY order_id的哈希聚合得到一张较小的临时结果然后再跟 orders 表做 LEFT JOIN。由于子查询结果已经是“按订单分组唯一”的JOIN 过程不会产生中间行放大后续的 GROUP BY customer_name 也只是在主表行数上进行。直接 LEFT JOIN 再 GROUP BY 的执行计划则不同数据库需要先做一轮完整的 LEFT JOIN把 order_items 的每一行都拼到 orders 上结果集行数等于 orders 行数乘以平均明细数然后再对这个放大后的结果做分组。如果订单平均有 20 条明细中间结果行数就是订单数的 20 倍。数据量小时差别不大数据量大时这 20 倍的中间结果会占用更多内存排序和聚合的耗时也会肉眼可见地上涨。3.4 注意点COUNT 与 SUM 的语义在子查询方案里有一个容易踩的坑就是 COUNT 到底写在哪一层。如果统计“明细行数”在子查询里用COUNT(*)如果统计“有明细的订单数”在外层用COUNT(o.order_id)如果统计“有商品的订单数”且明细表可能为空则COUNT(t.order_id)会更准确因为 LEFT JOIN 后没有明细的订单 t.order_id 是 NULLCOUNT 不会把 NULL 计进去。很多人习惯不管三七二十一外层统统COUNT(*)。这在 LEFT JOIN 场景下是有问题的COUNT(*)会把所有匹配到的行都数进去。即使使用了预聚合子查询由于子查询保证一行COUNT(*)还是安全的但如果直接 JOIN 了多个一对多表COUNT(*)会爆炸。还有一个点是 SUM 的空值问题。SUM(NULL)在大多数数据库中返回 NULL而不是 0。如果你希望没有明细时外层显示 0千万记得包COALESCE。这也是报表里经常出现“空数据单元格”的原因之一。4. 正解二按场景换武器不一定要 JOIN4.1 只判断“有没有明细”EXISTS 代替 LEFT JOIN很多时候你根本不需要明细表里的数据只需要判断这个订单有没有对应的明细。比如查“所有没有商品的订单”或者“所有有商品的客户”。这种情况下用 LEFT JOIN 再 GROUP BY等于绕了一大圈还要小心行膨胀。更干净的写法是用 EXISTS / NOT EXISTSSELECT o.order_id, o.customer_name FROM orders o WHERE EXISTS ( SELECT 1 FROM order_items oi WHERE oi.order_id o.order_id );这种写法的好处是子查询只要找到一条匹配记录就会立即返回不会去枚举所有明细也不会放大主表行数。它本质上是“布尔判断”而不是“集合连接”所以根本不存在 LEFT JOIN 与 GROUP BY 的冲突问题。同理如果你想查“没有明细的订单”把 EXISTS 换成 NOT EXISTS 即可。很多报表里喜欢的“用 LEFT JOIN WHERE 右表字段 IS NULL”其实也可以用 NOT EXISTS 替代后者可读性更好执行计划在索引合理时也不会差。4.2 需要明细行但不想破坏聚合窗口函数取一条还有一类场景你希望结果集里能看到明细表里某一列的信息比如订单的“第一个商品名称”但你又不想让这个字段参与聚合。错误的做法是把商品名称直接塞进 GROUP BY那样订单会被拆成多组统计订单数就会翻倍。正确的做法是用窗口函数给每个订单内的明细编号只取编号为 1 的那条SELECT order_id, customer_name, first_product_name FROM ( SELECT o.order_id, o.customer_name, oi.product_name AS first_product_name, ROW_NUMBER() OVER ( PARTITION BY o.order_id ORDER BY oi.item_id ) AS rn FROM orders o LEFT JOIN order_items oi ON o.order_id oi.order_id ) x WHERE rn 1;这个方案既保留了明细表里的信息又不会因为“每个订单有多行”而破坏主表统计。需要注意窗口函数是在 JOIN 之后才计算的它不能解决主表字段被复制的问题但它能帮你从复制后的多行中挑一条出来展示也算一种补充手段。如果主表字段本身有多个度量值需要聚合依然建议先用 3.2 的预聚合方案再在预聚合结果上做一次窗口函数展示两套组合使用。4.3 LEFT JOIN GROUP BY 但绑定主键什么时候算是“安全”有人可能会问那我写LEFT JOIN ... GROUP BY 主表主键是不是就一定安全答案是只有在“SELECT 的聚合项全部来自明细表且你只按主表主键分组”时才是安全的。比如SELECT o.order_id, COUNT(oi.item_id) AS item_cnt, SUM(oi.quantity) AS total_qty FROM orders o LEFT JOIN order_items oi ON o.order_id oi.order_id GROUP BY o.order_id;这个查询没有对主表字段做聚合所有聚合对象都是明细表字段。JOIN 导致主表行数膨胀但明细表字段本来就是多行状态所以结果正确。这里的 GROUP BY order_id 其实是把订单分组组内聚合明细字段天然合理。一旦你把主表字段比如 total_amount也放进 SELECT 或聚合里就必须警惕了。你可以在 SELECT 中直接列出 o.total_amount但前提是 GROUP BY 里也要把它包含进去SELECT o.order_id, o.total_amount, COUNT(oi.item_id) AS item_cnt FROM orders o LEFT JOIN order_items oi ON o.order_id oi.order_id GROUP BY o.order_id, o.total_amount;因为有 JOIN 导致的行放大GROUP BY order_id, total_amount虽然能把订单维度的信息带进来但这个写法能继续扩展的余地很小。如果主表有 20 个字段你总不能全列进 GROUP BY。所以我的建议是能用预聚合子查询就优先用按主键绑定的写法只适合“SELECT 项特别少”的场景。4.4 用 DISTINCT 能否救场另一种被频繁尝试的办法是DISTINCT。既然直接 JOIN 后主表字段被复制了那先 DISTINCT 出主表字段再去 JOIN 明细是不是就行了SELECT d.customer_name, COUNT(d.order_id) AS order_cnt, SUM(d.total_amount) AS total_amount FROM ( SELECT DISTINCT order_id, customer_name, total_amount FROM orders ) d LEFT JOIN order_items oi ON d.order_id oi.order_id GROUP BY d.customer_name;这个写法能救回 order_cnt 和 total_amount因为你在 JOIN 之前先把 orders 里的主表字段搞成了“只保留唯一组合”。但它有个硬伤SUM(oi.quantity)依然算的是每个订单明细的合计数如果 order_items 里某个订单有多行JOIN 后这个订单还是多行所以你要先对明细预聚合才能拿到总件数。而且 DISTINCT 本身对表的扫描成本不低如果 orders 表字段很多DISTINCT 会产生一个巨大的临时结果。现实中我更倾向于用SELECT * FROM orders直接 JOIN 预聚合明细子查询而不是先 DISTINCT 主表。总而言之DISTINCT 只能解决“主表字段被重复”的困境并不能解决“明细字段需要在 JOIN 前聚合”的问题。它更像是一个特定条件下的补丁而非通用解法。5. 其他常见变形一个订单多个统计维度5.1 同时关联多张子表连环 JOIN 数据膨胀问题真实业务里一个订单不仅有明细表还可能有退款表、发货表、评价表。每个表都可能与订单是一对多的关系。问题复杂度立刻上升。假设订单还关联了一张退款记录表每笔退款对应一个订单的多条记录。你如果直接一口气把明细表和退款表都 JOIN 上来SELECT o.order_id, COUNT(oi.item_id) AS item_cnt, COUNT(r.refund_id) AS refund_cnt FROM orders o LEFT JOIN order_items oi ON o.order_id oi.order_id LEFT JOIN refunds r ON o.order_id r.order_id GROUP BY o.order_id;这时两个子表之间会互相叉乘假设订单 A 有 3 条商品明细、2 条退款记录JOIN 后的结果行数是 3×26 行。COUNT(oi.item_id)会数到 6 而不是 3COUNT(r.refund_id)也会数到 6 而不是 2。正确做法是分别预聚合再 JOINSELECT o.order_id, t1.item_cnt, t2.refund_cnt FROM orders o LEFT JOIN ( SELECT order_id, COUNT(*) AS item_cnt FROM order_items GROUP BY order_id ) t1 ON o.order_id t1.order_id LEFT JOIN ( SELECT order_id, COUNT(*) AS refund_cnt FROM refunds GROUP BY order_id ) t2 ON o.order_id t2.order_id;每个子查询都已经把各自的一对多压成“一对一”两个子查询 JOIN 时不会再产生叉乘。这个原则在 N 张从表场景下同样适用你只需要保证 JOIN 的每一张右侧表都已经是按外键唯一聚合后的结果。5.2 多字段统计COUNT DISTINCT 与 SUM 的配合统计商品种类数时常见写法是COUNT(DISTINCT product_name)。如果你在预聚合子查询里做SELECT order_id, COUNT(DISTINCT product_name) AS product_kind_cnt, SUM(quantity) AS total_qty FROM order_items GROUP BY order_id;注意COUNT(DISTINCT ...)在数据量大时性能一般因为它需要额外去重。不过从语义角度它是准确的因为它是在明细表内部统计不涉及主表膨胀问题。如果统计维度还要上升到客户层比如“每个客户购买了多少种商品”你不能再简单地把COUNT(DISTINCT product_name)从订单层硬搬到客户层。因为同一商品可能出现在不同订单里而订单层已经把商品名汇总过一次直接在外层 SUM 会造成重复计算。这时候要么先拿到“客户-商品”的唯一组合再计数要么在 JOIN 后使用COUNT(DISTINCT product_name)但一定要基于客户级明细集合。这个例子说明层级一多“先聚合再 JOIN”的思路依然有效只是聚合的子查询层级要跟着业务语义走不能生搬硬套。5.3 分页查询下的精准统计先聚合再 LIMIT还有一个高频场景是分页。你查“订单列表”每页 20 条每条订单要显示对应的明细数量、总件数。如果用了直接 LEFT JOIN GROUP BY数据库得先把所有数据 JOIN 完再 GROUP BY再排序最后才能 LIMIT。中间那步行数膨胀如果发生在百万级订单表上分页接口性能会非常难看。正确做法是把分页放在最外层让预聚合子查询先行SELECT o.order_id, o.customer_name, o.total_amount, COALESCE(t.total_qty, 0) AS total_qty FROM orders o LEFT JOIN ( SELECT order_id, SUM(quantity) AS total_qty FROM order_items GROUP BY order_id ) t ON o.order_id t.order_id ORDER BY o.order_date DESC LIMIT 20 OFFSET 0;数据库可以先对 orders 表做 LIMIT 20再回头补关联信息虽然执行计划可能会选择先关联后 LIMIT但比起那种“先膨胀 100 倍再过滤”的写法代价已经是不同量级。实践中我还会把子查询里只需要 20 个订单的关联信息这个条件想办法推给优化器或者直接显式地先取订单主表分页结果再关联。这里给一个小技巧如果用的是 MySQL 8.0 或 PostgreSQL可以考虑把子查询结果做成临时表或者用WITH公共表表达式让优化器决策时更从容WITH order_agg AS ( SELECT order_id, SUM(quantity) AS total_qty, COUNT(*) AS item_cnt FROM order_items GROUP BY order_id ) SELECT o.order_id, o.customer_name, COALESCE(t.total_qty, 0) AS total_qty FROM orders o LEFT JOIN order_agg t ON o.order_id t.order_id ORDER BY o.order_date DESC LIMIT 20 OFFSET 0;CTE 的可读性比子查询更好在复杂报表里可以显著降低同事的阅读成本。6. 排查手册从现象到对策6.1 常见报错与处理遇到ONLY_FULL_GROUP_BY报错时第一反应不是“关掉这个模式”而是反思你的分组粒度是不是定义得自相矛盾。MySQL 在 5.7 默认开启 ONLY_FULL_GROUP_BY一旦你 SELECT 了某个非聚合字段但它既不在 GROUP BY 里也不是聚合函数就会报错。处理方式有三条把字段写进 GROUP BY但注意这可能会改变分组粒度用ANY_VALUE(字段)告诉数据库“这一组里取任意一个值就行”适合那个字段在主表里其实每个订单都一样的情况重新设计查询通过预聚合子查询把原字段变成聚合结果再 SELECT。我个人的习惯是优先做第三种因为ANY_VALUE用多了很容易掩盖真实的分组逻辑后面接手的人会被误导。另外NULL引起的“假错”也常见。比如WHERE total_amount NULL查不到数据是因为 SQL 里对 NULL 的判断要用IS NULL不是。这在加了 LEFT JOIN 之后更隐蔽右表没有匹配时右表字段是 NULL如果你在 WHERE 里写了右表字段 某值这个条件会把 NULL 行过滤掉效果等同于把 LEFT JOIN 变成了 INNER JOIN。排查时看到结果行数明显少于左表行数先检查 WHERE 条件里有没有对右表字段做判断。6.2 实操心得写这类查询的四个习惯踩过几次坑之后我给自己定了四个写 JOIN 聚合查询的默认规则分享出来第一先画一层“表关系草图”。把主表和每一张从表之间的行数关系标出来哪张表是 1哪张表是 N。只要有一对多就默认不要直接在主表字段上做聚合。第二凡是要聚合的字段先明确它属于哪一层。主表字段要聚合成主表数量就必须在主表行数上进行不能在 JOIN 后做从表字段可以在 JOIN 后做但最好也在从表自身先聚合。第三能用子查询先压成一行就不要硬 JOIN。只要右侧表按外键 GROUP BY 后能保证外键唯一就坚决先聚合。第四测试数据不能全部用“每个订单只有一条明细”的模拟数据。建测试用例时故意造一些一对多的订单乱序、空明细、多条明细这样 SQL 的问题会暴露得更早。6.3 速查表场景对解法业务场景推荐写法原因统计主表维度指标订单数、主表金额先用子查询聚合从表再 LEFT JOIN避免主表字段被复制只判断从表是否有记录EXISTS / NOT EXISTS无需 JOIN行数不膨胀需要展示从表中某一行信息但不想影响主表聚合子查询聚合从表 窗口函数取一条明细字段保持一对多语义不破坏主表统计需同时关联多张从表每张从表分别预聚合再 JOIN 主表防止从表之间互相叉乘订单列表分页展示主表分页 预聚合子查询 LEFT JOIN减少中间结果膨胀统计客户购买商品种类数客户层先做唯一组合再计数防止跨单重复计算这张表可以贴在工位边上写 SQL 前看一眼基本能躲掉大部分坑。7. 记一次生产环境踩坑为什么一个小报表跑了 9 秒7.1 问题背景与复盘有一次上线一个“订单汇总报表”页面本身很简单一个日期范围筛选出订单然后展示每位客户的订单数、订单金额、商品总件数、退款金额。听起来不难但生产环境一跑就是 9 秒多页面直接超时。一开始没人想到是 LEFT JOIN GROUP BY 的问题因为单笔订单的明细平均只有 2 条开发环境根本看不出差异。后来抓了慢查询日志发现 SQL 的预估扫描行数接近 1.2 亿。为什么这么夸张因为这个报表把订单表、明细表、退款表、发货表四张表全部直接 JOIN 在一起再按客户 GROUP BY。中间每一行订单都跟明细、退款、发货互相叉乘如果一笔订单有 2 条明细、1 条退款、2 条发货记录JOIN 后就是 4 行再乘以海量订单数中间结果直接爆炸。处理方式就是拆分。先把明细表按订单预聚合退款表按订单预聚合发货表按订单预聚合再把这三个已经压扁的子查询 LEFT JOIN 到订单表上。改完之后同样数据量下查询从 9 秒降到了 0.8 秒结果还比之前准了——因为之前退款金额也出现了被明细翻倍计算的错误只不过报表里数字看着“差不多”一直没人发现。之后我们就把这一类 SQL 写法固化成团队规范新的报表查询必须遵守“先聚合再 JOIN”原则。7.2 优化后的 SQL这就是当时优化后的核心片段WITH order_main AS ( SELECT order_id, customer_name, total_amount, order_date FROM orders WHERE order_date 2025-01-01 AND order_date 2025-02-01 ), item_agg AS ( SELECT order_id, SUM(quantity) AS total_qty, COUNT(*) AS item_cnt FROM order_items GROUP BY order_id ), refund_agg AS ( SELECT order_id, SUM(refund_amount) AS refund_amount, COUNT(*) AS refund_cnt FROM refunds GROUP BY order_id ), ship_agg AS ( SELECT order_id, COUNT(*) AS ship_cnt FROM shipments GROUP BY order_id ) SELECT m.customer_name, COUNT(m.order_id) AS order_cnt, SUM(m.total_amount) AS order_total_amount, COALESCE(SUM(i.total_qty), 0) AS total_qty, COALESCE(SUM(r.refund_amount), 0) AS total_refund_amount, COALESCE(SUM(s.ship_cnt), 0) AS ship_cnt FROM order_main m LEFT JOIN item_agg i ON m.order_id i.order_id LEFT JOIN refund_agg r ON m.order_id r.order_id LEFT JOIN ship_agg s ON m.order_id s.order_id GROUP BY m.customer_name;这个写法的精髓在于四个来源表每一张都先通过 CTE 聚合成外键唯一的行集最后在主表上进行业务维度分组的聚合。没有中间行膨胀没有交叉统计执行计划也简单得多。7.3 经验总结这次踩坑给我最大的教训是写 SQL 不能只追求“结果看起来对”要时刻关注数据处理过程中行数变了多少次。特别是当多个一对多表同时出现在一个查询里中间结果的行数会呈现乘法增长指数级的膨胀很容易把数据库内存和查询时间吃掉。报表字段越多越要警惕这种膨胀。另一个经验是线上环境和开发环境的数据分布差别很大。开发环境可能每个订单只有一条明细而生产环境有的订单几十条明细SQL 里的行膨胀问题在低数据量下完全隐形一到生产就原形毕露。因此新上线报表尽量拿生产数据的小样本做压测别只看功能正确性。我个人现在写这类查询基本都会先问自己一句这个 JOIN 会让哪张表的行数翻倍翻倍之后我要统计的字段受不受影响如果答案不清晰那就宁可多写两行子查询也不要图省事闭眼 LEFT JOIN。数据量小时看不出差距等你看见差距的那一天往往就是线上事故了。
阅读完成 · 觉得有帮助？
