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

在实际的MySQL运维过程中,我们经常会遇到业务需要给某张表添加字段的情况,本文将介绍几种加字段的方法,感兴趣的朋友可以参考下

问题描述

前几天同事问了我一个问题:业务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

它的执行示意图如下:

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

我们有一个原表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,这个功能使得上面的过程变成了下面这样:

MySQL 8.0 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*frominformation_schema.innodb_tableswherenamelike'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

1rowinset(0.00sec)

可以看到,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快速加列的资料请关注钦钦技术栈其它相关文章!

原文链接:https://mp.weixin.qq.com/s/jEDA6svpo9n7Dic8Y4xDrA

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/7174/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-07-31 3:34:53
下一篇 2022-07-31 3:35:06

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!