GROUP BY 与 COUNT 的效率问题filesort、临时表大家好我是一名拥有10年以上经验的DBA老兵。做这个系列源于一个朴素的愿望把踩过的坑、总结的经验系统化输出希望能帮到刚入行或想进阶的兄弟们。让我们开始今天的第12天内容。面试考点GROUP BY 什么时候会用临时表什么时候用 filesortCOUNT(*)、COUNT(1)、COUNT(列名) 到底哪个快GROUP BY 为什么会扫描全表怎么优化到走索引派生表 vs 临时表有什么区别为什么派生表有时更慢MySQL 8.0 对 GROUP BY 做了什么优化Loose Index Scan、取消隐式排序背景引入 说白了GROUP BY 写起来简单但它偷偷给你挖了俩大坑——临时表 filesort。有天凌晨 1 点数据中台同学突然打电话说报表卡了半个小时了明天老板看不到报表要骂人的。我登录数据库一看CPU 100%有一条 SQL 跑了 28 分钟还没结束。类似这样的 SQLSELECTcity,COUNT(*)AScntFROMordersWHEREorder_time2026-04-01GROUPBYcityORDERBYcntDESCLIMIT10;一看执行计划——Extra 列写着Using where; Using temporary; Using filesort。你知道这仨词凑一块意味着什么吗我问那个写 SQL 的同学。他说“就是查出来慢一点呗。”我说——这等于 MySQL 在内存里给你建了张临时表把 3000 万行一行行插进去然后全表扫描一遍排序最后返回 10 行。这不是 SQL这是 CPU 烤机程序。他当时就傻了“我以为 GROUP BY 不就是分个组吗”今天的目标把 GROUP BY 和 COUNT 的性能陷阱一个一个拆给你看——面试必问、报表必踩。核心概念GROUP BY 的流水线究竟在干嘛先给你拆解一下 GROUP BY 的执行流程。你以为它很简单其实它做了三件事第一步把数据按分组字段排序。因为相同值的行挨在一起才能分组统计。第二步遍历排序后的结果一边遍历一边计数。遇到相同值就累加遇到新值就新开一个分组。第三步如果还有 ORDER BY再排一次。如果你 GROUP BY 之后还要排序那就是二次排序。问题在哪MySQL 怎么拿到有序的数据呢如果分组字段上没有索引MySQL 就没法直接按顺序遍历。这时候它只能建一张临时表把符合条件的行全插进去对临时表做 filesort遍历排序后的临时表做分组3000 万行数据先写临时表再全表排序再遍历。这三步每一步都在烧 CPU。什么时候会触发 Using temporaryMySQL 的规则很简单如果 GROUP BY 的列没有可用索引 → 先排序才能分组 → 排序需要空间 → 建临时表。但可用索引比你以为的苛刻——不是有索引就行索引列的顺序决定一切。举个例子你有一张订单表order_time有索引但city没有索引SELECTcity,COUNT(*)FROMordersWHEREorder_time2026-04-01GROUPBYcity;会用临时表。因为虽然order_time可以过滤但过滤后的数据按city分组时city没有索引MySQL 只能先建临时表排序。那建个联合索引idx(order_time, city)行不行——不一定。因为 WHERE 是order_time 范围查询索引扫描时数据按order_time排序而不是按city。同一个order_time内的 city 有序但不同 order_time 的 city 交错在一起——GROUP BY 还是需要临时表。正确的索引顺序是idx(city, order_time)——GROUP BY 列在前。MySQL 可以走 Loose Index Scan直接按city遍历索引对于每个 city只需要检查该 city 下有没有符合order_time ...的行。记忆法则WHERE 是等值查询时索引顺序 WHERE 列在前WHERE 是范围查询时索引顺序 GROUP BY 列在前。面试必问Q: GROUP BY 一定需要排序吗不一定。MySQL 8.0 之前GROUP BY 默认会隐式排序。8.0 开始取消了隐式排序。但不管哪个版本只要 GROUP BY 的列有合适的索引都不需要排序也不需要临时表。Q: 临时表是在内存里还是磁盘上默认在内存里但如果数据量超过tmp_table_size就会转成磁盘临时表。磁盘临时表的性能是内存的 1/10 甚至更低。这就是为什么测试环境 10 万行没问题生产 3000 万行就炸了的原因。COUNT 三兄弟*、1、列名到底谁最快这题面试 10 个人有 8 个答错。COUNT(*)-- 统计行数COUNT(1)-- 统计行数COUNT(city)-- 统计 city 不为 NULL 的行数我见过很多技术负责人跟开发说“不要用 COUNT(*)要改成 COUNT(1)更快。”这是谣言。从 MySQL 5.7 开始COUNT(*) 和 COUNT(1) 的执行计划完全一样性能没有区别。MySQL 优化器会把它们优化成同一种执行方式——统计行数不读取列值。但 COUNT(列名) 不一样——它需要读取这一列的值然后排除 NULL。这才是真的慢。写法含义性能推荐COUNT(*)统计行数不管 NULL最快✅ 推荐COUNT(1)统计行数不管 NULL和 COUNT(*) 一样✅ 可以用COUNT(列名)统计该列不为 NULL 的行数最慢❌ 除非你就是要排除 NULL还有一个常见坑COUNT(DISTINCT city)。这个写法会先去重再计数。如果 city 没有索引MySQL 会先建临时表去重再统计。又是一轮临时表 filesort。面试解答Q: Using filesort 是不是一定在磁盘上排序filesort 这个名字起得特别坑人。filesort 不一定在磁盘上排序。它分两种Quick sort数据量小于sort_buffer_size在内存里排序Merge sort数据量大分成多个小块在内存排完再归并到磁盘但不管在哪排序——只要出现了 filesort就说明你没有用上索引排序——这才是慢的根本原因。Q: GROUP BY 出来的结果默认是排序的吗MySQL 5.7 及之前GROUP BY x 等效于 GROUP BY x ORDER BY x结果是排序的。8.0 开始取消了隐式排序结果顺序不保证有序。这就是为什么有些同学升级到 8.0 后发现 GROUP BY 结果顺序变了——不是 bug是官方改了。Q: 怎么让 GROUP BY 不用临时表核心原则让 GROUP BY 的列成为索引的前缀且是连续的。但有一个关键细节WHERE 是等值→ 索引顺序WHERE 列在前GROUP BY 列在后WHERE 是范围,→ 索引顺序GROUP BY 列在前WHERE 列在后比如(a, b, c)上有联合索引WHERE a xxx GROUP BY b→ ✅ 不用临时表(a, b, c)上有联合索引WHERE a xxx GROUP BY b→ ❌ 仍用临时表因为范围条件打断前缀(b, a)上有联合索引WHERE a xxx GROUP BY b→ ✅ 可以用 Loose Index Scan实战案例执行环境要求MySQL 5.7 或 8.0以下均为可执行的测试 SQL案例一GROUP BY 有无索引的天差地别准备工作请先在你的 MySQL 中执行以下建表和数据插入-- ① 建表只建 order_time 索引DROPTABLEIFEXISTSorders;CREATETABLEorders(idINTPRIMARYKEYAUTO_INCREMENT,cityVARCHAR(50),amountDECIMAL(10,2),order_timeDATETIME,statusVARCHAR(20),INDEXidx_order_time(order_time));-- ② 插入测试数据INSERTINTOorders(city,amount,order_time,status)VALUES(北京,100.00,2026-04-01 10:00:00,paid),(上海,200.00,2026-04-01 11:00:00,paid),(北京,150.00,2026-04-01 12:00:00,paid),(上海,250.00,2026-04-02 10:00:00,paid),(广州,180.00,2026-04-02 11:00:00,paid);ANALYZETABLEorders;① 只有 order_time 索引-- ③ 请执行 EXPLAIN把输出贴给我EXPLAINSELECTcity,COUNT(*)AScntFROMordersWHEREorder_time2026-04-01GROUPBYcity;--------------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | --------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | NULL | range | idx_order_time | idx_order_time | 6 | NULL | 5 | 100.00 | Using index condition; Using temporary | ---------------------------------------------------------------------------------------------------------------------------------------------------关键发现Using temporary已经出现——临时表的代价从这里开始。虽然这次没显示Using filesort数据量小、版本差异但只要Using temporary出现数据量一上来 filesort 必来。看到了吧Using index conditionICP 下推只能减轻过滤负担改不了分组必须建临时表的命运。② 加了联合索引后的效果翻车现场先按我之前说的方案跑-- ④ 加联合索引 WHERE列在前ALTERTABLEordersADDINDEXidx_order_time_city(order_time,city);-- ⑤ 再跑 EXPLAINEXPLAINSELECTcity,COUNT(*)AScntFROMordersWHEREorder_time2026-04-01GROUPBYcity;------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | NULL | index | idx_order_time,idx_order_time_city | idx_order_time_city | 209 | NULL | 5 | 100.00 | Using where; Using index; Using temporary | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Using temporary 还在翻了。为什么MySQL 用这个索引时数据是按order_time排序的同一天上海和北京的订单交错在一起city 没有全局有序。⚠️关键坑WHERE 是范围查询索引按order_time排序后city只对同一个order_time值内有序。GROUP BY 需要city全局连续——做不到。③ 修复换个索引顺序-- ⑥ 建对索引GROUP BY 列在前WHERE 列在后ALTERTABLEordersADDINDEXidx_city_order_time(city,order_time);-- ⑦ 再跑EXPLAINSELECTcity,COUNT(*)AScntFROMordersWHEREorder_time2026-04-01GROUPBYcity;---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | NULL | index | idx_order_time,idx_order_time_city,idx_city_order_time | idx_city_order_time | 209 | NULL | 5 | 100.00 | Using where; Using index | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Using temporary 消失了。Extra只剩Using where; Using index。换了个索引顺序执行路径完全不同。用EXPLAIN FORMATTREEMySQL 8.0.22看得更清楚- Group aggregate: count(0) - Filter: (orders.order_time TIMESTAMP2026-04-01 00:00:00) (cost0.75 rows5) - Index scan on orders using idx_city_order_time (cost0.75 rows5)干干净净——先走idx_city_order_time索引扫描city 天然有序直接分组聚合中间一行Filter搞定 WHERE 条件。没有临时表没有 filesort。在我们那个报表场景3000 万行从 28 分钟降到 3 秒。数据中台同学都惊了“我改了什么怎么这么快”我说“没改 SQL就加了个索引——关键是把 GROUP BY 列放前面。”同一个 SQL索引顺序不同差了一整个临时表。派生表 vs 临时表你以为一样的其实差远了很多人以为派生表和临时表是一回事。不是。派生表指 FROM 子句里的子查询比如FROM (SELECT ...) t。MySQL 会把这个子查询的结果物化成一个临时表——但它默认没有索引。如果外层还要对这个派生表做 JOIN 或者 WHERE那就是全表扫描慢得飞起。-- 这个派生表 t 没有索引SELECTt.city,SUM(t.cnt)FROM(SELECTcity,COUNT(*)AScntFROMordersGROUPBYcity)tWHEREt.cnt100GROUPBYt.city;临时表指 MySQL 在执行过程中自己建的内部表Using temporary用于排序、分组、去重。核心区别派生表临时表谁建的你的 SQL 写的MySQL 自己建的能不能加索引5.6 及之前不能。5.7部分场景自动加。8.0CTE 替代不能MySQL 内部管理常见坑派生表结果集大 外层 JOIN 慢GROUP BY 无索引触发8.0 解法用 CTE 替代派生表可读性更好优化器处理也更聪明建联合索引MySQL 8.0 的 CTEWITH cte AS (SELECT ...) SELECT ... FROM cte。和派生表功能一样但① 可以多次引用同一个 CTE② 物化只执行一次③ 执行计划更可控。案例二COUNT(DISTINCT) 的隐藏坑很多人写去重计数喜欢这么写SELECTcity,COUNT(DISTINCTstatus)FROMordersGROUPBYcity;这个写法在 MySQL 里会触发什么先按 city 分组每个分组里再对 status 去重。每个分组都要建一次临时哈希表。改成这样写更清晰-- 先去重再分组SELECTcity,COUNT(*)FROM(SELECTDISTINCTcity,statusFROMorders)tGROUPBYcity;⚠️但注意这个改法是先整体去重再分组适合分组数多但每个分组去重开销不大的场景。如果反过来——status基数极大分组数极少——那COUNT(DISTINCT)反而可能更快。没有银弹。MySQL 8.0 的 Lateral Derived Table还能这么写-- 8.0.14 支持SELECTu.name,o.cntFROMusers u,LATERAL(SELECTCOUNT(DISTINCTcity)AScntFROMordersWHEREuser_idu.id)o;LATERAL 允许派生表引用前面的表字段省掉了关联子查询逐行执行的痛苦。面试问到 8.0 新特性时可以提这个。-- ⑨ 请在你的环境跑一下 EXPLAINEXPLAINSELECTcity,COUNT(DISTINCTstatus)FROMordersGROUPBYcity;-- ⑩ 然后跑改写后的版本EXPLAINSELECTcity,COUNT(*)FROM(SELECTDISTINCTcity,statusFROMorders)tGROUPBYcity;-- ⑨ COUNT(DISTINCT) 原写法 ------------------------------------------------------------------------------------------------------------------------------------------------ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | NULL | index | idx_order_time_city,idx_city_order_time | idx_city_order_time | 209 | NULL | 5 | 100.00 | NULL | ------------------------------------------------------------------------------------------------------------------------------------------------ -- ⑩ 派生表改写版本 -------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | -------------------------------------------------------------------------------------------------------------------- | 1 | PRIMARY | derived2 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using temporary | | 2 | DERIVED | orders | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using temporary | --------------------------------------------------------------------------------------------------------------------出人意料COUNT(DISTINCT)的 Extra 是 NULL而派生表改写版反而多了一层Using temporary。原因在这里idx_city_order_time已经让 GROUP BY 走索引了COUNT(DISTINCT status)在每个 city 分组内只有 3~5 个可能值内存里一个哈希表就搞定了——不需要磁盘级临时表。而派生表方案先把city status整体去重物化成一个派生表外层再 GROUP BY凭空多了一道工序。这就是为什么我说没有银弹——有索引时COUNT(DISTINCT)可能更好没索引时派生表方案可能更好。一切看 EXPLAIN别猜。避坑指南⚠️ 真实踩过的坑GROUP BY 列没索引 临时表 filesort只要 GROUP BY 的列没有可用索引必然触发临时表和排序建议WHERE 等值 → 建idx(WHERE列, GROUP BY列)WHERE 范围 → 建idx(GROUP BY列, WHERE列)GROUP BY ORDER BY 不同列 二次排序GROUP BY city 按 city 排序完了 ORDER BY cnt 按计数排序等于排了两次建议能不能去掉排序或者业务妥协临时表转磁盘 性能暴跌tmp_table_size 默认 16M数据量一大就转磁盘建议不是调大 tmp_table_size而是优化 SQL 让它不用临时表COUNT(DISTINCT) 在大数据量下 去重开销大每个分组都要去重分组多了开销爆炸建议先整体去重再分组GROUP BY 多列索引顺序不对 白建了建了 idx(city, status)但 GROUP BY status, city → 用不上索引建议GROUP BY 列的顺序和索引列的顺序必须一致 GROUP BY 优化五步走下次写 GROUP BY按这个顺序检查第一步看 WHERE 条件是什么 第二步看 GROUP BY 的列是什么 第三步这俩加起来有没有联合索引 ├── 有 → 看 WHERE 是等值还是范围 │ ├── 等值 → WHERE 列在前 OK │ └── 范围 → 必须 GROUP BY 列在前 └── 没有 → 建索引按上述规则排顺序 第四步看有没有 ORDER BY ├── ORDER BY 和 GROUP BY 同列 → 索引搞定 └── ORDER BY 不同列 → 能不能去掉排序 第五步看 COUNT 写的是什么 ├── COUNT(*) 或 COUNT(1) → 没问题 ├── COUNT(列名) → 确认你真的需要排除 NULL 吗 └── COUNT(DISTINCT) → 考虑能不能先去重再分组延伸讨论为什么列存做 GROUP BY 是降维打击聊到这里必须说一句MySQL InnoDB 是行存天生就不适合大规模聚合查询。你想想刚才的执行流程为了统计city分组MySQL 要把整行数据id、city、amount、order_time、status…都读出来然后才能拿到city字段的值去分组。读了 90% 根本用不上的数据。如果是列存ClickHouse、Doris、StarRocks…只需要读取city这一列的数据其他列完全不碰数据是按列连续存储的IO 量是行存的 1/NN 是列数对于 SUM/COUNT 这种聚合CPU 可以直接对连续内存做向量运算甚至不需要 GROUP BY 完再排序很多列存引擎直接在聚合过程中就完成了排序简单说行存 3000 万行要读 3000 万行完整数据列存只需要读 3000 万个 city 值。这不是优化不优化的问题这是存储引擎的基因问题。所以很多公司的架构是业务写入走 MySQL行存点查快报表分析走列存引擎GROUP BY 快中间通过 Binlog 同步这才是解决报表慢的根本方案——而不是在 MySQL 上死磕索引。思考题 互动时间你写过的最慢的 GROUP BY SQL 慢到什么程度当时是怎么优化的面试官问你COUNT(*)、COUNT(1)、COUNT(主键)、COUNT(普通列) 四个哪个最快你怎么排顺序业务需求统计每个城市每天的订单量你会怎么建索引总结面试考点GROUP BY 无索引 临时表 filesort这是 GROUP BY 慢的根本原因联合索引优化 GROUP BYWHERE 等值 →idx(WHERE列, GROUP BY列)WHERE 范围 →idx(GROUP BY列, WHERE列)COUNT(*) COUNT(1)5.7 性能一样都比 COUNT(列名) 快COUNT(DISTINCT)大数据量下开销大考虑先去重再分组临时表转磁盘数据量超过 tmp_table_size 会转磁盘性能暴跌MySQL 8.0 取消隐式排序不要依赖 GROUP BY 默认排序列存 vs 行存大规模 GROUP BY 是列存的主场行存天生不占优势今日行动打开你的慢查询日志搜一下Using temporary和Using filesort。找到一条看一下它的 GROUP BY 列是什么有没有合适的索引。今天就优化一条——改完跑一下 EXPLAIN看 Extra 是不是清爽了。截图发到评论区告诉我你救回了多少毫秒。下期预告ORDER BY 的实现原理 —— 面试必问有问题欢迎评论区交流明天见
