MySQL8.0 如何快速加列
前言:
很早就听说MySQL8.0支持快速加列,可以实现大表秒级加字段。笔者自己本地也有8.0环境,但一直未进行测试。本篇文章我们就一起来看下MySQL8.0快速加列到底要如何操作。
1.了解背景信息
表结构的变更是业务运行过程中比较常见的需求之一,在MySQL的环境中,可以使用Alter语句来完成这些操作,这些Alter语句对应的操作通常也称之为DDL操作。通常情况下大表的DDL操作都会对业务有很明显的影响,需要在业务空闲,或者是维护的时候做。MySQL5.7支持OnlineDDL,大部分DDL不影响对表的读取和写入,但是依然会消耗非常多的时间,且占用额外的磁盘空间,并会造成主从延迟。所以大表DDL仍是一件令DBA头痛的事。
听闻MySQL8.0解决了这件令DBA头痛的事,那让我们来详细了解下吧。想了解新功能,最简单的方法就是查阅官方文档。查阅官方文档得知,快速加列即InstantAddColumn,该功能自MySQL8.0.12版本引入,是由腾讯游戏DBA团队贡献。注意一下,此功能只适用于InnoDB表。
2.快速加列测试
快速加列采用的是instant算法,使得添加列时不再需要rebuild整个表,只需要在表的metadata中记录新增列的基本信息即可。在alter语句后增加ALGORITHM=INSTANT即代表使用instant算法,如果未明确指定,则支持instant算法的操作会默认使用。如果ALGORITHM=INSTANT指定但不支持,则操作立即失败并显示错误。
关于列的DDL操作,是否支持instant等算法,官方文档给出了一个表格,现整理如下,星号表示不是全部支持,有依赖项。
Instant | InPlace | RebuildsTable | 允许并发DML | 仅修改元数据 | |
---|---|---|---|---|---|
添加列 | Yes* | Yes | No* | Yes* | No |
删除列 | No | Yes | Yes | Yes | No |
重命名列 | No | Yes | No | Yes* | Yes |
更改列顺序 | No | Yes | Yes | Yes | No |
设置列默认值 | Yes | Yes | No | Yes | Yes |
更改列数据类型 | No | No | Yes | No | No |
扩展VARCHAR列大小 | No | Yes | No | Yes | Yes |
删除列默认值 | Yes | Yes | No | Yes | Yes |
更改自动增量值 | No | Yes | No | Yes | No* |
设置列为null | No | Yes | Yes* | Yes | No |
设置列notnull | No | Yes* | Yes* | Yes | No |
修改ENUM/SET列的定义 | Yes | Yes | No | Yes | Yes |
instant算法使用最广泛的应该是添加列了,可以看到使用该算法还是有些限制的,一些限制如下:
- 如果alter语句包含了addcolumn和其他的操作,其中有操作不支持instant算法的,那么alter语句会报错,所有的操作都不会执行。
- 只能顺序加列,仅支持在最后添加列,而不支持在现有列的中间添加列。
- 不支持压缩表,即该表行格式不能是COMPRESSED。
- 不支持包含全文索引的表。
- 不支持临时表。
- 不支持那些在数据字典表空间中创建的表。
说的再多不如实际来测下,下面我们以8.0.19版本为例来实际验证下:
#利用sysbench生成一张1000W的大表 mysql>selectversion(); +-----------+ |version()| +-----------+ |8.0.19| +-----------+ 1rowinset(0.00sec) mysql>selectcount(*)fromsbtest1; +----------+ |count(*)| +----------+ |10000000| +----------+ #增加无默认值的列 mysql>altertablesbtest1addcolumncol1varchar(20),algorithm=instant; QueryOK,0rowsaffected(0.63sec) Records:0Duplicates:0Warnings:0 #增加有默认值的列 mysql>altertablesbtest1addcolumncreate_timetimestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',algorithm=instant; QueryOK,0rowsaffected(0.58sec) Records:0Duplicates:0Warnings:0 #不显式指定instant算法 mysql>altertablesbtest1addcolumncol2varchar(20); QueryOK,0rowsaffected(0.55sec) Records:0Duplicates:0Warnings:0 #设置列的默认值 mysql>altertablesbtest1altercolumncol1setdefault'sql',algorithm=instant; QueryOK,0rowsaffected(0.02sec) Records:0Duplicates:0Warnings:0 #指定InPlace算法添加列,(5.7版本添加列使用该算法) mysql>altertablesbtest1addcolumncol_inplacevarchar(20),algorithm=inplace; QueryOK,0rowsaffected(1min23.30sec) Records:0Duplicates:0Warnings:0
通过以上测试,我们可以发现,使用instant算法添加列基本都在1s内完成,对于大表来说这个速度是非常快的,业务基本无感知。当使用5.7版本的inplace算法时,则添加列的时间上升至数分钟。对比看来8.0版本的快速加列功能确实非常实用!
总结:
虽然快速加列存在一些限制,instant算法也只适用于部分DDL操作,但8.0的这项新功能已经足以令人兴奋,很大程度上解决了大表加字段的大难题。通过这篇文章,希望各位能了解到这项新功能,是不是想升级到8.0了呢,可以着手准确起来了。
以上就是MySQL8.0如何快速加列的详细内容,更多关于MySQL8.0快速加列的资料请关注毛票票其它相关文章!
声明:本文内容来源于网络,版权归原作者所有,内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:czq8825#qq.com(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。