【MySQL运维】使用OPTIMIZE整理索引碎片解决删除数据磁盘不释放问题

在对MySQL数据进行删除的时候会遇到“使用drop table命令删除表后，可以正常回收表占用的磁盘空间。但是通过delete删除了大量数据后表空间没有回收”这么一个问题。这是因为通过delete命令删除的数据（不止是删除数据会造成空洞，插入数据也会），其实只是把记录的位置或者数据页标记为了“可复用”，后续的写入就可以直接使用可复用的这部分空间，但磁盘文件大小却不会变。这些可复用而没有被使用的空间就像一块块碎片，只有进行过碎片整理后才会释放掉空间

#方法1 通过不做任何操作的ALTER TABLE重建表，只需要指定引擎即可
ALTER TABLE 表名 engine=InnoDB;

#方法2
OPTIMIZE TABLE 表名;

#区别：alter table t engine = InnoDB（也就是recreate），而optimize table等于recreate+analyze

而在重建的过程中可能还会发生“重建后的表反而更大的问题”。这是因为如果数据是顺序写入的，那么这些索引都是紧凑的，并没有什么碎片产生，而随机插入的数据就可能造成索引的数据页分裂。线上业务中一般log表都是顺序插入，没有大量delete的情况下是没有所谓的碎片的。而重新收缩的过程中，页会按一定比例重新整理数据页，所以收缩之后文件就反而变大了。