如何在MySQL中利用Optimize-Table回收由于大量Delete产生的表空间碎片-linux运维-PHP中文网

optimize table能回收delete后的碎片，因其本质是重建表：新建临时表、仅拷贝有效数据、重写紧凑页结构、原子替换原表，从而释放被标记删除但未归还的操作系统空间。

如何在mysql中利用optimize-table回收由于大量delete产生的表空间碎片

Optimize Table 为什么能回收 delete 后的碎片

MySQL 的 DELETE 操作（尤其在 InnoDB 表中）并不会立即归还磁盘空间给操作系统，而是把行标记为“已删除”，空出的空间保留在页内供后续 INSERT 复用。长期高频删改后，页内碎片增多、页利用率下降，表文件（.ibd）体积膨胀但实际数据占比低。此时 OPTIMIZE TABLE 实质是重建表：创建新临时表 → 拷贝有效行 → 重建索引 → 替换原表 → 删除旧文件。整个过程释放了被逻辑删除占据的物理空间。

执行 Optimize Table 前必须确认的三件事

不是所有场景都适合直接运行 OPTIMIZE TABLE，忽略前提可能引发锁表、磁盘爆满或无效操作：

确认存储引擎是 InnoDB 或 MyISAM —— OPTIMIZE TABLE 对 MEMORY、CSV 等无效，且对 InnoDB 实际调用的是 ALTER TABLE ... FORCE（5.6+）或重建流程
检查磁盘剩余空间是否 ≥ 当前表大小的 2 倍 —— 重建过程需同时存旧表 + 新表，空间不足会导致操作中断并留下损坏状态
确认表没有被长事务或未提交的 DML 占用 —— OPTIMIZE TABLE 需要排他元数据锁（MDL），若存在活跃事务会阻塞，超时后报错 Lock wait timeout exceeded

替代方案：ALERT TABLE ... ENGINE=InnoDB 更可控

在 MySQL 5.6+ 中，OPTIMIZE TABLE t 对 InnoDB 表等价于 ALTER TABLE t ENGINE=InnoDB。后者语义更明确，且支持更多控制选项：

MySQL

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

下载

加 ALGORITHM=INPLACE（仅限部分修改，不适用于纯重建）—— 但 ENGINE=InnoDB 强制触发重建，所以实际仍为 COPY 算法，无法避免锁表
可配合 LOCK=NONE 尝试无锁（仅当满足 Online DDL 条件时生效，而重建表不满足，最终仍会降级为 LOCK=SHARED）

推荐写法：

ALTER TABLE `user_log` ENGINE=InnoDB, ALGORITHM=COPY, LOCK=EXCLUSIVE;

显式声明行为，避免隐式猜测

执行后如何验证碎片是否真正回收

不能只看 SHOW TABLE STATUS 的 Data_length，它反映的是聚簇索引占用字节数，受页填充率影响大。更可靠的验证方式是结合系统表和文件系统：

查 information_schema.INNODB_SYS_TABLES 获取表空间 ID，再关联 INNODB_SYS_TABLESPACES 看 FILE_SIZE 和 ALLOCATED_SIZE 变化
直接对比磁盘文件大小：
```
ls -lh /var/lib/mysql/mydb/user_log.ibd
```
注意：如果启用了 innodb_file_per_table=OFF，表数据存于共享表空间 ibdata1，OPTIMIZE TABLE 无法收缩该文件 —— 这是常见误判点，必须提前确认配置