几个月前帮一个朋友调他手里的报表系统发现几个看起来差不多的统计SQL跑出来的结果就是不对。有的是重复计数有的是分组分错还有两个查询结果根本对不上。最后排查下来问题全出在聚合函数、分组查询和联合查询这几个基础环节上。MySQL基础扎实不扎实真不是看你会不会写SELECT而是看你能不能把这些组合用对、用稳。这篇文章就把这三块内容拿出来彻底捋一遍从原理到语法从常见误区到排查手法结合我实际踩过的坑来讲看完应该能帮你把统计类SQL写得更干净、更不容易出幺蛾子。1. 聚合函数统计报表的核心工具聚合函数干的事情本质就是把“多行数据压缩成一行结果”。它不返回明细只返回汇总值。这也是它跟普通函数最大的区别——普通函数是“一行进一行出”聚合函数是“多行进一行出”。MySQL里最常用的聚合函数就五个COUNT、SUM、AVG、MAX、MIN。业务上百分之九十以上的统计需求都是靠它们完成的。1.1 COUNT的三种写法到底差在哪COUNT可能是大家最熟悉、也最容易写错的聚合函数。最常见的就是COUNT(*)、COUNT(1)、COUNT(字段名)这三者在开发中反复出现网上的争论也从来没停过。先说结论COUNT(*)统计的是“表的行数”不管这行里的字段是不是NULL只要这张表里有这一行就算进去。COUNT(1)统计逻辑和COUNT(*)一样也是统计所有行数只是写法上换成了一列常量1。COUNT(字段名)统计的是“这个字段非NULL值的个数”只要字段值是NULL这一行就不计数。举个例子你就明白了。假设有一张员工表里面有5条记录其中“部门编号”字段有1条是NULLSELECT COUNT(*), COUNT(1), COUNT(dept_id) FROM employee;结果是5、5、4。这就是COUNT(字段名)最经典的坑——你以为数的是有多少人结果因为有人没填部门编号直接从计数里消失了报表上的人数跟实际就对不上。至于COUNT()和COUNT(1)的性能之争在现代MySQL版本里已经没什么意义了。InnoDB引擎对COUNT()做了专门的优化不需要逐行判断字段是否为空直接按最小成本扫描。COUNT(1)也一样所以两者在性能上基本没有差别。真正要注意的是业务上“统计记录数”用COUNT(*) “统计某个属性有值的数量”用COUNT(字段名)。1.2 SUM和AVG的NULL处理机制SUM求和。AVG求平均。这俩一看名字就懂但它们对NULL的处理方式经常让人在报表结果上栽跟头。先看SUM。SUM函数会自动忽略NULL值也就是说NULL不会参与加法计算。“1 NULL 3”在普通算术里结果是NULL但SUM会把它当成“1 0 3”计算出4。这个机制本身是合理的但你心里必须有数——SUM里的NULL不会被当成0去参与计算只是被跳过了。所以如果你的业务规则是“缺勤按0分计入总分”那光靠SUM是搞不定的你得先用IFNULL或COALESCE把NULL转成0再做聚合逻辑才正确。再看AVG。很多人以为AVG是先SUM再除以COUNT(*)其实不是。AVG的计算方式是“SUM(非NULL值) / COUNT(非NULL值)”。分母是“过滤掉NULL后的个数”不是总行数。比如5个学生考试1个人缺考成绩为NULL其余4人成绩分别是80、90、70、60。AVG(score)计算出来是(80907060)/4 75而不是除以5得60。这个逻辑本身没毛病但要看你业务上到底是想算“参加考试的人的平均分”还是“全班所有人的平均分”。如果是后者就得先补NULLSELECT AVG(IFNULL(score, 0)) FROM exam;1.3 MAX/MIN不只是能用数字MAX和MIN是很多人的“舒适区”觉得就是求最大最小值没什么可讲的。实际上它们可以作用在日期、字符串、枚举类型上。在MySQL里MAX对日期会按时间先后取“最晚”的那个MIN取“最早”的。字符串会按字典序比较大小。如果一张订单表里你直接SELECT MAX(order_date)、MIN(order_date)就能拿到第一单和最后一单的日期不用先排序再LIMIT效率高得多。我自己经常用的一种组合是MAX和MIN搭配DATEDIFF算订单跨度SELECT DATEDIFF(MAX(order_date), MIN(order_date)) AS active_days FROM orders WHERE user_id 1024;一条语句就能算出某个用户从第一单到最后一单中间隔了几天连临时表都不用建。这种“聚合函数直接参与表达式计算”的用法比先查出来再在应用层计算省事太多。1.4 聚合函数与去重统计的搭配技巧业务里经常遇到“去重计数”的需求比如统计有多少个不同的用户下了订单。这时候就要用COUNT(DISTINCT 字段)SELECT COUNT(DISTINCT user_id) FROM orders;这个写法很直观但要注意两点。第一COUNT(DISTINCT 字段)也会自动忽略NULL原理和COUNT(字段名)一致。第二这个函数在数据量大时性能会比较差因为它需要在内存里维护一个去重集合。如果表的数据量超过几百万行去重字段又没有索引一次查询可能要跑好几秒甚至更久。遇到这种情况如果需要“精确去重统计”可以把大表按业务维度拆成小结果集再聚合或者考虑用汇总表提前算好。如果业务上允许一定误差也可以考虑用近似算法比如HyperLogLog这种方案不过MySQL原生没有直接提供一般得借助中间件或单独的服务。对绝大多数中小型系统来说直接COUNT(DISTINCT ...)配合合理索引就够了。超过千万行还要精确去重那已经不是MySQL单表该干的事了得从表结构设计上重新考虑。还有一个偏技巧性的场景就是“同一张表里按多个条件分别统计”可以用SUM配合CASE WHEN一次查出所有汇总SELECT SUM(CASE WHEN type new THEN 1 ELSE 0 END) AS new_cnt, SUM(CASE WHEN type old THEN 1 ELSE 0 END) AS old_cnt, COUNT(*) AS total_cnt FROM orders;这样只扫一遍表就把多个统计口径的结果同时拿出来了。比写三条独立SQL再去应用层合并效率高一截代码也清晰。2. 分组查询把汇总结果按维度拆开聚合函数解决的是“整体汇总”但业务报表上更常见的是“分维度汇总”——按部门统计人数、按月统计销售额、按城市统计订单量。这就轮到GROUP BY出场了。2.1 GROUP BY的执行顺序是理解的钥匙好多SQL写的烂根子不在于语法记没记住而在于脑子里没有那张“执行顺序图”。GROUP BY的执行顺序尤其重要。MySQL的SQL执行顺序简略版是这样的先FROM确定从哪张表取数据再WHERE过滤掉不满足条件的行然后GROUP BY把剩下的行按指定字段分组再聚合对每一组分别计算聚合函数接着HAVING过滤掉不满足条件的分组再SELECT把需要的列取出来之后ORDER BY排序最后LIMIT限制返回条数记住这个顺序很多纠结的点都能迎刃而解。比如经典的问题WHERE和HAVING有什么区别答案就在执行顺序里——WHERE在分组前过滤行HAVING在分组后过滤组。WHERE过滤的行不会进入聚合计算HAVING过滤的是已经聚合完的分组结果。SELECT dept_id, COUNT(*) FROM employee WHERE status active GROUP BY dept_id HAVING COUNT(*) 10;这段SQL的逻辑是先只保留在职员工然后按部门分组最后只返回人数超过10的部门。WHERE里的status是针对原始行的条件HAVING里的COUNT(*) 10是针对分组结果的约束两者阶段完全不同。2.2 分组后SELECT列的限制问题很多新手会写这种SQLSELECT name, dept_id, COUNT(*) FROM employee GROUP BY dept_id;在MySQL 5.7以前的版本这条SQL可能还能跑通只是拿到的name是“该组里的随便一个值”完全不可预期。但在MySQL 5.7及之后的版本默认开启了ONLY_FULL_GROUP_BY模式这种写法直接报错。ONLY_FULL_GROUP_BY的核心规则是SELECT里的非聚合列必须同时出现在GROUP BY子句里。为什么因为分组之后每组可能有多行但聚合结果只有一行。如果SELECT后面放一个既没被GROUP BY、又不是聚合函数的普通列数据库不知道该展示这一组里的哪一行语义上就是不确定的。这不仅仅是报不报错的问题而是业务逻辑的问题。你按部门分组统计人数这时候你还想知道“这个部门里某个人的名字”这个需求本身就是矛盾的——一组里可能有好几个人到底要哪个如果真需要组内某个具体信息要么用MAX(name)、MIN(name)这种聚合方式要么用后面要讲的GROUP_CONCAT把所有人的名字拼起来要么用窗口函数MySQL 8.0单独开一列。2.3 GROUP_CONCAT分组里最有用的辅助函数说到GROUP_CONCAT这是分组查询里一个特别实用的函数。它能把组内多行的某个字段拼接成一个字符串用逗号隔开。SELECT dept_id, GROUP_CONCAT(name ORDER BY age SEPARATOR 、) FROM employee GROUP BY dept_id;这个函数的意义在于GROUP BY之后你既能看到组的统计结果又能看到组里的明细内容。比如统计“每个分类下的全部标签”一条SQL直接搞定SELECT category_id, GROUP_CONCAT(tag_name) FROM product_tags GROUP BY category_id;使用GROUP_CONCAT时有几个细节值得注意。第一默认拼接长度限制是1024字节字符串长了会被截断。需要调大限制就执行SET SESSION group_concat_max_len 1048576;但要注意这个设置只对当前会话生效。第二拼接的顺序默认是“不确定的”需要明确顺序时得在函数内部用ORDER BY控制。第三拼接结果里如果某个值是NULL它不会出现在结果里也不会当NULL字符串拼进去。2.4 多字段分组和排序的配合GROUP BY后面可以跟多个字段语义是“按这些字段的组合值进行分组”。比如SELECT year(order_date), month(order_date), COUNT(*) FROM orders GROUP BY year(order_date), month(order_date);这条SQL就是按年、月两个维度分组统计每个月的订单量。这里有个常见误区——有人以为得先写个拼接字段再分组其实直接用函数表达式做分组键就行并不需要先把这个结果算出来存成一张临时表。不过如果某个字段在WHERE或GROUP BY里频繁使用又经过了函数转换那索引可能就失效了。比如上面那个year(order_date)如果order_date上建了索引这样写也没法用上索引因为MySQL得先把每行的order_date算成年份才能做分组比较。数据量大时这种写法性能会明显下降。这也是为什么很多表在设计时就单独存一列order_year、order_month——就是为了让分组能直接走索引避开函数处理。分组结果默认是不排序的。如果你想按“订单量从高到低”看结果得在ORDER BY里指定SELECT dept_id, COUNT(*) AS cnt FROM employee GROUP BY dept_id ORDER BY cnt DESC;注意ORDER BY后面可以引用SELECT里定义的别名cnt这是MySQL支持的特性不管是从语义清晰度还是从查询性能上看都比再写一遍COUNT(*)更好读。3. 联合查询多段结果集合并联合查询英文叫UNION。它的作用是把两个或更多SELECT语句的结果“上下拼接”成一个更大的结果集。用个生活化的比喻好比你有两个Excel工作表一个是北京的销售额一个是上海的销售额现在你想把它们粘在同一个Sheet页里从上往下罗列出来这就是UNION干的事。3.1 UNION和UNION ALL的核心区别UNION会对最终结果集做“去重”UNION ALL不会。这个区别听起来很小但对性能和结果语义的影响非常大。用一段对比来看-- UNION自动去重但需要额外排序去重开销 SELECT name FROM employee_a UNION SELECT name FROM employee_b; -- UNION ALL直接拼接保留所有重复行 SELECT name FROM employee_a UNION ALL SELECT name FROM employee_b;如果两个表里存在同名员工UNION只保留一条UNION ALL会把两条都保留下来。在大多数业务场景里“合并明细数据”时不应该去重比如把两个月的订单明细合并你肯定不希望同一天下了两单就自动少一单。所以我的经验是除非明确知道要精确去重否则优先用UNION ALL。原因有两个一是性能。UNION在实现上要去重这个“去重”通常需要额外的排序或哈希操作。如果结果集很大UNION的执行时间和内存消耗会明显高于UNION ALL。二是语义。UNION自动去重的标准是两个SELECT的所有列的取值完全一致。如果列里有小数、日期时间等精度敏感的字段去重可能产生你意料之外的结果——两条数据其实并不一样只是在你没注意到的某个字段上碰巧相等。3.2 联合查询的列对应规则和常见报错UNION有两条硬性规则违反任何一条都会直接报错。第一两个SELECT的“列数量”必须一致。左边的SELECT查了3列右边的SELECT必须也查3列。如果你某段查询的列数不一致比如SELECT name, age FROM employee_a UNION ALL SELECT name FROM employee_b;MySQL会报The used SELECT statements have a different number of columns。这个错非常常见。拼接临时报表时左边临时加了备注列右边忘了补就会踩到。第二对应列的数据类型要兼容。所谓兼容不是要求完全一样而是要能隐式转换。比如左边是VARCHAR右边是INTMySQL会把右边的数转成字符串拼接一般不会报错。但两边类型差距过大比如左边是DATETIME右边是INT就可能报类型错误或产生无意义的值。联合查询的列名以“第一个SELECT”的列名为准。比如SELECT name AS employee_name FROM employee_a UNION ALL SELECT real_name FROM employee_b;最终结果集的列名是employee_name右侧的real_name只影响右侧那一列的数据来源不影响输出列名。这也就意味着右边SELECT的别名写了也是白写。3.3 UNION里ORDER BY和LIMIT怎么放很多人第一次在UNION里用ORDER BY会想着直接把ORDER BY放在最后一行后面SELECT name FROM employee_a UNION ALL SELECT name FROM employee_b ORDER BY name;这个语法实际上是对的而且在MySQL里作用范围是整个联合查询的结果集也就是对合并后的整体做排序。但如果我把ORDER BY放在第一个SELECT内部呢SELECT name FROM employee_a ORDER BY age UNION ALL SELECT name FROM employee_b;这个写法在MySQL里会报错——ORDER BY不能出现在单个SELECT的内部除非加上括号。正确方式是用括号把单个SELECT包起来(SELECT name FROM employee_a ORDER BY age LIMIT 10) UNION ALL (SELECT name FROM employee_b ORDER BY age LIMIT 10);这种用法在分页合并场景里很有用。比如每个部门取前10名再合并就可以像上面这样用括号限制每个子查询的排序和条数。还有一个细节如果要对最终结果集做LIMIT只需要放在整个联合查询的末尾SELECT name FROM employee_a UNION ALL SELECT name FROM employee_b ORDER BY name LIMIT 20;这里ORDER BY是作用于最终合并结果集的。如果把LIMIT放在内层单查里情况就完全不同了。3.4 UNION和JOIN怎么选这是另一个容易混淆的地方。UNION是“纵向拼接”JOIN是“横向拼接”。用表格来理解UNION把两张表“上下摞起来”增加行数列数不变。JOIN把两张表“左右贴起来”增加列数行数看对应关系。举例来说-- 纵向拼接把华北区和华南区的销售额放在同一列 SELECT region, amount FROM sales_north UNION ALL SELECT region, amount FROM sales_south; -- 横向拼接把订单表和用户表通过user_id关联把用户名补到订单旁边 SELECT o.order_id, u.name FROM orders o JOIN users u ON o.user_id u.id;业务上如果要做“不同维度、但结构相似的数据合并”比如汇总多个渠道的广告投放记录用UNION。如果是“一张主表需要补充另一张表的详细信息”比如订单上带出用户手机号那用JOIN。两个操作解决的问题完全不一样不存在谁替代谁的问题。4. 常见问题与排查技巧实录这一节是我实际带项目时总结出来的高频问题速查。每一个都有真实案例按照踩坑顺序来排。4.1 ONLY_FULL_GROUP_BY模式下SQL突然报错有次系统升级从MySQL 5.5升到5.7原本跑得好好的分组SQL突然开始报this is incompatible with sql_modeonly_full_group_by。当时一头雾水后来才搞清楚是默认sql_mode变了。解决方案有两个层面。第一个是改SQL让SELECT里的非聚合列都出现在GROUP BY里或者包一层聚合函数。这是最推荐的做法因为改的是逻辑不管到哪个版本都不会有问题。第二个是修改sql_mode比如在配置文件里去掉ONLY_FULL_GROUP_BY。这个操作能让旧SQL继续跑但代价是那些“语义不确定”的SQL又能执行了容易埋下隐性bug。我的建议是从SQL层面做适配把那些不确定的列要么加进GROUP BY要么换成聚合函数。不要为了图省事修改全局配置这是根治问题的方式。4.2 COUNT出来数量明显不对排查思路要找准“统计人数少了几个”这类问题最典型的根因就是COUNT(字段名)跳过了NULL。排查时一眼看不出来时可以先把WHERE条件去掉跑一次再用SELECT COUNT(*), COUNT(1), COUNT(可能为空的字段) FROM 表名做一次字段级对比基本立刻就能定位。另一个相关的坑是在多表JOIN后COUNT()出现重复计数。比如订单表JOIN订单明细表因为一个订单有多条明细订单表那行会被关联出多行COUNT()统计出来的就是明细行数而不是订单数。这种情况下统计订单数应该用COUNT(DISTINCT 主表主键)。这个问题在报表开发里相当常见也是面试官最爱追问的考察点。4.3 UNION结果集的隐式转换坑有次做导出功能左边SELECT了一个status字段是TINYINT一边是两个字符的VARCHAR结果合并出来的数据有些行显示0有些行显示状态0格式极不统一。排查后发现UNION合并时MySQL会把结果集的数据类型统一成“更宽”的那一侧两个类型之间发生了隐式转换。处理这类问题的思路是在UNION的各个分支里手动把数据类型转换成一致的比如都转成字符串再拼接。避免依赖MySQL自己去做类型统一否则在不同版本里行为可能有细微差别。4.4 聚合结果排序前先看执行计划如果你发现分组统计SQL跑得特别慢先别急着加索引先用EXPLAIN看一下执行计划。经验上最常出现的几个信号显示Using temporary说明GROUP BY阶段用到了临时表。显示Using filesort说明排序没走索引。显示typeALL说明在扫全表。这三样同时出现时优先检查WHERE条件和GROUP BY字段是否都建了索引。如果能改动SQL让分组字段走索引就能减少临时表和文件排序的代价。另外说明一点MySQL 8.0.13之前有个特性叫“松散索引扫描”可以优化部分GROUP BY查询直接走索引但前提是分组字段满足最左前缀原则。实战里与其去记这些优化细节不如先EXPLAIN看实际执行计划再根据计划来调整。5. 几个实战体会做数据分析或报表开发时聚合、分组、联合查询这三样东西会一直用到它们的组合变化几乎是无穷的。我自己的体会是这几块内容并不难理解真正值钱的是“把逻辑想清楚”再动手写SQL。我在实际工作中发现很多SQL性能问题的根源不是表太大而是执行顺序理解不到位。写SQL之前先在脑子里过一遍WHERE、GROUP BY、HAVING的先后关系就能顺手改掉很多低级错误。另外给聚合和分组的字段合理命名别名是让报表SQL可维护的关键。别写一堆COUNT(*) AS c1、MAX(score) AS m1出来三个月后你自己看到都头疼。用order_cnt、max_score这种直白名字后面接需求的人能省不少力气。最后再分享一个衡量SQL质量的小技巧写完一条统计SQL后拿它跟业务需求原文比对一遍——每个过滤条件是否都对应需求里的一个限定词每个分组维度是否都对应“按XX统计”的说法每个聚合字段是否都对应“有多少/总和/平均”之类的指标。对上号了这条SQL基本就不会出大问题。这个习惯说起来简单但对提升SQL准确率帮助极大。