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

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

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

数据删除流程

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

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

何时复用?

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

删除整页数据

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

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

删除整个表

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

总结

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

增删改-造成空洞

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

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

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

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

重建表-清除空洞

如何清除空洞?

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

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

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

1
alter table A engine=InnoDB;

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

详细流程:

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 来做。

hoxis wechat
一个脱离了高级趣味的程序员,关注回复1024有惊喜~
赞赏一杯咖啡
0%