MySQL浅析之空间回收(4)

MySQL浅析之空间回收

1. 前言

我们在日常使用MySQL中，可能会发现这么个问题：

当我们数据库占用空间太大时，我们把一个最大的表的数据删了一半，可是表文件的大小还是没变。

我们就来聊聊表的空间回收：(默认讨论使用的是InnoDB引擎)

在一个表中包含两个部分，分别为表结构定义和数据。在MySQL 8.0版本以前，表结构是存在以.frm为后缀的文件里。而MySQL 8.0版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小，所以我们主要是看表数据。

2. 参数innodb_file_per_ftable

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table控制的：

1. 这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
2. 这个参数设置为ON表示的是，每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。

那么设置了这个参数有什么好处呢？

其实当我们设置为ON时，一个表单独存储为一个文件更加容易管理。**当我们不需要时，可以通过drop table命令，系统就会直接删除这个文件。**但是，如果我们放在共享表空间中了，那么即使删掉了，空间也是不会回收的。

因此，在日常使用中，我们都是默认将这个参数设置为ON。

但是，我们遇到的更多的删除数据的场景是删除某些行，这时就遇到了我们文章开头的问题：表中的数据被删除了，但是表空间却没有被回收。

3. 数据删除流程

我们可以看看上图，如果我们要删除ID为50的这个记录，InnoDB引擎只会把ID为50这个记录标记为删除。如果之后要再插入一个ID在30和60之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

如果我们要删除数据页上的所有记录，那么整个数据页也将会被复用。

但是我们需要明白，数据页的复用和记录的复用是不同的。

记录的复用，只限于符合范围条件的数据。比如上面的这个例子，ID为50这条记录被删除后，如果插入一个ID是55的行，可以直接复用这个空间。但如果插入的是一个ID是80的行，就不能复用这个位置了。

数据页的复用，可以复用到任何位置。如果将数据页page 1上的所有记录删除以后，page 1会被标记为可复用。这时候如果要插入一条ID=50的记录需要使用新页的时候，page 1是可以被复用的。

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

进一步地，如果我们用delete命令把整个表的数据删除呢？结果就是，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。

这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

并且，我们需要知道，不只是删除数据才会造成空洞，有时候插入数据也是会造成空洞的。

假设我们page 1满了，如果这时再插入一条ID为55的数据，这是就会造成页分裂，而此时page 1末尾空出来的空间就形成了空洞。

**了解了什么是空洞，那么我们不难知道，要想将空间回收，就必须解决掉这些空洞。**而如何删掉这些空洞呢，我们可以使用重建表来解决。

4. 重建表

重建表原理其实很简单，假如我们需要对表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会自动完成转存数据、交换表名、删除旧表的操作。

显然，花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表A的话，就会造成数据丢失。因此，在整个DDL过程中，表A中不能有更新。也就是说，这个DDL不是Online的。

而在MySQL 5.6版本开始引入的Online DDL，对这个操作流程做了优化。

我给你简单描述一下引入了Online DDL之后，重建表的流程：

1. 建立一个临时文件，扫描表A主键的所有数据页；
2. 用数据页中表A的记录生成B+树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中，对应的是图中2的状态；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件，对应的就是图中3的状态；
5. 用临时文件替换表A的数据文件。

5. 小结

了解了为什么删除数据，空间大小没有变化或变化不大的原因，是因为存在空洞现象。

空洞是指可以使用的空间，却并没有被使用。

其次介绍了删除空洞的办法，通过alter table A engine=InnoDB来重建表，从而删除空洞。