美文网首页
MySQL实战 | 13 为什么表数据删掉一半,表文件大小不变?

MySQL实战 | 13 为什么表数据删掉一半,表文件大小不变?

作者: hoxis | 来源:发表于2019-05-14 03:17 被阅读0次

    经常会有同学来问我,我的数据库占用空间太大,我把一个最大的表删掉了一半的数据,怎么表文件的大小还是没变?

    InnoDB 表的组成:表结构定义和数据。

    数据删除流程

    InnoDB 中,数据是以 B+ 树结构来存储数据的:

    image

    假设删掉表记录 R4,此时 InnoDB 只是把 R4 标记为已删除状态,后续这个位置可以插入新的数据,但是磁盘文件大小并不会变化。

    何时复用?

    比如,插入一个 ID 是 400 的记录,就可以直接复用原来 R4 的空间,若插入的是 800,为了保持 B+ 树的结构,就不能复用该空间了。

    删除整页数据

    当删除了整页数据后,InnoDB 会将该页标记为已删除,整页都可复用。

    同时,若两个相邻的数据页利用率都比较低,系统会把两页上的数据整合到一个页中,另一个页就会标记为可复用。

    删除整个表

    此时,所有的数据页都会标记为可复用,但是磁盘空间仍然不会变小。

    总结

    delete 操作,只是把记录的位置标记为「可复用」,但是磁盘大小不会变化,这些可以复用,而未被使用的空间,看起来就像空洞

    增删改-造成空洞

    当数据是随机插入时,就可能造成索引的数据页分裂。

    image

    如图所示,由于 page A 写满,此时插入 ID 为 550 的数据,就不得不申请新的数据页,页分裂完成后,A 上就会留下空洞。

    另外,更新索引上的值,其实是删除旧值,插入新值,这个过程同样会造成空洞。

    综上,经过大量增删改操作的表,都是可能存在大量空洞的,若要收缩表空间,就要清除这些空洞。

    重建表-清除空洞

    如何清除空洞?

    直接的想法就是,新建一个表结构相同的表,然后按主键 ID 递增的顺序,将原表中的数据,插入到新表。

    这样,新表中就不会存在空洞了。新表的主键索引也会更加紧凑,数据页的利用率也更高。

    上面的操作可以通过下面的语句自动完成:

    alter table A engine=InnoDB;
    

    MySQL 会自动完成转存数据、交换表名、删除旧表的操作。

    image

    详细流程:

    1、新建临时文件,扫描原表 A 的所有数据页;
    2、根据表 A 的记录生成 B+ 树,存储到临时文件中;
    3、生成临时文件过程中,对 A 的所有操作都会记录在一个日志文件中,对应图中的 state2 状态;
    4、临时文件生成后,将日志文件中的操作应用于临时文件,得到一个完整的数据文件,对于 state3;
    5、用临时文件替换表 A 的数据文件;

    表重建的过程是允许对表 A 做增删改操作的,因此是一个 Online DDL(MySQL5.6+)

    另外,根据表 A 重建出来的数据是放在“tmp_file”里的,这个临时文件是 InnoDB 在内部创建出来的。整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说,没有把数据挪动到临时表,是一个“原地”操作,这就是“inplace”名称的来源。

    上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说,这个操作是很消耗 IO 和 CPU 资源的。

    因此,如果是线上服务,你要很小心地控制操作时间。如果想要比较安全的操作的话,我推荐你使用 GitHub 开源的 gh-ost 来做。


    你的关注是对我最大的鼓励!

    关注本公众号,后台回复「2018」即可获取传智播客 2018 最新 Python 和 Java 教程。

    公众号提供CSDN资源免费下载服务!


    相关文章

      网友评论

          本文标题:MySQL实战 | 13 为什么表数据删掉一半,表文件大小不变?

          本文链接:https://www.haomeiwen.com/subject/wnxjaqtx.html