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

当数据库存储了一些已经不需要的数据，需要清除节省空间，之后发现表文件的占用大小还是不变的，这是为什么呢？？？

今天围绕数据库表的回收，讨论下如何处理这个问题。

首先还是针对应用最广泛的InnoDB引擎展开分析，一个InnoDB表包含两部分，即：表结构定义和数据。在MySQL8.0之前，表结构数据存在.frm文件里，MySQL8.0版本开始，这些数据存在系统数据库里，因为表结构定义占用的空间很小。

接下来，先说为什么删了一半表数据无法让这些数据进入表的空间回收，然后再介绍正确回收空间的方法。

参数innodb_file_per_table

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

1. 这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起
2. 这个参数设置为ON表示的是，每个InnoDB表数据存储在一个以.ibd为后缀的文件中，从MySQL 5.6.6版本开始， 它的默认值就是ON了。

无论使用什么版本，都将这个值设置为ON，因为如果不这么做，把表数据放在共享表空间，drop table是不会触发空间回收，但是把表数据隔离出去，是可以触发空间回收的。

所以，将innodb_file_per_table设置为ON， 是推荐做法， 我们接下来的讨论都是基于这个
设置展开的。

虽然说上面是很直接的就drop table了，但是真的业务场景，我们都是去delete某一行的数据，问题是delete了之后，数据却没有消失，占有空间减小。

下面来看一下删除流程

数据删除流程

我们先再看一下InnoDB中索引的图，之前介绍索引时候曾经提到过，InnoDB里的数据都是基于B+树的结构组织的。

假设要删除R4这个记录，InnoDB只会把R4这个记录标记为删除，如果之后再插入一个ID在300和600之间的记录时，可能会复用这个位置，但是磁盘文件的大小并不会缩小。

现在已经知道了InnoDB的数据是按照页去存储的，那么如果我们删掉了一个数据页上的所有记录，会怎么样？

答案是：整个数据页也就可以被复用了。

但是数据页的复用和记录的复用是不同的。复用一个记录，只限于符合范围条件的数据，复用一个页，可以拿过来任何种类的数据到任何位置。
但是如果相邻两个数据页的利用率小，系统就会把这两个页上的数据合并到其中一个页上，只不过那个被合并的另一个页面(是原来就有的页面，不是复用的页面)就会被标记为可以被复用。

进一步地，如果用delete删除整个表的数据呢？结果就是，所有的数据页都变为可以复用，但是在磁盘上文件占用不会减小。这也就形成了“空洞”

不止是删除数据会造成空洞，插入数据也会。 如果数据是按照索引递增插入，那么就是紧凑的，但是如果是随机插入的，就会造成索引的数据页分裂。

假设数据页PageA已经满了，这时再插入一行数据会如何呢？

由于pageA满了，再插入数据，就不得不重新申请一个新的页面来保存数据了，页分裂完成后，pageA的末尾就留下了空洞。

要减少库表占用的磁盘，就需要收缩表，而重建表，就可以达到这样的目的。

重建表

如果现在新建一个和原来的表A相同结构的表B，然后按照主键ID自增的顺序，把数据一行行的从表A读出来再插入到B中，由于B是新建的表，所以不存在空洞，这样就起到了收缩表A的作用。

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

但是上面仅仅是MySQL5.5之前的情况，在MySQL5.6对这个操作流程做了优化：

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

可以看到， 与前面的过程不同之处在于， 由于日志文件记录和重放操作这个功能的存在， 这个方案在重建表的过程中， 允许对表A做增删改操作。 这也就是Online DDL名字的来源。

但是，对于大表来说这个操作是很好资源的。