MySQL优化表时临时文件错误的常见原因与排查步骤详解

OPTIMIZE TABLE 之所以需要临时文件，其根本原因在于它对 InnoDB 表的本质是重建操作：先创建一个结构完全相同的新表，再将原表数据逐行复制过去，同时重建索引，最后通过原子替换覆盖原表。整个流程会生成多种中间文件——排序所用的归并临时文件、索引重建的缓冲区，以及最关键的那个临时表文件（如 #sql-xxx）。这些文件默认不占用内存，而是直接写入磁盘，并且其存放路径并非仅由 tmpdir 单独决定。

OPTIMIZE TABLE 为什么需要临时文件

因为 OPTIMIZE TABLE 对 InnoDB 表的本质是重建：先建一个结构相同的新表，再把原表数据逐行拷过去，最后原子替换。这个过程会产生大量中间文件——包括排序用的归并临时文件、重建索引的缓冲区、以及最终的临时表文件（如 #sql-xxx）。这些文件默认不走内存，而是落盘，且位置不由 tmpdir 单独决定。

报 Temporary file write failure 的真实原因

错误提示显示“写临时文件失败”，但实际瓶颈往往并不在于“临时”二字，而在于物理路径和空间归属：

• MySQL 5.7+ 中，OPTIMIZE TABLE 产生的临时表文件（如 #sql-xxx）默认写在原表所在目录，也就是 datadir 下对应数据库子目录里，不是 tmpdir。
• 即使 df -h 显示数据盘还有空余，也可能因为 inode 耗尽、配额限制（例如 RDS 的 loose_rds_max_tmp_disk_space）、或挂载点设置了 noexec/nosuid 导致静默失败。
• 日志中若出现 Operating system error number 28，基本可以确认是“设备上没有剩余空间”。但要注意：这个“设备”指的是文件所在的挂载点，不一定是你认为的那个盘。

为什么改 tmp_table_size 没用

tmp_table_size 和 max_heap_table_size 只控制内存临时表的上限，而 OPTIMIZE TABLE 的临时文件属于 DDL 过程文件，完全绕过这两个参数。它们只影响 GROUP BY、ORDER BY 等 SQL 层的临时结果，与表重建无关。

常见误操作：

• 只调大 tmp_table_size，没有同步修改 max_heap_table_size → 实际仍取较小值，无效。
• 把 tmpdir 改到了 SSD，但 datadir 仍在机械盘且已满 → OPTIMIZE 依然卡住。
• 以为 innodb_tmpdir 能接管所有临时文件 → 它只影响 InnoDB 内部排序/索引创建，不接管 #sql-xxx 这类 DDL 临时表。

真正有效的应对方式

核心思路很简单：让 OPTIMIZE TABLE 的临时文件落到有足够空间、权限正确、且与 datadir 物理隔离的位置。

• MySQL 5.6+ 可动态设置：SET GLOBAL innodb_file_per_table = ON;（确保后续新建表独立存储），再用 ALTER TABLE ... ENGINE=InnoDB 手动重建，它会尊重 innodb_file_per_table 和当前 datadir 配置。
• MySQL 8.0.29+ 支持 ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE 替代部分 OPTIMIZE 场景，避免全表拷贝。
• 最稳妥的做法：停写、导出数据（mysqldump 或 mysqlpump）、删表、重建、导入 —— 虽慢，但可控，且临时文件由客户端进程管理，不受 MySQL 服务端磁盘限制。

最容易被忽略的一点：OPTIMIZE TABLE 在主从架构下会完整重放整个重建过程，从库 IO 和 SQL 线程压力剧增，可能直接拖垮复制链路。线上大表务必评估主从延迟容忍度，别只盯着本地磁盘。