很多人在刚接触数据库查询时往往把“查询语句”和“聚合函数”当成两个独立的知识点来学先学SELECT、WHERE、JOIN再学GROUP BY和聚合函数最后做练习题时才发现总是凑不到一块去。尤其在面试或实际开发中一个“统计订单量”“算客单价”的需求就能把这两块内容彻底打通。我最早在项目里写统计报表时也踩过不少坑比如COUNT(*)和COUNT(列)结果对不上、GROUP BY后面乱写列名导致SQL报错、IN子查询查出来一堆NULL让结果变空等等。这篇就专门聊聊表格查询语句和聚合函数怎么才能真正用熟练从执行逻辑到实战排查一次说清楚适合刚入门SQL的开发新手也适合写了不少查询但总被慢查询和死锁困扰的同学。1. 从需求到SQL先理清查询语句的执行逻辑1.1 别急着写SQL先搞懂语句执行顺序很多初学者学SQL时习惯按照书写顺序去理解先写SELECT再写FROM然后WHERE、GROUP BY、HAVING、ORDER BY一路往后排。但真实执行顺序完全不是这样。数据库引擎拿到一条查询语句后第一件事是确定数据从哪来也就是FROM然后是JOIN关联接着才是WHERE过滤再是对过滤结果做GROUP BY分组分组后使用HAVING过滤组之后才是SELECT投影出需要的列最后做ORDER BY排序和LIMIT分页。弄清楚这个顺序你才能理解为什么WHERE里不能直接用聚合函数做条件过滤为什么GROUP BY之后SELECT列名会受限。举个例子假如有一张订单明细表你想看每个客户的订单总金额并且只要总金额大于1000的客户。如果按照书写顺序写WHERE SUM(amount) 1000数据库在执行WHERE阶段时根本还没有分组SUM(amount)没有意义所以语法上直接不允许你需要用HAVING SUM(amount) 1000因为HAVING是在GROUP BY之后执行的。很多报错其实就是执行顺序没理顺造成的。1.2 一条查询语句的完整生命周期我们那一条最常见的查询拆开看。以MySQL为例客户端发送SQL后服务端会经历连接器、分析器、优化器、执行器四个阶段。分析器负责检查语法和词法如果你的SQL里表名写错了或者字段名不存在通常在这里就报错了优化器负责决定用哪个索引、以什么顺序关联表这直接影响查询快慢执行器最后调用存储引擎接口把满足条件的数据返回给客户端。理解这个生命周期后你在排查慢查询时就知道该去看执行计划因为执行计划反映的正是优化器决定出来的执行路径。很多查询慢的问题根本不是SQL本身写错了而是优化器选错了索引或者根本没用索引。比如你在WHERE条件里对索引列做了函数运算WHERE DATE(create_time) 2024-01-01这时索引就失效了因为优化器没法直接通过B树定位到具体日期范围。改成WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00让索引列保持原始形态才能走索引。1.3 用一个真实场景熟悉执行链路我拿一个电商项目的订单表来演示表结构比较简单CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, product_name VARCHAR(100), amount DECIMAL(10,2), status TINYINT COMMENT 1待支付 2已支付 3已取消, create_time DATETIME );现在要统计每个用户已支付订单的总金额并且只需要总金额大于1000的用户按总金额降序排列。正确写法SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE status 2 GROUP BY user_id HAVING SUM(amount) 1000 ORDER BY total_amount DESC;执行过程大致是先从订单表里筛出status2的记录然后按user_id分组对每组做SUM(amount)再过滤掉总金额小于等于1000的组最后排序返回。这一条语句就把WHERE、GROUP BY、HAVING、ORDER BY全部串起来了也是聚合查询最典型的骨架。2. 聚合函数核心用法与易错细节2.1 五大聚合函数的行为差异聚合函数是对一组值执行计算并返回单个值的函数SQL标准里最常用的就是COUNT、SUM、AVG、MAX、MIN。它们的行为逻辑完全围绕“分组”展开不写GROUP BY时整个结果集被当成一个大组所以SELECT COUNT(*) FROM orders能返回全表行数。COUNT用来统计行数SUM用来求和AVG用来算平均值MAX和MIN分别取最大值和最小值。行为差异上有几个点值得注意SUM和AVG会自动忽略NULL值因为NULL在SQL里表示未知参与加减乘除后结果还是未知所以直接跳过而COUNT(列)会忽略NULL但COUNT()不会忽略任何行。如果一列中有大量NULL你用COUNT(列)统计的数量和COUNT()查出来的行数对不上这属于正常现象不是bug。MAX和MIN对文本列也能使用按照字符排序规则取最大值或最小值。日期列也可以取最近或最早的日期。AVG要注意精度问题尤其对DECIMAL类型MySQL默认可能返回四舍五入后的结果如果需要更高精度可以使用ROUND(AVG(amount), 2)直接控制小数位。2.2 COUNT(*)与COUNT(列)的经典坑这个坑几乎每个SQL开发者都会遇到。COUNT(*)统计的是结果集中的总行数不管某列是否为NULLCOUNT(列名)统计的是该列非NULL值的个数。看个例子SELECT COUNT(*), COUNT(user_id), COUNT(status) FROM orders;如果orders表有100条记录其中user_id有5条为NULLstatus有10条为NULL那么COUNT()返回100COUNT(user_id)返回95COUNT(status)返回90。当我在这张表上去判断“用户是否填写了ID”时如果用COUNT()会误以为所有行都有ID必须用COUNT(user_id)。另一个常见误区是在LEFT JOIN后使用COUNT(右表列)来做存在性统计。比如统计有订单的用户数有人会这么写SELECT COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id o.user_id。如果某个用户没有订单那么o.id是NULLCOUNT(o.id)会忽略掉这些NULL行所以结果实际上只统计了有订单的用户数这个写法反而歪打正着。但如果把它换成COUNT(*)就会把无订单的用户也统计进去结果就错了。这再次说明SQL的语义差别必须靠实际数据来验证不能凭感觉写。2.3 GROUP BY与聚合函数组合的分组逻辑GROUP BY的本质是把结果集按一个或多个列的值分成多个小组聚合函数在每个小组内独立执行。这里最核心的规则是SELECT后面出现的非聚合列必须出现在GROUP BY里。比如前面订单表你要按用户统计写了SELECT user_id, product_name, SUM(amount) FROM orders GROUP BY user_idMySQL在ONLY_FULL_GROUP_BY模式下会直接报错因为product_name没有出现在GROUP BY里。原因很简单同一个user_id可能对应多个product_name数据库不知道该返回哪一个。如果确实需要每个组里的某一个具体值可以把product_name也加入GROUP BY或者在子查询里先聚合再关联取明细。这个报错是MySQL 5.7以后默认开启的严格模式带来的很多老教程没提这一点导致不少人从旧版本迁移过来后一脸懵。需要注意的是GROUP BY可以跟多个列比如GROUP BY user_id, status这表示先按user_id分组再在组内按status细分聚合函数会在每个(user_id, status)组合上执行。这种多列分组在做维度统计时非常常用比如“统计每个用户的每种订单状态数量和金额”就靠它。2.4 聚合结果过滤必须用HAVINGWHERE用于过滤行HAVING用于过滤组。这是SQL面试题里最常被问的概念之一。执行顺序上WHERE先执行HAVING后执行所以HAVING中可以使用聚合函数WHERE中不行。实际开发中我见过不少同事用WHERE来过滤聚合结果比如SELECT user_id, COUNT(*) AS cnt FROM orders WHERE COUNT(*) 5 GROUP BY user_id;这条SQL必然报错。正确做法是SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id HAVING COUNT(*) 5;还有一点值得注意HAVING的过滤发生在分组之后所以如果能在WHERE阶段过滤掉的数据尽量在WHERE里过滤这样参与分组的数据量更少聚合速度更快。比如上面例子里的status 2在WHERE里写和在HAVING里写都能得到同样的结果但WHERE写法性能更好因为减少了进入分组的数据行数。这条经验写进简历里都是加分项。3. 去重查询与复杂聚合实战3.1 DISTINCT与GROUP BY的去重区别去重是查询语句里非常高频的需求常见实现方式有两种SELECT DISTINCT和GROUP BY。二者在单列去重上效果几乎一样但思路不同。DISTINCT是“投影后去重”它在SELECT阶段对最终输出的结果集去重GROUP BY是“分组后取每组首行”它天然产生唯一分组键。举个例子查订单表中出现过的所有用户IDSELECT DISTINCT user_id FROM orders; SELECT user_id FROM orders GROUP BY user_id;两条语句结果一致。它们的底层执行逻辑不太一样DISTINCT在MySQL中通常会对结果集做排序或哈希去重而GROUP BY也会走排序或哈希分组。对于单列去重的场景两者性能差距不大但如果你还要同时统计每组的数量就必须用GROUP BY了因为聚合函数必须在分组语境下才成立。DISTINCT COUNT这种写法也只是对COUNT的结果做去重没有分组统计的能力。3.2 多列去重怎么实现多列去重有两种常见需求第一种是返回所有不重复的列组合用SELECT DISTINCT col1, col2即可第二种是只针对某几列判断重复但返回完整字段或统计数量这时DISTINCT就不够用了需要用GROUP BY配合聚合函数或子查询。比如订单表里有user_id和status你想知道每个用户每种状态各有多少单SELECT user_id, status, COUNT(*) AS order_count FROM orders GROUP BY user_id, status;如果你想查“每个用户最新的一条订单记录”典型的分组取最大值的场景直接GROUP BY没法拿到整行需要子查询或窗口函数SELECT o.* FROM orders o JOIN ( SELECT user_id, MAX(create_time) AS max_time FROM orders GROUP BY user_id ) t ON o.user_id t.user_id AND o.create_time t.max_time;不过这个方法在同一个用户有两条完全相同的create_time时会返回多行如果只要一行可以用窗口函数ROW_NUMBER()SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM orders ) t WHERE rn 1;相比之下窗口函数语义更清晰也更容易扩展到“取第二新”“取最早”之类的需求。3.3 聚合函数搭配CASE WHEN做条件统计一个高频场景是在同一张表里按某个条件拆成多个统计列。比如统计每个用户已支付和未支付的订单数常规做法是查两次再合并但一个更优雅的做法是用CASE WHEN配合COUNT或SUMSELECT user_id, COUNT(CASE WHEN status 2 THEN 1 END) AS paid_count, COUNT(CASE WHEN status 1 THEN 1 END) AS unpaid_count, SUM(CASE WHEN status 2 THEN amount ELSE 0 END) AS paid_amount FROM orders GROUP BY user_id;这里的技巧是CASE WHEN返回NULL时COUNT会忽略所以用COUNT(CASE WHEN ... THEN 1 END)可以实现条件计数。如果使用COUNT(CASE WHEN ... THEN 1 ELSE 0 END)结果就会变成统计该组总数因为ELSE 0并不计入COUNT的忽略范围COUNT(0)是非NULL值会被统计进去这又回到了COUNT(列)和COUNT(*)语义差异的坑。用SUM(CASE WHEN ... THEN amount ELSE 0 END)可以实现条件求和这在做不同状态下的金额统计时非常实用一次查询就能生成一张多维度的报表行。3.4 窗口函数与聚合函数的分工配合窗口函数是理解聚合函数之后一个很好的进阶方向。聚合函数把多行压缩成一行窗口函数则不改变行数而是在每一行都返回一个聚合计算的结果。比如你既要看每笔订单明细又要看每个用户的总金额用传统GROUP BY就做不到同时保留明细和汇总但用窗口函数可以SELECT id, user_id, amount, SUM(amount) OVER (PARTITION BY user_id) AS user_total FROM orders;这里SUM(amount) OVER (PARTITION BY user_id)会为每一行计算所属用户的总金额不压缩行数。窗口函数在分组排名、同比环比、移动平均等场景中非常强大但它的执行性能和普通聚合不同使用时要确保PARTITION BY和ORDER BY列上有合适的索引否则对全表数据做窗口计算可能比聚合查询慢得多。在实际项目中我的建议是如果只需要分组汇总结果用GROUP BY如果需要明细和汇总共存或者需要在分组内排名优先考虑窗口函数。两者互相配合能覆盖绝大多数统计分析需求。4. 高频报错IN查询语句的典型问题排查4.1 IN子句遇到NULL导致结果为空IN查询语句报错或者结果异常是开发中非常高频的问题。最经典的就是IN子查询返回了NULL值导致的“看似正确但结果不对”。看下面的例子SELECT * FROM orders WHERE user_id IN ( SELECT user_id FROM users WHERE level vip );如果子查询中某一行user_id为NULLIN的语义是“等于列表中的任意一个”。而SQL中任何值与NULL比较的结果都是“未知”不会返回TRUE。所以当IN列表里包含NULL时匹配结果会少掉那些原本应该通过其他值匹配出来的行甚至在某些数据库连结果都不返回。解决办法是让子查询排除NULLWHERE user_id IN ( SELECT user_id FROM users WHERE level vip AND user_id IS NOT NULL );如果是IN后直接跟手动指定的列表比如IN (1, 2, NULL)也要尽量排除NULL避免语义不清晰。4.2 IN列表过大导致慢查询当IN后面的列表包含数千甚至上万个值时查询性能会急剧下降。这里有两个层面第一SQL语句本身会变得很长网络传输和解析成本上升第二优化器很难对这么大的IN列表做有效的索引范围扫描可能退化为全表扫描。我在实际工作中遇到过一个案例同事把一万多个ID拼进IN列表去查数据结果一条查询跑了十几秒。排查后先用临时表方案解决把ID批量导入临时表然后JOIN临时表查询性能从十几秒降到了几百毫秒。另一个思路是分批查询把一万个ID拆成每批500个分多次查询再合并结果但这样会增加应用层复杂度。对于MySQL如果IN列表不是特别大比如几百个且列上有索引通常还是能走到索引的但超过千级别后优化器可能就不再走索引了。此时最稳妥的方式是使用临时表或JOIN。4.3 IN与EXISTS的语义和性能差异IN和EXISTS在很多场景能互相替换但语义有差异。IN会先执行子查询把结果集物化再和外部查询逐行匹配EXISTS则是一种“存在性检查”只要子查询返回任何一行就停止不关心具体内容。当子查询的结果集很小而外层表很大时IN通常表现不错当外层表很大、子查询结果集也很大时EXISTS配合索引关联往往更高效。实践中有一个常见误区EXISTS子查询里SELECT *和SELECT 1性能相同因为EXISTS只看是否存在行不看选择列。所以你可以放心写SELECT 1。4.4 IN查询报错的定位方法遇到IN语句报错时我是按这个顺序排查的第一步检查子查询本身能不能独立执行。单独运行子查询看返回结果是否包含NULL或异常数据类型。第二步检查数据类型是否匹配。比如外层的user_id是INT子查询返回的是VARCHAR类型某些数据库会隐式转换导致索引失效甚至报错。第三步查看执行计划确认是否走了索引。如果没走索引考虑改写为JOIN或EXISTS。第四步检查IN列表长度是否过大并评估是否需要拆分为批量查询。用EXPLAIN看执行计划时如果Extra列出现“Using where”而不见索引使用记录就要特别小心。IN列表过大会导致优化器放弃索引直接扫全表这时type列会变成ALL或indexkey列变成NULL问题定位就非常直观了。5. 慢查询、死锁与语句阻塞的MySQL排查实录5.1 开启慢查询日志找到真正的“罪魁祸首”慢查询是所有数据库应用都会面临的问题MySQL里最直接的定位工具就是慢查询日志。默认情况下MySQL慢查询日志是关闭的可以临时开启或直接写进配置文件SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;long_query_time1表示执行时间超过1秒的SQL都会被记录。线上环境建议把这个阈值设成0.5或1太低会记录太多噪声太高容易漏掉潜在问题。拿到慢查询日志后使用mysqldumpslow工具可以快速聚合出频率最高的慢SQLmysqldumpslow -s at -t 10 /var/log/mysql/slow.log-s at表示按平均查询时间排序-t 10表示只看排名前十的SQL。这些通常就是最值得优化的对象。注意慢查询日志并不直接告诉你连接信息只记录SQL文本和耗时后期要根据SQL特征定位到具体业务接口。5.2 用EXPLAIN看执行计划逐项排查慢的原因定位到慢SQL后我一般会在慢SQL前面加EXPLAIN关键字查看执行计划。重点关注几个字段type列是访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALL。如果看到ALL或index基本可以判断是全表扫描或全索引扫描需要警惕。key列显示实际用到的索引如果为NULL就表示没走索引。rows列是优化器预估扫描的行数这个数越大查询越慢。Extra列经常隐藏关键线索Using filesort表示文件排序Using temporary表示使用临时表Using where表示服务层过滤。举个例子有一个订单查询慢EXPLAIN结果如下EXPLAIN SELECT * FROM orders WHERE user_id 1 ORDER BY create_time DESC;发现type为ALLkey为NULL说明没走索引。检查之后发现user_id上确实有索引但SQL里写的是WHERE user_id 1字符串和INT类型隐式类型转换导致索引失效。改成数字类型后type变成了ref查询瞬间变快。这就是执行计划发现隐式转换问题的典型过程。5.3 死锁是怎么产生的如何避免死锁在MySQL里通常发生在多个事务同时以不同顺序获取多个锁时是典型的加锁顺序不一致导致的“循环等待”。假设事务A先锁了订单表的某一行再尝试更新用户表某一行事务B先锁了用户表的同一行再尝试更新订单表的那一行。两个事务互相等待对方释放锁MySQL检测到死锁后会自动回滚其中一个事务并报错错误码为1213。我遇到过一个真实案例两个批量更新任务同时跑一个按订单ID升序更新另一个按订单ID降序更新结果在某些数据边界上产生死锁。解决方式有两种一是统一加锁顺序所有事务都按同一个排序规则获取锁二是把一个大事务拆分成多个小事务减少锁持有时间。另一个常见的死锁场景是批量插入数据时并发冲突唯一索引两个事务都在插入相同的键各自持有间隙锁和插入意向锁最终互相等待。避免方法是使用INSERT ... ON DUPLICATE KEY UPDATE或先查询再插入的幂等方案减少并发冲突的可能。5.4 语句阻塞的排查思路与常用命令语句阻塞简单来说就是一个事务持有了锁但迟迟不提交或回滚导致其他事务一直在等待。MySQL中可以通过以下命令找出当前阻塞链SELECT * FROM information_schema.innodb_trx; SELECT * FROM information_schema.innodb_lock_waits; SELECT * FROM performance_schema.data_lock_waits;第一个语句列出了所有正在运行的事务可以看到trx_state字段是LOCK WAIT还是RUNNING以及trx_started时间。如果某个事务已经运行了很久但没有任何更新操作很可能是应用层忘记提交事务。innodb_lock_waits能直接看到谁在等待谁。blocking_trx_id是被阻塞事务等待的事务IDblocking_lock_id对应的行在innodb_trx表里能看到持锁事务的信息。定位到持锁事务后需要去业务应用里检查这个事务对应的代码确认是应该提交还是应该回滚。如果线上急需恢复可以执行KILL 1060115;这里1060115是持锁事务的trx_mysql_thread_id。杀事务是最后手段一定要确认该事务没有进行关键业务操作否则可能造成数据不一致。5.5 索引设计对慢查询的决定性影响排查慢查询时索引设计几乎是绕不开的环节。索引的价值在于用空间换时间但也不是越多越好。每个索引在插入、更新、删除时都需要维护索引过多会拖慢写入性能。联合索引要遵循最左前缀原则比如建立了索引(idx_user_status)即(user_id, status)那么WHERE user_id ?能走索引WHERE user_id ? AND status ?也能走但WHERE status ?单独使用就无法走这个联合索引。回表问题也值得留意如果查询的列都包含在索引里就能实现覆盖索引避免回表查找数据行。比如订单表有(user_id, status)联合索引执行SELECT user_id, status FROM orders WHERE user_id 1;这个查询可以只扫索引就返回结果Extra列显示Using index性能非常好。而SELECT *就不得不回表取完整行开销更大。查询频繁且数据量大的场景可以适当设计一些覆盖索引来提升性能。6. 聚合函数在不同数据库中的细节差异6.1 MongoDB聚合管道和传统SQL聚合的映射MongoDB的聚合函数不是传统SQL风格的SELECT COUNT()而是通过聚合管道Aggregation Pipeline分阶段处理。常用的阶段有$match、$group、$sort、$project等它们和SQL的WHERE、GROUP BY、ORDER BY、SELECT可以一一对应。比如订单表在MongoDB里要统计每个用户的订单数量db.orders.aggregate([ { $match: { status: 2 } }, { $group: { _id: $user_id, count: { $sum: 1 }, totalAmount: { $sum: $amount } } }, { $sort: { totalAmount: -1 } } ]);$match相当于WHERE$group相当于GROUP BY$sum相当于SUM。相比SQLMongoDB聚合管道的好处是每个阶段都显式声明便于理解数据流在每个阶段的形态变化但代价是管道如果很长性能消耗也不小。6.2 MSSQL CLR聚合函数排序失效的问题MSSQL里可以通过CLR自定义聚合函数来扩展原生聚合能力比如实现自定义的字符串拼接、分组TopN等。很多人写完CLR聚合函数后发现排序顺序不生效问题往往出在SQL Server对CLR聚合函数的数据处理方式上。CLR聚合函数在处理输入数据时默认是按索引顺序流式读取的如果查询中没有明确的ORDER BY或者索引顺序和期望顺序不一致传入聚合函数的数据顺序就不稳定。解决方法是重写聚合逻辑在聚合函数内部做排序或者在查询外部用子查询先排好序再聚合。例如SELECT user_id, dbo.MyCustomAgg(column1) FROM ( SELECT user_id, column1 FROM orders ORDER BY user_id, create_time DESC ) AS sorted GROUP BY user_id;子查询里的ORDER BY有时候会被优化器忽略更稳妥的方式是在CLR代码内部维护一个有序列表在Terminate时统一输出这样排序就与外部查询计划无关了。6.3 不同数据库聚合函数的兼容性聚合函数语法在不同数据库中有高度相似之处但仍然存在不少小差异。比如字符串聚合函数MySQL的GROUP_CONCAT和PostgreSQL的STRING_AGG、SQL Server的STRING_AGG、Oracle的LISTAGG功能类似函数名完全不同。做跨数据库迁移时这部分代码必须逐个改写。还有NULL处理逻辑的差异。MySQL的AVG忽略NULL值但如果你希望把NULL当作0参与计算需要提前用IFNULL或COALESCE处理。SQL Server同样忽略NULL但在一些特殊聚合场景下表现略有不同。写跨数据库代码时最好用COALESCE(列, 0)显式转换避免同一个SQL语句在两个数据库里计算结果不一致。需要注意的是SQL标准本身也在演进新版本数据库会引入新的聚合能力。保持对标准SQL的熟悉同时关注目标数据库的官方文档是最好的兼容性策略。7. 一个完整的MySQL聚合与慢查询优化案例7.1 案例背景前阵子我帮一个团队优化报表查询场景是统计每天每个商品类目的销售额和订单量。原始SQL长这样SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, category_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders WHERE create_time 2024-01-01 AND create_time 2024-02-01 GROUP BY day, category_id ORDER BY day, total_amount DESC;这个查询在订单量几百万的情况下执行时间从最初的几百毫秒一路涨到十几秒报表页面卡到无法接受。优化前先用EXPLAIN看执行计划发现Extra列里有Using temporary和Using filesort说明MySQL为了GROUP BY和ORDER BY创建了临时表并做了文件排序这是性能瓶颈所在。7.2 优化前的分析执行计划显示type为ALL扫描行数接近全表索引完全没有派上用场。原因很直接create_time虽然有索引但DATE_FORMAT(create_time, %Y-%m-%d)对列做了函数计算导致索引失效。同时GROUP BY中使用了表达式day这也是导致临时表的诱因之一。第一项优化是把日期表达式改成范围查询让索引走起来WHERE create_time 2024-01-01 AND create_time 2024-02-01然后分组列改成原始时间列的前缀方式MySQL 5.7以上支持在GROUP BY中使用表达式但仍会引入临时表。更好的做法是增加一个冗余字段day_dateDATE类型写入时直接落明细日期查询时GROUP BY day_date索引和分组都更高效。7.3 优化后的SQL与效果加冗余字段后优化SQL可以写成SELECT day_date, category_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders WHERE day_date 2024-01-01 AND day_date 2024-01-31 GROUP BY day_date, category_id ORDER BY day_date, total_amount DESC;这时候EXPLAIN的type变成了ref或rangeExtra列不再出现Using temporary和Using filesort。执行时间从十几秒降到几百毫秒以内效果非常明显。另一个思路是如果表已经很大且实时统计压力大可以构建汇总表每分钟或每小时把聚合结果写入单独的统计表查询时直接读汇总表性能提升更显著。具体做法是把原订单表作为明细表汇总表存储(day_date, category_id)的订单数和销售额用定时任务或事件调度器增量更新。这样即使数据量再翻几倍报表查询也可以维持在毫秒级。7.4 案例给我们的几个经验这个案例里踩的坑很典型总结几条经验第一查询语句里对索引列做函数运算是让索引失效最常见的原因能用范围查询就尽量用范围查询。第二GROUP BY的列尽量使用原始列或冗余列避免在分组字段上做表达式计算以减少Using temporary的触发概率。第三ORDER BY的字段如果和GROUP BY字段不一致很容易触发Using filesort必要时可以调整排序字段或者用子查询。第四对于固定维度的统计需求汇总表是成本最低收益最高的方案和索引优化配合使用效果最好。每次优化完查询都要重新用EXPLAIN回归一遍确认执行计划和预期一致避免改完SQL结果相同但性能反而变差的情况。8. 查询与聚合中的实战心得分享8.1 规范化SQL书写习惯在实际业务中SQL写得规范不规范直接影响后续排查和维护成本。我的习惯是保留字都用大写表名字段名用小写别名的命名清晰有含义比如total_amount不要写成ta。多表关联时使用表前缀比如o.user_id避免多张表有同名字段时产生歧义。GROUP BY和ORDER BY的字段顺序尽量保持一致减少排序开销。另外每个查询在编写时就要想到执行计划的合理性而不是写完能跑就完事。遇到问题多EXPLAIN几次建立“先看执行计划再优化”的肌肉记忆比依赖断点调试SQL效率高得多。8.2 聚合查询性能优化清单尽量在WHERE阶段过滤掉不需要的数据减少参与分组的数据量。避免对索引列做函数或隐式类型转换保证索引可用。GROUP BY和ORDER BY使用同一组字段减少文件排序。COUNT(*)用COUNT(主键)或COUNT(1)没有明显性能差异不用刻意纠结这个但COUNT(列)语义要清楚。大表聚合查询优先考虑汇总表、物化视图或定时预计算方案。使用EXPLAIN ANALYZEMySQL 8.0支持可以查看实际执行时间和每步扫描行数优化瓶颈定位更准确。8.3 最后一个小技巧如果在一个查询里既需要总数又需要分页后的明细比如“返回订单列表同时返回符合条件的总条数”很多人会写两条SQL一条COUNT统计一条SELECT分页查询。这个做法本身没问题但一次请求两次查询会多一次Round Trip。可以用窗口函数COUNT(*) OVER()在明细查询里同时返回总数SELECT id, user_id, amount, COUNT(*) OVER() AS total_count FROM orders WHERE status 2 ORDER BY create_time DESC LIMIT 0, 20;每一行都会携带total_count字段应用层直接取第一行即可。这种方式在数据量不大时很实用少一次数据库交互。数据量很大时仍然建议分两条SQL因为窗口函数会先计算全量计数再分页性能不一定更优。查询语句和聚合函数这两块内容掌握不难但真正用得顺手必须建立在大量实际场景的推敲上。每次遇到报错和慢查询都是把这两块知识彻底打通的好机会多写几次、多排查几轮自然就能形成肌肉记忆。 
长河君 · 2026/9/7 21:35:55 · 阅读 1.2k SQL查询与聚合函数实战：从执行逻辑到慢查询优化
很多人在刚接触数据库查询时往往把“查询语句”和“聚合函数”当成两个独立的知识点来学先学SELECT、WHERE、JOIN再学GROUP BY和聚合函数最后做练习题时才发现总是凑不到一块去。尤其在面试或实际开发中一个“统计订单量”“算客单价”的需求就能把这两块内容彻底打通。我最早在项目里写统计报表时也踩过不少坑比如COUNT(*)和COUNT(列)结果对不上、GROUP BY后面乱写列名导致SQL报错、IN子查询查出来一堆NULL让结果变空等等。这篇就专门聊聊表格查询语句和聚合函数怎么才能真正用熟练从执行逻辑到实战排查一次说清楚适合刚入门SQL的开发新手也适合写了不少查询但总被慢查询和死锁困扰的同学。1. 从需求到SQL先理清查询语句的执行逻辑1.1 别急着写SQL先搞懂语句执行顺序很多初学者学SQL时习惯按照书写顺序去理解先写SELECT再写FROM然后WHERE、GROUP BY、HAVING、ORDER BY一路往后排。但真实执行顺序完全不是这样。数据库引擎拿到一条查询语句后第一件事是确定数据从哪来也就是FROM然后是JOIN关联接着才是WHERE过滤再是对过滤结果做GROUP BY分组分组后使用HAVING过滤组之后才是SELECT投影出需要的列最后做ORDER BY排序和LIMIT分页。弄清楚这个顺序你才能理解为什么WHERE里不能直接用聚合函数做条件过滤为什么GROUP BY之后SELECT列名会受限。举个例子假如有一张订单明细表你想看每个客户的订单总金额并且只要总金额大于1000的客户。如果按照书写顺序写WHERE SUM(amount) 1000数据库在执行WHERE阶段时根本还没有分组SUM(amount)没有意义所以语法上直接不允许你需要用HAVING SUM(amount) 1000因为HAVING是在GROUP BY之后执行的。很多报错其实就是执行顺序没理顺造成的。1.2 一条查询语句的完整生命周期我们那一条最常见的查询拆开看。以MySQL为例客户端发送SQL后服务端会经历连接器、分析器、优化器、执行器四个阶段。分析器负责检查语法和词法如果你的SQL里表名写错了或者字段名不存在通常在这里就报错了优化器负责决定用哪个索引、以什么顺序关联表这直接影响查询快慢执行器最后调用存储引擎接口把满足条件的数据返回给客户端。理解这个生命周期后你在排查慢查询时就知道该去看执行计划因为执行计划反映的正是优化器决定出来的执行路径。很多查询慢的问题根本不是SQL本身写错了而是优化器选错了索引或者根本没用索引。比如你在WHERE条件里对索引列做了函数运算WHERE DATE(create_time) 2024-01-01这时索引就失效了因为优化器没法直接通过B树定位到具体日期范围。改成WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00让索引列保持原始形态才能走索引。1.3 用一个真实场景熟悉执行链路我拿一个电商项目的订单表来演示表结构比较简单CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, product_name VARCHAR(100), amount DECIMAL(10,2), status TINYINT COMMENT 1待支付 2已支付 3已取消, create_time DATETIME );现在要统计每个用户已支付订单的总金额并且只需要总金额大于1000的用户按总金额降序排列。正确写法SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE status 2 GROUP BY user_id HAVING SUM(amount) 1000 ORDER BY total_amount DESC;执行过程大致是先从订单表里筛出status2的记录然后按user_id分组对每组做SUM(amount)再过滤掉总金额小于等于1000的组最后排序返回。这一条语句就把WHERE、GROUP BY、HAVING、ORDER BY全部串起来了也是聚合查询最典型的骨架。2. 聚合函数核心用法与易错细节2.1 五大聚合函数的行为差异聚合函数是对一组值执行计算并返回单个值的函数SQL标准里最常用的就是COUNT、SUM、AVG、MAX、MIN。它们的行为逻辑完全围绕“分组”展开不写GROUP BY时整个结果集被当成一个大组所以SELECT COUNT(*) FROM orders能返回全表行数。COUNT用来统计行数SUM用来求和AVG用来算平均值MAX和MIN分别取最大值和最小值。行为差异上有几个点值得注意SUM和AVG会自动忽略NULL值因为NULL在SQL里表示未知参与加减乘除后结果还是未知所以直接跳过而COUNT(列)会忽略NULL但COUNT()不会忽略任何行。如果一列中有大量NULL你用COUNT(列)统计的数量和COUNT()查出来的行数对不上这属于正常现象不是bug。MAX和MIN对文本列也能使用按照字符排序规则取最大值或最小值。日期列也可以取最近或最早的日期。AVG要注意精度问题尤其对DECIMAL类型MySQL默认可能返回四舍五入后的结果如果需要更高精度可以使用ROUND(AVG(amount), 2)直接控制小数位。2.2 COUNT(*)与COUNT(列)的经典坑这个坑几乎每个SQL开发者都会遇到。COUNT(*)统计的是结果集中的总行数不管某列是否为NULLCOUNT(列名)统计的是该列非NULL值的个数。看个例子SELECT COUNT(*), COUNT(user_id), COUNT(status) FROM orders;如果orders表有100条记录其中user_id有5条为NULLstatus有10条为NULL那么COUNT()返回100COUNT(user_id)返回95COUNT(status)返回90。当我在这张表上去判断“用户是否填写了ID”时如果用COUNT()会误以为所有行都有ID必须用COUNT(user_id)。另一个常见误区是在LEFT JOIN后使用COUNT(右表列)来做存在性统计。比如统计有订单的用户数有人会这么写SELECT COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id o.user_id。如果某个用户没有订单那么o.id是NULLCOUNT(o.id)会忽略掉这些NULL行所以结果实际上只统计了有订单的用户数这个写法反而歪打正着。但如果把它换成COUNT(*)就会把无订单的用户也统计进去结果就错了。这再次说明SQL的语义差别必须靠实际数据来验证不能凭感觉写。2.3 GROUP BY与聚合函数组合的分组逻辑GROUP BY的本质是把结果集按一个或多个列的值分成多个小组聚合函数在每个小组内独立执行。这里最核心的规则是SELECT后面出现的非聚合列必须出现在GROUP BY里。比如前面订单表你要按用户统计写了SELECT user_id, product_name, SUM(amount) FROM orders GROUP BY user_idMySQL在ONLY_FULL_GROUP_BY模式下会直接报错因为product_name没有出现在GROUP BY里。原因很简单同一个user_id可能对应多个product_name数据库不知道该返回哪一个。如果确实需要每个组里的某一个具体值可以把product_name也加入GROUP BY或者在子查询里先聚合再关联取明细。这个报错是MySQL 5.7以后默认开启的严格模式带来的很多老教程没提这一点导致不少人从旧版本迁移过来后一脸懵。需要注意的是GROUP BY可以跟多个列比如GROUP BY user_id, status这表示先按user_id分组再在组内按status细分聚合函数会在每个(user_id, status)组合上执行。这种多列分组在做维度统计时非常常用比如“统计每个用户的每种订单状态数量和金额”就靠它。2.4 聚合结果过滤必须用HAVINGWHERE用于过滤行HAVING用于过滤组。这是SQL面试题里最常被问的概念之一。执行顺序上WHERE先执行HAVING后执行所以HAVING中可以使用聚合函数WHERE中不行。实际开发中我见过不少同事用WHERE来过滤聚合结果比如SELECT user_id, COUNT(*) AS cnt FROM orders WHERE COUNT(*) 5 GROUP BY user_id;这条SQL必然报错。正确做法是SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id HAVING COUNT(*) 5;还有一点值得注意HAVING的过滤发生在分组之后所以如果能在WHERE阶段过滤掉的数据尽量在WHERE里过滤这样参与分组的数据量更少聚合速度更快。比如上面例子里的status 2在WHERE里写和在HAVING里写都能得到同样的结果但WHERE写法性能更好因为减少了进入分组的数据行数。这条经验写进简历里都是加分项。3. 去重查询与复杂聚合实战3.1 DISTINCT与GROUP BY的去重区别去重是查询语句里非常高频的需求常见实现方式有两种SELECT DISTINCT和GROUP BY。二者在单列去重上效果几乎一样但思路不同。DISTINCT是“投影后去重”它在SELECT阶段对最终输出的结果集去重GROUP BY是“分组后取每组首行”它天然产生唯一分组键。举个例子查订单表中出现过的所有用户IDSELECT DISTINCT user_id FROM orders; SELECT user_id FROM orders GROUP BY user_id;两条语句结果一致。它们的底层执行逻辑不太一样DISTINCT在MySQL中通常会对结果集做排序或哈希去重而GROUP BY也会走排序或哈希分组。对于单列去重的场景两者性能差距不大但如果你还要同时统计每组的数量就必须用GROUP BY了因为聚合函数必须在分组语境下才成立。DISTINCT COUNT这种写法也只是对COUNT的结果做去重没有分组统计的能力。3.2 多列去重怎么实现多列去重有两种常见需求第一种是返回所有不重复的列组合用SELECT DISTINCT col1, col2即可第二种是只针对某几列判断重复但返回完整字段或统计数量这时DISTINCT就不够用了需要用GROUP BY配合聚合函数或子查询。比如订单表里有user_id和status你想知道每个用户每种状态各有多少单SELECT user_id, status, COUNT(*) AS order_count FROM orders GROUP BY user_id, status;如果你想查“每个用户最新的一条订单记录”典型的分组取最大值的场景直接GROUP BY没法拿到整行需要子查询或窗口函数SELECT o.* FROM orders o JOIN ( SELECT user_id, MAX(create_time) AS max_time FROM orders GROUP BY user_id ) t ON o.user_id t.user_id AND o.create_time t.max_time;不过这个方法在同一个用户有两条完全相同的create_time时会返回多行如果只要一行可以用窗口函数ROW_NUMBER()SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM orders ) t WHERE rn 1;相比之下窗口函数语义更清晰也更容易扩展到“取第二新”“取最早”之类的需求。3.3 聚合函数搭配CASE WHEN做条件统计一个高频场景是在同一张表里按某个条件拆成多个统计列。比如统计每个用户已支付和未支付的订单数常规做法是查两次再合并但一个更优雅的做法是用CASE WHEN配合COUNT或SUMSELECT user_id, COUNT(CASE WHEN status 2 THEN 1 END) AS paid_count, COUNT(CASE WHEN status 1 THEN 1 END) AS unpaid_count, SUM(CASE WHEN status 2 THEN amount ELSE 0 END) AS paid_amount FROM orders GROUP BY user_id;这里的技巧是CASE WHEN返回NULL时COUNT会忽略所以用COUNT(CASE WHEN ... THEN 1 END)可以实现条件计数。如果使用COUNT(CASE WHEN ... THEN 1 ELSE 0 END)结果就会变成统计该组总数因为ELSE 0并不计入COUNT的忽略范围COUNT(0)是非NULL值会被统计进去这又回到了COUNT(列)和COUNT(*)语义差异的坑。用SUM(CASE WHEN ... THEN amount ELSE 0 END)可以实现条件求和这在做不同状态下的金额统计时非常实用一次查询就能生成一张多维度的报表行。3.4 窗口函数与聚合函数的分工配合窗口函数是理解聚合函数之后一个很好的进阶方向。聚合函数把多行压缩成一行窗口函数则不改变行数而是在每一行都返回一个聚合计算的结果。比如你既要看每笔订单明细又要看每个用户的总金额用传统GROUP BY就做不到同时保留明细和汇总但用窗口函数可以SELECT id, user_id, amount, SUM(amount) OVER (PARTITION BY user_id) AS user_total FROM orders;这里SUM(amount) OVER (PARTITION BY user_id)会为每一行计算所属用户的总金额不压缩行数。窗口函数在分组排名、同比环比、移动平均等场景中非常强大但它的执行性能和普通聚合不同使用时要确保PARTITION BY和ORDER BY列上有合适的索引否则对全表数据做窗口计算可能比聚合查询慢得多。在实际项目中我的建议是如果只需要分组汇总结果用GROUP BY如果需要明细和汇总共存或者需要在分组内排名优先考虑窗口函数。两者互相配合能覆盖绝大多数统计分析需求。4. 高频报错IN查询语句的典型问题排查4.1 IN子句遇到NULL导致结果为空IN查询语句报错或者结果异常是开发中非常高频的问题。最经典的就是IN子查询返回了NULL值导致的“看似正确但结果不对”。看下面的例子SELECT * FROM orders WHERE user_id IN ( SELECT user_id FROM users WHERE level vip );如果子查询中某一行user_id为NULLIN的语义是“等于列表中的任意一个”。而SQL中任何值与NULL比较的结果都是“未知”不会返回TRUE。所以当IN列表里包含NULL时匹配结果会少掉那些原本应该通过其他值匹配出来的行甚至在某些数据库连结果都不返回。解决办法是让子查询排除NULLWHERE user_id IN ( SELECT user_id FROM users WHERE level vip AND user_id IS NOT NULL );如果是IN后直接跟手动指定的列表比如IN (1, 2, NULL)也要尽量排除NULL避免语义不清晰。4.2 IN列表过大导致慢查询当IN后面的列表包含数千甚至上万个值时查询性能会急剧下降。这里有两个层面第一SQL语句本身会变得很长网络传输和解析成本上升第二优化器很难对这么大的IN列表做有效的索引范围扫描可能退化为全表扫描。我在实际工作中遇到过一个案例同事把一万多个ID拼进IN列表去查数据结果一条查询跑了十几秒。排查后先用临时表方案解决把ID批量导入临时表然后JOIN临时表查询性能从十几秒降到了几百毫秒。另一个思路是分批查询把一万个ID拆成每批500个分多次查询再合并结果但这样会增加应用层复杂度。对于MySQL如果IN列表不是特别大比如几百个且列上有索引通常还是能走到索引的但超过千级别后优化器可能就不再走索引了。此时最稳妥的方式是使用临时表或JOIN。4.3 IN与EXISTS的语义和性能差异IN和EXISTS在很多场景能互相替换但语义有差异。IN会先执行子查询把结果集物化再和外部查询逐行匹配EXISTS则是一种“存在性检查”只要子查询返回任何一行就停止不关心具体内容。当子查询的结果集很小而外层表很大时IN通常表现不错当外层表很大、子查询结果集也很大时EXISTS配合索引关联往往更高效。实践中有一个常见误区EXISTS子查询里SELECT *和SELECT 1性能相同因为EXISTS只看是否存在行不看选择列。所以你可以放心写SELECT 1。4.4 IN查询报错的定位方法遇到IN语句报错时我是按这个顺序排查的第一步检查子查询本身能不能独立执行。单独运行子查询看返回结果是否包含NULL或异常数据类型。第二步检查数据类型是否匹配。比如外层的user_id是INT子查询返回的是VARCHAR类型某些数据库会隐式转换导致索引失效甚至报错。第三步查看执行计划确认是否走了索引。如果没走索引考虑改写为JOIN或EXISTS。第四步检查IN列表长度是否过大并评估是否需要拆分为批量查询。用EXPLAIN看执行计划时如果Extra列出现“Using where”而不见索引使用记录就要特别小心。IN列表过大会导致优化器放弃索引直接扫全表这时type列会变成ALL或indexkey列变成NULL问题定位就非常直观了。5. 慢查询、死锁与语句阻塞的MySQL排查实录5.1 开启慢查询日志找到真正的“罪魁祸首”慢查询是所有数据库应用都会面临的问题MySQL里最直接的定位工具就是慢查询日志。默认情况下MySQL慢查询日志是关闭的可以临时开启或直接写进配置文件SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;long_query_time1表示执行时间超过1秒的SQL都会被记录。线上环境建议把这个阈值设成0.5或1太低会记录太多噪声太高容易漏掉潜在问题。拿到慢查询日志后使用mysqldumpslow工具可以快速聚合出频率最高的慢SQLmysqldumpslow -s at -t 10 /var/log/mysql/slow.log-s at表示按平均查询时间排序-t 10表示只看排名前十的SQL。这些通常就是最值得优化的对象。注意慢查询日志并不直接告诉你连接信息只记录SQL文本和耗时后期要根据SQL特征定位到具体业务接口。5.2 用EXPLAIN看执行计划逐项排查慢的原因定位到慢SQL后我一般会在慢SQL前面加EXPLAIN关键字查看执行计划。重点关注几个字段type列是访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALL。如果看到ALL或index基本可以判断是全表扫描或全索引扫描需要警惕。key列显示实际用到的索引如果为NULL就表示没走索引。rows列是优化器预估扫描的行数这个数越大查询越慢。Extra列经常隐藏关键线索Using filesort表示文件排序Using temporary表示使用临时表Using where表示服务层过滤。举个例子有一个订单查询慢EXPLAIN结果如下EXPLAIN SELECT * FROM orders WHERE user_id 1 ORDER BY create_time DESC;发现type为ALLkey为NULL说明没走索引。检查之后发现user_id上确实有索引但SQL里写的是WHERE user_id 1字符串和INT类型隐式类型转换导致索引失效。改成数字类型后type变成了ref查询瞬间变快。这就是执行计划发现隐式转换问题的典型过程。5.3 死锁是怎么产生的如何避免死锁在MySQL里通常发生在多个事务同时以不同顺序获取多个锁时是典型的加锁顺序不一致导致的“循环等待”。假设事务A先锁了订单表的某一行再尝试更新用户表某一行事务B先锁了用户表的同一行再尝试更新订单表的那一行。两个事务互相等待对方释放锁MySQL检测到死锁后会自动回滚其中一个事务并报错错误码为1213。我遇到过一个真实案例两个批量更新任务同时跑一个按订单ID升序更新另一个按订单ID降序更新结果在某些数据边界上产生死锁。解决方式有两种一是统一加锁顺序所有事务都按同一个排序规则获取锁二是把一个大事务拆分成多个小事务减少锁持有时间。另一个常见的死锁场景是批量插入数据时并发冲突唯一索引两个事务都在插入相同的键各自持有间隙锁和插入意向锁最终互相等待。避免方法是使用INSERT ... ON DUPLICATE KEY UPDATE或先查询再插入的幂等方案减少并发冲突的可能。5.4 语句阻塞的排查思路与常用命令语句阻塞简单来说就是一个事务持有了锁但迟迟不提交或回滚导致其他事务一直在等待。MySQL中可以通过以下命令找出当前阻塞链SELECT * FROM information_schema.innodb_trx; SELECT * FROM information_schema.innodb_lock_waits; SELECT * FROM performance_schema.data_lock_waits;第一个语句列出了所有正在运行的事务可以看到trx_state字段是LOCK WAIT还是RUNNING以及trx_started时间。如果某个事务已经运行了很久但没有任何更新操作很可能是应用层忘记提交事务。innodb_lock_waits能直接看到谁在等待谁。blocking_trx_id是被阻塞事务等待的事务IDblocking_lock_id对应的行在innodb_trx表里能看到持锁事务的信息。定位到持锁事务后需要去业务应用里检查这个事务对应的代码确认是应该提交还是应该回滚。如果线上急需恢复可以执行KILL 1060115;这里1060115是持锁事务的trx_mysql_thread_id。杀事务是最后手段一定要确认该事务没有进行关键业务操作否则可能造成数据不一致。5.5 索引设计对慢查询的决定性影响排查慢查询时索引设计几乎是绕不开的环节。索引的价值在于用空间换时间但也不是越多越好。每个索引在插入、更新、删除时都需要维护索引过多会拖慢写入性能。联合索引要遵循最左前缀原则比如建立了索引(idx_user_status)即(user_id, status)那么WHERE user_id ?能走索引WHERE user_id ? AND status ?也能走但WHERE status ?单独使用就无法走这个联合索引。回表问题也值得留意如果查询的列都包含在索引里就能实现覆盖索引避免回表查找数据行。比如订单表有(user_id, status)联合索引执行SELECT user_id, status FROM orders WHERE user_id 1;这个查询可以只扫索引就返回结果Extra列显示Using index性能非常好。而SELECT *就不得不回表取完整行开销更大。查询频繁且数据量大的场景可以适当设计一些覆盖索引来提升性能。6. 聚合函数在不同数据库中的细节差异6.1 MongoDB聚合管道和传统SQL聚合的映射MongoDB的聚合函数不是传统SQL风格的SELECT COUNT()而是通过聚合管道Aggregation Pipeline分阶段处理。常用的阶段有$match、$group、$sort、$project等它们和SQL的WHERE、GROUP BY、ORDER BY、SELECT可以一一对应。比如订单表在MongoDB里要统计每个用户的订单数量db.orders.aggregate([ { $match: { status: 2 } }, { $group: { _id: $user_id, count: { $sum: 1 }, totalAmount: { $sum: $amount } } }, { $sort: { totalAmount: -1 } } ]);$match相当于WHERE$group相当于GROUP BY$sum相当于SUM。相比SQLMongoDB聚合管道的好处是每个阶段都显式声明便于理解数据流在每个阶段的形态变化但代价是管道如果很长性能消耗也不小。6.2 MSSQL CLR聚合函数排序失效的问题MSSQL里可以通过CLR自定义聚合函数来扩展原生聚合能力比如实现自定义的字符串拼接、分组TopN等。很多人写完CLR聚合函数后发现排序顺序不生效问题往往出在SQL Server对CLR聚合函数的数据处理方式上。CLR聚合函数在处理输入数据时默认是按索引顺序流式读取的如果查询中没有明确的ORDER BY或者索引顺序和期望顺序不一致传入聚合函数的数据顺序就不稳定。解决方法是重写聚合逻辑在聚合函数内部做排序或者在查询外部用子查询先排好序再聚合。例如SELECT user_id, dbo.MyCustomAgg(column1) FROM ( SELECT user_id, column1 FROM orders ORDER BY user_id, create_time DESC ) AS sorted GROUP BY user_id;子查询里的ORDER BY有时候会被优化器忽略更稳妥的方式是在CLR代码内部维护一个有序列表在Terminate时统一输出这样排序就与外部查询计划无关了。6.3 不同数据库聚合函数的兼容性聚合函数语法在不同数据库中有高度相似之处但仍然存在不少小差异。比如字符串聚合函数MySQL的GROUP_CONCAT和PostgreSQL的STRING_AGG、SQL Server的STRING_AGG、Oracle的LISTAGG功能类似函数名完全不同。做跨数据库迁移时这部分代码必须逐个改写。还有NULL处理逻辑的差异。MySQL的AVG忽略NULL值但如果你希望把NULL当作0参与计算需要提前用IFNULL或COALESCE处理。SQL Server同样忽略NULL但在一些特殊聚合场景下表现略有不同。写跨数据库代码时最好用COALESCE(列, 0)显式转换避免同一个SQL语句在两个数据库里计算结果不一致。需要注意的是SQL标准本身也在演进新版本数据库会引入新的聚合能力。保持对标准SQL的熟悉同时关注目标数据库的官方文档是最好的兼容性策略。7. 一个完整的MySQL聚合与慢查询优化案例7.1 案例背景前阵子我帮一个团队优化报表查询场景是统计每天每个商品类目的销售额和订单量。原始SQL长这样SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, category_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders WHERE create_time 2024-01-01 AND create_time 2024-02-01 GROUP BY day, category_id ORDER BY day, total_amount DESC;这个查询在订单量几百万的情况下执行时间从最初的几百毫秒一路涨到十几秒报表页面卡到无法接受。优化前先用EXPLAIN看执行计划发现Extra列里有Using temporary和Using filesort说明MySQL为了GROUP BY和ORDER BY创建了临时表并做了文件排序这是性能瓶颈所在。7.2 优化前的分析执行计划显示type为ALL扫描行数接近全表索引完全没有派上用场。原因很直接create_time虽然有索引但DATE_FORMAT(create_time, %Y-%m-%d)对列做了函数计算导致索引失效。同时GROUP BY中使用了表达式day这也是导致临时表的诱因之一。第一项优化是把日期表达式改成范围查询让索引走起来WHERE create_time 2024-01-01 AND create_time 2024-02-01然后分组列改成原始时间列的前缀方式MySQL 5.7以上支持在GROUP BY中使用表达式但仍会引入临时表。更好的做法是增加一个冗余字段day_dateDATE类型写入时直接落明细日期查询时GROUP BY day_date索引和分组都更高效。7.3 优化后的SQL与效果加冗余字段后优化SQL可以写成SELECT day_date, category_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders WHERE day_date 2024-01-01 AND day_date 2024-01-31 GROUP BY day_date, category_id ORDER BY day_date, total_amount DESC;这时候EXPLAIN的type变成了ref或rangeExtra列不再出现Using temporary和Using filesort。执行时间从十几秒降到几百毫秒以内效果非常明显。另一个思路是如果表已经很大且实时统计压力大可以构建汇总表每分钟或每小时把聚合结果写入单独的统计表查询时直接读汇总表性能提升更显著。具体做法是把原订单表作为明细表汇总表存储(day_date, category_id)的订单数和销售额用定时任务或事件调度器增量更新。这样即使数据量再翻几倍报表查询也可以维持在毫秒级。7.4 案例给我们的几个经验这个案例里踩的坑很典型总结几条经验第一查询语句里对索引列做函数运算是让索引失效最常见的原因能用范围查询就尽量用范围查询。第二GROUP BY的列尽量使用原始列或冗余列避免在分组字段上做表达式计算以减少Using temporary的触发概率。第三ORDER BY的字段如果和GROUP BY字段不一致很容易触发Using filesort必要时可以调整排序字段或者用子查询。第四对于固定维度的统计需求汇总表是成本最低收益最高的方案和索引优化配合使用效果最好。每次优化完查询都要重新用EXPLAIN回归一遍确认执行计划和预期一致避免改完SQL结果相同但性能反而变差的情况。8. 查询与聚合中的实战心得分享8.1 规范化SQL书写习惯在实际业务中SQL写得规范不规范直接影响后续排查和维护成本。我的习惯是保留字都用大写表名字段名用小写别名的命名清晰有含义比如total_amount不要写成ta。多表关联时使用表前缀比如o.user_id避免多张表有同名字段时产生歧义。GROUP BY和ORDER BY的字段顺序尽量保持一致减少排序开销。另外每个查询在编写时就要想到执行计划的合理性而不是写完能跑就完事。遇到问题多EXPLAIN几次建立“先看执行计划再优化”的肌肉记忆比依赖断点调试SQL效率高得多。8.2 聚合查询性能优化清单尽量在WHERE阶段过滤掉不需要的数据减少参与分组的数据量。避免对索引列做函数或隐式类型转换保证索引可用。GROUP BY和ORDER BY使用同一组字段减少文件排序。COUNT(*)用COUNT(主键)或COUNT(1)没有明显性能差异不用刻意纠结这个但COUNT(列)语义要清楚。大表聚合查询优先考虑汇总表、物化视图或定时预计算方案。使用EXPLAIN ANALYZEMySQL 8.0支持可以查看实际执行时间和每步扫描行数优化瓶颈定位更准确。8.3 最后一个小技巧如果在一个查询里既需要总数又需要分页后的明细比如“返回订单列表同时返回符合条件的总条数”很多人会写两条SQL一条COUNT统计一条SELECT分页查询。这个做法本身没问题但一次请求两次查询会多一次Round Trip。可以用窗口函数COUNT(*) OVER()在明细查询里同时返回总数SELECT id, user_id, amount, COUNT(*) OVER() AS total_count FROM orders WHERE status 2 ORDER BY create_time DESC LIMIT 0, 20;每一行都会携带total_count字段应用层直接取第一行即可。这种方式在数据量不大时很实用少一次数据库交互。数据量很大时仍然建议分两条SQL因为窗口函数会先计算全量计数再分页性能不一定更优。查询语句和聚合函数这两块内容掌握不难但真正用得顺手必须建立在大量实际场景的推敲上。每次遇到报错和慢查询都是把这两块知识彻底打通的好机会多写几次、多排查几轮自然就能形成肌肉记忆。