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