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