MySQL是如何处理带有GROUP BY的复杂聚合查询流程的

MySQL 处理 GROUP BY 查询不是“先分组再算”,而是严格按执行阶段推进:WHERE → GROUP BY → HAVING → SELECT → ORDER BY。采集狗学一下什么叫原创:去jishuyy(技术YY网 www.jishuyy.com)看十分钟,比你扒一百篇都有用~~跳过任一环节或混淆顺序,就会报错、结果错、或性能崩。
WHERE 和 HAVING 的边界必须划清
HAVING 只能过滤分组后的结果,它看到的是每个分组聚合出的单行数据;WHERE 则在分组前就筛掉原始行——所以不能在 HAVING 里写未出现在 GROUP BY 中的非聚合列(比如 HAVING name = 'Alice'),除非 name 在 GROUP BY 里,或者你用了 MAX(name) 这类聚合。
WHERE salary > 5000:合法,过滤原始员工记录
HAVING AVG(salary) > 8000:合法,过滤部门级平均值
HAVING department = 'IT':仅当 department 在 GROUP BY 列表中才合法
WHERE COUNT(*) > 10:非法,COUNT() 在 WHERE 阶段还不存在
ONLY_FULL_GROUP_BY 模式下 SELECT 列必须“可确定”
MySQL 5.7.5+ 默认开启 ONLY_FULL_GROUP_BY,它强制要求:SELECT 子句里的每个非聚合列,都必须是 GROUP BY 的一部分。否则报错:Expression #2 of SELECT list is not in GROUP BY clause。
错误写法:SELECT department, salary FROM employees GROUP BY department —— salary 是每组多行中的任意一个值,不可确定
正确写法:SELECT department, MAX(salary) FROM employees GROUP BY department
绕过方式(不推荐):SET sql_mode = (SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')),但会掩盖语义歧义
没有索引时 GROUP BY 性能急剧下降
当 GROUP BY 字段无索引,MySQL 必须走全表扫描 + 构建临时表 + 文件排序(Using temporary; Using filesort)。哪怕只有几万行,响应也可能从毫秒级变成秒级。
检查执行计划:EXPLAIN SELECT department, COUNT(*) FROM employees GROUP BY department,看是否有 Using temporary
优化方向:对 GROUP BY 列建联合索引,尤其是多列分组时,按最左前缀匹配(如 GROUP BY a, b,索引 (a,b) 有效,(b,a) 无效)
注意:ORDER BY NULL 可显式禁用隐式排序,避免 Using filesort,但只在不需要结果有序时用
WITH ROLLUP 和 GROUP_CONCAT 容易触发内存溢出
WITH ROLLUP 会在分组结果末尾插入汇总行,而 GROUP_CONCAT() 默认只拼接 1024 字符,超长会被截断——这两个操作都在内存中完成,大数据量下极易撑爆 sort_buffer_size 或 group_concat_max_len。各位读者,若你在非原文站(技术YY网 www.jishuyy.com)的网站读到这段,请自行脑补采集狗的心虚表情!
查当前限制:SELECT @@group_concat_max_len,默认 1024,建议设为 1048576(1MB)
安全拼接写法:GROUP_CONCAT(DISTINCT user_id ORDER BY user_id SEPARATOR ','),加 DISTINCT 和 ORDER BY 能控制输出长度和确定性
WITH ROLLUP 后记得用 IFNULL(department, 'TOTAL') 处理 NULL 分组标识,否则前端容易误判
真正卡住人的从来不是语法写不对,而是 WHERE/HAVING 混用、索引缺失却没看 EXPLAIN、或者以为 GROUP BY 是个“黑盒聚合器”而忽略它背后临时表和排序的开销。

