经常会有同学来问我，我的数据库占用空间太大，我把一个最大的表删掉了一半的数据，怎么表文件的大小还是没变？

InnoDB 表的组成：表结构定义和数据。

数据删除流程

InnoDB 中，数据是以 B+ 树结构来存储数据的：

image

假设删掉表记录 R4，此时 InnoDB 只是把 R4 标记为已删除状态，后续这个位置可以插入新的数据，但是磁盘文件大小并不会变化。

何时复用？

比如，插入一个 ID 是 400 的记录，就可以直接复用原来 R4 的空间，若插入的是 800，为了保持 B+ 树的结构，就不能复用该空间了。

删除整页数据

当删除了整页数据后，InnoDB 会将该页标记为已删除，整页都可复用。

同时，若两个相邻的数据页利用率都比较低，系统会把两页上的数据整合到一个页中，另一个页就会标记为可复用。

删除整个表

此时，所有的数据页都会标记为可复用，但是磁盘空间仍然不会变小。

总结

delete 操作，只是把记录的位置标记为「可复用」，但是磁盘大小不会变化，这些可以复用，而未被使用的空间，看起来就像空洞。

增删改-造成空洞

当数据是随机插入时，就可能造成索引的数据页分裂。

image

如图所示，由于 page A 写满，此时插入 ID 为 550 的数据，就不得不申请新的数据页，页分裂完成后，A 上就会留下空洞。

另外，更新索引上的值，其实是删除旧值，插入新值，这个过程同样会造成空洞。

综上，经过大量增删改操作的表，都是可能存在大量空洞的，若要收缩表空间，就要清除这些空洞。

重建表-清除空洞

如何清除空洞？

直接的想法就是，新建一个表结构相同的表，然后按主键 ID 递增的顺序，将原表中的数据，插入到新表。

这样，新表中就不会存在空洞了。新表的主键索引也会更加紧凑，数据页的利用率也更高。

上面的操作可以通过下面的语句自动完成：

alter table A engine=InnoDB;

MySQL 会自动完成转存数据、交换表名、删除旧表的操作。

image

详细流程：

1、新建临时文件，扫描原表 A 的所有数据页；
2、根据表 A 的记录生成 B+ 树，存储到临时文件中；
3、生成临时文件过程中，对 A 的所有操作都会记录在一个日志文件中，对应图中的 state2 状态；
4、临时文件生成后，将日志文件中的操作应用于临时文件，得到一个完整的数据文件，对于 state3；
5、用临时文件替换表 A 的数据文件；

表重建的过程是允许对表 A 做增删改操作的，因此是一个 Online DDL（MySQL5.6+）

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

上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。

因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，我推荐你使用 GitHub 开源的 gh-ost 来做。

---

你的关注是对我最大的鼓励！

关注本公众号，后台回复「2018」即可获取传智播客 2018 最新 Python 和 Java 教程。

公众号提供CSDN资源免费下载服务！

---