经常会有同学来问我,我的数据库占用空间太大,我把一个最大的表删掉了一半的数据,怎么表文件的大小还是没变?
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资源免费下载服务!
网友评论