optimize table 对 innodb 表无效的主因是 innodb_file_per_table=off 或空间未归还操作系统；即使为 on，innodb 默认保留释放空间供复用，且需满足 tmpdir 空间充足、innodb_fast_shutdown=0 等条件。
OPTIMIZE TABLE 为什么有时没效果
MySQL 的 OPTIMIZE TABLE 对 InnoDB 表实际执行的是 ALTER TABLE ... FORCE（重建表），但它只在满足特定条件时才真正释放磁盘空间。常见现象是执行后 data_length 没变、free_space 仍为 0，或者 ibd 文件大小几乎不变。
- 如果启用了
innodb_file_per_table = OFF，所有表共用系统表空间ibdata1，OPTIMIZE TABLE无法回收空间到文件系统，只能在内部重用空闲页 - 即使启用了
innodb_file_per_table，InnoDB 默认不会把释放的空间还给操作系统，而是保留在 buffer pool 和数据文件中供后续插入复用 -
OPTIMIZE TABLE过程中会加元数据锁（MDL），若表上有长事务或未提交的 DML，命令会卡住，看起来“没反应”
确认碎片是否真存在，别盲目 OPTIMIZE
直接看 information_schema.TABLES 的统计值容易误判——data_free 是 InnoDB 内部估算值，不一定对应真实可回收空间。更可靠的判断方式是结合行数与物理大小趋势分析：
- 查出表的行数和平均行长：
SELECT table_rows, avg_row_length FROM information_schema.TABLES WHERE table_name = 'your_table' AND table_schema = 'your_db'; - 对比
data_length / table_rows是否远高于业务预期（比如平均行 2KB，但算出来是 15KB），再结合历史增长曲线判断是否异常膨胀 - 检查
SHOW TABLE STATUS LIKE 'your_table'中的Data_free：若长期 > 100MB 且持续增长，才值得干预
替代方案比 OPTIMIZE TABLE 更可控
对大表来说，OPTIMIZE TABLE 阻塞写入、耗时长、易失败。生产环境更推荐分步操作：
- 用
ALTER TABLE your_table ENGINE=InnoDB ALGORITHM=INPLACE LOCK=NONE;替代（MySQL 5.6+），它能在线重建，不锁写入（但需确保无全文索引、虚拟列等限制） - 对超大表（>50GB），优先考虑
pt-online-schema-change工具，它通过影子表+触发器实现零锁表整理 - 如果目标只是释放磁盘空间，确认
innodb_file_per_table = ON后，先OPTIMIZE TABLE，再手动DISCARD TABLESPACE+IMPORT TABLESPACE（极少用，风险高，仅限明确需要收缩单个.ibd文件时）
执行前必须检查的三个配置项
漏掉任一配置，OPTIMIZE TABLE 可能白跑一趟，甚至引发主从延迟或 OOM：
-
innodb_file_per_table：必须为ON，否则空间无法归还文件系统；可通过SELECT @@innodb_file_per_table;确认 -
innodb_fast_shutdown：建议设为0（即完全关闭），避免优化后重启时因未刷脏页导致恢复慢 -
tmpdir空间：重建过程会用临时目录存中间数据，确保其所在磁盘剩余空间 > 当前表data_length的 1.5 倍
碎片整理不是定期保养项目，它是针对具体膨胀现象的手术式操作。最常被忽略的是：没确认 innodb_file_per_table 状态就开干，结果整个过程只在内存里打转，磁盘一点没瘦。
