月初运营要一份「各城市客户数、订单数、客单价」的报表。开发写完 SQL 发过去,运营自己用明细表拉了个 Excel 一对,数量差了 15%。开发说「SQL 就那几行,不可能错」,运营说「你的 SQL 和明细就是不一样」。最后查出来三个问题叠在一起:JOIN 明细表之后 COUNT(*) 数的是明细行;有的城市客户没有订单,LEFT JOIN 又被 WHERE 过滤掉了;还有一列用了 COUNT(remark),把备注为空的订单漏掉了。
聚合查询的坑大多不是语法错,而是「统计口径」和「数据实际形态」对不上。这篇按报表需求的顺序往下走:先数对,再分组,再处理一对多,最后看子查询和窗口函数。
场景走查:一张报表要过四道关
需求拆开是四步:按城市分组;每组数客户;每组数订单;每组算订单总金额和平均值。每一步都可能出错,而且错法各不相同。写成一条 SQL 之前,先确认三件事:分组键是什么、统计的粒度是什么、没有数据的组要不要显示为 0。
COUNT 的三种写法,结果真的不同
COUNT(*) 数的是「结果集里有多少行」,包括所有列都是 NULL 的行。COUNT(列名) 数的是「这一列有多少个非 NULL 值」。COUNT(DISTINCT 列名) 数的是去重后的非 NULL 值。三者不是同义词。
在演示数据上直接验证:订单表 1998 行,其中有备注的只有 118 行。
SELECT COUNT(*) AS all_rows, COUNT(remark) AS with_remark
FROM orders;
-- all_rows = 1998
-- with_remark = 118
如果报表写的是 COUNT(remark) 来统计订单数,就会少报 94%。这不是极端案例:任何允许为 NULL 的列,拿来做计数都会漏。统计行数就用 COUNT(*),语义最清楚,优化器也知道它只要数行,不需要判断某个列。
COUNT(DISTINCT customer_id) 常被用来数「有多少个不同客户下过单」。它要额外做去重,成本比 COUNT(*) 高,但在「一对多 JOIN 后数主表实体」时是必要的兜底。更好的办法还是先修正统计粒度,别让 JOIN 把行数放大。
COUNT(*) 在 InnoDB 里也不是免费的。它要扫索引或全表来数行,数据量大时可能比列表查询还慢。如果业务只需要一个大概数字,可以看 EXPLAIN 的 rows 估算,或维护一张计数表;但计数表要额外维护,更新时也有竞争。精确和性能要按场景选,不要默认「数一下就很快」。
EXPLAIN 的 rows 估算,或者维护计数表,别每次都跑精确 COUNT(*)。精确计数在大表上本身就是一次全表或全索引扫描。顺带把「空字符串」和「NULL」也数清楚:实测 SELECT COUNT(*) FROM orders WHERE remark IS NULL 得到 1880,WHERE remark = '' 得到 0。也就是说这张表里「没备注」全部是 NULL,不是空串。如果先按「空串 = 没备注」写报表,会得出「所有订单都有备注」的错误结论。统计前先确认列里到底存的是 NULL 还是空串,比背 COUNT 的语法重要。
除 COUNT 之外,MIN、MAX 也有个容易被忽略的点:如果列上有索引,优化器可以直接读索引一端拿到结果,计划里是 Select tables optimized away,一行都不用扫;列上没有索引就只能全表扫。所以「查最早一笔订单时间」这类查询,给时间列建索引能同时服务排序和 MIN/MAX。SUM 和 AVG 没有这种捷径,必须遍历所有参与的行,数据量大时成本是实打实的。
GROUP BY 与 HAVING:先筛行,再筛组
GROUP BY city 把城市相同的行归到一组,每组算一个聚合值。HAVING 是在分组之后过滤「组」,WHERE 是在分组之前过滤「行」。这个顺序决定了它们能写什么:
-- 只统计已支付订单里,订单数超过 300 的城市
SELECT c.city, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.city
HAVING COUNT(o.id) > 300
ORDER BY order_count DESC;
把 o.status = 'paid' 放 HAVING 里语法也能过,但语义不对:它会在分组后才过滤,而且不是聚合条件,数据库可能报错或给出意料之外的结果。记住:行级条件放 WHERE,组级条件放 HAVING。
HAVING 里可以直接引用 SELECT 中定义的别名,比如 HAVING order_count > 300,MySQL 和 PostgreSQL 都支持;但 WHERE 里不行。另一个要点是 HAVING 用不上索引,它作用在已经分好的组上,所以能前移到 WHERE 的条件尽量前移,先减少参与分组的行数。
MySQL 默认开启 ONLY_FULL_GROUP_BY,这是好事。下面这条想按城市分组,却把客户姓名也选出来:
SELECT city, name FROM customers GROUP BY city;
实测直接报错:
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'docs_sql_demo.customers.name' ...
一个城市有 17 个客户,name 到底该取哪一个?数据库拒绝猜。正确做法是要么把 name 也放进 GROUP BY,要么用聚合函数明确表达(比如 MIN(name)),要么改成窗口函数。
ONLY_FULL_GROUP_BY。它拦下的是语义模糊的查询,关掉之后错误结果会静默返回,比报错难查得多。GROUP BY 可以按多列分组,比如 GROUP BY city, status,这时每一组是「某城市的某状态」。如果还想在结果里加一行总计,MySQL 支持 WITH ROLLUP,它会在末尾追加汇总行,但汇总行的分组列是 NULL,读的时候要判断。报表需求复杂时,窗口函数或单独跑汇总查询往往比 ROLLUP 更好控制。
一对多聚合:先聚合,再 JOIN
上一节已经看到,JOIN 明细表会把订单行放大。聚合时同样如此:一张订单三条明细,SUM(o.amount) 就会加三次。修法不是换聚合函数,而是调整顺序——先在明细表按订单聚合,再和订单表关联:
SELECT o.id, o.amount, i.item_total
FROM orders o
JOIN (
SELECT order_id, SUM(qty * price) AS item_total
FROM order_items
GROUP BY order_id
) i ON i.order_id = o.id
WHERE o.id = 1;
如果只是统计「订单总金额」,根本不需要 JOIN 明细表:SELECT SUM(amount) FROM orders 就够了。很多人是因为页面上要同时显示「订单金额」和「商品件数」才把两张表硬拼在一起,结果两个指标互相污染。正确做法是分别聚合,再按订单 id 拼装。
聚合对 NULL 的处理也要注意:SUM 遇到全 NULL 的组返回 NULL,不是 0;AVG 会忽略 NULL 行,分母只算非 NULL 的个数;COUNT 返回 0。报表要显示 0 时用 COALESCE(SUM(x), 0);但如果业务上「没有数据」和「合计为 0」需要区分,就别急着兜底,先把语义定清楚。
报表里往往不止一个指标。比如同时要「订单数、商品件数、总金额」,正确写法是把不同粒度的指标分别聚合,再按订单 id 关联:
SELECT o.id, o.amount,
COALESCE(i.qty_total, 0) AS qty_total,
COALESCE(i.line_count, 0) AS line_count
FROM orders o
LEFT JOIN (
SELECT order_id, SUM(qty) AS qty_total, COUNT(*) AS line_count
FROM order_items
GROUP BY order_id
) i ON i.order_id = o.id
WHERE o.customer_id = 7;
这里用 LEFT JOIN 而不是 INNER JOIN,因为有些订单没有明细,报表要显示 0 而不是整行消失;COALESCE 把 NULL 转成 0。如果反过来把明细表直接 JOIN 订单表再 SUM(o.amount),金额会被明细行数放大几倍,订单数也会变成明细行数。判断标准很简单:一个聚合值对应一个粒度,粒度不同的指标不要放在同一次聚合里算。
翻车现场:客单价被 JOIN 放大了 1.5 倍
还是月初那张报表:「各城市客单价」。开发写的是先 JOIN 明细,再按城市聚合:
SELECT c.city, SUM(o.amount) / COUNT(DISTINCT o.id) AS aov
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
GROUP BY c.city;
看起来分子分母都做了处理,实际上 SUM(o.amount) 已经被明细行放大了。实测同一批数据:先关联明细再 SUM(o.amount) 得到 205427.58,只按订单聚合得到 136951.72,前者正好是后者的 1.5 倍。订单明细平均每单 1.5 行,放大倍数就等于这个平均行数。分母用了 COUNT(DISTINCT o.id) 去重,分子却没有,两个指标的口径就不一致了。
为什么 COUNT(DISTINCT) 能兜住、SUM 兜不住?因为去重是显式指定的,而 SUM 只是把「当前结果集里的每一行」加起来。数据库不知道你想加的是订单金额而不是明细金额,它只按行算。修法只有一条:让聚合发生在正确的粒度上。
SELECT c.city, SUM(o.amount) / COUNT(o.id) AS aov
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.city;
这样分子分母都在订单粒度上,客单价才对。如果报表还必须显示商品件数,就单独聚合明细再关联,别把两个粒度的指标塞进同一条 JOIN。
判断有没有被放大,有个很快的自检:先单独算一遍每个指标的基准值,再和 JOIN 后的结果对比。订单数、客户数、金额这三类指标最适合这样核对。数字差 1.5 倍、2 倍这种「整齐的倍数」,基本都是行数被放大,而不是数据本身有问题。
子查询:IN、EXISTS 和关联子查询
子查询有三种常见形态。标量子查询返回一个值,比如 (SELECT MAX(amount) FROM orders);IN 子查询返回一个集合;EXISTS 子查询只关心「有没有匹配行」。
「找出北京客户的订单」可以写成 IN:
SELECT id FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE city = '北京');
实测执行计划里,MySQL 把 IN 改写成了半连接:先扫 customers(101 行,type=ALL),再按 customer_id 去 orders 的索引找(type=ref,每客户约 20 行)。这个计划是合理的。写成 EXISTS:
SELECT id FROM orders o
WHERE EXISTS (
SELECT 1 FROM customers c
WHERE c.id = o.customer_id AND c.city = '北京'
);
实测计划反而变差:orders 全表扫描 1998 行,customers 是 DEPENDENT SUBQUERY,每扫一行订单就回查一次客户。虽然客户表只有 101 行、走主键 eq_ref,往返次数仍然多。这里的关键不是「EXISTS 一定比 IN 好」,而是优化器对两种写法的改写能力不同,具体要看 EXPLAIN。
反过来,「找出下过单的客户」用 EXISTS 往往更自然,因为它只判断存在性,不需要把订单 id 全部收集起来:
SELECT c.id, c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
关联子查询(子查询里引用外层的列)如果写不好,会变成「外层每行执行一次子查询」,成本随外层行数线性增长。能改写成 JOIN 或 EXISTS 的,优先改写,再用 EXPLAIN 验证。
派生表(FROM 里的子查询)在 MySQL 8.0 里不一定会物化。简单查询可能被合并进外层,复杂聚合、带 LIMIT 或多次引用的子查询才会真正生成临时结果。这解释了为什么「同样的子查询,加一个 GROUP BY 就变慢」——执行方式变了。看 EXPLAIN 里有没有 <derived2>,能判断子查询是否被单独执行。
还有一类「反连接」需求:找出没有下过单的客户。写成 NOT IN 要小心,如果子查询结果里有 NULL,整个条件会恒为 UNKNOWN,一行都查不出来——这是三值逻辑的坑,不是数据错了。更稳的写法是 NOT EXISTS,或者 LEFT JOIN ... WHERE o.id IS NULL。三种写法在 EXPLAIN 里的计划不同,写完对比一次 rows,别凭印象选。
窗口函数:分组排名但不减少行数
GROUP BY 会把每组压成一行,窗口函数不会。它按分区计算排名、累计值,同时保留原始每一行。MySQL 8.0 支持窗口函数,取「每个客户最近一笔订单」就很方便:
SELECT id, customer_id, amount, created_at
FROM (
SELECT id, customer_id, amount, created_at,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY created_at DESC
) AS rn
FROM orders
) t
WHERE rn = 1;
实测对 customer_id = 7 取前五行,rn 依次是 1 到 5,按时间倒序。窗口函数不是银弹:它通常要在分区内排序,数据量大时有额外开销;ROW_NUMBER、RANK、DENSE_RANK 在并列名次上的行为不同,取「最近一笔」用 ROW_NUMBER 更稳,因为它保证每行编号唯一。
窗口函数还有 RANK、DENSE_RANK 和 LAG/LEAD。RANK 遇到并列会跳号(1、1、3),DENSE_RANK 不跳号(1、1、2);取「每组最新一条」用 ROW_NUMBER,做排行榜用 RANK。它们都依赖窗口内的排序,数据量大时成本不低,别在千万行表上随手加一个 OVER。
窗口函数还有个容易忽略的坑:ROW_NUMBER 面对并列值时,谁拿 rn=1 并不保证稳定。两笔订单时间完全相同,两次查询可能各取一条。要稳定,就在 ORDER BY 里补唯一列,比如 ORDER BY created_at DESC, id DESC。这和分页要加唯一排序键是同一个道理。
现象对照
| 现象 | 真实原因 | 第一步查什么 |
|---|---|---|
| 报表订单数比明细少 | 用了 COUNT(可空列),NULL 被跳过 | 换成 COUNT(*),对比两个数字 |
| 订单数比实际多几倍 | JOIN 明细表后 COUNT(*) 数的是明细行 | 看 COUNT(*) 与 COUNT(DISTINCT order_id) 的差异 |
| 报错 1055,说列不在 GROUP BY 中 | ONLY_FULL_GROUP_BY 拦下了语义模糊的查询 | 决定这一列取哪个值:放进分组键或用聚合函数 |
| 没有订单的城市在报表里消失 | LEFT JOIN 后把条件写进了 WHERE | 把条件挪到 ON,或用 COALESCE 补 0 |
| 查询随数据量变慢,CPU 升高 | 关联子查询让外层每行都执行一次 | EXPLAIN 看有没有 DEPENDENT SUBQUERY |
分组查询出现 Using temporary | 分组列没有索引,需要建临时表 | 看 EXPLAIN 的 Extra,考虑给分组列建索引 |
| 客单价或总额比明细高整倍 | JOIN 明细后 SUM 按明细行重复累加 | 先单表算一遍基准值,对比倍数 |
COUNT(某列) 明显小于总行数 | 该列有大量 NULL,COUNT(列) 只数非空值 | 用 COUNT(*),或分别统计 IS NULL 与空串 |
边界与代价
聚合本身要遍历数据。没有索引时,GROUP BY 往往要建临时表,数据量大到内存放不下就落磁盘;ORDER BY 再叠加排序,成本继续上涨。给分组列建索引能省掉临时表,但索引也会增加写入成本,是否值得要看查询频率。
临时表是不是落盘,计划里看不出来。Using temporary 只说明用了临时表,是否转磁盘取决于 tmp_table_size 与 max_heap_table_size 的较小值。看到分组查询突然变慢,先查 Created_tmp_disk_tables 有没有增长,再决定是加索引、缩小结果集还是调参数。调大内存参数要谨慎,每个连接都可能占一份。
COUNT(DISTINCT ...) 在 MySQL 里通常是精确去重,代价不低;数据量特别大时,业务上能接受近似值的话,可以考虑基数估算或维护计数表,但要明确告诉使用方「这是估算」。
聚合的空值语义也容易误会:SUM 对全 NULL 的组返回 NULL 而不是 0,AVG 忽略 NULL 行(分母是非 NULL 的个数),COUNT 返回 0。报表要显示 0 时,用 COALESCE(SUM(x), 0) 兜底。
AVG 的分母尤其值得注意:它数的是非 NULL 行,不是总行数。同样一批数据,AVG(remark) 和 SUM(remark)/COUNT(*) 会得到不同结果,因为前者把 NULL 行直接排除了。要「平均值」先定清楚分母是谁,再选写法;两种口径都可以用,就是别混着用,更别在不同报表里各用一种。
PostgreSQL 在 GROUP BY 上更宽松一些,支持 GROUP BY DISTINCT、GROUPING SETS、ROLLUP 等高级分组;MySQL 8.0 也支持 WITH ROLLUP,但语法细节不同。跨库时别假设写法一致。
分组的排序行为也有版本差异:MySQL 8.0 去掉了 GROUP BY 的隐式排序,想要有序结果必须显式写 ORDER BY;老版本默认会按分组键排,升级后如果代码依赖这个顺序,结果就会变。要稳定,就自己把排序写清楚,别依赖数据库的实现细节。
报表类 SQL 还应该养成「逐指标验收」的习惯:每加一个聚合列,就单独跑一条只算这个指标的 SQL 做基准,再和报表结果比对。金额、订单数、客户数这三个指标最容易出错,也最容易验。别等运营发现数字不对才回头查,那时你已经不知道是哪一步放大的了。
如果你在 8.0.18 及以上版本复跑本篇的 JOIN 聚合,可能会看到 Extra 里出现 Using join buffer (hash join):8.0.18 引入了 Hash Join,等值连接在无法走索引时改用哈希表配对,执行方式与 8.0.12 的嵌套循环不同,耗时对比也会变。具体输出和性能差异本文未在本机验证,标记为待确认;结论不变——先保证粒度正确,再谈快慢。
小结
先定统计粒度,再选聚合函数:数行用 COUNT(*),数非空值用 COUNT(列),数实体用 COUNT(DISTINCT 主键)。行级过滤放 WHERE,组级过滤放 HAVING。一对多先聚合再关联。子查询写完一定看 EXPLAIN,别让 DEPENDENT SUBQUERY 悄悄拖慢整条 SQL。
当天可以自己验证的练习:
- 用
COUNT(*)和COUNT(remark)统计同一张订单表,解释差值。 - 写一条违反
ONLY_FULL_GROUP_BY的 SQL,记录完整报错,再改成合法写法。 - 用 JOIN 明细表统计订单总额,再单表统计,算出放大倍数。
- 把
IN子查询改写成EXISTS,用EXPLAIN对比两者的rows和select_type。 - 用
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC)取每个客户最近一单。
下一篇讲建表与约束:主键、外键、唯一与默认值。查询写得再对,也挡不住脏数据进库:重复手机号、负库存、空状态,很多问题应该在建表时就拦掉。接下来看哪些规则该交给数据库,哪些交给应用层,以及改表为什么要挑时间。