ITPub博客

首页 > 数据库 > MySQL > 学习记录:MySQL碎片化的原因及解决方案?

学习记录:MySQL碎片化的原因及解决方案?

原创 MySQL 作者:myis55555 时间:2021-01-15 21:48:53 0 删除 编辑

底层知识

INNODB表的存储

一个 InnoDB 表包含两部分,即:表结构定义和数据。在 MySQL 8.0 版本以前,表结构是存在以 .frm 为后缀的文件里。而

MySQL 8.0 版本,则已经允许把表结构定义放在系统数据表中了。

表数据既可以存在共享表空间里,也可以是单独的文件。这个行为是由参数innodb_file_per_table 控制的:

1. 这个参数设置为 OFF 表示的是,表的数据放在系统共享表空间,也就是跟数据字典放在一起;

2. 这个参数设置为 ON 表示的是,每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

不需要这个表的时候,通过 drop table 命令,系统就会直接删除这个文件。

从 MySQL 5.6.6 版本开始,它的默认值就是 ON 。

数据处理流程

InnoDB的数据都是用B+树的结构组织的。

删除一条记录,InnoDB引擎只会把记录标记为删除,之后如果插入数据是按照索引排序刚好落在这个区间内,可能会复用这个位置,但是磁盘文件的大小不会缩小。

InnoDB的数据是按页存储的,如果删掉一个数据页上的所有记录,整个数据页会被复用。而记录的复用是需要满足范围条件的,只有在范围条件内的数据才会被复用。

如果相邻的两个数据页利用率很小,系统会把两个页上的数据合并到其中一个页上,另外的一个数据页就被标记为可复用。

其实不止删除数据会造成空洞,插入数据也会。

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

更新索引上的值,可以理解为删除一个旧的值,再插入一个新值。也会造出空洞!

经过大量增删改的表,都是可能是存在空洞的。所以,如果能够把这些空洞去掉,就能达到收缩表空间的目的。

碎片产生的原因

(1)表的存储会出现碎片化,每当删除了一行内容,该段空间就会变为空白、被留空,而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大;

(2)当执行插入操作时,MySQL会尝试使用空白空间,但如果某个空白空间一直没有被大小合适的数据占用,仍然无法将其彻底占用,就形成了碎片;

(3)当MySQL对数据进行扫描时,它扫描的对象实际是列表的容量需求上限,也就是数据被写入的区域中处于峰值位置的部分;

重建表的方式处理碎片化

通过alter table A engine=InnoDB 命令来重建表。在 MySQL 5.5 版本之前,这个命令会自动完成转存数据、交换表名、删除旧表的操作。

在往临时表插入数据的过程中,有新数据写入到A表,会造成数据丢失。这个操作不是Online的。

MySQL 5.6 版本开始引入的 Online DDL,重建表流程。

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

1.  建立一个临时文件,扫描表 A 主键的所有数据页;

2.  用数据页中表 A 的记录生成 B+ 树,存储到临时文件中;

3.  生成临时文件的过程中,将所有对 A 的操作记录在一个日志文件( row log )中,对应的是图中 state2 的状态;

4.  临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表 A 相同的数据文件,对应的就是图中 state3 的状态;

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

在流程中,alter 语句在启动的时候需要获取 MDL 写锁,但是这个写锁 为了实现 Online,MDL 读锁不会阻塞增删改操作。在真正拷贝数据之前就退化成读锁了。

那为什么不干脆直接解锁呢?为了保护自己,禁止其他线程对这个表同时做 DDL 。

而对于一个大表来说, Online DDL 最耗时的过程就是拷贝数据到临时表的过程,这个步骤的执行期间可以接受增删改操作。所以,相对于整个 DDL 过程来说,锁的时间非常短。对业务来说,就可以认为是 Online 的。

需要补充说明的是,上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说,这个操作是很消耗 IO 和 CPU 资源的。因此,如果是线上服务,你要很小心地控制操作时间。如果想要比较安全的操作的话,我推荐你使用 GitHub 开源的 gh-ost 来做。


参考文章:丁奇,MySQL45讲,13.为什么表数据删掉一半,表文件大小不变?


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69903557/viewspace-2749819/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-12-22

  • 博文量
    17
  • 访问量
    6578