数据库表的空间回收

innodb_file_per_table：控制表数据的存在形式。设置为ON表示数据存放在.ibd为后缀的文件中。设置为OFF表示数据存放在共享表空间，这种情况下即使表删除了空间不会回收。因此一般情况下推荐设置为ON。

1.数据删除插入流程

无论是删除还是插入，数据更新后表空间处于可复用但是还未被使用的空间，都属于“空洞”。这些空洞尚未被InnoDB释放回收，从而导致数据页空间利用率比较低。

• 删除

复用：无论是删除innoDB索引树上的一条记录，还是删除一个数据页上的所有记录。InnoDB并不会直接将整行记录或者整个数据页都删除，而是在这条记录的位置或者这个数据页上标记为“可复用”，这样新的数据插入后直接使用原本的空间即可。

其中行复用和页复用的区别在于，新插入行复用位置的记录ID，会受到原先位置记录的约束。而页复用则不受任何数据索引或者是主键的限制。

• 插入

举个例子，原先数据页中某条记录，由于插入新的记录导致数据页分裂，与新记录合并存放到新的数据页。此时该条数据原先所在的位置就是数据空洞。

2.重建表

重建表：新建一张表，将旧的存在多个空洞的表数据全部一行一行拷贝到新表当中。显然得到的新表中数据页利用率更高，并且主键索引更加紧凑。下面介绍三种重建表的策略。

DDL：由于执行DDL语句后可能会修改表的结构，因此会触发“重建表”整个过程。

2.1COPY

alter table t engine =innodb,ALGORITHM=copy

在service层创建一个临时表来对数据进行拷贝，最后用临时表替换原表。

在整个过程中表A仅允许读操作，阻塞所有DML更新写操作。由于需要消耗额外的数据空间，因此仅适用于数据量比较小的场景。

2.2Inplace

alter table t engine =innodb,ALGORITHM=inplace

在InnoDB内部创建临时文件tmp_file进行修改复制(这里可以近似看作是原地修改表的字段)，整个DDL过程都是在InnoDB内部进行。对于server层而言并没有将数据挪动到临时表，相当于原地操作。

这里要注意，说是“原地修改”，实际上临时文件也需要占用一定的磁盘空间。适用于大表修改的场景。

2.3Online DDL

重建表时如果有新的数据要写入，会导致数据丢失，因此引入Online DDL对操作流程进行优化，整个过程如下：

• 执行DDL语句前，获取MDL写锁。

• 🔥拷贝数据之前，MDL写锁退化为MDL读锁，从而保证在执行DDL和拷贝时，不会阻塞其它线程的DML增删改操作。这就是Online名称的由来。

• 创建一个临时文件，根据A表中主键数据页的有效记录生成B+树，记录到临时文件中。此时表是最紧凑的。

• 此时如果要写入数据，写入到旧的A表后，统一将所有操作记录在一个row log文件当中。

• 临时文件生成完后，按顺序进行以下两个步骤：

  • ①将MDL读锁升级到MDL写锁，为接下来重放更新操作做准备，此时一定要控制多并发DML。

  • ②将row log所有写操作应用到临时文件中。此时临时文件表数据时最新的，但是并不一定是最紧凑的，因为往表中插入新的数据后可能产生新的空洞。

• 将临时文件代替表A的数据文件。

区别与联系：显然online DDL用到了临时文件，因此online一定是inplace的。反之inplace不一定是online可多并发DML的，比如全文索引。

3.总结

要想达到真正的删除表并且释放空间，需要delete+alter，或者truncate(drop+create)