为什么MySQL数据删除了一半，表文件体积却没变小？

前言

随着表记录的增加，表体积也会越发变大，为了性能考虑，我们可能会尝试通过delete命令，删除部分无用的数据，以期达到减小表体积的效果。不过令人失望的是，即使我们把表中的数据全部删除，表的体积也不会有丝毫的减少。

今天我们来聊聊，缩小表体积的正确姿势。

Mysql是如何删除数据的

我们知道，Mysql中的数据是以B+树的形式存放的，当我们删除一条数据时，Mysql的innodb引擎找到这条记录后，只会将这条记录所在的位置标记为删除，而不会真的删除磁盘上的文件。当有新的记录插入时，可能会复用这个位置。

另外，innodb的数据是按数据页的形式存放的，如果整个页上的数据都被删除了，那这个页就会被标记为可复用了。

所以，即使我们使用delete命令删除整个表的记录，也仅仅是所有的数据页被标记为可复用，但是磁盘上的文件，并不会变小。

换句话说，通过delete命令是无法回收表空间的。

进一步的，当我们多次使用delete命令删除数据后，大量的数据页上会出现很多可以复用而没有被使用的空洞。

当然，除了删除数据会造成空洞，插入、更新数据也会。

总得来说，经过大量增删改的表，基本都会存在很多的数据空洞。如果我们想办法减少这些空洞，自然可以节省大量的表空间。

重建表

重建表的逻辑也很简单：新建一个和原表结构一样的表，将原表中的数据，按照主键递增的方式， 把数据一行一行地从原表读出，然后写入新表。

在新表中，主键索引更加紧凑，空间利用率更高，体积自然也更小。

重建表的命令也很简单：

alter table t engine=InnoDB;

需要注意的是，Mysql 5.5 版本之前，这个DDL命令不是online的，在执行此命令时，务必要选择业务低峰期。

从5.6版本开始，此命令已被优化为online DDL。

online DDL的流程是这样的：

1，扫描原表中的所有数据，生成一颗新的B+树，将其存入一个临时文件。

2，存入临时文件的过程中，如果有对原表的增删改操作，将这些操作存入一个日志文件。

3，临时文件生成完以后，将日志中的操作重放到临时文件中，这样就得到了一个与原表一样的数据文件。

4，用临时文件替换原表的数据。

由于日志文件的存在，在重建表的过程中，原表仍然是可以操作的，所以可以称之为online DDL。

总结

如果我们要对一个表缩容，仅仅通过delete命令删除数据是不行的，需要使用alter table命令进行表重建。alter table在5.6之后是online DDL，但是会消耗大量的IO及CPU，所以建议在业务低峰期使用此命令。5.5之前的版本，建议业务停止后再进行操作。

写文不易，朋友们点赞关注支持一下吧。