外观
15 - ORDER BY 和 GROUP BY 为什么会突然打满磁盘
摘要与阅读定位
一条报表 SQL 只返回几行,也可能处理大量记录。排序要保存用于比较的候选,分组要维护各组的汇总状态;这些中间数据超过相应内存预算后,可能转向临时文件或磁盘临时表。多个查询同时运行时,临时空间需求还会叠加。本文先用五行数据解释排序和分组,再区分不同存储路径,最后用固定规模的样本说明如何定位资源消耗。
- 前置:07 聚合窗口语义、12 索引顺序、14 运行统计。
- 对比:算法 08 内存排序与外部归并是算法模型,不等于 MySQL 固定算子;应用:08 分页。
- 后续:24 资源分诊、25 慢 SQL SOP。基线 MySQL 8.0.18+ / 8.4、InnoDB;TempTable 限额规则按小版本区分。遵循 A,落盘、磁盘尺寸与耗时应在目标环境测量。
初次先完成下面的五行排序和分组,再读第 1—2 节资源模型。第 3 节参数和第 4—5 节一千行观察是第二层;真正制造溢写仅用于另行授权的隔离演练,不是理解本文的必做步骤。
从五行开始:排序不是分组,返回少也不等于读得少
以下 SQL 用于隔离实例。先在原连接提交或回滚旧事务,再执行初始化;若 temp_basic 已存在,先确认归属,不覆盖其他实验的数据:
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit = 1;
CREATE TABLE temp_basic (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
amount DECIMAL(10, 2) NOT NULL
) ENGINE = InnoDB;
INSERT INTO temp_basic VALUES (1, 10, 30), (2, 20, 10), (3, 10, 50), (4, 20, 20), (5, 30, 40);
SELECT id, amount FROM temp_basic ORDER BY amount DESC, id DESC LIMIT 2;
SELECT customer_id, COUNT(*) AS order_rows, SUM(amount) AS total_amount
FROM temp_basic GROUP BY customer_id ORDER BY customer_id;按输入数据推导,第一条查询的预期输出为 (3,50)、(5,40),分别表示 ID 和金额。第二条查询的预期输出如下:
| customer_id | order_rows | total_amount |
|---|---|---|
| 10 | 2 | 80 |
| 20 | 2 | 30 |
| 30 | 1 | 40 |
ORDER BY 安排行的先后顺序,GROUP BY 把同一客户的行归到一组,再由 COUNT、SUM 计算汇总值。例如,客户 10 的两行金额为 30 和 50,因此计数为 2、合计为 80。GROUP BY 本身不保证输出顺序,本例另用 ORDER BY customer_id 明确按客户编号排列。
本表没有提供金额顺序的索引。为确定金额最高的两条,数据库需要检查候选记录,不能看到两行就停止,因为后面可能还有更大的金额。它可以采用只保留少量候选的优化,但具体执行方式由计划决定。因此,返回两行不等于只读取两行;五行样本也不能证明发生了临时文件写入。
查询为排序、分组、去重或组合结果暂时保存的工作状态,称为中间数据。排序状态可能包含排序键和记录定位信息;分组状态可能包含客户编号、当前计数和金额合计。它们是执行查询的辅助数据,不是应用创建的业务表。
先区分两个问题:查询要处理多少输入,以及处理过程中要同时保存多少状态。输入越多,读取和计算的工作通常越多;需要保留的候选越多、每项越宽,排序状态就可能越大。分组状态还与组数及每组保存的内容有关,不能只看最终返回行数判断成本。状态超过相应内存预算后转向其他允许的存储路径,称为溢写。
1. 现象:200 MiB × 300 是预算警报,不是实测容量
假设三百个查询同时持有各 200 MiB 临时数据,合计需求约为 58.6 GiB。这是容量预算示例,不是测量记录:MiB、GiB 是二进制容量单位,1024 MiB 等于 1 GiB。实际查询的峰值未必同时出现,临时数据的存续时间、共享内存限额、文件重用及并发执行任务也不同,不能直接把一次查询的最大值乘连接数当作实际占用。
调大查询可用的内存,可能减少单次查询落盘,却也可能让更多并发查询把状态留在 RAM(内存)中,造成 OOM(内存不足故障)。提高某项共享额度也不保证所有临时文件都会减少,因为排序和临时表可能使用不同预算。要解释磁盘为何突然增长,必须同时看单次工作量、同时运行的查询数和实例总预算。
2. 原理:四条路径不要混读
执行计划里的 filesort 表示需要额外排序,不能根据名称中的 file 判断它一定写磁盘。内部临时表则是数据库为保存中间结果而使用的表结构,可以采用 TempTable、MEMORY 或 InnoDB 等实现。两者描述的工作不同,下面的四类情况也不是每条查询必经的四个阶段。
其中,mmap(内存映射)是把文件映射到进程地址空间的机制。通过地址访问数据,并不意味着底层没有文件 I/O(输入/输出)。物化是把中间结果保存下来,供后续任务使用。
| 路径 | 作用与可能介质 | 不能直接推出 |
|---|---|---|
| filesort | 访问路径不提供所需顺序时额外排序,可在内存或写排序文件 | 名称有 file 所以一定落盘 |
| 内存内部临时表 | 聚合、去重、物化等中间结构,常用 TempTable,也可能 MEMORY | GROUP BY 一定先排序、每次临时表必落盘 |
| TempTable mmap | 配置允许时把溢出数据放到内存映射临时文件 | 不计 Created_tmp_disk_tables 所以没文件 I/O |
| InnoDB 内部磁盘临时表 | 内部表超限或需磁盘路径时的存储 | 临时表数量等于文件字节数或 filesort 次数 |
从通用执行模型看,Sort Aggregate(排序聚合)先让相同分组键的记录相邻,再逐组汇总;Hash Aggregate(哈希聚合)用散列表按分组键查找和更新汇总状态。散列表是一种按键定位数据的结构。MySQL 的临时表聚合可能借助键查找或唯一性维护,但不能仅凭“临时表”就认定它等同于其他产品的哈希聚合算子。算子是执行计划中的具体任务,应以目标版本的计划为依据。
当排序状态无法全部留在内存中,常见的外部排序模型是分批排序并保存有序片段,再将这些片段合成整体有序的结果,这一步称为归并。实际排序项可能保存排序键与行定位信息,也可能携带其他输出列;不能因为查询使用 SELECT *,就断言所有大字段都进入排序缓冲。
Top N 指只选择排序后的前 N 条。它可以减少需要保留的候选,但没有适用的有序访问路径时,仍可能处理全部输入。OFFSET 还要求跳过前面的记录,会增加候选需求,详见 08 分页。
3. 版本与参数:共享额度不是每会话固定配额
这是可选配置分析,初次不必记住每个变量。先分清“单个临时表的限制”“某实现共享内存额度”“排序任务的缓冲额度”,再核对目标版本与现场值;它们不能全部相加为每连接固定成本。
先只读采样,不修改全局配置:
sql
SELECT VERSION();
SHOW VARIABLES WHERE Variable_name IN (
'internal_tmp_mem_storage_engine','tmp_table_size','max_heap_table_size',
'temptable_max_ram','temptable_max_mmap','temptable_use_mmap',
'sort_buffer_size','tmpdir'
);MySQL 8.4 中,这几项额度需要分别理解:
| 参数与实现 | 约束对象 |
|---|---|
TempTable 的 tmp_table_size | 单个内存内部临时表;超限可转换为 InnoDB 磁盘临时表 |
temptable_max_ram | TempTable 共享的 RAM 资源,不是每连接各有一份同额预算,也不包含所有线程本地内存块 |
MEMORY 内部临时表的 tmp_table_size、max_heap_table_size | 使用两者中的较小值作为限制 |
应用显式执行 CREATE TABLE ... ENGINE=MEMORY 创建的表,不采用上述内部临时表的同一自动转换流程。
MySQL 8.0 中,tmp_table_size 对 TempTable 的限制随小版本演进,不能把 8.4 的行为推广到所有 8.0 版本。mmap 是否可用还取决于对应开关和额度;不能仅凭“使用 8.4 默认配置”认定 mmap 已启用。分析时保存现场值,不照抄旧版本的默认值。
sort_buffer_size 是相应排序缓冲的上限,MySQL 可按需增量分配,并非建立连接就固定分配这么多内存。一个会话也可能同时需要排序、临时表等多种资源。实例总内存预算还要包括 Buffer Pool(InnoDB 缓存数据页等内容的缓冲池)、连接和后台线程等开销,并受操作系统或容器的内存上限约束。
4. 实现:一千行有界样本,只验证工作形态
下面用数字表的三个别名分别表示个位、十位和百位,通过交叉连接生成固定的 1000 行。这个样本用于观察排序与聚合的工作方式,不用于制造内存溢出。即使计划提示额外排序,小样本没有临时磁盘增长也是正常结果。
先结束实验连接中的旧事务,并确认隔离库中的同名 temp_* 表属于本实验。以下初始化会删除并重建 temp_report、temp_digit,不修改全局参数。
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit=1;
DROP TABLE IF EXISTS temp_report;
DROP TABLE IF EXISTS temp_digit;
CREATE TABLE temp_digit (digit INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO temp_digit VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE temp_report (
id BIGINT PRIMARY KEY,customer_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,created_at DATETIME NOT NULL,
payload VARCHAR(500) NOT NULL,
KEY idx_temp_customer_time (customer_id,created_at,id)
) ENGINE=InnoDB;
INSERT INTO temp_report
SELECT num,MOD(num-1,10)+1,MOD(num,100),
TIMESTAMPADD(MINUTE,num,'2026-09-01'),REPEAT('x',500)
FROM (
SELECT 1+ones.digit+10*tens.digit+100*hundreds.digit AS num
FROM temp_digit AS ones CROSS JOIN temp_digit AS tens
CROSS JOIN temp_digit AS hundreds
) AS seq;
SELECT COUNT(*) AS rows_count,SUM(amount) AS amount FROM temp_report;
ANALYZE TABLE temp_report;预期输出为 1000 行、金额合计 49500。编号为 1—1000,金额由编号除以 100 的余数生成,因此 0—99 各出现十次,合计为 10 × (0 + … + 99) = 49500。每个客户有 100 行,created_at 在这批数据中唯一;amount 没有排序索引。
5. 验证:排序、窄定位和聚合分别留证
前两条查询比较两种取数方式:直接返回包含 payload 的记录,以及先选出前五个 ID、再按主键取得完整记录。后者称为延迟关联,目的是让选候选的阶段只返回必要列。随后比较按客户编号与按汇总金额排序的聚合查询。EXPLAIN ANALYZE 会实际执行查询并增加观测开销,以下示例仍只用于隔离环境。
sql
SELECT id,amount,payload FROM temp_report
ORDER BY amount DESC,id DESC LIMIT 5;
SELECT report.id,report.amount,report.payload
FROM temp_report AS report
JOIN (
SELECT id FROM temp_report ORDER BY amount DESC,id DESC LIMIT 5
) AS top_ids ON top_ids.id=report.id
ORDER BY report.amount DESC,report.id DESC;
SELECT customer_id,COUNT(*) AS rows_count,SUM(amount) AS amount
FROM temp_report GROUP BY customer_id ORDER BY customer_id;
EXPLAIN ANALYZE
SELECT id,amount,payload FROM temp_report ORDER BY amount DESC,id DESC LIMIT 5;
EXPLAIN ANALYZE
SELECT customer_id,SUM(amount) AS amount
FROM temp_report GROUP BY customer_id ORDER BY amount DESC,customer_id;前两条查询的预期结果 ID 均为 999、899、799、699、599,金额均为 99:金额相同时,id DESC 让编号较大的记录优先。先选 ID 不保证更快,因为实际排序项(tuple)保存哪些字段、是否物化,以及按主键重新取行的成本,都会影响收益;重新取得所需完整行称为回表。
客户 1—9 的预期金额分别为 4600、4700、…、5400,客户 10 为 4500,每个客户均有 100 行。按汇总金额排序与按客户索引顺序读取不是同一任务,应分别查看执行计划。
在独立实验会话中,对目标查询执行前后分别采样以下会话累计值,用差值观察这段时间新增的工作,不重置全局统计:
sql
SHOW SESSION STATUS WHERE Variable_name IN (
'Created_tmp_tables','Created_tmp_disk_tables',
'Sort_rows','Sort_scan','Sort_range','Sort_merge_passes'
);
DESCRIBE performance_schema.events_statements_history;前后增量只能作为这段会话时间窗口的线索,因为诊断语句也可能产生额外工作。要归因到单次查询,可由独立观察会话从 Performance Schema(MySQL 的运行事件与统计设施)保存目标 SELECT 的语句事件。THREAD_ID 标识内部线程,EVENT_ID 标识该线程上的事件;具体采集条件和方法见 14。还要区分普通 SELECT 与 EXPLAIN ANALYZE 的事件,后者包含观测开销。
Created_tmp_disk_tables 不计入 TempTable 使用 mmap 创建的磁盘临时路径。因此,即使它的增量为零,也不能排除 mmap 或 filesort 临时文件。Sort_merge_passes 是归并次数相关的统计,不是文件字节数;临时表数量也不能代表峰值容量,因为不同表的大小可能差很多。
判断磁盘压力时,需要联合观察文件 I/O、tmpdir(临时文件目录)与临时表空间的实际使用,以及语句工作量。已经打开后又删除目录项的临时文件,可能不出现在普通目录列表中,却仍被数据库持有;操作系统层面的确认应由管理员按环境进行。
6. 排障:何时扩内存,何时减少工作
| 证据 | 假设 | 优先单变量实验 |
|---|---|---|
| 聚合前输入巨大而组数少 | 可提前过滤/减少连接放大 | 05、06先验证语义 |
| 排序候选保存的字段多或较宽 | 排序中间状态可能较大 | 减少返回列或延迟关联,查实际排序项与计划 |
| ORDER BY 与索引不匹配 | 额外排序不可直接消除 | 12比较顺序、过滤与回表 |
| 临时工作并发暴涨 | 同时资源需求超预算 | 限制报表并发、隔离调度,再评估额度 |
| 临时文件增长但磁盘临时表计数不大 | filesort/mmap 等未在该计数中反映 | 联合观察文件类型、语句事件和设备延迟 |
溢写不一定意味着必须调大内存:在并发预算内允许落盘,可以避免中间状态无上限地占用 RAM。临时文件与数据、日志位于同一设备时,还可能争用 I/O 资源。扩大 tmpdir 所在磁盘的容量,只增加可存放的空间,不自动提高带宽,也不保证降低 P99 延迟,即 99% 请求耗时不超过的延迟值。
7. 工程取舍、风险与清理
优化时先减少输入量和中间数据的列宽,再评估索引有序访问、Keyset 分页、预聚合和负载隔离,最后单独调整已确认的引擎额度。Keyset 分页利用上一页的排序键继续查询,减少大 OFFSET 的跳过成本,见 08 分页。预聚合把部分汇总工作提前完成,但需要处理更新、删除后的汇总修正和重算,见 04;把报表迁移到副本还受 27 复制延迟约束。
若要验证溢写,只在有磁盘硬上限、并发上限和外部停止条件的可丢弃实例分阶段放大数据,记录实际文件字节和峰值 RAM,不在共享实例调低全局额度制造事故。取消查询后须确认任务结束、事务清理和临时资源回收;不能手工删除仍被数据库持有的文件。26负责终止风险。
其他数据库产品的排序内存、临时空间,以及分布式节点间传输和溢写规则,需要按各自版本理解,不能直接套用 MySQL 的 TempTable 变量。
实验结束后,先让实验连接结束事务;确认数据不再需要且表归属正确,再在隔离库删除 temp_report、temp_digit、temp_basic,不删除整个实验库。接下来可按 24 资源定位 → 25 慢 SQL 排查继续分析。
8. 名词解释
| 名词 | 含义 |
|---|---|
| 中间数据 | 排序、分组等任务暂存的候选或汇总状态,不是最终业务表 |
| filesort | MySQL 的额外排序线索,可以只用内存,也可能使用临时文件 |
| 内部临时表 | 数据库为完成语句内部工作而创建的中间结构 |
| TempTable / MEMORY | MySQL 可采用的两种内存临时表实现,限制与转换规则不同 |
| mmap | 把文件映射到地址空间的机制;用于临时数据时仍可能涉及文件 I/O |
| 溢写 | 中间数据超过相应内存预算后转向允许的其他存储路径 |
| Top N / 归并 | 选择排序前 N 条 / 将多个有序片段组合成有序结果 |
| OOM / RAM | 内存不足故障 / 运行时使用的内存,限额还受操作系统或容器约束 |
参考资料
- MySQL 8.4 Reference Manual:Internal Temporary Table Use in MySQL、ORDER BY Optimization、Performance Schema Statement Event Tables。
- 适用条件:所有实际落盘、文件字节、算子和并发性能应在目标实例留证。