Windows Server 2019环境MySQL 8.0.23关联分组查询报Error 1114表已满错误求助

数据老陈

2026-10-24

MySQL 8.0 关联查询触发1114「临时表已满」问题排查

报错定位

报错信息中d:\tmp#sql1e5c_18_1eb是MySQL执行复杂查询时自动创建的磁盘临时表，不属于业务数据文件、也不属于InnoDB共享表空间（ibdata1），因此常规检查数据目录空间、innodb_data_file_path配置无法定位该问题。

执行SHOW VARIABLES LIKE 'tmpdir'; 获取MySQL实际使用的临时文件目录，禁止默认假设临时文件和数据目录位于同分区：
- 统计该目录所在分区的实际剩余可用空间，避免因tmpdir指向空间不足的分区触发报错
- 确认MySQL服务运行账号（Windows Server 2019下默认运行账号为NT SERVICE\MySQL80）对该目录的写入权限：权限不足导致临时文件写入达到配额上限时，系统不会提示权限错误，只会报临时表已满
- 检查该分区的文件系统格式：若为FAT32格式，单文件最大支持4GB存储，哪怕分区剩余空间充足，临时表写入到4GB大小就会触发报错。当前查询需要关联4300万行明细，临时表实际需要数十GB存储空间，远超过FAT32的单文件上限。

磁盘临时表使用独立的InnoDB临时表空间，与innodb_data_file_path=ibdata1:12M:autoextend配置无关联：

执行SHOW VARIABLES LIKE 'innodb_temp_data_file_path'; 查看临时表空间配置，重点核对：
- 是否为临时表设置了自动扩展的最大上限（如配置为ibtmp1:12M:autoextend:max:2G），一旦临时表增长到设定的max值，即使磁盘空间充足也会触发报错
- 临时表空间文件是否存在磁盘锁定，导致无法正常增长

原SQL执行效率极低，是触发超大临时表的核心原因：

原SQL执行流程：优化器会先扫描全量4300万行tblpdet数据，关联580万行tblp获取每条明细的datum值，再按katbr分组计算min(date(datum))，整个中间结果集需要存储4300万行关联后的明细，临时表体积会达到数十GB级别。
优化后的等价写法，将12万行的分组计算下推到子查询阶段，最终临时表仅需要存储和返回结果等量的12万行数据，完全可以在内存临时表中完成计算，无需落盘：
```
SELECT d.katbr, MIN(p.datum) AS min_datum
FROM (
    SELECT katbr, MIN(id) AS min_tblp_id
    FROM tblpdet
    GROUP BY katbr
) d
JOIN tblp p ON p.id = d.min_tblp_id;
```
该写法利用了id与datum的正相关特性：同一katbr下最早的datum值，必然对应关联tblp表的最小id，无需拉取全量明细参与计算。

若暂时无法修改SQL，可通过调整参数临时规避问题：

内容的提问来源于stack exchange，提问作者Vlada