搜索
写经验 领红包

mysql删除数据表空间不变原因是什么(mysql删除数据表空间不变原因有哪些)

导语:MySQL删除数据表空间不变原因

作为一名资深的CV 战士,我们可能很少去关注 MySQL的数据文件存储在哪里。当我们执行一批删除操作的时候,也不会去观察数据库文件的大小到底有没有变化。下面我们一起来看一下 MySQL 数据存储相关的原理。

在MySQL 8.0 之前,表结构定义是存储在 .frm 为后缀的文件里,如下图所示。因为表结构定义占用空间比较小,下面主要介绍一下 表数据的存储相关的东西。

innodb_file_per_table

表数据可以存储在共享表空间里(ibdata1、ibdata2......),也可以单独存储,这个是有innodb_file_per_table 参数决定的。innodb_file_per_table 设置为 OFF 时,表示表的数据存储在 共享表空间,也就是和数据字典存放在一起;当设置为ON 时,每个 InnoDB 的表数据都会存储在一个 .ibd 的文件中。(从 MySQL 5.6.6 开始默认为 ON)

innodb_file_per_table 建议设置为 ON,也就是每个表数据单独存储。当我们使用 drop 删除表时,就可以回收磁盘空间了。否则磁盘空间还不一定会被回收。

数据删除流程

上面是主键索引树的结构,比如当我们要删除 id = 500 的记录。此时500 位置上的空间就会空出来,磁盘的空间占用并不会减小。MySQL是按页存储的,当我们把整个 PageA 的数据都删除掉时。此时磁盘的空间也不会减小,而仅仅是PageA 是可以复用。当然,我们使用 delete 命令把所有数据都删除掉之后,其磁盘空间也不会减小的。也是只之前所有的数据页都是可复用而已。

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

如果所插入的数据是按照索引递增的插入的,那么索引就是紧凑的。如果是随机写入的,就有可能会造成索引的数据页分裂。

从上面的结果可以看出,当新写入一个 550 时,由于 PageA 满了,页分裂出来了 PageB ,也造成了数据页的空洞。而数据页的空洞可以通过 重建表的方式来达到去除空洞的目的。

重建表

我们可以使用 alter table A engine=InnoDB; 来重建表,其过程如下图所示:

从上图可以看出,首先MySQL 会创建出一个 tmp 临时表,然后将数据按照主键自增的方式插入,最后将 tmp 表覆盖 A 表。在MySQL 5.5 及以前,上面的重建表是 Offline的,也就是重建的过程中不对外提供服务。

在MySQL 5.6 版本开始引入 Online DDL ,也就是重建表的过程中也可以对外提供服务,如下图所示:

从上图可以看出,Online 的重建表与 Offline 的不同在于:如果在重建的过程中有表的更改,其会存储在 row log 当中,当重建完成后,再执行 row log 中的更改。

Online 和 inplace

上面重建表的第一张图,把表 A 导出来的数据是存放在 tmp 表中的,这个 tmp 表是在 Server 层创建的。而第二张图中 Online 操作,是 tmp_file 这个临时文件是在 InnoDB 内部创建的。对于 Server 来说,它是一个原地的操作,这就是 inplace 的由来。比如磁盘只有1.2TB,此时你需要对一张 1TB 大小的 table 做 inplace 操作,此时磁盘是不够用的。

默认的情况下,我们使用 alter table t engine=InnoDB; 其实就等于 alter table t engine=InnoDB, ALGORITHM=inplace; 。那么于此对应的就是 alter table t engine=InnoDB, ALGORITHM=copy; ,此时是强拷贝,就对应的是 第一张图中的过程。

从 MySQL 5.6 开始,alter table t engine=InnoDB 就是 Online的步骤(recreate过程);analyze table t 其实不是重建表,只是对索引的信息做重新统计,没有修改数据,整个过程中加了 MDL 写锁;optimize table t 相当于 recreate + analyze 的过程。

参考:《极客时间:MySQL实战》、《高性能MySQL》

本文内容由小德整理编辑!