最近一直被业务方追着要各种统计报表越写越发现MySQL里最常用、也最容易被写错的就是聚合、分组、联合这三类查询。它们能应付从日常看板、月度对账到后台分析的大部分需求但同时也是慢查询和“莫名报错”的重灾区。网上单讲某个函数的文章很多能把它们串起来、告诉你每步该注意什么的却很少。这篇文章就用实际项目里最常见的写法把这三类查询的底层逻辑、坑点和性能优化一次讲透。1. 聚合查询先把“算总数”这件事做对1.1 五个核心聚合函数的使用边界聚合查询的核心就是聚合函数我常用的就五个COUNT、SUM、AVG、MAX、MIN。它们接收一组值返回一个汇总结果配合在SELECT列里使用实现“按某些条件统计总量”的需求。先说COUNT它有几种写法结果差别很大。COUNT()统计的是记录行数不管某列是不是NULL都计入COUNT(列名)只统计该列非NULL的行数COUNT(1)和COUNT()效果基本一样只是不取具体列的值谁快谁慢在MySQL里差别很小但我习惯写COUNT(*)表示“统计行数”写COUNT(字段)表示“统计非空值数量”。这里有个经典场景如果统计客户下单次数客户ID有没有NULL直接影响结果用错就会少算一批数据。SUM和AVG都天然忽略NULL值NULL参与运算会直接让结果变NULL所以要对列做IFNULL或COALESCE预处理。一个常见的报名统计例子用户表里有个“年龄”字段有的用户没填SUM(年龄)很顺畅AVG(年龄)也没问题因为NULL都被跳过了。但如果你想统计“有多少人的年龄大于平均年龄”直接拿AVG结果去比较时没填年龄的行不会报错只是被忽略容易让你误以为所有行都参与了判断。MAX和MIN相对省心字符串、数字、日期都能比大小但不要以为只能数值列用。比如查“最近一次登录时间”直接用MAX(login_time)就行。还有一个容易被忽略的点聚合函数可以组合使用一条SQL里同时放SELECT COUNT(*), SUM(amount), AVG(amount), MAX(amount), MIN(amount)每个函数独立作用于同一结果集互不干扰这在做汇总报表时很实用。1.2 聚合前先想清楚的三个问题第一个问题要不要去重统计“有多少用户看过这个商品”和“有多少次浏览记录”是两个概念前者要去重用COUNT(DISTINCT user_id)后者直接COUNT(*)就行。DISTINCT可以放在任意聚合函数里面比如SUM(DISTINCT price)意思是只对不重复的price求和但实际业务里很少这么用因为同价格商品本来就多种多样一下就把金额算小了。第二个问题过滤条件放在哪一层普通WHERE在聚合之前过滤聚合之后还想过滤就得用HAVING后面分组查询里详细说。但这里先提醒一点WHERE里能过滤掉的数据越多聚合函数处理的数据越少性能越好所以能用WHERE千万别让数据堆积到聚合之后。第三个问题临时类型转换。有时聚合前需要先处理字段类型比如“订单金额”是 VARCHAR 存储的直接SUM会出现隐式转换或者报错要先CAST(amount AS DECIMAL(10,2))在查询里写清楚类型转换比让数据库猜安全得多。2. 分组查询把数据切片后再统计2.1 GROUP BY 的执行顺序和 ONLY_FULL_GROUP_BY分组查询是聚合查询的进阶玩法先按某个字段把数据分成几堆再对每一堆分别做聚合。它的执行顺序是FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT很多人直接按书写顺序理解SQL结果得出错误结论这里要格外注意。GROUP BY后面跟的是“分组依据”通常是一个或多个字段例如GROUP BY department, status。分完组后SELECT里能查询的列就只有两类分组字段本身、聚合函数计算结果。问题是MySQL默认开启了ONLY_FULL_GROUP_BY模式一旦一个非聚合列既不在GROUP BY里也不在聚合函数里直接报错SELECT user_id, order_time, SUM(amount) FROM orders GROUP BY user_id;这条SQL在ONLY_FULL_GROUP_BY开启时就会报错列order_time必须在GROUP BY里或聚合函数里。这条规则很多人觉得烦但它是防止错误查询意外的因为同一组里order_time很可能有多条取值选出哪条都是“随机决定”语义不明确。真要获取某组某个字段的具体值方案是明确写成MAX(order_time)或MIN(order_time)告诉数据库你要取这组里的最大值或最小值。2.2 HAVING 和 WHERE 的天然分工WHERE筛选的是原始记录行HAVING筛选的是分组之后的聚合结果。同一个字段可以用两次先WHERE过滤掉部分原始数据再HAVING过滤不满足条件的组。举个例子统计每个部门里工资高于一万的人数并且只要人数超过20的部门SELECT department_id, COUNT(*) AS cnt FROM employees WHERE salary 10000 GROUP BY department_id HAVING cnt 20;这条SQL的关键点在于WHERE先干掉小于一万的工资记录减少参与分组的行数之后COUNT统计的就是“高工资人数”最后HAVING把人数不足20的部门组丢弃。这里有个常见误区有人想过滤聚合结果时不写HAVING而把条件塞进WHERE写完发现结果不对很大概率就是WHERE执行太早分组还没发生自然不知道组内有多少行。反过来HAVING里也不要放能在WHERE解决的简单条件因为HAVING是在分组和聚合之后执行数据量早被放大好几倍性能上不划算。2.3 GROUP_CONCAT把组内数据拼成一行除了统计数量分组查询还常用于“把组内某字段拼起来”典型场景是一个订单号对应多个商品名想要一行展示“订单号 商品列表”。这个需求用GROUP_CONCAT最顺手SELECT order_id, GROUP_CONCAT(product_name ORDER BY product_id SEPARATOR 、) FROM order_items GROUP BY order_id;GROUP_CONCAT默认用逗号分隔可以指定SEPARATOR改变分隔符可以加ORDER BY控制拼接顺序还能配合DISTINCT去重。但它的输出长度受系统变量group_concat_max_len限制默认1024字节拼接结果太长会被截断。做报表时常常需要提前调大SET group_concat_max_len 102400;否则纯文本列表看起来像被“拦腰斩断”。2.4 用ROLLUP做小计GROUP BY做的结果集是“每个分组一行”但报表里经常还要“所有分组的总计”。用WITH ROLLUP可以直接在分组结果后面追加一个小计行SELECT department_id, COUNT(*) AS cnt, SUM(salary) AS total FROM employees GROUP BY department_id WITH ROLLUP;结果最后一行department_id是NULLcnt是全表行数total是工资总额。要注意的是小计行的分组字段是NULL页面展示时千万别直接当“某个部门”渲染通常要在外层查询里判断是否为NULL再替换成“总计”文字。3. 联合查询把多张表拼起来用3.1 JOIN连接INNER、LEFT、RIGHT、CROSS的取舍业务数据很少都在一张表里订单表、用户表、商品表天然分开查询时要“拼”在一起。JOIN是横向拼接把两张表按连接条件合并成一张宽表。最常用的是INNER JOIN只保留两边都匹配的行、LEFT JOIN保留左表全部行右表没有匹配则补NULL、RIGHT JOIN保留右表全部行左表没有匹配则补NULL。CROSS JOIN是笛卡尔积实际业务慎用一不小心就是几十万乘以几十万的巨大结果集。我平时工作里LEFT JOIN用得最多因为“主表全保留明细表可缺失”的语义很符合业务例如“所有用户及其订单”没下过单的用户也要展示用户表就是左表。INNER JOIN则适合“只要有效订单”比如统计已支付订单对应的用户信息两表都有记录才算数。RIGHT JOIN的理论存在但实践中几乎可以用LEFT JOIN调换顺序替代可读性反而更好。写JOIN时连接条件用ON结果集中的过滤条件用WHERE。这两个容易混但语义差异很大ON决定行怎么匹配WHERE决定匹配后留下哪些行。拿LEFT JOIN举例如果把右表过滤条件写在WHERE里会神奇地丢失左表中原本要保留的行因为WHERE是在连接完成后执行右表不匹配时是NULLNULL不等于任何值行就被干掉了。3.2 自连接和“一对多”引起的重复行自连接就是表自己和自己连接常用于内部有层级关系的表比如员工表里有上级ID想查“员工姓名 上级姓名”直接把员工表当两张表用SELECT e1.name AS emp_name, e2.name AS manager_name FROM employee e1 LEFT JOIN employee e2 ON e1.manager_id e2.id;还有个高频事故左表一行匹配右表多行结果集直接变多倍。这就是“一对多连接带来的重复行”。第一次统计订单金额时我发现SUM怎么偏大好几倍排查到最后发现订单明细表里一个订单有多条商品JOIN之后订单主表每行被复制了多次再对订单总额聚合自然爆炸。解决办法是在聚合之前先按订单主表维度汇总或者再加上DISTINCT或者干脆拆成两步查询。3.3 UNION和UNION ALL纵向拼接结果集JOIN是横向把列拼起来UNION是纵向把行拼起来它把多个SELECT的结果上下合并成一张大表。最常用的场景是“分月看板”每个月单独查销售数据再用UNION ALL拼到一起SELECT 2024-01 AS month, SUM(amount) AS total FROM orders_202401 UNION ALL SELECT 2024-02, SUM(amount) FROM orders_202402 UNION ALL SELECT 2024-03, SUM(amount) FROM orders_202403;UNION和UNION ALL的区别是前者会自动去重后者完全保留重复行。别小看这一点UNION自带DISTINCT逻辑要去重就要排序或建临时表数据量大时性能明显下降。业务上如果明确各行不会重复直接UNION ALL。UNION还有两个硬性要求每个SELECT的列数必须一致对应列的类型要兼容。列名以第一个SELECT的别名为准。有个比较隐蔽的问题如果union各段查询里ORDER BY位置写错整个排序会失效需要先把分段查询包一层子查询或者在最外层统一ORDER BY否则结果顺序不可预期。4. 组合实战一个场景打通三个知识点4.1 业务场景和数据表设计理论讲再多不如跑一遍完整例子。我这里虚构一个常见的电商数据模型用户表usersid、name、register_date、city、订单表ordersid、user_id、amount、status、order_date、订单明细表order_itemsid、order_id、product_name、quantity、price。实际需求是统计2024年各月、各城市的下单金额和订单数最后展示给运营看板。这个需求需要分组、聚合、连接三个能力同时上场。4.2 第一步先做单表聚合如果只要订单表自己的统计可以先按月份和状态聚合SELECT DATE_FORMAT(order_date, %Y-%m) AS month, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE status paid GROUP BY DATE_FORMAT(order_date, %Y-%m);这一步把订单先按月份分组聚合出每个月的订单数和总金额。注意GROUP BY用的是DATE_FORMAT(order_date, %Y-%m)这是一个表达式不是原字段所以在SELECT里也用同样的表达式才能对应上。很多人在这里把GROUP BY写成order_date本身结果每个月还是按“天”分组数据被拆碎。4.3 第二步连接用户表补充城市维度现在要按“城市”看数据就必须把用户表拼进来。订单表里只有user_id城市的唯一来源是users.city。于是JOIN加上再调整GROUP BYSELECT DATE_FORMAT(o.order_date, %Y-%m) AS month, u.city, COUNT(*) AS order_cnt, SUM(o.amount) AS total_amount FROM orders o INNER JOIN users u ON o.user_id u.id WHERE o.status paid GROUP BY month, u.city;这里GROUP BY可以写别名month吗MySQL的GROUP BY允许引用SELECT中的别名但有时可读性差反而容易出错尤其涉及复杂表达式时同样内容在GROUP BY里写两次很容易让审查者困惑。我的建议是短查询写别名方便复杂查询里统一写表达式原样。4.4 第三步用HAVING筛选有效分组比如运营只想知道月订单量超过10的城市组合就需要加上HAVING过滤SELECT DATE_FORMAT(o.order_date, %Y-%m) AS month, u.city, COUNT(*) AS order_cnt, SUM(o.amount) AS total_amount FROM orders o INNER JOIN users u ON o.user_id u.id WHERE o.status paid GROUP BY month, u.city HAVING order_cnt 10;这个场景就非常典型WHERE在连接和分组前就把未支付的订单扔掉减少参与分组的行数HAVING在分组统计后发现某个月某城市只有几单直接丢弃。4.5 第四步用UNION ALL合并多段统计数据如果订单数据按月份放在不同物理表虽然是糟糕设计但历史系统里确实存在展示全年趋势就需要UNIONSELECT Q1 AS quarter, month, city, order_cnt, total_amount FROM ( SELECT DATE_FORMAT(order_date, %Y-%m) AS month, u.city, COUNT(*) AS order_cnt, SUM(o.amount) AS total_amount FROM orders o JOIN users u ON o.user_id u.id WHERE o.status paid AND order_date BETWEEN 2024-01-01 AND 2024-03-31 GROUP BY month, u.city ) t1 UNION ALL SELECT Q2, month, city, order_cnt, total_amount FROM ( SELECT DATE_FORMAT(order_date, %Y-%m) AS month, u.city, COUNT(*) AS order_cnt, SUM(o.amount) AS total_amount FROM orders o JOIN users u ON o.user_id u.id WHERE o.status paid AND order_date BETWEEN 2024-04-01 AND 2024-06-30 GROUP BY month, u.city ) t2;这么写的好处是方便给每个季度一个统一标签多段查询保持列结构一致外层看板拿到结果直接渲染。4.6 性能表现和索引影响跑同样逻辑时大表查询快不快要看索引和连接顺序。这个例子里的过滤条件是order_date和status连接条件user_id所以orders表最需要的是idx_status_datestatus和order_date联合索引、idx_user_iduser_id索引users表主键自带索引。老版本MySQL里写WHERE o.status paid AND order_date BETWEEN ...时把选择性更高的条件放前面更稳新版本优化器基本不用太操心。如果发现JOIN后聚合特别慢还要留意是不是在连接前没过滤掉无用数据。例如用户表中大量测试账号最后一个外层的WHERE u.city ! 测试可以让JOIN阶段减少很多行。连接后聚合的数据集越小聚合越快这个道理和前面WHERE的优先级一脉相承。5. 高频错误速查与性能调优心得5.1 报错和异常结果速查表现象可能原因解决方案报错only_full_group_bySELECT里有非分组列且不在聚合函数中把列加入GROUP BY或用MAX/MIN/ANY_VALUE包裹查出来的订单金额比实际大很多一对多JOIN造成重复行再对主表字段聚合先单表聚合再JOIN或对明细表去重LEFT JOIN后左表行数变少右表的过滤条件误写在WHERE里把过滤条件移到ON或改成子查询再连接HAVING里用不了WHERE能解决的字段把普通字段放到了HAVING里逻辑没问题但性能差尽量提到WHEREHAVING只放聚合条件UNION查询结果顺序不对分段查询内单独ORDER BY外层未排在外层统一ORDER BYGROUP_CONCAT结果被截断超出group_concat_max_len限制调大系统变量或限制拼接长度聚合函数返回NULL源数据全是NULL或表内无匹配行用IFNULL/COALESCE兜底或先确认WHERE条件明明有数据但统计不到数据类型不一致例如订单金额是字符串且带空格先TRIM/CAST清洗再聚合5.2 关于索引和慢查询的几条经验第一WHERE后面写的函数。如果对索引列做了函数计算比如DATE_FORMAT(order_date,%Y-%m) 2024-01MySQL很难直接命中索引通常要做全表扫描。优化办法是改成范围条件order_date 2024-01-01 AND order_date 2024-02-01这样反而更清晰对索引友好是在生产环境里更稳妥的写法。第二GROUP BY字段的索引。如果经常按uid分组就要给uid建索引因为分组本质上要做排序或哈希有序数据能让它快很多。但联合索引最左前缀的规则很现实如果查询里用GROUP BY (a, b)那么索引也要从a开始否则还是全扫描。第三别在SELECT列里写一大串聚合函数还不看执行计划。一条SQL里有COUNT、SUM、AVG、MAX、MIN好几个聚合函数联合使用时确实可以一气呵成但数据量上了千万级可以先EXPLAIN一下看是不是扫了太多行再考虑是否拆成多条查询。执行计划里type是ALL时就该考虑加索引了。第四临时表的使用。遇到复杂联合查询时我经常把要聚合的表先在子查询里缩到很小再JOIN其他表。“先缩小结果集再连接”这条原则对慢查询挽救效果极好。比如订单表上亿条要先按订单ID过滤出近三个月的订单才能和用户表JOIN。6. 写在最后的实操习惯聚合查询、分组查询、联合查询是SQL里最常用也最容易被低估的技能。我把平时踩坑的教训浓缩成几条习惯写查询前先倒推执行顺序搞清楚WHERE什么时候执行、GROUP BY怎么分组、HAVING能不能用写JOIN时就先想清楚是一对多还是一对一避免重复行悄悄放大SUM的结果写UNION时默认用UNION ALL除非你确实需要去重新写统计SQL之前一定EXPLAIN看一眼有没有走全表扫描。这些习惯不是一天养成的但每改一次后续排查和调优都能省下大量时间。你可以拿手头一个真实的订单表或用户表跑一遍上面的例子跑完就会理解为什么同样一条统计有人能三分钟写完有人要折腾半小时还结果不对。SQL不难难在把每条语句执行的顺序和结果集的变化过程想透彻。
