通过 alter table 来实现重建表，同事大呼开眼界了

hello，大家好，我是张张，「架构精进之路」公号作者。

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

希望今天的讲解对大家有所帮助，谢谢！

Thanks for reading!

作者：架构精进之路，十年研发风雨路，大厂架构师，CSDN 博客专家，专注架构技术沉淀学习及分享，职业与认知升级，坚持分享接地气儿的干货文章，期待与你一起成长。

关注并私信我回复“01”，送你一份程序员成长进阶大礼包，欢迎勾搭。