当前位置：首页 > news >正文

《100个“反常识”经验11：删了30万行数据表还是那么大？》

news 2026/4/26 2:58:56

本期摘要

你用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。