前言

记得在很久之前，我在学习某个关于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官方文档》