为什么表数据删一半，表文件大小不变？

1.引言

可能会有人问，为什么把表中的数据删除了一半，但是表文件的大小没变？

首先，我们以InnoDB引擎为例。一个InnoDB表包含两部分，表结构定义和数据。在MySQL8.0版本以前，表结构是存在以.frm为后缀的文件里。MySQL8.0之后，允许把表结构放在系统数据表中了，因为占用很小。

表数据既可以存在共享表空间里，也可以是单独的文件。这个是由参数innodb_file_per_table控制的，当参数为OFF时，表的数据放在共享表空间内，也就是和数据字典存放在一起。当参数为ON时，每个InnoDB表数据存储在以.ibd为后缀的文件。从MySQL5.6开始，这个参数默认就是ON。

推荐将innodb_file_per_table参数设置为ON，因为一个表单独存储为一个文件更容易管理。

2.删除数据流程

根据前面的章节，我们知道InnoDB里面的索引都是用B+树来存储的。

假设要删除R4这个记录，InnoDB引擎只会把R4这个记录标记为删除。如果之后插入的数据在300和600之间，那么就可能会复用这个位置。但是磁盘文件的大小不会缩小，这种就像存在“空洞”一样。

因为InnoDB的数据是按页存储的，那如果删掉了一整个数据页的数据会怎么样？答案是整个数据页就可以被复用。

但是，数据页的复用和记录的复用是不同的。

• 记录的复用：只限于符合范围条件中的数据。例如插入数据700的话，那就不能复用400的位置。
• 数据页的复用：当整个数据页从B+树中摘除后，可以复用任何位置。当其它地方需要新页的时候，是可以被复用的。

因此，回到之前的问题。当把整个表的数据删除了，所有数据页都会变为可复用，但是不会改变文件在磁盘中的大小。

3.插入数据流程

不仅删除数据会造成这种情况，插入数据也会造成这种情况。如下图所示：

例如上图中，由于Page A满了，再插入一个新值，就会造成数据页的分裂，也会出现“空洞”。

4.重建表

通过重建表的方式就可以达到去除“空洞”。

我们可以新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，将数据一行一行地从表A读出来再插入到表B中。这样就可以消除表A主键索引上的空洞。如下图所示：

在MySQL中，我们可以使用alter table A=InnoDB命令来重建表。 在MySQL5.5之前，过程就和上面描述地是一样的。

显然这种方式最花时间的地方在往临时表插入数据上。这就导致如果在这个过程中，有新的数据写入A表，就会造成数据流程。因此这种方式不是Online的。

在MySQL5.6版本之后开始引入了Online DDL，对流程进行了优化，流程如下：

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

如下图所示：

来源：自己整理的MySQL实战45讲笔记