mysql InnoDB 数据碎片浅析与处理

项目场景：

项目中，有一张表，由于历史数据的原因，导致数据积累了1亿条，占据磁盘 170多G，后来磁盘告警，磁盘扩容之后，数据删除之后，留下了 100多G的数据碎片。

mysql 8.0.26

---

原因分析：

由于优化表（optimize）操作会锁表，这张表又是经常插入的表，此时必须得搞清楚两个问题：

1. 数据碎片是否会被重新使用
   数据删除之后，在磁盘只是被标记为已删除，并不会直接被使用。

   何时被利用？

   • 当一个page的所有数据被标记为删除时，innoDB 引擎会将这个page 附加到 extent后（不清楚何时！）
   • 当插入的数据正好位于这个区间时(pre.id, next.id)，主键值在前后两行主键之间¹

2. 优化操作具体是怎么执行的，会锁表多久

   • 新建一个与表A结构相同的表B
   • 按照主键递增的顺序，把表A中的数据一行一行读出，然后再插入表B
   • 表B的主键索引更紧凑，数据页的利用率也更高
   • 表B作为临时表，数据从表A导入到表B，然后用表B替换A

   ALTER TABLE table_name ENGINE=InnoDB [, ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}]²

   ALGORITHM 子句是可选的。如果省略 ALGORITHM 子句，如果支持 INSTANT 则用 INSTANT。否则，使用 ALGORITHM=INPLACE。如果不支持 ALGORITHM=INPLACE，则使用 ALGORITHM=COPY

   • COPY 表锁，禁止 DML（SELECT、UPDATE、INSERT、DELETE）操作
   • INPLACE 在操作的准备和执行阶段可能会短暂地对表进行独占元数据锁定。通常，支持并发 DML。
   • INSTANT 操作只修改数据字典中的元数据, 不涉及碎片清理，无锁

锁表的时间具体多久，取决于机器的性能和碎片的多少。
经测试 7 G 的数据碎片，清理大概花费了 3分钟左右，机器是 4 核 16 G的

解决方案：

在业务低峰期，我们是在 23:30 - 01:30 执行
ALTER TABLE table_name ENGINE=InnoDB, ALGORITHM=INPLACE;

参考文章

---

1. MySQL – 空间回收 ↩︎

2. ALTER TABLE Statement ↩︎