8.0 新特性 - Instant Add Column

前言

MySQL 8.0.12 版本支持 “快速加列” 功能，既添加字段时可以支持 “INSTANT” 快速完成。通过只修改数据字典的方法来实现大表快速加列，避免之前加列操作必须做的数据拷贝，从而大幅缩小大表加列所需的时间，减少对系统的影响。

最开始的快速加列只能将列加在表中的末尾，随着该特性的不断完善，在 MySQL 8.0.29 中增加了在表的任何位置添加（或删除）列功能。

支持版本：
MySQL 版本 8.0.12 及以上
腾讯云内核版本 MySQL 5.7 20190830 及以上
腾讯云内核版本 MySQL 8.0 20200630 及以上

1. 使用快速加列

1.1 准备数据

以下是测试表结构：

create table tb1(
    id bigint auto_increment primary key ,
    a varchar(10) not null ,
    b varchar(10) not null,
    c varchar(200) not null
);

表中数据量有 200 万行：

root@mysql[test]>select count(*) from tb1;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+

1.2 算法对比

手动指定 DDL 算法为 INPLACE 查看需要花费多长时间？

root@mysql[test]>ALTER TABLE tb1 ADD COLUMN e varchar(10) DEFAULT '1',ALGORITHM=INPLACE;
Query OK, 0 rows affected (16.88 sec)

从执行结果看，当使用 INPLACE 算法给 200 万行的测试表中添加一个字段需要花费 16 秒。

接下来，我们不指定算法，因为 MySQL 会默认使用对数据库性能影响最小的算法，加列支持 INSTANT 所以默认会使用 INSTANT 算法。

root@mysql 10:33:  [test]>ALTER TABLE tb1 ADD COLUMN f varchar(10) DEFAULT '1';
Query OK, 0 rows affected (0.05 sec)

从执行结果上来看，几乎是瞬间完成。

虽然 MySQL 默认会使用并发性最高的算法，但是不同的版本以及不同的变更类型可能会产生预期外的情况，所以建议执行 DDL 时指定算法，如果不支持会报错，那么此时再按照实际情况制定变更计划。

-- 执行一个不支持 INSTANT 算法的语句
ALTER TABLE tb1 engine=innodb, ALGORITHM=INSTANT;
-- 由于不支持返回错误，保障变更安全
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

1.3 列顺序和删除

MySQL 8.0.12 支持 INSTANT 算法添加字段，但只能加在表中最后一列，不能指定顺序且删除列也不支持 INSTANT 算法。直到 8.0.29 版本，支持指定顺序且删除列也支持 INSTANT 算法。

查看此时 tb1 测试表的结构：

CREATE TABLE `tb1` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `a` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
  `b` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
  `c` varchar(200) COLLATE utf8mb4_general_ci NOT NULL,
  `e` varchar(10) COLLATE utf8mb4_general_ci DEFAULT '1',
  `f` varchar(10) COLLATE utf8mb4_general_ci DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=utf8mb4；

使用 INSTANT 算法，添加一个 d 字段，添加在 c 字段的后面。

ALTER TABLE tb1 ADD COLUMN d varchar(10) DEFAULT '1' after c, ALGORITHM=INSTANT;

使用 INSTANT 算法，删除 f 字段。

root@mysql[test]>ALTER TABLE tb1 DROP f, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.04 sec)

2. 工作原理

2.1 Instant Add Column

在没有快速加列的功能时，每次添加字段都需要重建表空间，就需要大量的 IO 及时间。

当使用快速加列时，只会修改数据字典，增加 新列的定义和 新列的默认值。需要读取数据时 MySQL 会将 新增列的默认值，追加到读取的数据后。

需要写入数据时，使用了新的数据格式 (增加了 instant 标志位 和 “列数” 字段)。

相当于是 伪造列，那么是否可以一直伪造下去呢？

以下是快速加列的限制：

1. 添加列的操作不能与其它 ALTER TABLE 操作放在同一条语句组合中。
2. 在 MySQL 8.0.29 之前，一列只能作为表的最后一列添加，不支持将列添加到其他列中的任何其他位置。从 MySQL 8.0.29 开始，可以将即时添加的列添加到表中的任何位置。
3. ROW_FORMAT=COMPRESSED、具有 FULLTEXT 的表、临时表不支持 Instant Add Column。
4. 添加列会评估行的大小，如果超出限制会抛出异常。

2.2 表如何维护

当使用 INSTANT 添加一个列或删除一个列时，都会创建一个新的行版本，当超出限制时会抛出异常。
可以通过查询 INFORMATION_SCHEMA.INNODB_TABLES来查询行版本：

-- Version: 8.0.29
SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES;

当 TOTAL_ROW_VERSIONS 大于 64 会报错：ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE. 使用 ALTER TABLE 重建表空间时 TOTAL_ROW_VERSIONS 会归 0。

ALTER TABLE table_name engine=innodb, ALGORITHM=INPLACE;

总结

MySQL 8.0 引入的 Instant Add Column 是一个非常实用的特性。然而，由于有 64 次即时更改的限制，建议通过 INFORMATION_SCHEMA.INNODB_TABLES 对表版本进行监控。以避免在不知不觉中达到即时更改限制，并在即将超过限制时规划重建。