您的位置:  首页 > 技术杂谈 > 正文

表数据都删了一半,可表文件还是那么大?

2022-03-28 12:00 https://my.oschina.net/u/4526289/blog/5501260 华为云开发者社区 次阅读 条评论
摘要:由于DB占用空间太大,我删除了大表的一半数据,可为啥这表文件的大小没变?数据库表的空间回收到底是怎么做的呢?

本文分享自华为云社区《为什么表数据删掉一半,表文件大小不变?》,作者: JavaEdge。

由于DB占用空间太大,我删除了大表的一半数据,可为啥这表文件的大小没变?

数据库表的空间回收到底是怎么做的呢?

InnoDB表包含:

  • 表结构定义(所占空间小)
  • 表数据(重点)

MySQL版本:

  • <8.0,表结构存在于 .frm 后缀文件里
  • 8.0,允许将表结构定义放在系统数据表。

为何直接删除表数据无法回收表空间?

如何正确回收空间?

1 innodb_file_per_table

表数据

  • 既能存在于共享表空间
  • 也能是单独的文件

该行为由参数innodb_file_per_table决定:

  • OFF,表的数据放在系统共享表空间,和数据字典放一起
  • ON,每个InnoDB表数据存储在一个 .ibd 后缀文件

从MySQL 5.6.6版本开始,默认值为ON。

推荐无论哪个版本,都将该值设为ON:

  • 因为一个表单独存储为一个文件更容易管理,不需要时,直接drop table,系统就会删除该文件
  • 若放在共享表空间中,即使表删掉了,空间也不会回收

因此后续讨论都默认该设置为ON。

删除整个表时,可用drop table回收表空间。但更常见的场景是删除某些行,于是就会发现:表中的数据被删除了,但表空间没有被回收!

2 数据删除流程

InnoDB索引示意图

假设,我们要删掉D4,InnoDB引擎只会把D4这个记录标记为删除。若之后要再插入一个ID在300、600之间的记录时,可能会复用该位置。但磁盘文件并不会缩小。

3 InnoDB的数据按页存储,若删掉一个数据页上的所有记录,会咋样?

整个数据页就能被复用了。

但是,数据页的复用跟记录的复用不同:

  • 记录的复用,只限于符合范围条件的数据。比如D4记录被删除后,若插入一个ID=400的行,可直接复用该空间。但若插入ID=800,就不能复用该位置
  • 而当整个页从B+树里摘掉后,可复用到任何位置

若将数据页pageA上的所有记录删除后,pageA会被标记为可复用。这时若插入一条ID=20的记录,需要使用新页时,pageA就能被复用。

若相邻两个数据页利用率都很小,系统就会把这俩页上的数据合到其中一个页上,另外一个数据页就被标记为【可复用】。

4 若用delete命令删除整个表的数据呢?

所有的数据页都会被标记为【可复用】,但磁盘上的文件不会变小。

delete命令其只是将记录的位置或数据页标记为“可复用”,但磁盘文件的大小不会变。即通过delete命令不能回收表空间。这些可以复用但实际没有被使用的空间,看起来就像“空洞”。

不仅删除数据会造成空洞,插入数据也会。

5 插入数据导致的“空洞”

若数据按索引递增顺序插入,则索引是紧凑的。但若数据是随机插入的,就可能造成索引的数据页分裂。

假设pageA已满,此时再插入一行数据,会怎样呢?

插入数据导致页分裂

由于pageA满,再插入ID=550时,就得再申请一个新页面pageB保存数据。页分裂完成后,pageA末尾就留下空洞(实际可能不止1个记录的位置是空洞)。

6 更新索引上的值会导致空洞吗?

更新可理解为删除一个旧值,再插入新值。所以也会造成空洞。

综上,经过大量增删改的表,都可能存在空洞。若能去掉这些空洞,就能达到收缩表空间的目的。重建表,就能达到目的。

7 重建表

若现在有一表A,要做空间收缩,为了去掉表中存在的空洞,可新建一个与表A结构相同的表B,然后按主键ID递增顺序,把数据一行行从表A里读出,再插入表B。

因为表B是新建表,所以表A主键索引上的空洞,在表B都不存在。显然表B的主键索引更紧凑,数据页的利用率更高。若将表B作为临时表,数据从表A导入表B的操作完成后,用表B替换A,就达到收缩表A空间的效果。

可用

alter table A engine=InnoDB

重建表。在MySQL 5.5前,这命令的执行流程跟我们前面描述的差不多,区别只是这个临时表B不需要自己创建,MySQL会自动完成转存数据、交换表名、删除旧表的操作。

改锁表DDL:

往临时表插入数据的过程最耗时,若在此过程中,有新数据要写入到表A,就会造成数据丢失。因此,整个DDL过程中,表A不能有更新,即这DDL不是Online的。

MySQL 5.6版本引入Online DDL,优化了该操作流程。

引入Online DDL后,重建表的流程

  • 建立一个临时文件,扫描表A主键的所有数据页
  • 用数据页中表A的记录生成B+树,存储到临时文件
  • 生成临时文件的过程中,将所有对A的操作记录在一个日志文件(row log),对应图中state2
  • 临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表A相同的数据文件,对应state3

用临时文件替换表A的数据文件。

Online DDL:

和上图不同在于,由于日志文件记录和重放操作的存在,该方案在重建表的过程中,允许对表A做增删改,这就是Online DDL名字来源。

DDL之前还要拿MDL写锁的,这也能叫Online DDL?

确实,图Online DDL流程中,alter语句在启动的时候需获取MDL写锁,但该写锁在真正拷贝数据前就退化成读锁了。

为什么要退化?

为了实现Online,MDL读锁不会阻塞增删改操作。

为何不直接解锁?

为了保护自己,禁止其他线程对该表同时做DDL。对一个大表,Online DDL最耗时过程就是拷贝数据到临时表时,这个步骤的执行期间可接受增删改操作。所以,相对于整个DDL过程,锁的时间非常短。对业务来说,就可认为是Online的。

上述这些重建方法都会扫描原表数据、构建临时文件。对于大表,这很消耗IO和CPU。因此,若是线上服务,要谨慎控制操作时间。若想要较安全的操作,推荐使用GitHub开源的gh-ost。

Online 和 inplace

图改锁表DDL中,把表A中的数据导出来的存放位置叫作tmp_table。这是个临时表,创建在server层。

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

若有一个1TB的表,磁盘空间1.2TB,能做个inplace的DDL吗?

不能。因为,tmp_file也是要占用临时空间的。重建表的这个语句alter table t engine=InnoDB,其隐含意思:

alter table t engine=innodb,ALGORITHM=inplace;

跟inplace对应的就是拷贝表的方式了,用法是:

alter table t engine=innodb,ALGORITHM=copy;

当你使用ALGORITHM=copy的时候,表示的是强制拷贝表,对应的流程就是图3的操作过程。

inplace跟Online是不是就一个意思?

不是的,只是在重建表这个逻辑中刚好是这样。

若给InnoDB表的一个字段加全文索引:

alter table t add FULLTEXT(field_name);

这个过程是inplace的,但会阻塞增删改操作,非Online。

如果说这两个逻辑之间的关系是什么的话,可以概括为:

  • DDL过程如果是Online的,就一定是inplace的
  • 反过来未必,即inplace的DDL,有可能不是Online的。截止到MySQL 8.0,添加全文索引(FULLTEXT index)和空间索引(SPATIAL index)就属于这种情况。

使用optimize table、analyze table和alter table这三种方式重建表

从MySQL 5.6版本开始

  • alter table t engine = InnoDB(也就是recreate)默认的就是上面图Online DDL的流程了
  • analyze table t 其实不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程中加了MDL读锁
  • optimize table t =recreate+analyze

 

点击关注,第一时间了解华为云新鲜技术~

展开阅读全文
  • 0
    感动
  • 0
    路过
  • 0
    高兴
  • 0
    难过
  • 0
    搞笑
  • 0
    无聊
  • 0
    愤怒
  • 0
    同情
热度排行
友情链接