1. 写在前面为什么 GROUP BY 和 HAVING 值得花时间吃透如果你写过几年 SQL大概率有过这样的经历一条 GROUP BY 语句在本地跑得飞快扔到线上数据量一上来就慢得像蜗牛又或者你只是想筛一下分组后的结果随手写了 WHERE结果发现查出来的数据和预期对不上。这些问题的根源往往就是对 GROUP BY 和 HAVING 这两个子句的理解还停留在“会用”的层面没有真正吃透它们的执行逻辑。GROUP BY 是 SQL 里最常用的聚合子句之一它和 HAVING 的组合在处理统计报表、用户行为分析、数据去重等场景下几乎是绕不开的。但这两兄弟也恰恰是面试中最高频的考点更是实际开发中踩坑最多的位置。ONLY_FULL_GROUP_BY 报错、“不是 GROUP BY 表达式”的异常、分页加排序后数据错乱、大表分组查询慢到超时……这些坑我全都踩过所以今天想把这些经验和教训一次性讲清楚。这篇文章会从基础语法和常见用法讲起然后深入执行原理、常见报错的原因与解决方案最后重点聊聊大数据量下的性能优化手段。不管你是刚接触 SQL 的新手还是已经写了好几年 SQL 的老手这篇文章里应该都有值得你停下来细看的内容。2. GROUP BY 基础先搞清楚它到底在做什么2.1 GROUP BY 的本质是“分组聚合”要理解 GROUP BY先记住一句话它做的事情是把一张表里的数据按照指定的列分成若干个组然后对每一组做聚合计算。这句话听起来简单但很多人写代码时并没有真正理解“分组”的含义。举个例子假设我们有一张订单表CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, product_name VARCHAR(50), amount DECIMAL(10,2), order_date DATE );我现在想知道每个用户一共下了多少单、花了多少钱。这里的“每个用户”就是分组维度也就是 GROUP BY user_id“一共下了多少单”是 COUNT(*) “花了多少钱”是 SUM(amount)。SQL 写出来就是这样SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY user_id;执行的过程可以这样理解MySQL 先把所有订单按照 user_id 的值分成若干堆同一个 user_id 的所有订单在同一个堆里然后对每一堆分别执行 COUNT(*) 和 SUM(amount)最终每个堆输出一行结果。这里就引出了一个非常关键的规则SELECT 子句中出现的列要么出现在 GROUP BY 中要么被聚合函数包裹。比如上面这条 SQLuser_id 在 GROUP BY 里order_count 和 total_amount 是聚合值所以没问题。但如果我写成SELECT user_id, product_name, SUM(amount) FROM orders GROUP BY user_id;这个 SQL 在很多数据库里会直接报错或者即使 MySQL 默认允许执行返回的 product_name 也是毫无意义的——因为同一个 user_id 可能对应多个 product_name数据库根本不知道该返回哪一个。这个话题后面讲 ONLY_FULL_GROUP_BY 的时候还会再展开。2.2 分组函数COUNT、SUM、AVG、MAX、MIN 的使用细节聚合函数是 GROUP BY 的最佳搭档但每个函数都有自己的脾气用不好就会出问题。COUNT(*) 统计的是分组内的行数不管某列是不是 NULL都会算进去。COUNT(列名) 则只统计该列非 NULL 的行数。举例来说如果某个用户下的订单里有一半 refund_time 是 NULL那么 COUNT(refund_time) 得到的就是有退款时间记录的订单数而不是订单总数。这个细微的区别在做业务统计时经常会造成数据对不上排查半天才发现是这里的问题。SUM 和 AVG 都会忽略 NULL 值。这个设计其实挺合理的——一群订单里某个订单的金额是 NULL求平均数时不应该把它当 0 处理否则会把平均数拉低。但反过来如果业务上确实希望把 NULL 当 0 处理就得先用 IFNULL 或 COALESCE 做预处理比如 SUM(IFNULL(amount, 0))。MAX 和 MIN 除了能处理数值也能处理字符串和日期。比如想查每个用户最早的下单日期直接 MIN(order_date) 就行不需要先把日期转成时间戳再转回来省去很多麻烦。2.3 多列分组同时按多个维度统计实际业务中单列分组远远不够更多时候需要多列组合分组。比如说我想统计每个用户每天的下单金额那就是按 user_id 和 order_date 两个维度同时分组SELECT user_id, order_date, SUM(amount) AS daily_amount FROM orders GROUP BY user_id, order_date;多列分组的执行逻辑仍然和单列一致只不过分组的依据从“一个列的值相同”变成了“几个列的值都相同”。任何一列的值不同就算不同的分组。这个用法在生成报表时非常常用。比如“每个城市每个月的销售额”“每个品类每个季度的库存变化”本质上都是多列分组。需要注意的是多列分组的列顺序会影响结果的排序在旧版 MySQL 中但不会影响分组结果本身。所以在写多列分组时建议把选择性更高的列放在前面这样在某些执行计划下能更好地利用索引。3. HAVING 的定位它和 WHERE 到底有什么区别3.1 WHERE 先行 HAVING 后置这是 SQL 初学者最容易混淆的一对概念。我的建议是先从执行顺序来理解。一条常见的 SQL 执行顺序大致是这样的FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。也就是说WHERE 是在分组之前执行的行级过滤它筛选的是原始数据行HAVING 是在分组之后执行的组级过滤它筛选的是分组后的结果。用生活化的例子来类比WHERE 就像贴标签之前先筛人——比如我只统计 VIP 用户的订单那就先 WHERE 把非 VIP 用户的行过滤掉再分组HAVING 就像分组之后再筛组——比如我按用户分组统计订单金额只保留总额超过 10000 的用户就必须用 HAVING。所以下面这条 SQL 是合法的SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE order_date 2024-01-01 GROUP BY user_id HAVING total_amount 10000;WHERE 先过滤出 2024 年以后的订单再按用户分组求和最后 HAVING 只保留总金额大于 10000 的用户。如果我把 HAVING 换成 WHERE也就是 WHERE SUM(amount) 10000那数据库会直接报错因为 WHERE 执行时分组还没发生SUM(amount) 根本还不存在。3.2 什么时候用 WHERE什么时候用 HAVING搞清楚了执行顺序这个问题的答案就很清晰了能放在 WHERE 里的过滤条件尽量放在 WHERE 里。比如时间范围、状态字段、类型字段等这些都是原始数据行自带的属性。提前过滤掉不需要的行能减少分组的数据量对性能有明显帮助。HAVING 只用于对聚合结果的过滤。比如 SUM(amount) 10000、COUNT(*) 100、AVG(score) 60 这类条件只能写在 HAVING 里。有个容易踩的坑是如果你在 GROUP BY 中写了某个列然后在 WHERE 里用这个列做过滤这没问题但如果你在 WHERE 里用了聚合函数数据库一定会报错。所以“WHERE 里能不能用聚合函数”这个问题答案不是“不建议”而是“不能”。3.3 别用别名在 WHERE 里做过滤还有个细节我经常看到有人踩。有些同学喜欢给列起别名然后在 WHERE 里用别名做过滤比如SELECT user_id AS uid, SUM(amount) AS total FROM orders WHERE uid 1001 GROUP BY user_id;这条 SQL 在某些数据库比如 SQL Server里能跑通但在 MySQL 里通常不行。原因还是执行顺序——SELECT 子句是在 WHERE 之后才执行的WHERE 执行时别名 uid 根本还没定义。MySQL 的优化器有时候会做特殊处理但依赖这种行为等于给自己埋雷千万不能这么写。4. 进阶GROUP BY 的常见使用场景与高阶技巧4.1 分组后排序ORDER BY 的正确用法GROUP BY 和 ORDER BY 的组合也很常见。我想按每个用户的下单总金额从高到低排序最直接的写法是SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id ORDER BY total_amount DESC;这里 ORDER BY 能用别名 total_amount是因为 ORDER BY 是在 SELECT 之后执行的别名此时已经生效。不用别名直接写 SUM(amount) 也可以。还有一个很多人不知道的细节在 MySQL 8.0 之前GROUP BY 默认会按照分组的列做隐式排序这导致在某些场景下 GROUP BY 会比预期慢很多。MySQL 8.0 之后这个隐式排序被移除了所以在 8.0 及以上的版本中如果你需要对分组结果排序必须明确写 ORDER BY不要指望 GROUP BY 自动帮你排好。4.2 配合 DISTINCT 与 GROUP BY 的选择DISTINCT 和 GROUP BY 都能实现去重的效果但它们的定位不同。DISTINCT 用于完全去重返回的行中所有列的组合都不重复GROUP BY 则天生是为聚合准备的分组操作只不过如果你只 SELECT 分组列而不写聚合函数确实可以达到去重的效果。在我的实践中单纯去重用 DISTINCT 更直观也更容易让其他同事看懂你的意图但如果去重的同时还要统计数量、求和、平均等那就必须用 GROUP BY。比如“查出去重后的用户数”用 COUNT(DISTINCT user_id) “查每个用户的所有下单日期”用 GROUP BY user_id 配合 GROUP_CONCAT(order_date)。4.3 GROUP_CONCAT 与聚合文本拼接GROUP_CONCAT 是 MySQL 特有的聚合函数能把分组内的多行数据拼成一行字符串用分隔符隔开。比如我要看每个用户买过哪些商品SELECT user_id, GROUP_CONCAT(product_name ORDER BY order_date SEPARATOR 、) AS products FROM orders GROUP BY user_id;这个函数的默认分隔符是逗号但通常我们都会用 SEPARATOR 指定自己的分隔符。它还有一个很实用的参数是 DISTINCT可以去掉重复项比如 GROUP_CONCAT(DISTINCT product_name)。这里有个性能相关的点需要注意GROUP_CONCAT 默认的最大长度是 1024 字节超过的部分会被截断。如果拼接的内容比较长需要通过设置 group_concat_max_len 参数来调大比如 SET SESSION group_concat_max_len 102400;。这个坑比较隐蔽我曾经因为没注意这个默认值导致输出的商品列表被无故截断排查了很久才找到原因。4.4 在 GROUP BY 中使用表达式和函数GROUP BY 后面不一定只能写列名也可以是表达式或函数。比如我想按下单年份分组统计金额SELECT YEAR(order_date) AS order_year, SUM(amount) AS total_amount FROM orders GROUP BY YEAR(order_date);再比如按价格区间分组SELECT CASE WHEN amount 100 THEN 小额 WHEN amount 1000 THEN 中额 ELSE 大额 END AS amount_level, COUNT(*) AS order_count FROM orders GROUP BY amount_level;这个 CASE WHEN 配合 GROUP BY 的写法在做用户分层、价格区间统计时非常实用可以让原本需要多次查询的数据一次搞定。不过要注意的是在 SELECT 和 GROUP BY 中使用的函数和表达式要保持一致否则可能出现语义不匹配的问题。5. 高性能实践大表 GROUP BY 查询的优化策略5.1 使用索引消除 filesort 与临时表GROUP BY 查询慢的根本原因通常在于MySQL 需要把所有数据加载到内存中建立临时表再进行分组操作。数据量一大临时表可能会落到磁盘上性能急剧下降。要解决这个问题最有效的办法就是让 GROUP BY 的列走索引。MySQL 的索引结构天然就是有序的如果分组键恰好是索引的前缀列优化器就可以直接扫描索引的有序块来分组不需要额外建临时表这就叫松散索引扫描或紧凑索引扫描。比如我们对 orders 表建一个联合索引 (user_id, order_date)那么下面这条 SQL 大概率能用到索引来加速分组SELECT user_id, MAX(order_date) AS latest_order_date FROM orders GROUP BY user_id;因为索引本身就是按 user_id 排序的MySQL 扫描时只需要做一次顺序遍历把同一个 user_id 的索引项分到一组即可。判断是否真的用到索引可以用 EXPLAIN 查看执行计划如果 Extra 列里出现 Using temporary 和 Using filesort说明没有走索引优化需要调整索引策略或改写 SQL。5.2 覆盖索引让分组查询提速数倍覆盖索引是另一个被低估的优化手段。所谓覆盖索引指的是查询所需的所有列都能从索引中直接获取不需要回表访问数据行。对于 GROUP BY 查询来说如果分组列和聚合列都在索引里执行计划就会非常漂亮。还是上面的例子假设我经常需要按 user_id 分组统计金额总和那么建一个 (user_id, amount) 的联合索引就很合适CREATE INDEX idx_user_amount ON orders(user_id, amount);这样 MySQL 在扫描索引时既能按 user_id 分组也能直接读取 amount 的值做 SUM连表里的数据行都不用碰。在极端情况下这种优化能带来十倍以上的性能提升。5.3 延迟关联先缩小再分组延迟关联Deferred Join是我在大表分组查询中最常用的一招。它的核心思想是先用最快的办法把需要处理的主键 ID 范围缩小再把这些 ID 关联回原表取完整数据最后再进行分组聚合。举个实际场景有一张上亿行的订单表我要统计每个用户最近 30 天的下单总额。直接 GROUP BY user_id 肯定很慢因为优化器需要把上亿行数据全部读一遍再分组。用延迟关联的做法是SELECT o.user_id, SUM(o.amount) AS total_amount FROM ( SELECT DISTINCT user_id FROM orders WHERE order_date DATE_SUB(CURDATE(), INTERVAL 30 DAY) ) t JOIN orders o ON o.user_id t.user_id WHERE o.order_date DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY o.user_id;先用子查询只从索引里取到最近 30 天出现过的 user_id这时候走覆盖索引速度极快再把这些 user_id 关联回原表。这种方式把最大的扫描和分组操作都作用在了一个缩小了很多的结果集上性能提升非常可观。5.4 合理选择聚合列的数据类型与长度这是一个容易被忽略但影响巨大的细节。GROUP BY 的性能不仅取决于索引还取决于参与分组的列本身的数据类型。同样的逻辑用 INT 分组比用 VARCHAR(255) 分组快很多倍因为字符串比较的开销远大于整数比较。我见过一个实际案例某个日志表的分组列是用户手机号存成 VARCHAR(20)数据量五千万行GROUP BY 手机号要 15 秒。后来我把手机号字段改成 BIGINT手机号完全可以用整数存储去掉前导空格同样的查询变成了 3 秒。这个案例里还叠加了索引优化但数据类型本身的影响依然占了很大比重。所以设计表结构的时候尽量把分组用到的列设计成短类型能存 INT 就别存 VARCHAR能用 CHAR(32) 就别用 VARCHAR(255)。6. 常见报错与问题排查实战6.1 “不是 GROUP BY 表达式”报错详解这个报错应该是 MySQL 里 GROUP BY 相关最出名的错误了。完整报错信息通常长这样Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column db.orders.product_name which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_modeonly_full_group_by原因是 MySQL 5.7.5 及之后版本默认开启了 ONLY_FULL_GROUP_BY 模式要求 SELECT 中出现的所有非聚合列都必须出现在 GROUP BY 里或者被函数依赖。这个设计是为了避免上面提到的“同一个 user_id 对应多个 product_name 时到底返回哪个”的语义歧义。解决这个报错的思路有三种第一把非聚合列加到 GROUP BY 里。如果业务上确实需要按 user_id 和 product_name 两个维度看数据那就两个列都分组逻辑没毛病。第二用 ANY_VALUE() 函数包裹非聚合列。这个函数的作用是“随便返回一个值”相当于告诉 MySQL我知道这一组里有多个值我不在乎具体返回哪个。什么场景下可以这么做当你明确知道自己只关注聚合结果非聚合列只是顺带展示时用 ANY_VALUE 快速绕过检查是合理的。第三修改 sql_mode 去掉 ONLY_FULL_GROUP_BY。这种方案我不推荐因为这会让你丢失数据库的语义保护很容易写出结果不确定的 SQL。团队协作的项目里最好保持默认的 sql_mode 不要随意改动。6.2 分页排序结果不对分组后排序的歧义还有一个常见问题分组后用 ORDER BY 和 LIMIT 做分页结果出现重复或数据错乱。这通常不是 GROUP BY 的问题而是没有给 ORDER BY 提供唯一排序键导致的。分组聚合得到的结果集里如果排序键比如 total_amount相同那么两条记录的顺序是不确定的再配合 LIMIT就可能在不同页里出现相同的数据。解决办法很简单ORDER BY 后面加上一个唯一列作为次级排序键。比如SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id ORDER BY total_amount DESC, user_id ASC LIMIT 10 OFFSET 20;这样即使 total_amount 相同MySQL 也能根据 user_id 唯一确定排序分页结果就稳定了。这个点在写报表分页接口时一定要特别注意。6.3 聚合结果出现 NULL 的原因分析分组聚合的结果里经常会出现 NULL让人一脸懵。典型的情况有几种COUNT(*) 不会返回 NULL因为计算的是行数但从 0 行的分组里它会返回 0。SUM、AVG、MAX、MIN 如果作用的列全部是 NULL聚合结果就是 NULL而不是 0。这是因为这些函数会忽略 NULL 值当一组的列值全是 NULL 时没有任何值参与计算结果自然就是 NULL。解决的办法是使用 IFNULL 做兜底IFNULL(SUM(amount), 0) AS total_amount。这个看起来是常识但在联表查询的场景下特别容易踩坑。比如用 LEFT JOIN 把没有订单的用户也查出来这些用户的 SUM(amount) 就会是 NULL如果没有兜底前端拿到的就是空值直接展示成 0 更稳妥。6.4 HAVING 条件书写注意事项写 HAVING 的时候有两点提醒第一HAVING 里可以使用聚合函数没问题但不要在里面引用 SELECT 的别名之外的复杂表达式第二HAVING 里的条件如果想走索引那是不现实的因为它是在分组之后才执行的过滤所以不要指望给某个列建索引能加速 HAVING 的判断。正确的优化思路是尽量把能前置的过滤条件放到 WHERE 里HAVING 只保留对聚合结果的最小过滤。举个例子SELECT user_id, COUNT(*) AS cnt FROM orders WHERE order_date 2024-01-01 GROUP BY user_id HAVING cnt 10;这里把时间过滤放在 WHERE 里只让 HAVING 干它唯一擅长的活——过滤聚合后的行数。如果反过来把时间条件也写进 HAVING会导致大量无用的分组计算先执行一遍性能会差很多。7. 多表 JOIN 与 GROUP BY 结合时的性能陷阱7.1 JOIN 之后分组的数据膨胀问题在实际开发中GROUP BY 往往不是单独作用的它经常和 JOIN 一起出现。最典型的问题是数据膨胀比如订单表和订单明细表 JOIN一笔订单对应 N 条明细JOIN 之后结果集就膨胀成了 N 行这时候再按订单维度做 GROUP BY 和 SUM金额就会翻倍。这是一个经典陷阱。正确做法是分组操作要在 JOIN 之前或之后分别处理避免在已经膨胀的数据上做聚合。比如统计每个订单的总金额明细表里已经有 amount订单表里也有 amountJOIN 后无论 SUM 哪一个都可能被放大。合理的做法是先对明细表做 GROUP BY 汇总再 JOIN 订单表。SELECT o.order_id, d.total_detail_amount FROM orders o LEFT JOIN ( SELECT order_id, SUM(amount) AS total_detail_amount FROM order_details GROUP BY order_id ) d ON o.order_id d.order_id;这样做既避免了数据膨胀带来的重复计算也大大减少了 JOIN 两侧的数据量性能上更优。7.2 分组键选择优先用驱动表的唯一键多表 JOIN 后做 GROUP BY选择哪个列作为分组键也会影响性能。如果有可能优先使用驱动表通常是 FROM 后面的那个表中已经建了索引的列作为分组键这样优化器在做 JOIN 时就能顺便完成分组的一部分工作减少额外的分组开销。反之如果用被驱动表的非索引列做分组MySQL 往往需要先把 JOIN 结果整体物化成临时表再对新表做一次完整的分组操作这种场景下临时表的大小直接决定了查询的耗时。遇到这种情况我通常会用 EXPLAIN 看执行计划的 Extra 列如果出现 Using temporary就尝试调整 JOIN 顺序或者补索引尽量让分组走索引路径。7.3 子查询与 GROUP BY 的取舍有些场景下用子查询先聚合再 JOIN和直接 JOIN 后再 GROUP BY 得到的结果是一样的但性能差异却很悬殊。基本判断原则是如果子查询的结果集很小那么优先用子查询缩窄数据范围如果子查询本身也很大那不如直接 JOIN 后再分组让优化器统一处理。这里没有绝对正确的答案只能通过 EXPLAIN 和实际数据量来验证。我个人的习惯是先在开发环境用模拟数据测试两种写法的执行计划和耗时选择更优的方案后再上生产。SQL 优化不是背口诀而是要基于具体的表结构、数据分布和索引情况来决策。8. 真实案例一条慢查询从 20 秒优化到 0.5 秒最后分享一个我实际处理过的案例把前面提到的知识点串起来。背景是一个用户行为分析系统有一张 event_log 表记录用户的各种行为事件。表结构大致是CREATE TABLE event_log ( id BIGINT PRIMARY KEY, user_id INT NOT NULL, event_type VARCHAR(50) NOT NULL, event_time DATETIME NOT NULL, extra_data VARCHAR(500), KEY idx_user_time (user_id, event_time) );某个统计报表需要查询每天活跃超过 10 次的用户 ID线上原始 SQL 是SELECT user_id, COUNT(*) AS cnt FROM event_log WHERE event_time 2024-06-01 AND event_time 2024-06-02 GROUP BY user_id HAVING cnt 10;这个查询在线上跑了 20 秒用户反馈报表打开基本靠缘分。我用 EXPLAIN 看执行计划发现 Despite 有 idx_user_time 索引优化器还是扫描了整个索引范围因为 event_time 不是索引的最左前缀Extra 列显示 Using temporary; Using filesort。优化方案分两步第一步修改联合索引顺序把 event_time 放到前面ALTER TABLE event_log DROP INDEX idx_user_time; ALTER TABLE event_log ADD INDEX idx_time_user (event_time, user_id);这样 WHERE 条件中的 event_time 范围过滤就能直接走索引同时联合索引里包含了 user_id可以在扫描索引时直接完成分组和计数连回表都不需要。Extra 列变成了 Using index。第二步把 HAVING 的条件改为用 WHERE 完成——这里不能直接把 COUNT(*) 放 WHERE但可以把 10 次这个阈值固化成子查询无法实现所以还是保持 HAVING。不过由于索引覆盖HAVING 只需要作用在很小的结果集上开销已经可以忽略。优化后同样是查 6 月 1 日的数据耗时从 20 秒降到了 0.5 秒。这个案例充分说明了两个道理索引设计对 GROUP BY 查询的影响是决定性的基础的数据类型、索引顺序设计好能少写很多“伪优化”的 SQL。9. 最后的经验之谈GROUP BY 和 HAVING 这两个子句看起来只是 SQL 学习里的一个小知识点但它牵扯出来的执行顺序、索引优化、临时表策略、语义约束几乎覆盖了数据库查询优化的一半核心内容。我自己这些年写 SQL 的心得是遇到分组查询慢先别急着改 SQL先用 EXPLAIN 把执行计划看清楚定位是索引没用上、临时表太大、还是数据膨胀导致的再对症下药。很多时候只需要一个恰当的索引或一个合理的索引顺序调整就能让慢查询脱胎换骨。如果你也在为主键非 GROUP BY 报错烦恼先确认一下 sql_mode如果 GROUP BY 查询慢先检查 Extra 列有没有 Using temporary如果 JOIN 后的聚合数据翻倍先怀疑是不是字段膨胀。把这些常见问题都记在心里再复杂的报表需求也能从容应对。 SQL GROUP BY 与 HAVING 核心原理及性能优化实战
1. 写在前面为什么 GROUP BY 和 HAVING 值得花时间吃透如果你写过几年 SQL大概率有过这样的经历一条 GROUP BY 语句在本地跑得飞快扔到线上数据量一上来就慢得像蜗牛又或者你只是想筛一下分组后的结果随手写了 WHERE结果发现查出来的数据和预期对不上。这些问题的根源往往就是对 GROUP BY 和 HAVING 这两个子句的理解还停留在“会用”的层面没有真正吃透它们的执行逻辑。GROUP BY 是 SQL 里最常用的聚合子句之一它和 HAVING 的组合在处理统计报表、用户行为分析、数据去重等场景下几乎是绕不开的。但这两兄弟也恰恰是面试中最高频的考点更是实际开发中踩坑最多的位置。ONLY_FULL_GROUP_BY 报错、“不是 GROUP BY 表达式”的异常、分页加排序后数据错乱、大表分组查询慢到超时……这些坑我全都踩过所以今天想把这些经验和教训一次性讲清楚。这篇文章会从基础语法和常见用法讲起然后深入执行原理、常见报错的原因与解决方案最后重点聊聊大数据量下的性能优化手段。不管你是刚接触 SQL 的新手还是已经写了好几年 SQL 的老手这篇文章里应该都有值得你停下来细看的内容。2. GROUP BY 基础先搞清楚它到底在做什么2.1 GROUP BY 的本质是“分组聚合”要理解 GROUP BY先记住一句话它做的事情是把一张表里的数据按照指定的列分成若干个组然后对每一组做聚合计算。这句话听起来简单但很多人写代码时并没有真正理解“分组”的含义。举个例子假设我们有一张订单表CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, product_name VARCHAR(50), amount DECIMAL(10,2), order_date DATE );我现在想知道每个用户一共下了多少单、花了多少钱。这里的“每个用户”就是分组维度也就是 GROUP BY user_id“一共下了多少单”是 COUNT(*) “花了多少钱”是 SUM(amount)。SQL 写出来就是这样SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY user_id;执行的过程可以这样理解MySQL 先把所有订单按照 user_id 的值分成若干堆同一个 user_id 的所有订单在同一个堆里然后对每一堆分别执行 COUNT(*) 和 SUM(amount)最终每个堆输出一行结果。这里就引出了一个非常关键的规则SELECT 子句中出现的列要么出现在 GROUP BY 中要么被聚合函数包裹。比如上面这条 SQLuser_id 在 GROUP BY 里order_count 和 total_amount 是聚合值所以没问题。但如果我写成SELECT user_id, product_name, SUM(amount) FROM orders GROUP BY user_id;这个 SQL 在很多数据库里会直接报错或者即使 MySQL 默认允许执行返回的 product_name 也是毫无意义的——因为同一个 user_id 可能对应多个 product_name数据库根本不知道该返回哪一个。这个话题后面讲 ONLY_FULL_GROUP_BY 的时候还会再展开。2.2 分组函数COUNT、SUM、AVG、MAX、MIN 的使用细节聚合函数是 GROUP BY 的最佳搭档但每个函数都有自己的脾气用不好就会出问题。COUNT(*) 统计的是分组内的行数不管某列是不是 NULL都会算进去。COUNT(列名) 则只统计该列非 NULL 的行数。举例来说如果某个用户下的订单里有一半 refund_time 是 NULL那么 COUNT(refund_time) 得到的就是有退款时间记录的订单数而不是订单总数。这个细微的区别在做业务统计时经常会造成数据对不上排查半天才发现是这里的问题。SUM 和 AVG 都会忽略 NULL 值。这个设计其实挺合理的——一群订单里某个订单的金额是 NULL求平均数时不应该把它当 0 处理否则会把平均数拉低。但反过来如果业务上确实希望把 NULL 当 0 处理就得先用 IFNULL 或 COALESCE 做预处理比如 SUM(IFNULL(amount, 0))。MAX 和 MIN 除了能处理数值也能处理字符串和日期。比如想查每个用户最早的下单日期直接 MIN(order_date) 就行不需要先把日期转成时间戳再转回来省去很多麻烦。2.3 多列分组同时按多个维度统计实际业务中单列分组远远不够更多时候需要多列组合分组。比如说我想统计每个用户每天的下单金额那就是按 user_id 和 order_date 两个维度同时分组SELECT user_id, order_date, SUM(amount) AS daily_amount FROM orders GROUP BY user_id, order_date;多列分组的执行逻辑仍然和单列一致只不过分组的依据从“一个列的值相同”变成了“几个列的值都相同”。任何一列的值不同就算不同的分组。这个用法在生成报表时非常常用。比如“每个城市每个月的销售额”“每个品类每个季度的库存变化”本质上都是多列分组。需要注意的是多列分组的列顺序会影响结果的排序在旧版 MySQL 中但不会影响分组结果本身。所以在写多列分组时建议把选择性更高的列放在前面这样在某些执行计划下能更好地利用索引。3. HAVING 的定位它和 WHERE 到底有什么区别3.1 WHERE 先行 HAVING 后置这是 SQL 初学者最容易混淆的一对概念。我的建议是先从执行顺序来理解。一条常见的 SQL 执行顺序大致是这样的FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。也就是说WHERE 是在分组之前执行的行级过滤它筛选的是原始数据行HAVING 是在分组之后执行的组级过滤它筛选的是分组后的结果。用生活化的例子来类比WHERE 就像贴标签之前先筛人——比如我只统计 VIP 用户的订单那就先 WHERE 把非 VIP 用户的行过滤掉再分组HAVING 就像分组之后再筛组——比如我按用户分组统计订单金额只保留总额超过 10000 的用户就必须用 HAVING。所以下面这条 SQL 是合法的SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE order_date 2024-01-01 GROUP BY user_id HAVING total_amount 10000;WHERE 先过滤出 2024 年以后的订单再按用户分组求和最后 HAVING 只保留总金额大于 10000 的用户。如果我把 HAVING 换成 WHERE也就是 WHERE SUM(amount) 10000那数据库会直接报错因为 WHERE 执行时分组还没发生SUM(amount) 根本还不存在。3.2 什么时候用 WHERE什么时候用 HAVING搞清楚了执行顺序这个问题的答案就很清晰了能放在 WHERE 里的过滤条件尽量放在 WHERE 里。比如时间范围、状态字段、类型字段等这些都是原始数据行自带的属性。提前过滤掉不需要的行能减少分组的数据量对性能有明显帮助。HAVING 只用于对聚合结果的过滤。比如 SUM(amount) 10000、COUNT(*) 100、AVG(score) 60 这类条件只能写在 HAVING 里。有个容易踩的坑是如果你在 GROUP BY 中写了某个列然后在 WHERE 里用这个列做过滤这没问题但如果你在 WHERE 里用了聚合函数数据库一定会报错。所以“WHERE 里能不能用聚合函数”这个问题答案不是“不建议”而是“不能”。3.3 别用别名在 WHERE 里做过滤还有个细节我经常看到有人踩。有些同学喜欢给列起别名然后在 WHERE 里用别名做过滤比如SELECT user_id AS uid, SUM(amount) AS total FROM orders WHERE uid 1001 GROUP BY user_id;这条 SQL 在某些数据库比如 SQL Server里能跑通但在 MySQL 里通常不行。原因还是执行顺序——SELECT 子句是在 WHERE 之后才执行的WHERE 执行时别名 uid 根本还没定义。MySQL 的优化器有时候会做特殊处理但依赖这种行为等于给自己埋雷千万不能这么写。4. 进阶GROUP BY 的常见使用场景与高阶技巧4.1 分组后排序ORDER BY 的正确用法GROUP BY 和 ORDER BY 的组合也很常见。我想按每个用户的下单总金额从高到低排序最直接的写法是SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id ORDER BY total_amount DESC;这里 ORDER BY 能用别名 total_amount是因为 ORDER BY 是在 SELECT 之后执行的别名此时已经生效。不用别名直接写 SUM(amount) 也可以。还有一个很多人不知道的细节在 MySQL 8.0 之前GROUP BY 默认会按照分组的列做隐式排序这导致在某些场景下 GROUP BY 会比预期慢很多。MySQL 8.0 之后这个隐式排序被移除了所以在 8.0 及以上的版本中如果你需要对分组结果排序必须明确写 ORDER BY不要指望 GROUP BY 自动帮你排好。4.2 配合 DISTINCT 与 GROUP BY 的选择DISTINCT 和 GROUP BY 都能实现去重的效果但它们的定位不同。DISTINCT 用于完全去重返回的行中所有列的组合都不重复GROUP BY 则天生是为聚合准备的分组操作只不过如果你只 SELECT 分组列而不写聚合函数确实可以达到去重的效果。在我的实践中单纯去重用 DISTINCT 更直观也更容易让其他同事看懂你的意图但如果去重的同时还要统计数量、求和、平均等那就必须用 GROUP BY。比如“查出去重后的用户数”用 COUNT(DISTINCT user_id) “查每个用户的所有下单日期”用 GROUP BY user_id 配合 GROUP_CONCAT(order_date)。4.3 GROUP_CONCAT 与聚合文本拼接GROUP_CONCAT 是 MySQL 特有的聚合函数能把分组内的多行数据拼成一行字符串用分隔符隔开。比如我要看每个用户买过哪些商品SELECT user_id, GROUP_CONCAT(product_name ORDER BY order_date SEPARATOR 、) AS products FROM orders GROUP BY user_id;这个函数的默认分隔符是逗号但通常我们都会用 SEPARATOR 指定自己的分隔符。它还有一个很实用的参数是 DISTINCT可以去掉重复项比如 GROUP_CONCAT(DISTINCT product_name)。这里有个性能相关的点需要注意GROUP_CONCAT 默认的最大长度是 1024 字节超过的部分会被截断。如果拼接的内容比较长需要通过设置 group_concat_max_len 参数来调大比如 SET SESSION group_concat_max_len 102400;。这个坑比较隐蔽我曾经因为没注意这个默认值导致输出的商品列表被无故截断排查了很久才找到原因。4.4 在 GROUP BY 中使用表达式和函数GROUP BY 后面不一定只能写列名也可以是表达式或函数。比如我想按下单年份分组统计金额SELECT YEAR(order_date) AS order_year, SUM(amount) AS total_amount FROM orders GROUP BY YEAR(order_date);再比如按价格区间分组SELECT CASE WHEN amount 100 THEN 小额 WHEN amount 1000 THEN 中额 ELSE 大额 END AS amount_level, COUNT(*) AS order_count FROM orders GROUP BY amount_level;这个 CASE WHEN 配合 GROUP BY 的写法在做用户分层、价格区间统计时非常实用可以让原本需要多次查询的数据一次搞定。不过要注意的是在 SELECT 和 GROUP BY 中使用的函数和表达式要保持一致否则可能出现语义不匹配的问题。5. 高性能实践大表 GROUP BY 查询的优化策略5.1 使用索引消除 filesort 与临时表GROUP BY 查询慢的根本原因通常在于MySQL 需要把所有数据加载到内存中建立临时表再进行分组操作。数据量一大临时表可能会落到磁盘上性能急剧下降。要解决这个问题最有效的办法就是让 GROUP BY 的列走索引。MySQL 的索引结构天然就是有序的如果分组键恰好是索引的前缀列优化器就可以直接扫描索引的有序块来分组不需要额外建临时表这就叫松散索引扫描或紧凑索引扫描。比如我们对 orders 表建一个联合索引 (user_id, order_date)那么下面这条 SQL 大概率能用到索引来加速分组SELECT user_id, MAX(order_date) AS latest_order_date FROM orders GROUP BY user_id;因为索引本身就是按 user_id 排序的MySQL 扫描时只需要做一次顺序遍历把同一个 user_id 的索引项分到一组即可。判断是否真的用到索引可以用 EXPLAIN 查看执行计划如果 Extra 列里出现 Using temporary 和 Using filesort说明没有走索引优化需要调整索引策略或改写 SQL。5.2 覆盖索引让分组查询提速数倍覆盖索引是另一个被低估的优化手段。所谓覆盖索引指的是查询所需的所有列都能从索引中直接获取不需要回表访问数据行。对于 GROUP BY 查询来说如果分组列和聚合列都在索引里执行计划就会非常漂亮。还是上面的例子假设我经常需要按 user_id 分组统计金额总和那么建一个 (user_id, amount) 的联合索引就很合适CREATE INDEX idx_user_amount ON orders(user_id, amount);这样 MySQL 在扫描索引时既能按 user_id 分组也能直接读取 amount 的值做 SUM连表里的数据行都不用碰。在极端情况下这种优化能带来十倍以上的性能提升。5.3 延迟关联先缩小再分组延迟关联Deferred Join是我在大表分组查询中最常用的一招。它的核心思想是先用最快的办法把需要处理的主键 ID 范围缩小再把这些 ID 关联回原表取完整数据最后再进行分组聚合。举个实际场景有一张上亿行的订单表我要统计每个用户最近 30 天的下单总额。直接 GROUP BY user_id 肯定很慢因为优化器需要把上亿行数据全部读一遍再分组。用延迟关联的做法是SELECT o.user_id, SUM(o.amount) AS total_amount FROM ( SELECT DISTINCT user_id FROM orders WHERE order_date DATE_SUB(CURDATE(), INTERVAL 30 DAY) ) t JOIN orders o ON o.user_id t.user_id WHERE o.order_date DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY o.user_id;先用子查询只从索引里取到最近 30 天出现过的 user_id这时候走覆盖索引速度极快再把这些 user_id 关联回原表。这种方式把最大的扫描和分组操作都作用在了一个缩小了很多的结果集上性能提升非常可观。5.4 合理选择聚合列的数据类型与长度这是一个容易被忽略但影响巨大的细节。GROUP BY 的性能不仅取决于索引还取决于参与分组的列本身的数据类型。同样的逻辑用 INT 分组比用 VARCHAR(255) 分组快很多倍因为字符串比较的开销远大于整数比较。我见过一个实际案例某个日志表的分组列是用户手机号存成 VARCHAR(20)数据量五千万行GROUP BY 手机号要 15 秒。后来我把手机号字段改成 BIGINT手机号完全可以用整数存储去掉前导空格同样的查询变成了 3 秒。这个案例里还叠加了索引优化但数据类型本身的影响依然占了很大比重。所以设计表结构的时候尽量把分组用到的列设计成短类型能存 INT 就别存 VARCHAR能用 CHAR(32) 就别用 VARCHAR(255)。6. 常见报错与问题排查实战6.1 “不是 GROUP BY 表达式”报错详解这个报错应该是 MySQL 里 GROUP BY 相关最出名的错误了。完整报错信息通常长这样Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column db.orders.product_name which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_modeonly_full_group_by原因是 MySQL 5.7.5 及之后版本默认开启了 ONLY_FULL_GROUP_BY 模式要求 SELECT 中出现的所有非聚合列都必须出现在 GROUP BY 里或者被函数依赖。这个设计是为了避免上面提到的“同一个 user_id 对应多个 product_name 时到底返回哪个”的语义歧义。解决这个报错的思路有三种第一把非聚合列加到 GROUP BY 里。如果业务上确实需要按 user_id 和 product_name 两个维度看数据那就两个列都分组逻辑没毛病。第二用 ANY_VALUE() 函数包裹非聚合列。这个函数的作用是“随便返回一个值”相当于告诉 MySQL我知道这一组里有多个值我不在乎具体返回哪个。什么场景下可以这么做当你明确知道自己只关注聚合结果非聚合列只是顺带展示时用 ANY_VALUE 快速绕过检查是合理的。第三修改 sql_mode 去掉 ONLY_FULL_GROUP_BY。这种方案我不推荐因为这会让你丢失数据库的语义保护很容易写出结果不确定的 SQL。团队协作的项目里最好保持默认的 sql_mode 不要随意改动。6.2 分页排序结果不对分组后排序的歧义还有一个常见问题分组后用 ORDER BY 和 LIMIT 做分页结果出现重复或数据错乱。这通常不是 GROUP BY 的问题而是没有给 ORDER BY 提供唯一排序键导致的。分组聚合得到的结果集里如果排序键比如 total_amount相同那么两条记录的顺序是不确定的再配合 LIMIT就可能在不同页里出现相同的数据。解决办法很简单ORDER BY 后面加上一个唯一列作为次级排序键。比如SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id ORDER BY total_amount DESC, user_id ASC LIMIT 10 OFFSET 20;这样即使 total_amount 相同MySQL 也能根据 user_id 唯一确定排序分页结果就稳定了。这个点在写报表分页接口时一定要特别注意。6.3 聚合结果出现 NULL 的原因分析分组聚合的结果里经常会出现 NULL让人一脸懵。典型的情况有几种COUNT(*) 不会返回 NULL因为计算的是行数但从 0 行的分组里它会返回 0。SUM、AVG、MAX、MIN 如果作用的列全部是 NULL聚合结果就是 NULL而不是 0。这是因为这些函数会忽略 NULL 值当一组的列值全是 NULL 时没有任何值参与计算结果自然就是 NULL。解决的办法是使用 IFNULL 做兜底IFNULL(SUM(amount), 0) AS total_amount。这个看起来是常识但在联表查询的场景下特别容易踩坑。比如用 LEFT JOIN 把没有订单的用户也查出来这些用户的 SUM(amount) 就会是 NULL如果没有兜底前端拿到的就是空值直接展示成 0 更稳妥。6.4 HAVING 条件书写注意事项写 HAVING 的时候有两点提醒第一HAVING 里可以使用聚合函数没问题但不要在里面引用 SELECT 的别名之外的复杂表达式第二HAVING 里的条件如果想走索引那是不现实的因为它是在分组之后才执行的过滤所以不要指望给某个列建索引能加速 HAVING 的判断。正确的优化思路是尽量把能前置的过滤条件放到 WHERE 里HAVING 只保留对聚合结果的最小过滤。举个例子SELECT user_id, COUNT(*) AS cnt FROM orders WHERE order_date 2024-01-01 GROUP BY user_id HAVING cnt 10;这里把时间过滤放在 WHERE 里只让 HAVING 干它唯一擅长的活——过滤聚合后的行数。如果反过来把时间条件也写进 HAVING会导致大量无用的分组计算先执行一遍性能会差很多。7. 多表 JOIN 与 GROUP BY 结合时的性能陷阱7.1 JOIN 之后分组的数据膨胀问题在实际开发中GROUP BY 往往不是单独作用的它经常和 JOIN 一起出现。最典型的问题是数据膨胀比如订单表和订单明细表 JOIN一笔订单对应 N 条明细JOIN 之后结果集就膨胀成了 N 行这时候再按订单维度做 GROUP BY 和 SUM金额就会翻倍。这是一个经典陷阱。正确做法是分组操作要在 JOIN 之前或之后分别处理避免在已经膨胀的数据上做聚合。比如统计每个订单的总金额明细表里已经有 amount订单表里也有 amountJOIN 后无论 SUM 哪一个都可能被放大。合理的做法是先对明细表做 GROUP BY 汇总再 JOIN 订单表。SELECT o.order_id, d.total_detail_amount FROM orders o LEFT JOIN ( SELECT order_id, SUM(amount) AS total_detail_amount FROM order_details GROUP BY order_id ) d ON o.order_id d.order_id;这样做既避免了数据膨胀带来的重复计算也大大减少了 JOIN 两侧的数据量性能上更优。7.2 分组键选择优先用驱动表的唯一键多表 JOIN 后做 GROUP BY选择哪个列作为分组键也会影响性能。如果有可能优先使用驱动表通常是 FROM 后面的那个表中已经建了索引的列作为分组键这样优化器在做 JOIN 时就能顺便完成分组的一部分工作减少额外的分组开销。反之如果用被驱动表的非索引列做分组MySQL 往往需要先把 JOIN 结果整体物化成临时表再对新表做一次完整的分组操作这种场景下临时表的大小直接决定了查询的耗时。遇到这种情况我通常会用 EXPLAIN 看执行计划的 Extra 列如果出现 Using temporary就尝试调整 JOIN 顺序或者补索引尽量让分组走索引路径。7.3 子查询与 GROUP BY 的取舍有些场景下用子查询先聚合再 JOIN和直接 JOIN 后再 GROUP BY 得到的结果是一样的但性能差异却很悬殊。基本判断原则是如果子查询的结果集很小那么优先用子查询缩窄数据范围如果子查询本身也很大那不如直接 JOIN 后再分组让优化器统一处理。这里没有绝对正确的答案只能通过 EXPLAIN 和实际数据量来验证。我个人的习惯是先在开发环境用模拟数据测试两种写法的执行计划和耗时选择更优的方案后再上生产。SQL 优化不是背口诀而是要基于具体的表结构、数据分布和索引情况来决策。8. 真实案例一条慢查询从 20 秒优化到 0.5 秒最后分享一个我实际处理过的案例把前面提到的知识点串起来。背景是一个用户行为分析系统有一张 event_log 表记录用户的各种行为事件。表结构大致是CREATE TABLE event_log ( id BIGINT PRIMARY KEY, user_id INT NOT NULL, event_type VARCHAR(50) NOT NULL, event_time DATETIME NOT NULL, extra_data VARCHAR(500), KEY idx_user_time (user_id, event_time) );某个统计报表需要查询每天活跃超过 10 次的用户 ID线上原始 SQL 是SELECT user_id, COUNT(*) AS cnt FROM event_log WHERE event_time 2024-06-01 AND event_time 2024-06-02 GROUP BY user_id HAVING cnt 10;这个查询在线上跑了 20 秒用户反馈报表打开基本靠缘分。我用 EXPLAIN 看执行计划发现 Despite 有 idx_user_time 索引优化器还是扫描了整个索引范围因为 event_time 不是索引的最左前缀Extra 列显示 Using temporary; Using filesort。优化方案分两步第一步修改联合索引顺序把 event_time 放到前面ALTER TABLE event_log DROP INDEX idx_user_time; ALTER TABLE event_log ADD INDEX idx_time_user (event_time, user_id);这样 WHERE 条件中的 event_time 范围过滤就能直接走索引同时联合索引里包含了 user_id可以在扫描索引时直接完成分组和计数连回表都不需要。Extra 列变成了 Using index。第二步把 HAVING 的条件改为用 WHERE 完成——这里不能直接把 COUNT(*) 放 WHERE但可以把 10 次这个阈值固化成子查询无法实现所以还是保持 HAVING。不过由于索引覆盖HAVING 只需要作用在很小的结果集上开销已经可以忽略。优化后同样是查 6 月 1 日的数据耗时从 20 秒降到了 0.5 秒。这个案例充分说明了两个道理索引设计对 GROUP BY 查询的影响是决定性的基础的数据类型、索引顺序设计好能少写很多“伪优化”的 SQL。9. 最后的经验之谈GROUP BY 和 HAVING 这两个子句看起来只是 SQL 学习里的一个小知识点但它牵扯出来的执行顺序、索引优化、临时表策略、语义约束几乎覆盖了数据库查询优化的一半核心内容。我自己这些年写 SQL 的心得是遇到分组查询慢先别急着改 SQL先用 EXPLAIN 把执行计划看清楚定位是索引没用上、临时表太大、还是数据膨胀导致的再对症下药。很多时候只需要一个恰当的索引或一个合理的索引顺序调整就能让慢查询脱胎换骨。如果你也在为主键非 GROUP BY 报错烦恼先确认一下 sql_mode如果 GROUP BY 查询慢先检查 Extra 列有没有 Using temporary如果 JOIN 后的聚合数据翻倍先怀疑是不是字段膨胀。把这些常见问题都记在心里再复杂的报表需求也能从容应对。