【MySQL百日打怪升级第12天】GROUP BY 与 COUNT 的效率问题：filesort、临时表

GROUP BY 与 COUNT 的效率问题：filesort、临时表

大家好，我是一名拥有10年以上经验的DBA老兵。

做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。

让我们开始今天的第12天内容。

🎯面试考点

• GROUP BY 什么时候会用临时表？什么时候用 filesort？
• COUNT(*)、COUNT(1)、COUNT(列名) 到底哪个快？
• GROUP BY 为什么会扫描全表？怎么优化到走索引？
• 派生表 vs 临时表有什么区别？为什么派生表有时更慢？
• MySQL 8.0 对 GROUP BY 做了什么优化（Loose Index Scan、取消隐式排序）？

背景引入

💡 说白了：GROUP BY 写起来简单，但它偷偷给你挖了俩大坑——临时表 + filesort。

有天凌晨 1 点，数据中台同学突然打电话，说报表卡了半个小时了，明天老板看不到报表要骂人的。

我登录数据库一看，CPU 100%，有一条 SQL 跑了 28 分钟还没结束。

类似这样的 SQL：

SELECTcity,COUNT(*)AScntFROMordersWHEREorder_time>='2026-04-01'GROUPBYcityORDERBYcntDESCLIMIT10;

一看执行计划——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 就没法直接按顺序遍历。这时候它只能：

1. 建一张临时表
2. 把符合条件的行全插进去
3. 对临时表做 filesort
4. 遍历排序后的临时表做分组

3000 万行数据，先写临时表，再全表排序，再遍历。这三步，每一步都在烧 CPU。

什么时候会触发 Using temporary？

MySQL 的规则很简单：

如果 GROUP BY 的列没有可用索引 → 先排序才能分组 → 排序需要空间 → 建临时表。

但"可用索引"比你以为的苛刻——不是有索引就行，索引列的顺序决定一切。

举个例子，你有一张订单表，order_time有索引，但city没有索引：

SELECTcity,COUNT(*)FROMordersWHEREorder_time>='2026-04-01'GROUPBYcity;

会用临时表。因为虽然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(DISTINCT city)。

这个写法会先去重再计数。如果 city 没有索引，MySQL 会先建临时表去重，再统计。又是一轮临时表 + filesort。

面试解答

Q: Using filesort 是不是一定在磁盘上排序？

filesort 这个名字起得特别坑人。filesort 不一定在磁盘上排序。它分两种：

1. Quick sort：数据量小于sort_buffer_size，在内存里排序
2. 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_time>='2026-04-01'GROUPBYcity;

+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+----------------------------------------+ | 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 condition（ICP 下推）只能减轻过滤负担，改不了分组必须建临时表的命运。

② 加了联合索引后的效果（翻车现场）

先按我之前说的方案跑：

-- ④ 加联合索引 WHERE列在前ALTERTABLEordersADDINDEXidx_order_time_city(order_time,city);-- ⑤ 再跑 EXPLAINEXPLAINSELECTcity,COUNT(*)AScntFROMordersWHEREorder_time>='2026-04-01'GROUPBYcity;

+----+-------------+--------+------------+-------+------------------------------------+---------------------+---------+------+------+----------+-------------------------------------------+ | 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_time>='2026-04-01'GROUPBYcity;

+----+-------------+--------+------------+-------+--------------------------------------------------------+---------------------+---------+------+------+----------+--------------------------+ | 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 FORMAT=TREE（MySQL 8.0.22）看得更清楚：

-> Group aggregate: count(0) -> Filter: (orders.order_time >= TIMESTAMP'2026-04-01 00:00:00') (cost=0.75 rows=5) -> Index scan on orders using idx_city_order_time (cost=0.75 rows=5)

干干净净——先走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.cnt>100GROUPBYt.city;

临时表：指 MySQL 在执行过程中自己建的内部表（Using temporary），用于排序、分组、去重。

核心区别：

💡MySQL 8.0 的 CTE：WITH 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_id=u.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，别猜。

避坑指南

⚠️ 真实踩过的坑：

1. GROUP BY 列没索引 = 临时表 + filesort

   • 只要 GROUP BY 的列没有可用索引，必然触发临时表和排序
   • 建议：WHERE 等值 → 建idx(WHERE列, GROUP BY列)；WHERE 范围 → 建idx(GROUP BY列, WHERE列)

2. GROUP BY + ORDER BY 不同列 = 二次排序

   • GROUP BY city 按 city 排序，完了 ORDER BY cnt 按计数排序，等于排了两次
   • 建议：能不能去掉排序，或者业务妥协

3. 临时表转磁盘 = 性能暴跌

   • tmp_table_size 默认 16M，数据量一大就转磁盘
   • 建议：不是调大 tmp_table_size，而是优化 SQL 让它不用临时表

4. COUNT(DISTINCT) 在大数据量下 = 去重开销大

   • 每个分组都要去重，分组多了开销爆炸
   • 建议：先整体去重再分组

5. 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/N（N 是列数）
• 对于 SUM/COUNT 这种聚合，CPU 可以直接对连续内存做向量运算
• 甚至不需要 GROUP BY 完再排序，很多列存引擎直接在聚合过程中就完成了排序

简单说：行存 3000 万行要读 3000 万行完整数据，列存只需要读 3000 万个 city 值。

这不是优化不优化的问题，这是存储引擎的基因问题。

所以很多公司的架构是：

• 业务写入走 MySQL（行存，点查快）
• 报表分析走列存引擎（GROUP BY 快）
• 中间通过 Binlog 同步

这才是解决报表慢的根本方案——而不是在 MySQL 上死磕索引。

思考题

🤔 互动时间：

1. 你写过的最慢的 GROUP BY SQL 慢到什么程度？当时是怎么优化的？
2. 面试官问你：COUNT(*)、COUNT(1)、COUNT(主键)、COUNT(普通列) 四个哪个最快？你怎么排顺序？
3. 业务需求：统计每个城市每天的订单量，你会怎么建索引？

总结

🎯面试考点

• GROUP BY 无索引 = 临时表 + filesort：这是 GROUP BY 慢的根本原因
• 联合索引优化 GROUP BY：WHERE 等值 →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 的实现原理 —— 面试必问！

有问题欢迎评论区交流，明天见！