对于运行很长时间的数据库来说,往往会出现表占用存储空间过大的问题,可是将许多没用的表删除之后,表文件的大小并没有改变,想解决这个问题,就需要了解 InnoDB 如何回收表空间的。
对于一张表来说,占用空间重要分为两部分,表结构和表数据。通常来说,表结构定义占用的空间很小。所以空间的问题主要和表数据有关。
在 MySQL 8.0 前,表结构存储在以 .frm 为后缀的文件里。在 8.0,允许将表结构定义在系统数据表中。
关于表数据的存放
可以将表数据存在共享表空间,或者单独的文件中,通过 innodb_file_per_table
来控制。
- 如果为 OFF ,表示存在系统共享表空间中,和数据字典一起
- 如果为 ON,每个 InnoDB 表结构存储在 .idb 为后缀的文件中
在 5.6.6 以后,默认值为 ON.
建议将该参数设置为 ON,这样在不需要时,通过 drop table 命令,系统就会直接删除该文件。
但在共享表空间中,即使表删掉,空间也不会回收。
truncate = drop + create
数据删除流程
但有时使用 delete
删除数据时,仅仅删除的是某些行,但这可能就会出现表空间没有被回收的情况。
我们知道,MySQL InnoDB 中采用了 B+ 树作为存储数据的结构,也就是常说的索引组织表,并且数据时按照页来存储的。
在删除数据时,会有两种情况:
- 删除数据页中的某些记录
- 删除整个数据页的内容
比如想要删除 R4 这条记录:
InnoDB 直接将 R4 这条记录标记为删除,称为可复用的位置。如果之后要插入 ID 在 300 到 700 间的记录时,就会复用该位置。由此可见,磁盘文件的大小并不会减少。
而且记录的复用,只限于符合范围条件的数据。之后要插入 ID 为 800 的记录,R4 的位置就不能被复用了。
再比如要是删除了整个数据页的内容,假设删除 R3 R4 R5,为 Page A 数据页。
这时 InnoDB 就会将整个 Page A 标记为删除状态,之后整个数据都可以被复用,没有范围的限制。比如要插入 ID=50 的内容就可以直接复用。
并且如果两个相邻的数据页利用率都很小,就会把两个页中的数据合到其中一个页上,另一个页标记为可复用。
综上,无论是数据行的删除还是数据页的删除,都是将其标记为删除的状态,用于复用,所以文件并不会减小。对应到具体的操作就是使用 delete 命令.
而且,我们还可以发现,对于第一种删除记录的情况,由于复用时会有范围的限制,所以就会出现很多空隙的情况,比如删除 R4,插入的却是 ID=800.
插入操作也会造成空隙
在插入数据时,如果数据按照索引递增顺序插入,索引的结构会是紧凑的。但如果是随机插入的,很可能造成索引数据页分裂。
比如给已满的 Page A 插入数据。
由于 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 会自己完成数据,交换表名,删除旧表的操作。
但这就有一个问题,在 DDL 中,表 A 不能有更新,此时有数据写入表 A 的话,就会造成数据丢失。
在 5.6 版本后引入了 Online DDL。
Online DDL
Online DDL 在其基础上做了如下的更新:
重建表的过程如下:
- 建立一个临时文件,扫描表 A 主键的所有数据页。
- 用生成的数据页生成 B+ 树,存储到临时文件中。
- 生成临时文件时,如果有对 A 的操作,将其记录在日志文件中,对应图中 state 2 的状态。
- 临时文件生成后,将日志文件应用到临时文件中,得到与 A 表相同的数据文件,对应 state 3 状态。
- 用临时文件替换 A 表的数据文件。
由于 row log 日志文件存在,可以在重建表示,对表 A 进行 DML 操作。
需要注意的是,在 alter 语句执行前,会先申请 MDL 写锁,但在拷贝数据前会退化成 MDL 读锁,从而支持 DML 操作。
至于为什么不大 MDL 去掉,是防止其他线程对这个表同时做 DDL 操作。
对于大表来说,该操作很耗 IO 和 CPU 资源,所以在线上操作时,要控制操作时间。如果为了保证安全,推荐使用 gh-ost 来迁移。
Online 和 inplace
首先说一下 inplace 和 copy 的区别:
在 Online DDL 中,表 A 重建后的数据放在 tmp_file 中,这个临时文件是在 InnoDB 内部创建出来的。整个 DDL 在 InnoDB 内部完成。进而对于 Server 层来说,并没有数据移动到临时表中,是一个 "原地" 操作,所以叫 "inplace" .
而在之前普通的 DDL 中,创建后的表 A 是在 tmp_table 是 Server 创建的,所以叫 "copy"
对应到语句其实就是:
-- alter table t engine=InnoDB 默认为下面 alter table t engine=innodb,ALGORITHM=inplace; -- 走的就是 server 拷贝的过程 alter table t engine=innodb,ALGORITHM=copy;
需要注意的是 inplace 和 Online 并不是对应关系:
- DDL 过程是 Online,则一定是 inplace
- 如果是 inplace 的 DDL 不应当是 Online,如在 <= 8.0, 添加全文索引和空间索引就属于这种情况。
拓展
说一下 optimize,analyze,alter table 三种重建表之间的区别:
- alter table t engine = InnoDB(也就是 recreate)默认的是 Oline DDL 过程。
- analyze table t 不是重建表,仅仅是对表的索引信息做重新统计,没有修改数据,期间加 MDL 读锁。
- optimize table t 等于上两步的操作。
在事务里面使用 alter table 默认会自动提交事务,保持事务一致性
如果有时,在重建某张表后,空间不仅没有变小,甚至还变大了一点点。这时因为,重建的这张表本身没有空隙,在 DDL 期间,刚好有一些 DML 执行,引入了一些新的空隙。
而且 InnoDB 不会把整张表填满,每个页留下 1/16 给后续的更新用,所以可能远离是紧凑的,但重建后变成的稍有空隙。
总结
现在我们知道,在使用 delete 删除数据时,其实对应的数据行并不是真正的删除,InnoDB 仅仅是将其标记成可复用的状态,所以表空间不会变小。
通常来说,在标记复用空间时分为两种,一种是仅将某些数据页中的位置标记为删除状态,但这样的位置只会在一定范围内使用,会出现空隙的情况。
另一种是将整个数据页标记成可复用的状态,这样的数据页没有限制,可直接复用。
为了解决这个问题,我们可以采用重建表的方式,其中在 5.6 版本后,创建表已经支持 Online 的操作,但最后是在业务低峰时使用
以上就是MySQL删除数据,表文件大小依然没变的原因的详细内容,更多关于MySQL表文件大小的资料请关注其它相关文章!
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
更新日志
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]