SQL GROUP BY原理与实战：避开HAVING和ONLY_FULL_GROUP_BY的坑

前两天帮同事排查一个线上报表问题SQL长这样SELECT province, COUNT(*) FROM orders GROUP BY province;听着像是最基础的分组统计结果导出来的数据怎么都不对——省份对不上、订单总数差了好几万。查了半天发现他把GROUP BY当成去重来用SELECT里还混着一堆非分组字段。这类问题我在工作里见得实在太多哪怕是写了三四年SQL的人碰到多字段分组、HAVING过滤、ONLY_FULL_GROUP_BY这些细节一样容易翻车。坦白说SELECT .. GROUP BY在SQL里几乎和SELECT *一样常用但真正能把它的执行语义、边界条件讲清楚的人并不多。这篇文章我不打算写手册式的说明而是按我实际排查问题的思路把GROUP BY从原理到实战完整过一遍分组到底怎么分、多字段分组有哪些细节、HAVING的正确姿势、MySQL 5.7以后默认开启的ONLY_FULL_GROUP_BY为什么老让人报错以及线上排查时怎么利用GROUP BY快速定位账号问题和优化慢查询。刚入门的朋友可以当复习写了几年SQL的也可以对照看看自己有没有踩过类似的坑。1. GROUP BY不是去重工具分组、聚合与执行顺序的本质1.1 分拣豆子模型GROUP BY到底做了什么很多人对GROUP BY的认知停留在查出来的结果没有重复行因此会写出这样的语句SELECT id, name FROM user GROUP BY name;这本质上误解了分组语义。GROUP BY做的事其实更像分拣豆子把一把混合的黄豆、绿豆、红豆倒在一张桌上按颜色分别拣到不同的碗里。原来每一粒豆子的独立性没有了碗里只剩这个碗属于哪一类和这一类有多少粒。对应到SQL里GROUP BY把满足WHERE条件的所有行按照分组字段的值归并成若干个组每组在最终结果里只输出一行。所有原本属于同一组的行在被合并时丢失了个体特征——除非你用聚合函数把它们的某些属性汇总起来。那SELECT id ... GROUP BY name的问题出在哪id是行级别的信息同一组里可能有多个不同的id但结果只输出一行你说这个id取谁的SQL标准对这种行为的态度很明确不允许因为结果有歧义。MySQL 5.7以后默认开启了ONLY_FULL_GROUP_BY这种情况下直接报错在老版本里不报错只是悄悄返回一个不确定的值。很多人被这个不确定坑过后面第4章我会专门展开。为了讲清楚顺序可以先看一条完整的SQL执行过程SELECT department, COUNT(*) AS cnt FROM employee WHERE status active GROUP BY department HAVING cnt 50 ORDER BY cnt DESC LIMIT 10;这条SQL实际的执行顺序不是从SELECT开始的大致是FROM先确定数据源employee表。WHERE过滤status active的行把不在职的提前扔掉。GROUP BY按department把剩下的行分组。HAVING过滤掉分组后cnt 50的组。SELECT计算并输出department和COUNT(*)生成最终列。ORDER BY、LIMIT对整个结果排序并截取前10行。这个顺序能解释几乎所有GROUP BY相关的报错和逻辑错误。比如WHERE里不能写COUNT() 50因为聚合还没发生而HAVING里能用COUNT()取别名cnt因为HAVING在SELECT阶段之后。实测下来很多人记不住这个顺序导致的问题包括在WHERE里写聚合条件被MySQL直接报错、HAVING里过滤普通字段导致性能骤降、ORDER BY用SELECT别名在旧版本MySQL不识别。这些我在第3章和第4章会继续展开。1.2 聚合函数与NULL的恩怨分组后剩下的这一类有多少粒是靠聚合函数来算的。常用的五个是COUNT、SUM、AVG、MAX、MIN但细节上有几个大坑值得说COUNT(*)统计的是组内的行数不管某列是否为NULLCOUNT(column)只统计该列不为NULL的行数。两种写法都能过结果却可能差很多。SUM和AVG会自动忽略NULL值但如果你自己写除法比如SUM(column) / COUNT(*)NULL会直接影响分母导致平均值偏小。MAX和MIN也会忽略NULL但空表时返回NULL这个值传到下游程序里经常引发空指针或显示异常。举一个我实际见过的错误统计每个用户的付款金额有人写SELECT user_id, SUM(COALESCE(amount, 0)) AS total FROM payment GROUP BY user_id。逻辑上看似没问题但如果某个用户当天只有一笔退款且amount为负数SUM结果可能是负数下游对账逻辑直接炸了。后来确认需求是只看正常金额的流水改成在WHERE里提前过滤。这说明写GROUP BY之前先想清楚聚合的语义比语法更重要。2. 多字段分组与字段顺序粒度是个组合概念2.1 真正需要的往往是多维分组你搜group by 多个字段说明实际工作中很少只用单字段分组。比如统计每个部门每个职级的平均薪资你要的不是部门维度也不是职级维度而是部门 职级这个组合维度。这是多字段分组最典型的场景SELECT department, job_level, AVG(salary) AS avg_salary FROM employee GROUP BY department, job_level;分组的粒度是department和job_level的笛卡尔组合。执行时数据库先把所有(department, job_level)取值相同的行归成一组比如(研发部, P7)一组(研发部, P8)另一组(产品部, P7)又一组。有多少种不同的组合结果就有多少行。很多人忽略的一点是GROUP BY后面的字段顺序会影响结果集的排列习惯但不影响分组本身。GROUP BY department, job_level的结果在多数数据库里会先按department排列同一department内再按job_level排列因为分组过程通常伴随排序或哈希。你写成GROUP BY job_level, department输出顺序可能就变了。如果你只关心分组结果是否准确顺序无所谓如果你关心展示顺序建议单独加ORDER BY不要把思路建立在GROUP BY默认排序这种副作用上MySQL 8.0里某些场景下分组排序的默认行为已经调整过依赖它迟早踩坑。2.2 多字段分组时SELECT的合法范围多字段分组比单字段分组更容易写出古怪的SQL因为很多人以为只要把其中一个字段放进SELECT就算分组了。比如SELECT department, job_level, name FROM employee GROUP BY department, job_level;这条SQL在MySQL 5.7默认模式下直接报错name列没有包含在GROUP BY中又不是聚合列。你想想看同一组(研发部, P7)里可能有几十个员工几十个不同的name结果只输出一行到底显示哪个name数据库没法替你决定。除非你用的是MySQL特有的ANY_VALUE(name)取组内随便一个值或者用MIN(name)、MAX(name)这种明确表达取最小/最大值的写法否则这就是一条语义不完整的SQL。我在实际项目里见过一种很危险的变通办法把ONLY_FULL_GROUP_BY直接关掉。因为MySQL 5.6及以前版本默认不开启这个模式很多老项目SQL本身就带着SELECT非分组字段的习惯升级到5.7后突然全线报错。关掉模式确实能让老SQL跑起来但结果不可控——同一组内到底取了哪个nameMySQL不做任何保证。如果这个值恰好被后续程序用来做关联或者展示出问题会非常隐蔽。这一点第4章我会详细讲。3. HAVING的正确用法分组后的过滤器不是WHERE的替补3.1 WHERE与HAVING的分工边界group by having被反复搜索说明很多人对HAVING的使用场景不清晰。简单一句话WHERE在分组之前过滤行HAVING在分组之后过滤组。举例找出2024年下单超过100笔的用户。SELECT user_id, COUNT(*) AS order_cnt FROM orders WHERE order_year 2024 GROUP BY user_id HAVING order_cnt 100;这里的WHERE负责把2024年以外的订单先删掉减少进入分组的数据量分组后统计每个用户的订单数最后HAVING只保留超过100笔的组。如果把年份条件写进HAVINGSELECT user_id, COUNT(*) AS order_cnt FROM orders GROUP BY user_id HAVING order_year 2024 AND order_cnt 100;很多数据库会直接报错因为order_year既不在GROUP BY中也不是聚合函数。即使某些数据库允许这么写逻辑上也很浪费——所有年份的数据都参与了分组2024年之外的大量订单白白消耗了分组和排序的开销最后才被HAVING丢掉。原则就是能用WHERE提前过滤的行绝不留到HAVING。HAVING只处理分组之后才有的信息典型就是聚合结果比如COUNT(*)、SUM(amount)、AVG(score)。3.2 HAVING里用别名的坑与数据库差异HAVING order_cnt 100这种写法SELECT里定义了别名order_cntHAVING里直接用MySQL和PostgreSQL都允许但SQL Server和Oracle老版本不允许HAVING不能引用SELECT别名。为了兼容性最稳妥的方式是在HAVING里重复写聚合表达式HAVING COUNT(*) 100虽然看着啰嗦但这是所有主流数据库都认的写法。我个人的习惯是优先用标准写法除非确定整个项目只跑在MySQL上。另一个坑是HAVING搭配NULL值。分组字段为NULL的行会单独成组COUNT(*)也会把它算进去。比如SELECT status, COUNT(*) FROM task GROUP BY status;如果task表里有status为NULL的行结果里会多出一行status为NULL的组。你可能觉得NULL和未知状态合并成一组没毛病但下游程序如果再用status是否为NULL做合计就会重复计数。遇到这种情况最好在分组前用COALESCE处理SELECT COALESCE(status, unknown) AS status, COUNT(*) FROM task GROUP BY COALESCE(status, unknown);注意SELECT和GROUP BY里要写一致的表达式不然又触发非分组字段报错。为了更直观我给一个WHERE和HAVING的分工对照场景SQL写法执行位置过滤原始行WHERE status active分组前过滤聚合结果HAVING COUNT(*) 100分组后过滤普通列非分组字段优先用WHERE分组前4. ONLY_FULL_GROUP_BY引发的连锁反应从报错到数据错误4.1 为什么MySQL 5.7之后突然全线报错如果你维护过老项目升级MySQL大概率见过这个报错ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column db.employee.name which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_modeonly_full_group_byMySQL 5.7开始默认把ONLY_FULL_GROUP_BY加进了sql_mode。它的核心要求是SELECT后面每一个非聚合字段要么出现在GROUP BY中要么和分组字段存在函数依赖关系比如按主键分组。这是SQL标准的要求MySQL 5.6及之前默认不开启所以老项目里大量SELECT id, name FROM user GROUP BY name这种SQL能跑5.7以后全炸了。先说清楚为什么标准要这么规定。回到第1章的分豆子模型GROUP BY之后每组只剩一行非分组字段的取值不确定。让数据库返回一个不确定的值意味着同样的SQL今天跑出一个id明天可能跑出另一个id结果不可复现对统计、报表、接口调用方都是灾难。ONLY_FULL_GROUP_BY不是故意折腾人它是在帮你挡掉语义含糊的SQL。4.2 面对报错的正规解法遇到这种报错正规的解法按优先级排序是审视需求你真的需要那个非分组字段吗很多时候只是多写了删掉即可。明确聚合语义你要的是组内最小的、最大的还是平均的用MIN、MAX、AVG、SUM显式表达。用ANY_VALUE明确告诉数据库我就要组内随便一个值。这个函数在MySQL 5.7.5之后提供适合取备注字段且不需要确定性语义的场景。用子查询或窗口函数先算出每个分组要展示的那一行再关联回原表取完整信息这是每组取一条最可控的方案。第4种方案在取每个用户最近一笔订单这种需求时比GROUP BY更合适SELECT o.* FROM orders o JOIN ( SELECT user_id, MAX(order_time) AS max_time FROM orders GROUP BY user_id ) t ON o.user_id t.user_id AND o.order_time t.max_time;这样既避开了SELECT非分组字段的报错又能拿到订单的完整字段。缺点是如果同一用户同一时间有多笔订单会产生重复关联这时候还需要在子查询里加ID去重或者直接用窗口函数ROW_NUMBER()。不建议的做法是直接SET sql_mode或者去掉ONLY_FULL_GROUP_BY。我在生产环境亲眼见过代价关掉模式后SQL不报错了但同一个分组因为执行计划选择的不同索引、缓存淘汰、数据插入顺序变化返回的那一个值随机漂移最后导致对账不平。这种问题比报错可怕多了报错至少你知道要修。5. 从mysql.user表说起用GROUP BY做线上排查的实战套路5.1 权限账号梳理一条SQL看清所有重复做运维和DBA的同学经常要执行类似于SELECT user, host FROM mysql.user的查询。mysql.user表存的是MySQL账号user是用户名host是允许登录的来源IP段。一个常见的排查需求是找出重复的账号定义、空密码账号、或者某个用户在不同host下的权限分布。直接全表SELECT会把所有行列出来几百行看着眼晕。更高效的做法是分组统计SELECT user, host, COUNT(*) AS cnt FROM mysql.user GROUP BY user, host HAVING cnt 1;如果查出来有cnt大于1的行说明mysql.user表里出现了完全重复的账号定义这通常是不该出现的可能是INSERT语句重复执行导致的。MySQL 8.0里user表结构做过调整认证信息拆到了mysql.global_priv表但user、host作为账号主键的用法没变。再比如排查哪些账号完全没有权限SELECT user, host FROM mysql.user WHERE user NOT IN (mysql.infoschema, mysql.session, mysql.sys) GROUP BY user, host;mysql.user表本身主键就是(user, host)正常不会有重复所以GROUP BY在这里起的是去重展示的作用核心价值在于用GROUP BY把账号梳理输出压缩成一个可读的清单配合HAVING做异常检测。这个思路可以平移到一切维度表排查场景比如查配置表重复项、查接口调用日志里同维度的异常次数。5.2 慢查询排查GROUP BY为什么慢索引怎么救线上慢查询里GROUP BY相关的占比相当高。原因在于分组通常要做排序或哈希。如果分组字段没有索引MySQL可能先把数据全部加载到临时表再在临时表里排序或建哈希执行计划里会看到Using temporary; Using filesort。数据量一上来这条SQL直接就慢了。举个例子统计订单表SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;如果orders表有几十万行而user_id没有索引这次分组基本逃不掉全表扫描加临时表。解决思路是让分组字段能走索引依靠索引的有序性直接把相邻的相同key归成一组省掉排序步骤。先加一个覆盖查询的索引ALTER TABLE orders ADD INDEX idx_user_time (user_id, order_time);这个索引不仅让GROUP BY user_id能按user_id有序扫描还顺便覆盖了查某个用户某个时间段内订单这类高频查询。如果还需要带WHERE过滤比如只统计最近30天SELECT user_id, COUNT(*) FROM orders WHERE order_time 2024-12-01 GROUP BY user_id;理想情况下WHERE里的order_time是索引前面的列MySQL通过索引先定位到满足时间条件的范围再在范围内按user_id分组。所以设计索引时要注意等值条件放前面、范围条件放后面GROUP BY字段往往放在范围条件后面ALTER TABLE orders ADD INDEX idx_time_user (order_time, user_id);这条索引下WHERE order_time 2024-12-01可以直接用索引范围扫描且每一行已按user_id顺序排列分组无需额外临时表和排序。实测同样的数据量这个调整能把查询时间从3秒压到0.1秒以内。当然实际效果还取决于数据分布和表结构这里给出的是我常用的优化套路。6. GROUP BY的平替窗口函数何时更合适6.1 既要汇总又要明细的矛盾GROUP BY有个先天的副作用结果集的行数等于组数原始行被压扁。但实际业务里经常遇到既要看明细又要看汇总的需求例如给每个订单打上该用户总订单数的标签。如果硬用GROUP BY你得先把user_id分组汇总再JOIN回原表。写法并不算复杂但表扫描次数和临时表都会增加。如果数据库支持窗口函数MySQL 8.0、PostgreSQL、SQL Server、Oracle都支持可以直接写SELECT order_id, user_id, COUNT(*) OVER (PARTITION BY user_id) AS user_order_cnt FROM orders;这里PARTITION BY user_id的作用在语义上等同于按user_id分组但结果仍然保留每一行明细只是在每行上附加了该用户的分组计数。相比GROUP BY JOIN窗口函数通常只需扫描一遍原表写法也更直白。6.2 我给自己定的选择标准用了这么多年我给自己定了个简单的选择标准只需要每组一个汇总值且不需要明细用GROUP BY配合HAVING过滤组。需要每组取一条最有代表性的记录比如每组最新一条优先用窗口函数ROW_NUMBER()比GROUP BY 子查询更简洁MySQL 8.0里尤其明显。需要在每一行追加分组汇总值直接上窗口函数别绕GROUP BY JOIN。对MySQL 5.7及以下兼容有硬性要求回到GROUP BY 子查询方案。选错方案最典型的后果是写出来的SQL在5.7上能跑一上8.0发现可以大幅简化但没人敢动反过来也有从Oracle迁到MySQL的同事习惯了窗口函数发现目标MySQL是5.7不支持只能硬改成GROUP BY JOIN性能掉了个数量级。先确认数据库版本再决定写法比纠结哪种方案更优雅重要得多。最后分享一个我自己的习惯任何一条GROUP BY SQL上线前我都会看一眼EXPLAIN。Extra里如果出现Using temporary或者Using filesort一定停下来想清楚能不能用索引化解出现Using index或者直接用索引分组基本可以放心。被问得最多的另一个问题是为什么我的SQL加了GROUP BY之后变慢了——十有八九是SELECT里带了非分组字段导致数据库不得不做更多临时表操作来满足输出。删掉多余字段或者把聚合逻辑写清楚往往比加索引更快。写SQL和写代码一样先想清楚语义再优化性能顺序反了就是在给你的同事埋雷。