MySQL中InnoDB表空间碎片与MyISAM碎片如何整理-mysql教程-PHP中文网

根本原因是innodb_file_per_table关闭导致optimize无法释放空间；即使开启，也需tmpdir空间充足、避免外键失败，并优先用algorithm=inplace在线重建或逻辑重导。

mysql中innodb表空间碎片与myisam碎片如何整理

InnoDB表执行OPTIMIZE TABLE为什么没释放磁盘空间

根本原因几乎总是innodb_file_per_table关闭。查一下：SHOW VARIABLES LIKE 'innodb_file_per_table'，如果返回OFF，那所有InnoDB表数据都写进共享表空间ibdata1，OPTIMIZE TABLE只能整理页内结构，无法把空间还给操作系统。

即使该参数为ON，也要确认操作时tmpdir（临时文件目录）有足够剩余空间——OPTIMIZE会先建临时表，空间不足直接失败。

执行前务必检查：df -h /var/lib/mysql 和 df -h $(mysql -N -e "SELECT @@tmpdir")
OPTIMIZE TABLE对InnoDB等价于ALTER TABLE t ENGINE=InnoDB，不是“清理碎片”的万能开关
如果表有外键约束，OPTIMIZE可能失败，改用显式ALTER TABLE并加ALGORITHM=INPLACE（MySQL 5.6+）更可控

MyISAM表OPTIMIZE TABLE锁表现完全不同

MyISAM执行OPTIMIZE TABLE会重建.MYD和.MYI文件，彻底消除碎片，但期间整个表不可读不可写——不是S锁，是排它锁（X lock）。这意味着哪怕只是查一条记录，也会被阻塞。

而InnoDB的OPTIMIZE只加S锁（共享锁），允许并发读，但写操作全被拒绝。两者锁粒度不同，影响面差异很大。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

MyISAM大表优化前，必须评估业务能否接受全表不可写窗口
MyISAM没有在线DDL能力，OPTIMIZE是唯一标准手段；InnoDB则可选pt-online-schema-change或MySQL 8.0+的ALGORITHM=INSTANT（仅限部分变更）
MyISAM的DATA_FREE字段反映的是.MYD文件末尾未用空间，比InnoDB的DATA_FREE更直观可信

别只盯着DATA_FREE，InnoDB碎片率计算容易误导

DATA_FREE在InnoDB里常为0或固定几MB，跟实际页内碎片关系弱。它表示的是“分配给该表但尚未使用的空闲页数”，而InnoDB后台Purge线程会持续回收、复用这些页——所以看到DATA_FREE = 0不等于没碎片，看到DATA_FREE = 2GB也不代表真能释放2GB。

更靠谱的指标是结合AVG_ROW_LENGTH与实际行数估算：如果table_rows × avg_row_length远小于data_length，说明页内填充率低，存在大量空洞。

查实时统计需设information_schema_stats_expiry = 0，否则元数据可能缓存24小时
真正影响性能的是随机IO上升、缓冲池命中率下降，而不是DATA_FREE数字本身
频繁UPDATE变长字段（如TEXT）、无主键表、二级索引过多，都会加剧页分裂，比单纯看DATA_FREE更值得干预

真正安全的碎片整理路径只有两条

第一条路：确认innodb_file_per_table = ON后，对单表执行ALTER TABLE t ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=SHARED。成功则说明支持在线重建；失败则退到ALGORITHM=COPY（等价OPTIMIZE），但必须预留双倍磁盘空间。

第二条路：停机维护窗口内，用mysqldump逻辑导出+清空+重导入。适用于超大表（>100GB）或复制环境敏感场景，但要求binlog_format = ROW且主从延迟可接受。