MySQL表空间膨胀与碎片回收：data_free判据与安全重建

  1.  Home
  2. Text  
    3. Share to：

删除了一半数据，磁盘却没变小

「我把日志表清了 800 万行，磁盘为什么一点都没释放？」这是我在群里被问得最多的问题之一，而且对方的下一句通常是「是不是要重启 MySQL」。不用重启，但也不用绝望——这恰好是理解 InnoDB 存储结构最好的入口。这篇讲清楚三件事：为什么空间不还给你、什么时候该回收、以及回收时怎么不把站点搞挂。

我的一个视频站，日志表曾经从 12GB 膨胀到 61GB，其中 40% 是碎片。彻底解决的过程走了不少弯路，包括一次差点让站点停摆两小时的 OPTIMIZE TABLE，这里一并写下来。

先理解：InnoDB 的空间是怎么「记账」的

InnoDB 的逻辑单位是表空间（tablespace）。一张表在 innodb_file_per_table=ON（5.6 之后的默认值，务必确认）下对应一个 .ibd 文件；较新的 8.0 会把表空间拆成多个 .ibd 文件。文件内部按 页（page，默认 16KB）组织，页再组成 区（extent，1MB = 64 页）。

关键点：删行只是把这些行标记为可复用。DELETE 的物理动作是把记录打上删除标记、从索引里摘掉，页面本身仍然属于这张表。后面的 INSERT 可以复用这些空洞，但如果这张表此后没有足够的写入，空洞就一直占着文件大小。所以 ls -lh table.ibd 不变，df -h 也不变，而 SELECT COUNT(*) 掉了一大截——这就是「碎片」。

碎片分三种，处理方式完全不同：

  • 页内碎片：页里有大量空洞但页本身还被占用。这种靠 OPTIMIZE/重建能回收，但也最不影响性能，可以放着。
  • 页间碎片（文件尾部连续空洞）：文件末尾有一段完全空的区。InnoDB 在 8.0 之前的 OPTIMIZE 后能真正把文件截断；5.7 时代的 innodb_file_per_table + OPTIMIZE 是可回收的。
  • 索引碎片：B+ 树页填充率低（比如随机的 UUID 主键导致页分裂），索引体积虚高、查询 I/O 增加。这一类才是真正影响性能的，页内空洞只是浪费磁盘。

量化：用 SQL 算出到底浪费了多少

不要凭感觉判断要不要回收，先量。下面这段是我常驻在监控脚本里的查询，直接用 information_schema.TABLES：

SELECT
  table_name,
  engine,
  table_rows,
  ROUND(data_length / 1024 / 1024 / 1024, 2) AS data_gb,
  ROUND(index_length / 1024 / 1024 / 1024, 2) AS index_gb,
  ROUND((data_length + index_length) / 1024 / 1024 / 1024, 2) AS total_gb,
  ROUND(data_free / 1024 / 1024 / 1024, 2) AS free_gb,
  ROUND(100 * data_free / NULLIF(data_length + index_length + data_free, 0), 1) AS free_pct
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
ORDER BY data_free DESC
LIMIT 20;

data_free 就是当前已分配但未被使用的字节数。实操判据：free_pct > 30% 且 free_gb > 1GB 才值得动手。百分比高但只有几十 MB 的表，回收收益还不如停机风险大。table_rows 是估算值，误差可能到 30%，要精确行数得 COUNT(*)，但对「要不要回收」这个决策够用了。

更精确的一点：看索引的填充率。INFORMATION_SCHEMA.INNODB_INDEX_STATS（8.0 需要开 innodb_stats_persistent）里有 size 字段，能拿到每个索引占用的页数。sys.schema_table_statistics 也能给出每张表的读写比，一天写不到一百行、但被频繁全表扫的小表，碎片就不值得清。

OPTIMIZE TABLE 到底做了什么（以及为什么它不是唯一办法）

MySQL 8.0 里 OPTIMIZE TABLE 对 InnoDB 的实际语义是 ALTER TABLE ... FORCE, ALGORITHM=INPLACE，也就是重建表：创建一张新表、把数据按主键顺序搬过去、重建所有索引，最后原子地 RENAME。过程中需要额外的临时空间（约等于原表大小），期间持有 MDL 的排他锁在最后换名时短暂生效。

它会遇到的坑：

  • 磁盘空间要备足 2 倍以上。我的 61GB 表第一次失败就是因为只剩 70GB 空闲，中途 errno 28。可以指定 innodb_tmpdir 到另一块盘。
  • 长事务会让它卡死。重建前必须检查 information_schema.innodb_trx 里有没有超过 60 秒的事务，否则它等 MDL，它一等，后面所有查询全排队。SHOW PROCESSLIST 里会看到 Waiting for table metadata lock 越堆越多。
  • 主从延迟。重建期间产生的 binlog 会把从库压垮，尤其 binlog_format=ROW 且表很大的情况。
  • 8.0 里 data_free 不一定归零。因为 innodb_file_per_table 的区分配策略会预留一部分，回收后 free_pct 剩个位数是正常的。

如果表大到重建不现实（几百 GB），有两个更温和的路子：

路子一：gh-ost / pt-online-schema-change 做在线重建

原理是新建影子表、通过 binlog 或触发器同步增量、最后原子改名。对业务基本无感，代价是需要额外的磁盘和时间（一个大表跑几小时到一天）。gh-ost 更轻（不需要触发器），但需要 REPLICATION SLAVE 权限。

路子二：分区表 + 直接 DROP PARTITION

日志类表的正解其实是一开始就按月分区，过期数据 ALTER TABLE ... DROP PARTITION p202601;，瞬时释放整块空间，不产生碎片、不锁表。但分区表有硬限制：主键必须包含分区键、不支持外键、一张表最多 8192 个分区。已经有 1430 那篇文章详细讲过，这里只提醒一句——如果表已经很大又想改成分区，那还是要重建一次，早做早好。

一次差点停站的复盘

回到我的视频站。那张 video_access_log 表 61GB、1.2 亿行，碎片率 42%。当时我图省事，直接在业务高峰期跑了 OPTIMIZE TABLE。结果：

  1. 重建从 14:02 开始，到 14:40 还在跑；
  2. 14:35 开始有用户反馈页面很慢，SHOW PROCESSLIST 里一堆 Waiting for table metadata lock；
  3. 14:42 我看到 PHP-FPM 的 active processes 打满，网站 502；
  4. 14:48 我 KILL 了 OPTIMIZE 的会话——这其实帮了倒忙，因为 kill 后回滚要消耗更长时间，直到 15:31 才彻底结束。

真正该做的顺序是：先查明谁在占 MDL，杀掉那个长事务，再在低峰期执行。后来我把流程固化成脚本，包括四步前置检查：

-- 1. 检查长事务（结果非空就别动）
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
       trx_mysql_thread_id, LEFT(trx_query, 80) AS q
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30;

-- 2. 检查是否有未提交的 DDL 在排队
SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE = 'TABLE' AND LOCK_STATUS = 'PENDING';

-- 3. 检查从库延迟（在有从库的站必查）
SHOW SLAVE STATUS\G

-- 4. 检查剩余磁盘（要 > 表大小 * 2）
SELECT table_name,
       ROUND(data_length / 1024 / 1024 / 1024, 2) AS gb
FROM information_schema.TABLES
WHERE table_schema = DATABASE() AND table_name = 'video_access_log';

四项都过了才执行，并且把 lock_wait_timeout 临时调小（比如 10 秒），这样万一撞上锁，它自己会先放弃而不是把整站拖死：

SET SESSION lock_wait_timeout = 10;
SET SESSION innodb_lock_wait_timeout = 10;
ALTER TABLE video_access_log FORCE, ALGORITHM=INPLACE, LOCK=NONE;

注意 LOCK=NONE 是期望值不是保证，如果存储引擎做不到会报错，这正是我们要的——报错比默默锁表好。验证成功后再重建，最终 61GB 降到 34GB，碎片率变成 4%。

补充：碎片率之外，还要看「页填充率」和「碎片分布」

data_free 只告诉你一共有多少空闲页，不告诉你这些页在文件里是零散分布还是集中在尾部——这两者的处理成本差得远。集中在尾部的空洞，ALTER TABLE 之后能真真切切还给你；零散分布的，重建只是把同样多的页重新排一遍，文件大小几乎不变，只有 B+ 树变紧凑带来一点查询收益。

判断方式我用 innodb_space（来自 Jeremy Cole 的 innodb_ruby 工具包）看单个表的页分布，虽然装起来略麻烦，但比盲猜准：

gem install innodb_ruby
innodb_space -s ibdata1 -T mydb/posts -f space-page-type-summary
innodb_space -s ibdata1 -T mydb/posts -f space-extents-illustrate

输出里 INDEX 页和 FREE 页的比例、以及 FREE 页在 extent 分布图上的位置，一眼就能看出是「尾部大块空闲」还是「中间千疮百孔」。装不了 ruby 也没关系，用一个近似办法：SELECT * FROM information_schema.INNODB_TABLESPACES WHERE NAME = 'mydb/posts'; 里的 FILE_SIZE 与 ALLOCATED_SIZE 对比，比值远大于 1 说明有大量空闲页。

还必须强调一点：碎片并不等于慢。我见过一张 20GB 日志表碎片率 55%，查询照样毫秒级，因为它的访问模式是「按日期范围扫最新分区」，热点数据永远在缓冲池里。也见过 2GB 的表碎片率只有 8%，但每次查询要扫 300 万行，慢得离谱。真正决定快慢的是扫描行数，不是磁盘占用。所以回收碎片的动机应该分两个：为了磁盘空间（看 data_free），或为了提高 B+ 树局部性（看填充率与缓存命中率）。两者不要混为一谈，否则很容易为了「看起来整洁」去动一张根本不需要动的表。

治本：别让碎片长到 40%

碎片的根源几乎永远是三件事：

  • 高频 DELETE/UPDATE 的单表结构。日志表每天都在删旧数据，天然会累积碎片。解决方式是分区 + DROP PARTITION，或者按天分表（log_20261021），到期直接 DROP TABLE 比 DELETE 快几百倍，而且不产生碎片。
  • 随机主键。UUID 主键导致 B+ 树随机插入、页分裂、填充率掉到 50%。用 BIGINT AUTO_INCREMENT 或者有序的 UUID（如 UUIDv7 / ULID），同样的数据索引可以小三分之一。
  • 频繁的 varchar 更新变长。把 varchar(255) 从 10 字节改成 200 字节时，同一页放不下就会页分裂。把频繁更新的字段和不变字段拆表，或者用固定长度类型。

我做了一个每周日跑的 cron，只查 free_pct > 30 的表并告警，超过 50% 才进入人工处理队列。监测比清理重要，因为清理永远有停站风险，而监测是零成本的。

小结

删数据不释放磁盘不是 bug，是 InnoDB 的设计选择——用空间换写入性能。你要做的只是在「浪费的磁盘」和「回收的风险」之间做个取舍。三条结论可以直接拿走：

  1. 先量后动：data_free 占比超过 30% 且绝对值得过 1GB，才考虑回收；
  2. 回收前必查长事务，并设置短的 lock_wait_timeout 兜底，宁可失败也别锁站；
  3. 日志表用分区或分表，从根上避免碎片，比事后 OPTIMIZE 便宜十倍。

最后补一句：innodb_file_per_table 如果是 OFF，所有表都在 ibdata1 里，那删了数据连表级回收都做不到，只能整实例导出重建。这个参数 5.6 起默认 ON，但 5.5 时代升上来的老库记得检查一下。

Last modification：October 21st, 2026 at 01:27 pm
© 允许规范转载

相关文章

Leave a Comment