为什么MySQL在执行OPTIMIZE TABLE时会出现临时文件错误

OPTIMIZE TABLE需要临时文件是因为其对InnoDB表本质是重建操作：先创建结构相同的新表，再逐行复制数据并重建索引，最后原子替换；该过程生成的临时表文件（如#sql-xxx）默认写入原表所在datadir目录而非tmpdir，且不受tmp_table_size等内存参数控制，故磁盘空间、inode、挂载选项等均可能导致“Temporary file write failure”错误。

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 线程压力剧增，可能直接拖垮复制链路。线上大表务必评估主从延迟容忍度，别只盯着本地磁盘。