记一次极限的OPTIMIZE TABLE 经历

问题

MySQL服务所在磁盘容量大小为500G，目前已用了450G，马上不够用了。首先想到方案是进行扩容，但本例中，却不这样做。因为本例中，数据库中有一张200G的表，之所以这么大，是因为该表中的其中一个字段为text类型，存储的内容很长，但目前已不再使用，所以准备将字段全部置为null，以节省空间，预估可节省出150G的存储空间，可以再撑个1年半载没问题。

解决方案

首先将该字段分批全部设置为空，不是本文讨论的重点，略过。

置空查看磁盘空间，发现还是450G，并没有减少。这是正常的，因为MySQL innodb的存储机制，delete 语句删除数据、删除字段、字段置空，其空间不会立刻交还给操作系统。但该表如果有后续的插入操作，空间是可以复用的。但一个表空间别的表是不能复用的。如果想手动回收这些空间，可以执行OPTIMIZE TABLE table语句。

OPTIMIZE TABLE原理

这里我是参考了丁奇大佬《MySQL实战45讲》中的《13 | 为什么表数据删掉一半，表文件大小不变？》，这里借用网上对应一张图。

state1:要回收的数据表
state2: 执行OPTIMIZE TABLE ，mysql 会在数据库的目录下，创建一个临时文件。扫描表A所有数据，并存储进临时文件。在生成临时文件过程中，会把对A的操作记录在raw log中。
state3:临时文件生成后，把日志应用一下，让两个文件内容保持一致。
state4:接着改名字。用临时文件替换表A，并把表A的文件删掉。

实战

通过和其他表做对比，我预估200G表回收后剩余40G左右。而当前我的磁盘空间只剩50G左右，非常勉强，如果没有足够的空间，执行就会失败，操作会回滚，生成的临时文件应该也会删掉，但查资料说执行失败不会对原表造成影响。

执行OPTIMIZE TABLE，可以看到生成的临时文件，其实就是表结构frm和表数据ibd文件。idb文件不断增大，磁盘空间开始减少。

执行了大约20分钟，文件大小增大到38G左右时，文件增长的速度感觉开始变慢。等到增到41G时，我想我预估的40G是不是小了，怕把服务器磁盘占满，就准备取消操作。这时候突然提示，执行完成了！

再看看目录下的文件，可以看到原表的frm和idb已经删除了，tmp.frm和idb文件名称已经变成了原表，此时在查看磁盘空间，可用的磁盘空间增加了150G。这就是成功了啦!

总结

可以通过OPTIMIZE TABLE 回收掉没有在使用的磁盘空间碎片，但需要提前预估好供其“倒腾“使用的空间。