【MySQL】alter table TableName engine=InnoDB 完成表重建

通过alter table 来实现重建表 原文地址:https://zhuanlan.zhihu.com/p/610997918
mysql基础架构执行原理原文地址:https://blog.csdn.net/Kong_a/article/details/119775660
MDL锁介绍 原文地址:https://blog.csdn.net/weixin_43189971/article/details/126436023

 

1、应用背景

在日常工作开发中,在MySQL中,如果我们对大表频繁进行insert和delete操作,那么时间一长,这个表中会出现很多"空洞",也就是表碎片。

碎片产生的原因是insert随机值作为主键id,会产生很多数据页分裂操作;而delete掉一些排列有序的主键值,这些被delete的空间不会直接释放,而是仅仅进行delete的标记,这些空间如果不能被利用,那就会变成"空洞"。

2、重建表

关于重建表,这时候新建一张结构一样的临时表,把表内的数据导入到临时表,直接删除旧表,然后将临时表替换为旧表,从而释放这些空余的空间,让数据变得"紧凑些",完成重建操作。

 

我们其实可以通过如下命令来重建表:

alter table tableName engine=innodb

在MySQL5.5版本之前,这个命令的执行流程跟1操作差不多,区别只是在于这个临时表不需要你直接创建,MySQL会自动完成转存数据、交换表名、删除旧表的操作。

这个重建表的过程,在MySQL5.5之前,它的执行逻辑是下面这样的:

1、假设原表是A,新建一个表table B,和表A的表结构保持一致;
2、按照主键顺序,将表A的数据一行一行的读出来,插入到表B里面;
3、交换表A和表B的名称。

3、重建实现优化

通过上面的介绍可以发现,花时间最多的步骤是往临时表插入数据的过程,如果在这个过程中,有新的数据要写入到表 A 的话,就会造成数据丢失。

因此,在整个 DDL 过程中,旧表中不能有更新(也就是说,这个 DDL 不是 Online 的)。

在MySQL5.6及以后的版本里面,引入了Online DDL的方法,Online DDL的引入,使得上面的过程有了一点点不同,当执行如下命令的时候,

alter table tableName engine=innodb

MySQL5.6版本开始引入的Online DDL,对这个操作流程做了优化:

1、建立一个临时文件,扫描表A主键的所有数据页;
2、用数据页中表A的记录生产B+树,存储到临时文件中;
3、生产临时文件的过程中,将所有对A的操作记录在一个日志文件(row log)中,对应的是图中state2的状态;
4、临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表A相同的数据文件,对应的就是图中state3的状态;
5、用临时文件替换表A的数据文件。

 

 

执行alter语句时,需要获取MDL写锁,但是这个写锁在真正拷贝数据之前就退化成读锁。为了实现Online,MDL读锁不会阻塞增删改操作。
那为什么要从写锁退化成读锁而不干脆直接解除锁呢?为了保护自己,禁止其他线程对这个表同时做DDL。

4、答疑解惑

关于重建表,相信大家还会有其他的疑惑,一起来总结下。

Q1、在MySQL5.5之前,我们使用临时表作为重建的中间介质,在MySQL5.6之后,我们使用临时文件作为重建的中间介质,临时表和临时文件的区别是?

A:临时表是创建在server层面的,临时文件是创建在innodb层面的,所以Online DDL的整个过程都是在Innodb内部完成的,这种方法也称之为"inplace",相对应的,需要借助server层面临时表的过程,称之为"Copy"。

Q2、假设我们有一个1TB的表,磁盘只有1.2TB,那么还可以做inplace的DDL呢?

A:不可以,因为inplace方案中的临时文件也要占用一定的空间。

Q3、inplace 方案进行的表重建操作,都是Online DDL么?

A:不一定,例如增加全文索引的操作,这个操作是inplace的,但是会阻塞增删改查操作,因此不是Online DDL。应该说:Online DDL一定是inplace的,但是inplace方案进行的操作,不一定是Online的。

Q4、某个表的大小是1TB,进行alter table A engine=Innodb之后,表的空间没有缩小,反而增大了一点,这是为什么?

A:可能是因为表之前刚刚进行过一次alter table的操作,而且表上面的并发增删改比较多,在进行alter table 的过程中,这些操作都写进了log中,从而导致表的实际大小会增加。

 

 

posted @ 2023-10-12 10:18  Angel挤一挤  阅读(179)  评论(0编辑  收藏  举报