一聚教程网:一个值得你收藏的教程网站

热门教程

MySQL8.0如何快速加列 MySQL8.0快速加列代码示例

时间:2022-06-29 08:51:17 编辑:袖梨 来源:一聚教程网

MySQL8.0如何快速加列?本篇文章小编给大家分享一下MySQL8.0快速加列代码示例,文章代码介绍的很详细,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。

1.了解背景信息

表结构的变更是业务运行过程中比较常见的需求之一,在 MySQL 的环境中,可以使用 Alter 语句来完成这些操作,这些 Alter 语句对应的操作通常也称之为 DDL 操作。通常情况下大表的 DDL 操作都会对业务有很明显的影响,需要在业务空闲,或者是维护的时候做。MySQL 5.7 支持 Onpne DDL,大部分 DDL 不影响对表的读取和写入,但是依然会消耗非常多的时间,且占用额外的磁盘空间,并会造成主从延迟。所以大表 DDL 仍是一件令 DBA 头痛的事。

听闻 MySQL 8.0 解决了这件令 DBA 头痛的事,那让我们来详细了解下吧。想了解新功能,最简单的方法就是查阅官方文档。查阅官方文档得知,快速加列即 Instant Add Column ,该功能自 MySQL 8.0.12 版本引入,是由腾讯游戏DBA团队贡献。注意一下,此功能只适用于 InnoDB 表。

2.快速加列测试

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

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

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

如果 alter 语句包含了 add column 和其他的操作,其中有操作不支持 instant 算法的,那么 alter 语句会报错,所有的操作都不会执行。

只能顺序加列, 仅支持在最后添加列,而不支持在现有列的中间添加列。

不支持压缩表,即该表行格式不能是 COMPRESSED。

不支持包含全文索引的表。

不支持临时表。

不支持那些在数据字典表空间中创建的表。

说的再多不如实际来测下,下面我们以 8.0.19 版本为例来实际验证下:

# 利用sysbench生成一张1000W的大表
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19  |
+-----------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+

# 增加无默认值的列
mysql> alter table sbtest1 add column col1 varchar(20), algorithm=instant;
Query OK, 0 rows affected (0.63 sec)
Records: 0 Duplicates: 0 Warnings: 0

# 增加有默认值的列
mysql> alter table sbtest1 add column create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', algorithm=instant;
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0

# 不显式指定instant算法
mysql> alter table sbtest1 add column col2 varchar(20);
Query OK, 0 rows affected (0.55 sec)
Records: 0 Duplicates: 0 Warnings: 0

# 设置列的默认值
mysql> alter table sbtest1 alter column col1 set default 'sql',algorithm=instant;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

# 指定In Place算法添加列,(5.7版本添加列使用该算法)
mysql> alter table sbtest1 add column col_inplace varchar(20),algorithm=inplace;
Query OK, 0 rows affected (1 min 23.30 sec)
Records: 0 Duplicates: 0 Warnings: 0

通过以上测试,我们可以发现,使用 instant 算法添加列基本都在 1s 内完成,对于大表来说这个速度是非常快的,业务基本无感知。当使用 5.7 版本的 inplace 算法时,则添加列的时间上升至数分钟。对比看来 8.0 版本的快速加列功能确实非常实用!

热门栏目