InnoDB的DELETE仅逻辑标记删除,不归还OS空间;真正释放需OPTIMIZE TABLE或ALTER TABLE ENGINE=InnoDB重建表,但须满足innodb_file_per_table=ON、无长事务、磁盘空间充足三前提。

物理删除大量数据后空间不释放,是 InnoDB 的正常行为;变慢则大概率是因为碎片堆积导致随机 IO 增多、缓冲池命中率下降,或 purge 线程被阻塞。这两者不是副作用,而是同一机制的两面表现。
DELETE 只是标记删除,不归还 OS 空间
InnoDB 的 DELETE 永远不直接删页,只把记录头的 delete_mask 置 1,并加入 purge 队列。这些“已删”记录仍占据 B+ 树页,.ibd 文件大小、DATA_LENGTH、INDEX_LENGTH 全都不变。即使你 DELETE FROM t WHERE 1=1 删光整表,du -sh t.ibd 依然纹丝不动。
常见错误现象包括:
-
Data_free值飙升(比如从几 MB 变成几百 MB),说明页内空洞多,但整页没还给操作系统 -
SELECT COUNT(*)返回 0,但磁盘使用率没降,监控持续告警 - 执行
TRUNCATE TABLE t后空间立刻释放 —— 因为它是 DDL,直接删文件
为什么查询和写入会变慢
碎片不是“看不见的浪费”,它直接影响运行时性能:
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
- 页内空洞多 → 同样数量的数据占更多页 →
innodb_buffer_pool_pages_data上升,缓存效率下降 - B+ 树节点稀疏 → 查询需读更多页,
Innodb_page_reads显著增加 - purge 线程卡住(
History list length > 1000)→ 已删记录长期驻留 → undo 日志膨胀、MVCC 快照变大 →SELECT扫描链表更久 - INSERT 时优先复用空洞 → 写入位置不连续 → 随机 IO 上升,尤其在机械盘或高负载 SSD 上更明显
OPTIMIZE TABLE 能释放空间,但代价明确
OPTIMIZE TABLE t 在 MySQL 5.7/8.0 中本质是 ALTER TABLE t ENGINE=InnoDB,它重建聚簇索引:逐行读有效数据 → 按主键顺序重写进新 .ibd → 替换旧文件。这个过程真正释放空间,但也带来硬约束:
- 必须满足
innodb_file_per_table = ON,否则重建仍写入共享表空间ibdata1,白忙一场 - 需要额外磁盘空间 ≥ 当前
t.ibd大小,否则中途报ERROR 1034并锁死表 - 全程加
MDL_EXCLUSIVE锁,DML 全部阻塞,大表可能卡住元数据锁 - 不写 binlog(ROW 格式下),从库
.ibd大小与主库不一致
执行前务必检查:df -h /var/lib/mysql、SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(NOW() - trx_started) > 60、SHOW CREATE TABLE t\G 确认引擎和独立表空间。
线上大表不能停机时的替代方案
纯 SQL 没有“在线 shrink”的能力。InnoDB 不支持动态收缩数据文件,这是引擎层硬限制:
- 分区表可秒级释放:
ALTER TABLE orders DROP PARTITION p2023_q1—— 直接删整个.ibd子文件,前提是建表时用了PARTITION BY RANGE (create_time) -
pt-online-schema-change --alter="ENGINE=InnoDB"是目前最稳妥的在线重建方案,但要求磁盘余量 ≥ 表原始大小 - 导出再导入(
mysqldump --where="create_time > '2025-01-01'")可行,但存在数据窗口期,且耗时长、IO 压力大
最容易被忽略的是:purge 卡住时,哪怕 OPTIMIZE TABLE 跑完,实际释放量也可能极少——得先看 SHOW ENGINE INNODB STATUS\G 里 History list length 是否远高于 1000。

















