【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：

SELECT city, COUNT(*) AS cnt
FROM orders
WHERE order_time >= '2026-04-01'
GROUP BY city
ORDER BY cnt DESC
LIMIT 10;

一看执行计划——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 temporary？

MySQL 的规则很简单：

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

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

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

SELECT city, COUNT(*)
FROM orders
WHERE order_time >= '2026-04-01'
GROUP BY city;

会用临时表。 因为虽然 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 索引）
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    city VARCHAR(50),
    amount DECIMAL(10,2),
    order_time DATETIME,
    status VARCHAR(20),
    INDEX idx_order_time (order_time)
);

-- ② 插入测试数据
INSERT INTO orders (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');

ANALYZE TABLE orders;

① 只有 order_time 索引

-- ③ 请执行 EXPLAIN，把输出贴给我
EXPLAIN
SELECT city, COUNT(*) AS cnt
FROM orders
WHERE order_time >= '2026-04-01'
GROUP BY city;

+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+----------------------------------------+
| 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列在前
ALTER TABLE orders ADD INDEX idx_order_time_city (order_time, city);

-- ⑤ 再跑 EXPLAIN
EXPLAIN
SELECT city, COUNT(*) AS cnt
FROM orders
WHERE order_time >= '2026-04-01'
GROUP BY city;

+----+-------------+--------+------------+-------+------------------------------------+---------------------+---------+------+------+----------+-------------------------------------------+
| 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 列在后
ALTER TABLE orders ADD INDEX idx_city_order_time (city, order_time);

-- ⑦ 再跑
EXPLAIN
SELECT city, COUNT(*) AS cnt
FROM orders
WHERE order_time >= '2026-04-01'
GROUP BY city;

+----+-------------+--------+------------+-------+--------------------------------------------------------+---------------------+---------+------+------+----------+--------------------------+
| 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 没有索引
SELECT t.city, SUM(t.cnt)
FROM (
    SELECT city, COUNT(*) AS cnt
    FROM orders GROUP BY city
) t
WHERE t.cnt > 100
GROUP BY t.city;

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

核心区别：

	派生表	临时表
谁建的	你的 SQL 写的	MySQL 自己建的
能不能加索引	5.6 及之前：不能。5.7：部分场景自动加。8.0：CTE 替代	不能，MySQL 内部管理
常见坑	派生表结果集大 + 外层 JOIN = 慢	GROUP BY 无索引触发
8.0 解法	用 CTE 替代派生表，可读性更好，优化器处理也更聪明	建联合索引

💡 MySQL 8.0 的 CTE：WITH cte AS (SELECT ...) SELECT ... FROM cte。和派生表功能一样，但：① 可以多次引用同一个 CTE；② 物化只执行一次；③ 执行计划更可控。

案例二：COUNT(DISTINCT) 的隐藏坑

很多人写去重计数，喜欢这么写：

SELECT city, COUNT(DISTINCT status)
FROM orders
GROUP BY city;

这个写法在 MySQL 里会触发什么？

先按 city 分组，每个分组里再对 status 去重。每个分组都要建一次临时哈希表。

改成这样写更清晰：

-- 先去重再分组
SELECT city, COUNT(*)
FROM (
    SELECT DISTINCT city, status
    FROM orders
) t
GROUP BY city;

⚠️ 但注意：这个改法是"先整体去重再分组"，适合分组数多但每个分组去重开销不大的场景。如果反过来——status 基数极大，分组数极少——那 COUNT(DISTINCT) 反而可能更快。没有银弹。

MySQL 8.0 的 Lateral Derived Table 还能这么写：

-- 8.0.14+ 支持
SELECT u.name, o.cnt
FROM users u,
LATERAL (
    SELECT COUNT(DISTINCT city) AS cnt
    FROM orders
    WHERE user_id = u.id
) o;

LATERAL 允许派生表引用前面的表字段，省掉了关联子查询逐行执行的痛苦。面试问到 8.0 新特性时可以提这个。

-- ⑨ 请在你的环境跑一下 EXPLAIN
EXPLAIN
SELECT city, COUNT(DISTINCT status)
FROM orders
GROUP BY city;

-- ⑩ 然后跑改写后的版本
EXPLAIN
SELECT city, COUNT(*)
FROM (
    SELECT DISTINCT city, status
    FROM orders
) t
GROUP BY city;

-- ⑨ 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/N（N 是列数）
对于 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 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 的实现原理 —— 面试必问！
全本合集：《每天一个MySQL知识点，百日打怪升级》

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

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

背景引入

核心概念

GROUP BY 的"流水线"究竟在干嘛？

什么时候会触发 Using temporary？

COUNT 三兄弟：*、1、列名，到底谁最快？

面试解答

实战案例

案例一：GROUP BY 有无索引的天差地别

准备工作

① 只有 order_time 索引

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

③ 修复：换个索引顺序

派生表 vs 临时表：你以为一样的，其实差远了

案例二：COUNT(DISTINCT) 的隐藏坑

避坑指南

💡 GROUP BY 优化五步走

延伸讨论：为什么列存做 GROUP BY 是降维打击？

思考题

总结

热门文章

最新文章

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

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

背景引入

核心概念

GROUP BY 的"流水线"究竟在干嘛？

什么时候会触发 Using temporary？

COUNT 三兄弟：*、1、列名，到底谁最快？

面试解答

实战案例

案例一：GROUP BY 有无索引的天差地别

准备工作

① 只有 order_time 索引

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

③ 修复：换个索引顺序

派生表 vs 临时表：你以为一样的，其实差远了

案例二：COUNT(DISTINCT) 的隐藏坑

避坑指南

💡 GROUP BY 优化五步走

延伸讨论：为什么列存做 GROUP BY 是降维打击？

思考题

总结

热门文章

最新文章

相关电子书