MySQL删除数据,表文件大小依然没变的原因

对于运行很长时间的数据库来说,往往会出现表占用存储空间过大的问题,可是将许多没用的表删除之后,表文件的大小并没有改变,想解决这个问题,就需要了解 InnoDB 如何回收表空间的。

对于一张表来说,占用空间重要分为两部分,表结构和表数据。通常来说,表结构定义占用的空间很小。所以空间的问题主要和表数据有关。

在 MySQL 8.0 前,表结构存储在以 .frm 为后缀的文件里。在 8.0,允许将表结构定义在系统数据表中。

关于表数据的存放

可以将表数据存在共享表空间,或者单独的文件中,通过 innodb_file_per_table 来控制。

  • 如果为 OFF ,表示存在系统共享表空间中,和数据字典一起
  • 如果为> truncate = drop + create

    数据删除流程

    但有时使用 delete删除数据时,仅仅删除的是某些行,但这可能就会出现表空间没有被回收的情况。

    我们知道,MySQL InnoDB 中采用了 B+ 树作为存储数据的结构,也就是常说的索引组织表,并且数据时按照页来存储的。

    在删除数据时,会有两种情况:

    • 删除数据页中的某些记录
    • 删除整个数据页的内容

    比如想要删除 R4 这条记录:

    MySQL删除数据,表文件大小依然没变的原因

    InnoDB 直接将 R4 这条记录标记为删除,称为可复用的位置。如果之后要插入 ID 在 300 到 700 间的记录时,就会复用该位置。由此可见,磁盘文件的大小并不会减少。

    而且记录的复用,只限于符合范围条件的数据。之后要插入 ID 为 800 的记录,R4 的位置就不能被复用了。

    再比如要是删除了整个数据页的内容,假设删除 R3 R4 R5,为 Page A 数据页。

    这时 InnoDB 就会将整个 Page A 标记为删除状态,之后整个数据都可以被复用,没有范围的限制。比如要插入 ID=50 的内容就可以直接复用。

    并且如果两个相邻的数据页利用率都很小,就会把两个页中的数据合到其中一个页上,另一个页标记为可复用。

    综上,无论是数据行的删除还是数据页的删除,都是将其标记为删除的状态,用于复用,所以文件并不会减小。对应到具体的操作就是使用 delete 命令.

    而且,我们还可以发现,对于第一种删除记录的情况,由于复用时会有范围的限制,所以就会出现很多空隙的情况,比如删除 R4,插入的却是 ID=800.

    插入操作也会造成空隙

    在插入数据时,如果数据按照索引递增顺序插入,索引的结构会是紧凑的。但如果是随机插入的,很可能造成索引数据页分裂。

    比如给已满的 Page A 插入数据。

    MySQL删除数据,表文件大小依然没变的原因

    由于 Page A 满了,所以要申请 Page B,调整 Page A 的过程到 Page B,这也称为页分裂。

    结束后 Page A 就有了空隙。

    另外对于更新操作也是,先删除再插入,也会造成空隙。

    进而对于大量进行增删改的表,都有可能存在空洞。如果把空洞去掉,自然空间就被释放了。

    使用重建表

    为了把表中的空隙去掉,这时就可以采用重新建一个与表 A 结构相同的表 B,然后按照主键 ID 递增的顺序,把数据依次插入到 B 表中。

    由于是顺序插入,自然 B 表的空隙不存在,数据页的利用率也更高。之后用表 B 代替表 A,好像起到了收缩表 A 空间的作用。

    具体通过:

    alter table A engine=InnoDB

    在 5.5 版本后,该命令和上面提到的流程差不多,而且 MySQL 会自己完成数据,交换表名,删除旧表的操作。

    MySQL删除数据,表文件大小依然没变的原因

    但这就有一个问题,在 DDL 中,表 A 不能有更新,此时有数据写入表 A 的话,就会造成数据丢失。

    在 5.6 版本后引入了>MySQL删除数据,表文件大小依然没变的原因

    重建表的过程如下:

    1. 建立一个临时文件,扫描表 A 主键的所有数据页。
    2. 用生成的数据页生成 B+ 树,存储到临时文件中。

      MySQL删除数据,表文件大小依然没变的原因

扫一扫手机访问