MySQL 8.0 Online DDL快速加列的相关总结

吾爱主题阅读：103 2024-04-02 08:02:51 评论：0

问题描述

前几天同事问了我一个问题：业务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，结果如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 [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列的信息：

1 2 3 4 5 6 7 8 9 10 11 12 [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)