表数据都删了一半，可我的表文件咋还是那么大

本文分享自华为云社区《为什么表数据删掉一半，表文件大小不变？》，作者： JavaEdge。

由于 DB 占用空间太大，我删除了大表的一半数据，可为啥这表文件的大小没变？

数据库表的空间回收到底是怎么做的呢？

InnoDB 表包含：

• 表结构定义（所占空间小）

• 表数据（重点）

MySQL 版本：

• ＜8.0，表结构存在于 .frm 后缀文件里

• 8.0，允许将表结构定义放在系统数据表。

为何直接删除表数据无法回收表空间？

如何正确回收空间？

1 innodb_file_per_table

表数据

• 既能存在于共享表空间

• 也能是单独的文件

该行为由参数 innodb_file_per_table 决定：

• OFF，表的数据放在系统共享表空间，和数据字典放一起

• ON，每个 InnoDB 表数据存储在一个 .ibd 后缀文件

从 MySQL 5.6.6 版本开始，默认值为 ON。

推荐无论哪个版本，都将该值设为 ON：

• 因为一个表单独存储为一个文件更容易管理，不需要时，直接 drop table，系统就会删除该文件

• 若放在共享表空间中，即使表删掉了，空间也不会回收

因此后续讨论都默认该设置为 ON。

删除整个表时，可用 drop table 回收表空间。但更常见的场景是删除某些行，于是就会发现：表中的数据被删除了，但表空间没有被回收！

2 数据删除流程

InnoDB 索引示意图

假设，我们要删掉 D4，InnoDB 引擎只会把 D4 这个记录标记为删除。若之后要再插入一个 ID 在 300、600 之间的记录时，可能会复用该位置。但磁盘文件并不会缩小。

3 InnoDB 的数据按页存储，若删掉一个数据页上的所有记录，会咋样？

整个数据页就能被复用了。

但是，数据页的复用跟记录的复用不同：

• 记录的复用，只限于符合范围条件的数据。比如 D4 记录被删除后，若插入一个 ID=400 的行，可直接复用该空间。但若插入 ID=800，就不能复用该位置

• 而当整个页从 B+树里摘掉后，可复用到任何位置

  若将数据页 pageA 上的所有记录删除后，pageA 会被标记为可复用。这时若插入一条 ID=20 的记录，需要使用新页时，pageA 就能被复用。

若相邻两个数据页利用率都很小，系统就会把这俩页上的数据合到其中一个页上，另外一个数据页就被标记为【可复用】。

4 若用 delete 命令删除整个表的数据呢？

所有的数据页都会被标记为【可复用】，但磁盘上的文件不会变小。

delete 命令其只是将记录的位置或数据页标记为“可复用”，但磁盘文件的大小不会变。即通过 delete 命令不能回收表空间。这些可以复用但实际没有被使用的空间，看起来就像“空洞”。

不仅删除数据会造成空洞，插入数据也会。

3 插入数据导致的“空洞”

若数据按索引递增顺序插入，则索引是紧凑的。但若数据是随机插入的，就可能造成索引的数据页分裂。

假设 pageA 已满，此时再插入一行数据，会怎样呢？

插入数据导致页分裂

由于 pageA 满，再插入 ID=550 时，就得再申请一个新页面 pageB 保存数据。页分裂完成后，pageA 末尾就留下空洞（实际可能不止 1 个记录的位置是空洞)。

4 更新索引上的值会导致空洞吗？

更新可理解为删除一个旧值，再插入新值。所以也会造成空洞。

综上，经过大量增删改的表，都可能存在空洞。若能去掉这些空洞，就能达到收缩表空间的目的。重建表，就能达到目的。

5 重建表

若现在有一表 A，要做空间收缩，为了去掉表中存在的空洞，可新建一个与表 A 结构相同的表 B，然后按主键 ID 递增顺序，把数据一行行从表 A 里读出，再插入表 B。

因为表 B 是新建表，所以表 A 主键索引上的空洞，在表 B 都不存在。显然表 B 的主键索引更紧凑，数据页的利用率更高。若将表 B 作为临时表，数据从表 A 导入表 B 的操作完成后，用表 B 替换 A，就达到收缩表 A 空间的效果。

可用

alter table A engine=InnoDB

重建表。在 MySQL 5.5 前，这命令的执行流程跟我们前面描述的差不多，区别只是这个临时表 B 不需要自己创建，MySQL 会自动完成转存数据、交换表名、删除旧表的操作。

改锁表 DDL：

往临时表插入数据的过程最耗时，若在此过程中，有新数据要写入到表 A，就会造成数据丢失。因此，整个 DDL 过程中，表 A 不能有更新，即这 DDL 不是 Online 的。

MySQL 5.6 版本引入 Online DDL，优化了该操作流程。

引入 Online DDL 后，重建表的流程

• 建立一个临时文件，扫描表 A 主键的所有数据页

• 用数据页中表 A 的记录生成 B+树，存储到临时文件

• 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log），对应图中 state2

• 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应 state3

用临时文件替换表 A 的数据文件。

Online DDL：

和上图不同在于，由于日志文件记录和重放操作的存在，该方案在重建表的过程中，允许对表 A 做增删改，这就是 Online DDL 名字来源。

DDL 之前还要拿 MDL 写锁的，这也能叫 Online DDL？

确实，图 Online DDL 流程中，alter 语句在启动的时候需获取 MDL 写锁，但该写锁在真正拷贝数据前就退化成读锁了。

为什么要退化？

为了实现 Online，MDL 读锁不会阻塞增删改操作。

为何不直接解锁？

为了保护自己，禁止其他线程对该表同时做 DDL。对一个大表，Online DDL 最耗时过程就是拷贝数据到临时表时，这个步骤的执行期间可接受增删改操作。所以，相对于整个 DDL 过程，锁的时间非常短。对业务来说，就可认为是 Online 的。

上述这些重建方法都会扫描原表数据、构建临时文件。对于大表，这很消耗 IO 和 CPU。因此，若是线上服务，要谨慎控制操作时间。若想要较安全的操作，推荐使用 GitHub 开源的 gh-ost。

Online 和 inplace

图改锁表 DDL 中，把表 A 中的数据导出来的存放位置叫作 tmp_table。这是个临时表，创建在 server 层。

在图 4 中，根据表 A 重建出来的数据是放在“tmp_file”，该临时文件是 InnoDB 在内部创建的。整个 DDL 过程都在 InnoDB 内部完成。对于 server 层，没有把数据挪动到临时表，是个“原地”操作，这就是“inplace”名称来源。

若有一个 1TB 的表，磁盘空间 1.2TB，能做个 inplace 的 DDL 吗？

不能。因为，tmp_file 也是要占用临时空间的。重建表的这个语句 alter table t engine=InnoDB，其隐含意思：

alter table t engine=innodb,ALGORITHM=inplace;

跟 inplace 对应的就是拷贝表的方式了，用法是：

alter table t engine=innodb,ALGORITHM=copy;

当你使用 ALGORITHM=copy 的时候，表示的是强制拷贝表，对应的流程就是图 3 的操作过程。

inplace 跟 Online 是不是就一个意思？

不是的，只是在重建表这个逻辑中刚好是这样。

若给 InnoDB 表的一个字段加全文索引：

alter table t add FULLTEXT(field_name);

这个过程是 inplace 的，但会阻塞增删改操作，非 Online。

如果说这两个逻辑之间的关系是什么的话，可以概括为：

• DDL 过程如果是 Online 的，就一定是 inplace 的

• 反过来未必，即 inplace 的 DDL，有可能不是 Online 的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引(SPATIAL index)就属于这种情况。

使用 optimize table、analyze table 和 alter table 这三种方式重建表

从 MySQL 5.6 版本开始

• alter table t engine = InnoDB（也就是 recreate）默认的就是上面图 Online DDL 的流程了

• analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁

• optimize table t =recreate+analyze

点击关注，第一时间了解华为云新鲜技术~​