【MYSQL】delete把我磁盘空间给

【MYSQL】delete把我磁盘空间给"吃"了

Scroll Down

前言

记得在很久之前,我在学习某个关于MYSQL的课程时,需要插入大量的随机数据,约500W,插入之后硬盘空间直接少了约10G(具体大小取决于你插入什么样的数据),到这里位置我都觉得一切正常。可在之后,让我觉得"魔幻"的事情发生了。

我当时想利用这批已插入的数据测测我电脑跑delete语句的速度,然后我就编写代码,用delete语句删除了大概80%的数据。

整个删除过程都很顺利,可当我删完后查看一下磁盘空间,发现磁盘只多了1G,我整个人都懵了,在约10G的数据里我可是删了80%,怎么才还给我1G?难道我装的MYSQL里有个病毒?

虽然当时通过百度的方法勉强让MYSQL"吐"出了4,5G空间,但我还是不明白为啥会这样,直到我前一段时间看到相关书籍有讲解这一块,才终于解开了疑惑。

小贴士:你可能会疑惑,明明删除了80%的数据,但为什么才"吐"出来4G多?那是因为占用磁盘的可不止我们插入的数据,还有我们的插入&删除时候的操作记录,即binlog,而且根据binlog的格式不同,占用的存储空间也会有差异。

PS:无特别说明,环境默认是MYSQL5.7.21+InnoDB+默认配置。


Delete过程的空间去哪了

在这里我同样先无视锁机制、内存变化等因素,因为我们在讨论的主要是:执行Delete过程中,被删除的数据所占有的存储空间到底怎么了?


为了方便,我在这里借用一下掘金小册的图描述过程:

  1. 【一】:删除前

    • delete流程1.png
  2. 【二】:数据被标记为"已删除",但并未真正删除,要为mvcc服务。

    • delete流程2.png
  3. 【三】:数据被purge线程回收,空闲的空间被垃圾链表记录。

    • delete流程3.png

相信你看完上面这三图,应该大致明白删除数据后的空间去哪了。

如果还是不明白,请着重关注【三】的图,数据被删除以后空间其实并没有还给操作系统,而是继续由MYSQL持有,直到特定条件下该空间会被重用或者释放。


当明白了删除数据时,原来的存储空间仍然被MYSQL占有后,你可能会有新的疑问:为什么MYSQL要继续持有这部分空间,而不把这部分空间还给操作系统呢

原因很简单,如果剩余的空间都释放掉,那么MYSQL为了查询时不浪费寻道时间,可能会对现有的某部分数据进行重排序(重新安放),而这个重排序肯定是会影响性能的。所以MYSQL选择不释放原本占用的空间,而是把这些空闲的空间继续持有并记下来,等有机会重用时直接去垃圾链表取出来就完事了。


关于重用空间也有要注意的点:假如现在有一条数据占有存储空间10Mb,然后把这条数据删除,垃圾链表记录了有一个10M的空闲空间。之后如果有一条5Mb大小的记录要插入,而且它正好符合重用空间的条件,那么MYSQL就会把这10Mb的空间全部分配给新记录,不会再剩余10-5Mb


把占有的空间释放

可能会有人问:如果我的硬盘不够用了,我想释放掉MYSQL所占有的空闲空间,那该怎么做?

答案是:alter table A engine=InnoDB

上面这条语句其实就是建立一个新表,然后把现在的表中的数据插入到新表,最后删除旧表,并释放空间。并且在MYSQL的5.6版本引入了"在线"执行alter语句(官名:Online DDL)

所谓的Online DDL,拿上面的重建表语句举例子,其实就是指:在把旧表数据移动至新表时,不会禁止insert/update/delete操作(5.6之前是禁止的,表锁),而是通过一个叫row log的东西记录这些操作,之后重做这些操作。这样,我们就不用为了重建表而关闭线上服务系统,Online DDL名字由此而来。

下面再放两张图对比下Online和非Online:

非Online ddl.png

Online ddl.png

小贴士:上文提到过,在5.6版本之前,因为MDL写锁的存在,无门无法对表进行增/删/改。那5.6版本支持Online DDL,是不是意味着MDL写锁不存在了呢?其实并不是,只是在Online DDL的过程中MDL在读锁和写锁之间来回变,但由于写锁占有的时间很短,所以可以被当成"Online"。


一些有意义的Q/A

  • Q:Truncate 会释放表空间吗?
    A:会,Truncate 可以理解为drop+create。
  • Q:是不是 5.6 之后 所有的 alter 操作(增删字段、增删索引等)都是支持 online ddl?
    A:不是,全文索引(FULLTEXT index)不支持,还有空间索引(SPATIAL index)也不支持。
  • Q:原本数据占有1TB,可是重建表后变1.01TB了,为什么会这样?
    A:列举两种情况:
    • 删除记录锁产生的bin log、redo log等可能比数据本身还大。
    • MYSQL每个页都会预留一部分空间,预防如update语句更新后数据变得更大等情况,此时有预留空间就不用产生页分裂了。所以,有可能是你的表在重建前刚好占有了部分预留空间且数据页中没有"空洞",而重建表后,MYSQL又重新给每个页重新分配了预留空间,那0.01TB很可能就是被用来做预留空间了。
    • 可以参考下面的图:
    • 重建前.png

      重建中、重建后.png

第三问中为什么说可能没有"空洞"?页分裂、页合并,不是都会产生空间碎片吗?是的,所以出现这种情况你极有可能经过了这样的过程:【重建表】-> 【往重建后的表插入/更新数据】(此时为1TB)-> 【再次重建表】(变为1.01TB)。


参考资料:

  • 《MySQL是怎样运行的:从根儿上理解 MySQL》
  • 《MYSQL实战45讲》
  • 《MySQL技术内幕 InnoDB存储引擎 第2版》
  • 《MYSQL5.7官方文档》