问题
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 回收掉没有在使用的磁盘空间碎片,但需要提前预估好供其“倒腾“使用的空间。