本期摘要
你用DELETE删了30万行数据，df -h一看磁盘空间没变，表文件还是那么大。这不是Bug，是InnoDB存储引擎的设计特性：DELETE只标记删除，不释放磁盘空间，留下的位置叫“空洞”。真正释放空间需要执行OPTIMIZE TABLE或ALTER TABLE ENGINE=InnoDB。本文还提供了检查表碎片的SQL脚本，以及大表优化的工具推荐。
一次让人困惑的操作
业务需要清理半年前的日志数据，大概30万行。执行：
sql
DELETE FROM operation_logs WHERE created_at < '2026-01-01';
Query OK, 300000 rows affected。
再查表大小：
sql
SELECT table_name, ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb FROM information_schema.tables WHERE table_name = 'operation_logs';
结果：还是2GB，和删除前一样。
为什么DELETE不释放磁盘空间？
InnoDB存储引擎中，DELETE操作只是把数据行标记为“已删除”，并不会真正回收磁盘空间。这些空间称为“空洞”，后续INSERT可以复用，但文件大小不会缩小。
| 操作 | 行为 | 磁盘空间 |
|---|---|---|
| DELETE | 标记删除，空间留作复用 | 不释放 |
| INSERT | 优先填充空洞 | 不增加文件大小 |
| TRUNCATE | 重建表 | 释放 |
| OPTIMIZE | 整理碎片 | 释放 |
如何真正释放空间？
方法一：OPTIMIZE TABLE（推荐）
sql
OPTIMIZE TABLE operation_logs;
原理：重建表，消除空洞，回缩磁盘空间。
注意：执行期间会锁表，建议在业务低峰期操作。
方法二：ALTER TABLE重建
sql
ALTER TABLE operation_logs ENGINE=InnoDB;
效果和OPTIMIZE类似，也会重建表。
方法三：TRUNCATE（全删）
sql
TRUNCATE TABLE operation_logs;
删除所有数据并重置表，空间彻底释放。仅适用于全表删除。
真实案例对比
| 方法 | 执行前表大小 | 执行后表大小 | 锁表 | 耗时 |
|---|---|---|---|---|
| DELETE 30万行 | 2GB | 2GB | 行锁 | 几秒 |
| OPTIMIZE | 2GB | 1.2GB | 全表锁 | 几分钟 |
| TRUNCATE | 2GB | 0.1MB | 全表锁 | 瞬间 |
注意事项
频繁DELETE+INSERT会产生大量空洞，定期OPTIMIZE是必要的
大表OPTIMIZE耗时较长，建议用pt-online-schema-change工具在线操作
磁盘空间紧张时，不要只靠DELETE，记得跟进OPTIMIZE
一键检查表碎片脚本
sql
SELECT table_name, ROUND(data_length/1024/1024, 2) AS data_mb, ROUND(data_free/1024/1024, 2) AS free_mb, ROUND(data_free/data_length*100, 2) AS free_pct FROM information_schema.tables WHERE table_schema = 'your_database' ORDER BY free_pct DESC;
free_mb越大，碎片越严重，建议执行OPTIMIZE。
下期预告
《100个“反常识”经验12：死锁日志怎么看？10分钟学会》
原理与Python实战指南)
