目录
- 问题描述
- MySQL Online DDL加列的历史方法
- 01 Copy方法
- 02 Inplace方法
- MySQL8.0.12 引入的Instant方法
问题描述
前几天同事问了我一个问题：业务A从MySQL迁移到MongoDB的原因是什么？
说实话，这个问题还真不好回答，为什么要迁移，一定是遇到了某种瓶颈，可能是数据量也可能是数据类型等，于是我咨询了一下业务，最终得到了答案：这个业务中的某些表，要频繁的加字段。mongodb中加字段的成本几乎没有，而MySQL低版本中加字段的成本还是挺高的。
那么常用的MySQL添加字段的方法有哪些呢？这里我简单列举一下：
1、percona的pt-osc工具
2、github开源项目gh-ost工具
3、MySQL原生Online DDL
MySQL Online DDL加列的历史方法
01 Copy方法
MySQL5.5版本及之前的加列方法：Copy
它的执行示意图如下：
我们有一个原表A，只包含1个字段，它包含1、2、4、6这几条记录，当我们使用Copy算法加列时：
1、创建了一个新的表tmp-A，新表包含2个字段，
2、然后我们把表A的数据全部逐行拷贝到tmp-A这个新表里面，
3、然后用tmp-A表和A表做个交换，
这样，我们的新表就包含2个字段了。同时需要注意，新表中的数据记录比原表更加紧凑了。原表中可能由于删除了3和5两条记录，使得表中间留下了空洞，或者叫空间碎片。
可以看到，Copy算法需要拷贝一遍数据，需要额外的存储空间来存储tmp-A这个临时表。另外，在拷贝数据的过程中，表A的写入操作会丢失，也就是说，表A在alter table的过程中不能有数据更新。这可能是一个致命的缺点。
02 Inplace方法
MySQL5.6版本开始引入Online DDL，这个功能使得上面的过程变成了下面这样：
它的过程和上面的Copy算法有些不同：
1、Online DDL过程中，从表A提取B+树，并存储到一个中间文件tmp-file，而不是中间表tmp-A
2、步骤1执行过程中，对表A的写入，都会记录到row log中
3、步骤1执行完毕后，对tmp-file应用所有的row log，得到一个与表A数据相同的数据文件
4、利用数据文件tmp-file替换表A的数据文件即可。
这个过程中，由于row log的存在，使得在整个该表过程中，表A是可以进行增删改查的操作的，因为这些操作不会丢失。这也就是为什么把这个过程叫做Online DDL的原因。
另外，这里需要解释下，Copy算法中生成的tmp-A临时表是在Server层面创建的，而上述Online DDL操作中的tmp-file是在插件式存储引擎Innodb内部生成的，我们把这种在Innodb内部完成的变更操作，称之为Inplace（中文表示原地），也就是不需要将数据挪动到”server层的临时表”。
MySQL8.0.12 引入的Instant方法
MySQL8.0.12版本引入了Instant的方法，它让加列变得更加简单。instant算法添加列时不再需要 rebuild 整个表，只需要在表的 metadata 中记录新增列的基本信息即可。
我们来看它的优势，首先我们创建一个表t1，并插入26w条数据，然后分别添加数据列col_1,col_2,col_3,并显示指定加列的算法为copy、inplace、和instant，结果如下：
[test] 23:42:45> select count(1) from t1;
+----------+
| count(1) |
+----------+
| 262144 |
+----------+
1 row in set (0.06 sec)
方案一：copy
[test] 23:43:29> alter table t1 add col_1 int,algorithm=copy;
Query OK, 262144 rows affected (1.48 sec)
Records: 262144 Duplicates: 0 Warnings: 0
方案二：inplace
[test] 23:43:46> alter table t1 add col_2 int,algorithm=inplace;
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
方案三：instant
[test] 23:44:08> alter table t1 add col_3 int,algorithm=instant;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
m5480:mysqlha_common@10.41.28.124 [test] 23:44:14> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`age` int DEFAULT NULL,
`score` int DEFAULT NULL,
`col_1` int DEFAULT NULL,
`col_2` int DEFAULT NULL,
`col_3` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_sco` (`score`)
) ENGINE=InnoDB AUTO_INCREMENT=458730 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)
从结果不难看出，执行时间上：
copy> inplace > instant
与此同时，copy算法的受影响行数是全部表，而inplace和instant的算法影响的行数都是0，说明他们是Online DDL操作。
最后，我们还可以通过下面的方法查看instant列的信息：
[test] 23:53:01> SELECT * FROM information_schema.innodb_tables where name like 'test/t1'\G *************************** 1. row *************************** TABLE_ID: 1079 NAME: test/t1 FLAG: 33 N_COLS: 10 SPACE: 22 ROW_FORMAT: Dynamic ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single INSTANT_COLS: 6 1 row in set (0.00 sec)
可以看到，test.t1这个表的instant列序号是6，代表它是这个表的第7个列(列编号从0开始)。
当然，instant算法不支持删除普通列、无法设置列的顺序、还有一些其他的限制，详情可以查看官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
但这些限制并不影响它成为一个优秀的DDL功能。 相信通过MySQL版本的不断迭代，在后面的版本中，有更多的变更操作可以用到instant这种高效的算法。
以上就是MySQL 8.0 Online DDL快速加列的相关总结的详细内容，更多关于MySQL DDL快速加列的资料请关注NICE源码其它相关文章！
