【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中，从而导致表的实际大小会增加。