MySQL高频面试题:如何给亿级大表快速添加字段?

MySQL大表添加字段，会带来下面几个影响：

• 锁表(MDL锁)
• 主从同步延迟
• 负载较高
• 占用额外空间

由于会锁表，将会影响到正常业务处理，因此在大表加字段时，应该要慎重！！！在了解如何正确给大表加字段之前，咱们先了解了解DDL的算法和实现原理。

MySQL DDL自带的算法有3种，分别时copy，inplace和instant。

• copy算法：copy算法为最古老的算法，在 MySQL 5.5 及以下为默认算法。
• inplace算法：从 MySQL 5.6 开始，引入了 inplace 算法并且默认使用。inplace 算法还包含两种类型：rebuild-table 和 not-rebuild-table。这2个算法的区别在于是否需要rebuild表。MySQL 使用 inplace 算法时，会自动判断，能使用 not-rebuild-table 的情况下会尽量使用，不能的时候才会使用 rebuild-table。当 DDL 涉及到主键和全文索引相关的操作时，无法使用 not-rebuild-table，必须使用 rebuild-table。其他情况下都会使用 not-rebuild-table。
• instant算法：从 MySQL 8.0开始，引入了 instant 算法并且默认使用。目前 instant 算法只支持增加列等少量 DDL 类型的操作，其他类型仍然会默认使用 inplace。

copy算法-实现原理：

新建跟原表格一致的临时表，并在该临时表上执行DDL语句

锁原表，不允许DML，允许查询

逐行数据从原表拷贝到临时表中（这个过程是没有排序的）

拷贝结束后，原表禁止读操作，也就是原表此时不提供读写服务

进行rename操作，完成DDL过程

inplace算法-实现原理：

有3个阶段：prepare、execute、commit。

PREPARE

创建新的临时frm文件

持有EXCLUSIVE_MDL锁，禁止读写

根据alter类型，确定执行方式（copy，rebuild，no-rebuild）

更新数据字典的内存对象

若是需要rebuild，分配row_log对象记录的增量

若是需要rebuild， 生成新的临时ibd文件

EXECUTE

如果是仅修改元数据：

这部分无操作

其他，则是：

降低EXCLUSIVE-MDL锁，允许读写（copy 不允许写）

记录ddl执行过程中产生的增量row-log（仅rebuild类型需要）

扫描old_table的聚集索引每一条记录record

遍历新表的聚集索引和二级索引，逐一处理

根据record构造对应的索引项

将构造索引项插入sort_buffer块

将sort_buffer块插入新的索引

把row-log中的操作应用到新临时表中，应用到最后一个Block

COMMIT

升级到EXECLUSIVE-MDL锁，禁止读写

重做最后一部分的row_log增量

更新innodb的数据字典表

提交事务，写redo日志

修改统计信息

rename 临时的ibd文件、frm文件

DDL完成

instant算法-实现原理：

在增加列时，实际上只是修改了schema，并没有修改原来存储在文件中的行记录，不需要执行最耗时的rebuild和apply row log过程，因此效率非常高。

三方工具(pt-online-schema-change)-实现原理：

首先它会新建一张一模一样的表，表名一般是_new后缀

然后在这个新表执行更改字段操作

然后在原表上加三个触发器，DELETE/UPDATE/INSERT，将原表中要执行的语句也在新表中执行

最后将原表的数据拷贝到新表中，然后替换掉原表

以表格的形式，对比下这3种算法性能：

通过上面的分析，了解了DDL的算法和实现原理，就可以针对大表加字段制定解决方案了。