今天主要介绍一下MySQL 8.0.19 instant add column的新特性，基于亿级数据秒速增加字段，下面一起来看看吧～

MySQL DDL 的方法

MySQL 在大型表上的 DDL 会带来耗时较久、负载较高、额外空间占用、MDL、主从同步延时等情况。需要特别引起重视，而MySQL 的 DDL 有很多种方法。

MySQL 本身自带三种方法，分别是：copy、inplace、instant。

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

有一些第三方工具也可以实现 DDL 操作，最常见的是 percona 的 pt-online-schema-change 工具（简称为 pt-osc），和 github 的 gh-ost 工具，均支持 MySQL 5.5 以上的版本。

各类工具的对比

一般情况下的建议：

如果使用的是 MySQL 5.5 或者 MySQL 5.6，推荐使用 gh-ost
如果使用的是 MySQL 5.7，索引等不涉及修改数据的操作，建议使用默认的 inplace 算法。如果涉及到修改数据（例如增加列），不关心主从同步延时的情况下使用默认的 inplace 算法，关心主从同步延时的情况下使用 gh-ost
如果使用的是 MySQL 8.0，推荐使用 MySQL 默认的算法设置，在语句不支持 instant 算法并且在意主从同步延时的情况下使用 gh-ost

MySQL DDL 的原理简析

（1）copy算法

较简单的实现方法，MySQL 会建立一个新的临时表，把源表的所有数据写入到临时表，在此期间无法对源表进行数据写入。MySQL 在完成临时表的写入之后，用临时表替换掉源表。这个算法主要被早期（<=5.5）版本所使用。

（2）inplace 算法

从 5.6 开始，常用的 DDL 都默认使用这个算法。inplace 算法包含两类：inplace-no-rebuild 和 inplace-rebuild，两者的主要差异在于是否需要重建源表。

inplace 算法的操作阶段主要分为三个：

Prepare阶段： - 创建新的临时 frm 文件(与 InnoDB 无关)。 - 持有 EXCLUSIVE-MDL 锁，禁止读写。 - 根据 alter 类型，确定执行方式（copy，online-rebuild，online-not-rebuild）。更新数据字典的内存对象。 - 分配 row_log 对象记录数据变更的增量（仅 rebuild 类型需要）。 - 生成新的临时ibd文件 new_table（仅rebuild类型需要）。
Execute 阶段：降级EXCLUSIVE-MDL锁，允许读写。扫描old_table聚集索引（主键）中的每一条记录 rec。遍历new_table的聚集索引和二级索引，逐一处理。根据 rec 构造对应的索引项。将构造索引项插入 sort_buffer 块排序。将 sort_buffer 块更新到 new_table 的索引上。记录 online-ddl 执行过程中产生的增量（仅 rebuild 类型需要）。重放 row_log 中的操作到 new_table 的索引上（not-rebuild 数据是在原表上更新）。重放 row_log 中的DML操作到 new_table 的数据行上。
Commit阶段：当前 Block 为 row_log 最后一个时，禁止读写，升级到 EXCLUSIVE-MDL 锁。重做 row_log 中最后一部分增量。更新 innodb 的数据字典表。提交事务（刷事务的 redo 日志）。修改统计信息。rename 临时 ibd 文件，frm文件。变更完成，释放 EXCLUSIVE-MDL 锁。

（3）instant 算法

MySQL 8.0.12 才提出的新算法，目前只支持添加列等少量操作，利用 8.0 新的表结构设计，可以直接修改表的 metadata 数据，省掉了 rebuild 的过程，极大的缩短了 DDL 语句的执行时间。

（4）pt-online-schema-change

借鉴了 copy 算法的思路，由外部工具来完成临时表的建立，数据同步，用临时表替换源表这三个步骤。其中数据同步是利用 MySQL 的触发器来实现的，会少量影响到线上业务的 QPS 及 SQL 响应时间。

MySQL 8.0特性instant add column

MySQL 数据库针对亿级别的大表加字段是痛苦的，需要对表进行重建，MySQL 5.7 支持 Online DDL，大部分 DDL 不影响对表的读取和写入，但是依然会消耗非常多的时间，且占用额外的磁盘空间，并会造成主从延迟。所以大表 DDL 仍是一件令 DBA 头痛的事。而mysql8.0使用instant ADD COLUMN特性，只需很短的时间，字段就加好了，享受MongoDB那样的非结构化存储的灵活方便,无形中减少了开发的工作量。

快速加列采用的是 instant 算法，使得添加列时不再需要 rebuild 整个表，只需要在表的 metadata 中记录新增列的基本信息即可。在 alter 语句后增加 ALGORITHM=INSTANT 即代表使用 instant 算法，如果未明确指定，则支持 instant 算法的操作会默认使用。如果 ALGORITHM=INSTANT 指定但不支持，则操作立即失败并显示错误。

关于列的 DDL 操作，是否支持 instant 等算法，官方文档给出了一个表格，整理如下，星号表示不是全部支持，有依赖项。

instant 算法使用最广泛的应该是添加列了，可以看到使用该算法还是有些限制的，一些限制如下：

如果 alter 语句包含了 add column 和其他的操作，其中有操作不支持 instant 算法的，那么 alter 语句会报错，所有的操作都不会执行。
如果指定了AFTER，字段必须是在最后一列，否则需要重建表；
只能顺序加列, 仅支持在最后添加列，而不支持在现有列的中间添加列。
不支持压缩表，即该表行格式不能是 COMPRESSED。
不支持包含全文索引的表。
不支持临时表。
不支持那些在数据字典表空间中创建的表。
DROP COLUMN需要重建表；
modify修改字段属性需要重建表。

效果对比与演示

（1）初始化测试数据

创建数据的过程使用存储过程

#创建表
CREATE TABLE `large_user` (
  `id` bigint(20) DEFAULT NULL,
  `name` varchar(64) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) 
#创建日志表
CREATE TABLE `large_user_log` (
  `id` int(11) DEFAULT NULL,
  `msg` varchar(1000) DEFAULT NULL COMMENT '提交信息记录'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#创建存储过程
DELIMITER ;;
CREATE PROCEDURE `insert_large_user`(id_begin INT, id_end INT)
BEGIN

DECLARE i int;
SET i = id_begin;
SET AUTOCOMMIT = 0;

WHILE i >= id_begin && i <= id_end DO
    INSERT INTO large_user(id, name, age) VALUES (i, concat('user_', i % 100000), i % 100);

    SET i = i + 1;
    IF MOD(i, 100000) <=0 THEN
        INSERT INTO large_user_log (id, msg) VALUES(i, 'ready to commit');
        COMMIT;
    END IF;
END WHILE;

END;;
DELIMITER ;

#调用存储过程，产生一亿条数据
call insert_large_user(1, 100000000);

（2）使用效果对比

我们在Demo中分别使用两种数据库，一种是MySQL8.0，另一种是MySQL5.7做相关的测试。

1	select version()

8.0的版本为：8.0.33

5.7的版本为：5.7.18-txsql-log

（3）新增字段的SQL

1 2	ALTER TABLE `large_user` ADD COLUMN `id_card` varchar(255) NULL COMMENT '身份证号' AFTER `age`

MySQL8.0执行效果：Time: 0.068s

MySQL5.7执行效果：Time: 158.898s

（4）删除字段的SQL

1 2	ALTER TABLE `large_user` DROP COLUMN `id_card`

MySQL8.0执行效果：Time: 0.063s

MySQL5.7执行效果：Time: 282.09s