1. 一个订单统计的小场景带你重新认识聚合函数先讲一个我实际碰到过的例子。几年前在一家电商公司做后台报表运营同事发来一条SQL说是统计每个月订单量和销售额的SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders GROUP BY DATE_FORMAT(create_time, %Y-%m) ORDER BY month DESC;这条SQL看似没问题跑出来结果也像模像样。但过了两周运营反馈说2月份的数据对不上少了一部分。排查了一圈才发现——订单表里有几条amount字段为NULL的记录SUM(amount)直接把 NULL 跳过了导致统计口径出错。这就是聚合函数最典型的看着会、实际上坑很多的地方。很多人学 MySQL 的时候觉得聚合函数就是 COUNT、SUM、AVG、MAX、MIN 这几个单词的事背下来就会用。可真到了实际写报表、做统计、处理业务数据的时候NULL 值怎么处理、GROUP BY 和 HAVING 的执行顺序、去重统计怎么写、大表的聚合查询为什么会慢——这些问题才是真正影响你能不能把聚合函数用对的点。这篇文章我想把这些东西完整梳理一遍。不管你是刚接触数据库的新人还是已经写过几年 SQL 但没系统研究过聚合函数的开发者这篇文章都能帮你补齐那些看文档没人告诉你的细节。内容会覆盖六个常用聚合函数的边界行为、GROUP BY 的分组逻辑与常见误区、WHERE 和 HAVING 的正确分工、真实报表场景的组合用法、聚合查询的性能优化以及面试里容易考到的几个点。全程用我自己的经验和踩坑记录来讲尽量把每个为什么都说清楚。2. COUNT、SUM、AVG、MAX、MIN、GROUP_CONCAT每个函数都要知道的细节2.1 COUNT统计行数的两个姿势差别在 NULL先说 COUNT这是最基础也最容易用错的函数。它在 MySQL 里有两种写法COUNT(*)和COUNT(列名)这俩是有本质区别的。COUNT(*)统计的是结果集的行数不管这一行里有没有 NULL都会算进去。COUNT(列名)统计的是该列非 NULL 值的个数遇到 NULL 直接跳过。举个例子-- 表数据id 1/2/3其中 id2 的 remark 字段是 NULL SELECT COUNT(*) FROM t; -- 返回 3 SELECT COUNT(remark) FROM t; -- 返回 2那COUNT(1)呢很多人会问COUNT(*)和COUNT(1)哪个快。在 MySQL 里其实没区别优化器会把COUNT(1)转成COUNT(*)来处理性能是一样的。面试的时候如果有人跟你纠结这个你直接说MySQL 8.0 里两者等价优化器会统一处理就行。另外还有一个高频场景就是去重统计。比如统计一个用户表里出现了多少个不同的城市就得这样写SELECT COUNT(DISTINCT city) FROM users;这里要注意COUNT(DISTINCT 列名)同样会忽略 NULL。如果整列都是 NULL返回的是 0 而不是 NULL。如果你要统计至少出现过多少个有效值这个行为是符合直觉的但如果你想统计这个字段有多少种取值包括空值那就得额外处理一下比如COUNT(DISTINCT IFNULL(city, 空))。2.2 SUMNULL 不是 0这是最容易被坑的地方SUM(列名)用于对数值列求和。它不会把 NULL 当成 0 直接参与计算而是直接跳过 NULL 行。但如果整个列的所有行都是 NULLSUM的结果就是 NULL不是 0。我见过太多人在写统计代码的时候直接拿SUM(amount)的结果去 Java/PHP 里做运算结果报空指针就是因为没处理 NULL 的情况。正确做法是SELECT SUM(IFNULL(amount, 0)) AS total FROM orders;或者是用COALESCESELECT COALESCE(SUM(amount), 0) AS total FROM orders;这两者的区别在于IFNULL(amount, 0)是在求和之前先把每条记录的 NULL 转成 0COALESCE(SUM(amount), 0)是先求和如果整体结果是 NULL 再转成 0。实际使用中我更倾向于后者因为性能更好——不需要逐行做函数转换。还有一个高阶技巧用SUM(CASE WHEN ...)实现条件计数。比如统计一个订单表里支付成功和未支付的订单数可以一条 SQL 搞定SELECT SUM(CASE WHEN status paid THEN 1 ELSE 0 END) AS paid_cnt, SUM(CASE WHEN status unpaid THEN 1 ELSE 0 END) AS unpaid_cnt FROM orders;这种方式比COUNT(*) ... WHERE status paid然后再查一次要高效得多特别是只需要一行结果的场景。2.3 AVG平均值不是你想的那个平均值AVG(列名)的坑和 SUM 很像它也是忽略 NULL 的。这就意味着如果你想算所有人包括没有填写年龄的人的平均年龄直接写AVG(age)会把 NULL 的记录排除掉导致结果偏高。-- 年龄数据20、30、NULL SELECT AVG(age) FROM t; -- 返回 25因为 NULL 被跳过 SELECT AVG(IFNULL(age, 0)) FROM t; -- 返回 16.67NULL 按 0 计入哪种对取决于业务口径。如果 NULL 表示未知那排除掉是合理的如果 NULL 表示没有比如 0那就要用IFNULL或COALESCE先填充。另一个实际业务中经常遇到的需求是算加权平均。比如商品的单价不同、销量不同要算平均售价就不能直接用AVG(price)得这样SELECT SUM(price * sales_count) / SUM(sales_count) FROM products;这就是加权平均和算术平均的区别也是面试的时候经常会被问到的变体题。2.4 MAX 和 MIN简单直接但有几个应用技巧MAX和MIN相对简单分别取最大值和最小值也忽略 NULL。不过实际应用中它们有几个比较巧的用法场景一取每个用户最近一次下单时间。SELECT user_id, MAX(order_time) FROM orders GROUP BY user_id;场景二判断一个区间是否有重叠。比如查某一天是否有订单不需要COUNT(*) 0直接SELECT MAX(id) FROM orders WHERE create_time 2024-01-01如果返回 NULL 就说明没有。这在数据量大的时候效率高很多。场景三MAX/MIN 不只是数字和日期能用。字符串也能用取的是字典序最大/最小的值。这个在分组后取每组某个字段的首条记录时偶尔会用到。2.5 GROUP_CONCAT把多行拼成一行但别忽略长度限制GROUP_CONCAT是 MySQL 特有的聚合函数作用是把分组内的多个值拼成一个字符串。比如某个订单下有多个商品想列出来SELECT order_id, GROUP_CONCAT(product_name SEPARATOR 、) FROM order_items GROUP BY order_id;这个函数有两个地方非常容易出问题第一个是默认长度限制。MySQL 里group_concat_max_len默认值是 1024 字节也就是说拼接出来的字符串超过 1024 字节会被截断。我遇到过线上报表数据被截断的情况查了半天才发现是这个参数的问题。可以通过以下方式调大SET SESSION group_concat_max_len 102400; -- 当前会话临时生效 SET GLOBAL group_concat_max_len 102400; -- 全局生效重启失效第二个是拼接顺序。GROUP_CONCAT 默认不保证顺序如果你想让结果按某个字段排序要这样写SELECT order_id, GROUP_CONCAT(product_name ORDER BY price DESC SEPARATOR 、) FROM order_items GROUP BY order_id;这个函数在处理一对多关系的展示场景时特别好用但它只适合小规模拼接如果你的分组内数据量特别大建议还是走应用层处理。3. GROUP BY 的分组逻辑与最容易踩的三个坑3.1 分组背后的执行逻辑先分组再聚合GROUP BY和聚合函数是绑定在一起用的。它的核心逻辑可以理解为三步先把数据按照指定列的值分成若干组然后对每一组分别执行聚合函数最后返回每组一行结果。这个逻辑听起来简单但理解不到位就容易出问题。比如下面这条 SQL 在绝大多数 MySQL 版本下会直接报错SELECT user_id, order_status, COUNT(*) FROM orders GROUP BY user_id;报错的原因是在ONLY_FULL_GROUP_BY模式下你 SELECT 出来的列必须是分组列本身或者是被聚合函数包裹的列。这里的order_status既不是分组列也没有被聚合MySQL 不知道要返回哪个值所以干脆报错。这个模式的背后逻辑是分组后每组可能有多行如果你 SELECT 一个非分组列那这个值是不确定的。MySQL 5.7 之后默认开启了ONLY_FULL_GROUP_BY不允许这种模棱两可的写法。如果你在老的 MySQL 版本里跑出过看似正确的结果那纯粹是因为它取了某一行内容是不可预测的。3.2 坑一SELECT 了没有被聚合的列承接上面的问题如果你确实需要把订单状态也带出来又不能确定每组内只有一个状态那说明你的聚合粒度不对。正确做法是把order_status也加入 GROUP BYSELECT user_id, order_status, COUNT(*) FROM orders GROUP BY user_id, order_status;这样统计的就是每个用户每个状态下的订单数逻辑上才是清晰的。3.3 坑二GROUP BY 和 WHERE 的先后顺序分组前和分组后的过滤条件写法不一样这个我在下一章会详细展开。先记住一个口诀WHERE 管分组前的行HAVING 管分组后的组。3.4 坑三按函数结果分组GROUP BY后面不仅跟列名也可以跟表达式。比如按月分组SELECT DATE_FORMAT(create_time, %Y-%m), COUNT(*) FROM orders GROUP BY DATE_FORMAT(create_time, %Y-%m);这里有个细节虽然 SELECT 和 GROUP BY 都写了DATE_FORMAT(...)但 MySQL 允许你在 GROUP BY 里使用列的别名来简化写法SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) FROM orders GROUP BY month;MySQL 对 GROUP BY 的别名支持比标准 SQL 更宽松这也是它方便的地方。但要注意这种写法在 MySQL 里能用换到其他数据库比如 PostgreSQL就不一定行了。另外GROUP BY 的执行顺序在 WHERE 之后。这意味着你不能在 WHERE 里用分组后的条件比如WHERE COUNT(*) 1这个在下一章讲 HAVING 的时候会再强调。4. WHERE 和 HAVING 的分工条件过滤的先后顺序4.1 一条完整 SQL 的执行顺序在 MySQL 里一条 SQL 的各个关键字的执行顺序是FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT很多人以为 SQL 是照着自己写代码的先后顺序执行的其实不是。搞清楚这个顺序很多问题就迎刃而解了。比如你写SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE status paid -- 第2步先过滤行 GROUP BY user_id -- 第3步再分组 HAVING SUM(amount) 100 -- 第4步过滤组 ORDER BY total_amount DESC; -- 第6步最后排序执行到第 4 步的HAVING时分组和聚合已经完成了所以HAVING 里可以使用聚合函数。而第 2 步的WHERE发生在分组之前所以WHERE 里不能写聚合函数。4.2 为什么 HAVING 里的聚合条件不能挪到 WHERE 里很多新手会把HAVING SUM(amount) 100写成WHERE SUM(amount) 100然后报错。原因很简单WHERE 执行时分组还没发生SUM(amount) 根本算不出来。这就好比你要筛选平均分超过60分的学生得先算出每人的平均分然后才能筛选不可能在算平均分之前就看到结果。那什么时候用 WHERE、什么时候用 HAVING我的判断标准是如果条件能写在 WHERE 里就尽量写 WHERE。因为 WHERE 在分组前过滤掉不需要的行减少了分组的数据量性能更好。如果条件必须依赖聚合结果比如COUNT(*) 10、SUM(amount) 1000那只能写在 HAVING 里。如果两个都能写比如WHERE status paid也可以写成HAVING status paid那优先用 WHERE这是执行效率上的硬道理。4.3 一个合并统计的小例子假设要统计订单表里每个用户下单金额超过500元的月份数就可以把 WHERE 和 HAVING 配合起来用SELECT user_id, COUNT(*) AS qualified_months FROM ( SELECT user_id, DATE_FORMAT(create_time, %Y-%m) AS month, SUM(amount) AS total FROM orders WHERE status paid GROUP BY user_id, DATE_FORMAT(create_time, %Y-%m) HAVING SUM(amount) 500 ) AS monthly_stats GROUP BY user_id;外层再按用户汇总这样一层套一层的写法在实际报表里非常常见。4.4 阿里分组的间隔问题GROUP BY 后的空值处理GROUP BY 在分组时会把 NULL 值单独分成一组。比如SELECT city, COUNT(*) FROM users GROUP BY city;如果 city 列里有 NULL那结果里会多出一行NULL | 数量。这个行为有时候是你要的有时候不是。如果你不想看到这个分组可以在 GROUP BY 前加条件过滤SELECT city, COUNT(*) FROM users WHERE city IS NOT NULL GROUP BY city;或者用IFNULL(city, 未知)把 NULL 映射成一个可读的分组名。这个细节在展示层很容易被忽略但其实现实业务里未知城市的那组数据往往是最大的。5. 聚合函数在报表统计中的几个高频实操场景5.1 按月统计订单量与销售额这是最常见也最典型的场景。关键点在于时间格式化和聚合函数的配合SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS order_cnt, COALESCE(SUM(amount), 0) AS total_amount, COALESCE(AVG(amount), 0) AS avg_amount FROM orders WHERE create_time 2024-01-01 GROUP BY DATE_FORMAT(create_time, %Y-%m) ORDER BY month DESC;如果没有数据的时间段也要显示为 0那就得用一张日期维度表做 LEFT JOIN这是另一个话题但思路是明确的先造一份完整的时间序列再去关联聚合结果。5.2 找出每个分组内出现次数最多的项比如要统计每个商品分类下销量最高的商品一个比较直接的写法是SELECT category_id, product_name, sales_count FROM products WHERE (category_id, sales_count) IN ( SELECT category_id, MAX(sales_count) FROM products GROUP BY category_id );这种先聚合找出每组最大值再原表关联取明细的思路在业务里很常用。你也可以结合窗口函数MySQL 8.0用ROW_NUMBER()来做写法会更优雅但核心逻辑是一样的。5.3 用 GROUP BY HAVING 排查重复数据数据清洗的时候查重复记录靠的就是 HAVING COUNTSELECT id_card_no, COUNT(*) AS cnt FROM users GROUP BY id_card_no HAVING COUNT(*) 1;这个查询会把身份证号出现超过一次的用户列出来配合外面再包一层查询就能定位到具体是哪几条记录了。这种写法比你在应用层循环判断要高效得多。5.4 多维度分组按两个字段分组统计聚合函数不一定只能按一个维度分组。比如统计每个平台上每个商品分类的销量SELECT platform, category_id, SUM(sales_count) AS total_sales FROM product_sales GROUP BY platform, category_id ORDER BY platform, total_sales DESC;这个查询的结果就是一张平台 x 分类的矩阵。如果你还想要平台合计那一行可以用WITH ROLLUPSELECT IFNULL(platform, 全部平台) AS platform, IFNULL(category_id, 全部分类) AS category_id, SUM(sales_count) AS total_sales FROM product_sales GROUP BY platform, category_id WITH ROLLUP;WITH ROLLUP会自动生成汇总行和总计行在报表展示时特别好用。需要留意的是汇总行的platform或category_id是 NULL需要配合IFNULL来做展示美化。5.5 自定义分组按价格区间统计有时候分组的粒度不是某个字段本身而是一个范围。比如把商品按价格分成0-50、50-100、100以上三档然后统计每个档位的商品数。这时可以用CASE WHEN配合 GROUP BYSELECT CASE WHEN price 50 THEN 0-50 WHEN price 100 THEN 50-100 ELSE 100以上 END AS price_range, COUNT(*) AS product_cnt FROM products GROUP BY price_range;注意这里 GROUP BY 用的是 SELECT 里的别名price_rangeMySQL 支持这个写法非常方便。这类自定义分组在数据分析和报表场景里出现频率极高。5.6 统计占比用 SUM(CASE WHEN) 方式统计某类商品占总量的比例典型写法SELECT category_id, COUNT(*) AS total, SUM(CASE WHEN stock 0 THEN 1 ELSE 0 END) AS on_sale_cnt, CONCAT(ROUND(SUM(CASE WHEN stock 0 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2), %) AS on_sale_rate FROM products GROUP BY category_id;这种写法一步到位不需要先查总数再逐项计算比例。CONCAT(ROUND(...))是为了展示成百分比字符串如果你要在应用层继续计算可以只保留数值部分。6. 聚合函数性能优化数据量上来之后该注意的事6.1 COUNT(*) 在大表上的性能问题数据量到百万千万级别之后COUNT(*)会变得很慢因为 MySQL 的 InnoDB 引擎不像 MyISAM 那样直接存储了总行数它需要逐行扫描才能统计出结果。遇到这种情况有几个处理思路第一如果业务允许可以在应用层维护一个计数器表每次插入/删除时同步更新。这在只读统计场景下是最快的方案。第二如果只是需要估算行数用EXPLAIN SELECT COUNT(*) FROM t的结果里的rows字段虽然是估算值但对于不需要精确行数的场景足够了。第三精确统计需求通常无法完全绕开全表扫描但可以通过缩小 WHERE 条件范围来减少扫描的数据量。比如统计去年订单数加一个create_time 2024-01-01的过滤如果该字段有索引扫描行数会大幅降低。6.2 MAX/MIN 如何利用索引MAX和MIN在 MySQL 里有个独特优势如果目标列上有索引它不需要扫描全部数据直接取 B 树的最后一个或第一个叶子节点就行。这就是为什么在日期列上建了索引之后SELECT MAX(create_time) FROM orders能瞬间返回。所以如果你的业务经常需要查最新一条记录的时间、最大的订单号这种聚合值务必给对应列加上索引。不然每次都是全表扫描代价非常大。6.3 避免在索引列上使用函数在 WHERE 条件里对索引列使用函数会让索引失效。比如-- 这样写会导致索引失效全表扫描 SELECT COUNT(*) FROM orders WHERE YEAR(create_time) 2024; -- 正确写法范围查询索引生效 SELECT COUNT(*) FROM orders WHERE create_time 2024-01-01 AND create_time 2025-01-01;这个坑我在实际优化里见过太多次了。YEAR(create_time)看起来没毛病但优化器无法确定函数作用后的值对应哪个索引范围只能全表扫描。改成范围查询后性能会有一个量级的差别。6.4 GROUP BY 索引与 filesort 问题当 GROUP BY 的字段没有索引时MySQL 需要用临时表或文件排序来完成分组操作数据量大时性能会显著下降。在分组字段上创建索引可以直接利用索引的有序性完成分组避免额外的排序步骤。简单说如果你的 SQL 经常按照city分组统计那city上最好有索引。另外GROUP BY 的字段顺序和索引的字段顺序也需要匹配多列分组时要遵守最左前缀原则。6.5 聚集查询的结果缓存如果报表系统的数据变化不频繁可以考虑在应用层做缓存或者用 MySQL 的查询缓存8.0 之前。更简单的方式是建一张汇总表定时从明细表聚合数据写入汇总表查询直接读汇总表而不是每次实时聚合。这个思路在很多 BI 系统里叫预聚合是应对大数据量聚合查询最有效的手段之一。另外一个实用建议在做分析类查询时如果业务允许可以把这类查询放到从库上执行避免和线上写操作抢资源。结合热搜词里经常出现的mysql 主从复制这算是一个很常见的优化手段。6.6 警惕聚合函数导致的临时表GROUP BY配合ORDER BY或者DISTINCT时可能会产生临时表和 filesort。当数据量很大时临时表会写到磁盘性能急剧下降。看执行计划时如果看到Using temporary; Using filesort就要考虑优化了。优化方向一般是确保 GROUP BY 字段和 ORDER BY 字段一致且有索引或者减少 SELECT 的字段数量降低临时表的宽度。6.7 MySQL 8.0 的窗口函数聚合函数的进阶替代MySQL 8.0 引入了窗口函数和普通聚合函数的区别在于聚合函数会把多行合成一行窗口函数则是在保留所有行的同时额外输出一个聚合计算列。比如SELECT user_id, order_id, amount, SUM(amount) OVER (PARTITION BY user_id) AS user_total FROM orders;这个查询在每行后面附加了该用户的所有订单金额合计但不会像 GROUP BY 那样把多行压缩成一行。窗口函数在做分组内排名、累计值、移动平均等分析时比传统聚合函数灵活很多。如果你还在用 MySQL 5.7建议尽快规划升级到 8.0窗口函数带来的写法和性能优势非常明显。7. 面试官最爱问的几个聚合函数问题7.1 COUNT(*)、COUNT(1)、COUNT(列名) 有什么区别这是最经典的面试题。答案是COUNT(*)和COUNT(1)在 MySQL 中忽略索引都统计所有行包括 NULLCOUNT(列名)只统计该列非 NULL 的行。性能上COUNT(*)和COUNT(1)基本没区别MySQL 优化器会做等价转换。真正要注意的是业务口径——你到底是统计行数还是统计非空值的个数。7.2 WHERE 后面能不能用聚合函数不能。因为 WHERE 的执行顺序在聚合之前。如果你需要基于聚合结果做过滤用 HAVING。这是对执行顺序的直接考察。7.3 聚合函数和 NULL 的兼容性这个问题经常被包装成SUM 遇到 NULL 会怎样AVG 会不会把 NULL 当 0。要记住SUM 和 AVG 都会忽略 NULL只有当整列都为 NULL 时 SUM 结果为 NULL。如果需要 NULL 按 0 处理必须显式用 IFNULL 或 COALESCE。7.4 GROUP BY 和 DISTINCT 去重的区别两者都可以去重但 GROUP BY 是配合聚合函数使用的可以同时统计数量DISTINCT 只是简单去重不做统计。另外 GROUP BY 会排序DISTINCT 在 MySQL 里通常也不会完全无序但在语义上 GROUP BY 更常用于分组统计而不是单纯的去重。7.5 用一条 SQL 实现行转列这也是面试常见题。比如把每个用户的月度消费从多行转成多列SELECT user_id, SUM(CASE WHEN month 2024-01 THEN amount ELSE 0 END) AS jan, SUM(CASE WHEN month 2024-02 THEN amount ELSE 0 END) AS feb, SUM(CASE WHEN month 2024-03 THEN amount ELSE 0 END) AS mar FROM user_consumption GROUP BY user_id;这里考察的是对聚合函数配合 CASE WHEN 进行条件聚合的掌握程度。这种写法在报表领域非常实用。7.6 子查询中能不能用聚合函数可以但要注意子查询的上下文。比如在 SELECT 后面跟一个标量子查询里面可以用聚合函数SELECT user_id, (SELECT MAX(amount) FROM orders WHERE orders.user_id users.user_id) AS max_order_amount FROM users;这种写法每次循环都会执行子查询性能上不如 JOIN 加分组的方式。面试时如果遇到这种问题最好能主动说明性能较差建议改写为 JOIN GROUP BY面试官会觉得你有实战经验。8. 我平时写聚合查询的几个习惯最后分享几个我自己的实操习惯算是从踩坑里总结出来的经验。第一个习惯是写完聚合 SQL 一定在测试环境跑一遍用 EXPLAIN 看一眼执行计划。重点看有没有Using temporary、Using filesort有没有索引失效。不要以为功能对了就完事数据量上来之后性能问题会暴露得非常快。第二个习惯是聚合结果涉及 NULL 时对外输出前统一用 COALESCE 兜底。不管是写 SQL 还是在应用层处理都要把 NULL 的展示问题提前解决掉否则测试环境数据量小看不出问题上线后各种空指针和显示异常会接踵而至。第三个习惯是把聚合查询和业务事务分开。事务里尽量只做增删改统计聚合查询放到非事务上下文或者放到从库执行。聚合查询会持有读锁在高并发下可能影响主库的写入效率。第四个习惯跟热搜词里提到的mysql 将字符串转为日期有关——比较日期字段时避免字符串隐式转换和函数包裹。如果库里存的是字符串格式的日期先把它转成 DATE 类型再比较否则隐式转换会导致索引失效。比如建表时直接用 DATE 类型或者查询时用CAST显式转换SELECT COUNT(*) FROM orders WHERE CAST(create_time AS DATE) 2024-01-01;如果你在一个系统里长期和聚合函数打交道慢慢会形成一套自己的稳妥写法。这套写法不一定是最花哨的但一定是最经得起数据量考验的。聚合函数的本质是把多行变成一行的归纳逻辑想清楚每一行是怎么被归类、被计算的写出来的统计结果才真正可信。