如何往MySQL的大数据表添中加一列?
以前老版本 MySQL 添加一列的方式:
会造成锁表,简易过程如下:
- 新建一个和 Table1 完全同构的 Table2
- 对表 Table1 加写锁
- 在表 Table2 上执行 ALTER TABLE 你的表 ADD COLUMN 新列 char(128)
- 将 Table1 中的数据拷贝到 Table2
- 将 Table2 重命名为 Table1 并移除 Table1,释放所有相关的锁
如果数据量特别特别大,那么锁表时间很长,期间所有表更新都会阻塞,线上业务不能正常执行。
针对 MySQL 5.6(不包含)之前的版本,通过触发器将一个表的更新在另一个表上重复,并进行数据同步,当数据同步完成时,业务上修改表名为新表并发布。业务不会暂停。触发器设置类似于:
MySQL 5.6(包含) 以后的版本引入了在线 DDL 的功能
其中的参数:
- ALGORITHM:
- DEFAULT:默认方式,在 MySQL 8.0中,如果未显示指定 ALGORITHM,那么会优先选择 INSTANT 算法,如果不行再使用 INPLACE 算法,如果不支持 INPLACE 算法则使用 COPY 的方式完成
- INSTANT:8.0 中新添加的算法,添加列是立即返回。但是不能是虚拟列。这个原理很简单,对于新建一列,表所有原有数据并不是立刻发生变化,只是在表字典里面记录下这个列和默认值,对于默认的 Dynamic 行格式(其实就是 Compressed 的变种),如果更新了这一列则原有数据标记为删除在末尾追加更新后的记录。这样做就是没有提前预留出列空间,之后更新可能经常会发生行记录空间变动。但是对于大多数业务,都是最近的时间的记录才会修改,所以问题不大。
- INPLACE:在原表上直接进行修改,不会拷贝临时表,可以逐条记录修改,不会产生大量的 undolog 以及 redolog,不会占用很多 buffer。可以避免重建表带来的IO和CPU消耗,保证期间依然良好的性能和并发。
- COPY:拷贝到临时新表上进行修改。由于记录拷贝,会产生大量的 undolog 以及 redolog,并占用很多 buffer,对业务性能有影响。
- LOCK:
- DEFAULT:和 ALGORITHM 的 DEFAULT 类似
- NONE:无锁,允许并发读取和更新表
- SHARED:共享锁,允许读取不允许更新
- EXCLUSIVE:不允许读取和更新
各个版本支持的在线 DDL 修改使用的算法的对比
参考文档:
- MySQL 5.6: https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html
- MySQL 5.7:https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html
- MySQL 8.0:https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
可以通过:
类似的语句,实现在线增加字段。最好还是明确 ALGORITHM 以及 LOCK,这样执行 DDL 的时候能明确知道到底会对线上业务有多大影响。
同时,执行在线 DDL 的过程大概是
可以看出,在开始阶段需要 metadata lock,metadata lock 是在 5.5 才引入到mysql,之前也有类似保护元数据的机制,只是没有明确提出 metadata lock 概念而已。但是 5.5 之前版本(比如5.1)与5.5之后版本在保护元数据这块有一个显著的不同点是,5.1对于元数据的保护是语句级别的,5.5对于metadata的保护是事务级别的。所谓语句级别,即语句执行完成后,无论事务是否提交或回滚,其表结构可以被其他会话更新;而事务级别则是在事务结束后才释放 metadata lock。
引入 metadata lock 后,主要解决了2个问题,一个是事务隔离问题,比如在可重复隔离级别下,会话A在2次查询期间,会话B对表结构做了修改,两次查询结果就会不一致,无法满足可重复读的要求;另外一个是数据复制的问题,比如会话A执行了多条更新语句期间,另外一个会话B做了表结构变更并且先提交,就会导致 slave 在重做时,先重做 alter,再重做 update 时就会出现复制错误的现象。
如果当前有很多事务在执行,并且有那种包含大查询的事务,例如:
这样类似的会执行较长时间的事务,也会阻塞。
所以,原则上:
- 避免大事务
- 在业务低峰去做表结构变化
作者 | 智哥
更多技术干货敬请关注码农架构知乎号:码农架构 - 知乎
本文为码农架构原创内容,未经允许不得转载。
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
利用 Canvas 绘画一个未闭合的带进度条的圆环
最终效果图 一、定义变量 定义半径,定义圆环厚度,定义圆心位置、定义默认填充颜色 let radius = 75 let thickness= 10 let innerRadius = radius - thickness let x = 75 let y = 75 var canvas = document.getElementById('tutorial'); var ctx = canvas.getContext('2d'); ctx.fillStyle = "#f2d7d7"; 二、画第一个圆弧 ctx.beginPath(); ctx.arc(x, y, radius, Math.PI * 1.5, Math.PI) 注意 beginPath() 这个方法,生成路径的第一步。本质上,路径是由很多子路径构成,这些子路径都是在一个列表中,所有的子路径(线、弧形、等等)构成图形。而每次这个方法调用之后,列表清空重置,然后我们就可以重新绘制新的图形。 也就是说,这个方法可以用来给 Canvas图像 分组,绘制新的图形如果不调用此方法,那么新的图形会和前面的图形连接在一起 三、画第一个...
- 下一篇
零基础:用于调度任务的 systemd 定时器
导读:systemd 提供定时器有一段时间了,定时器替代了 cron 功能,这一特性值得看看。 systemd 提供定时器有一段时间了,定时器替代了 cron 功能,这一特性值得看看。本文将向你介绍在系统启动后如何使用 systemd 中的定时器来运行任务,并在此后重复运行。这不是对 systemd 的全面讨论,只是对此特性的一个介绍。 ##快速回顾:cron、anacron 与 systemd cron 可以以几分钟到几个月或更长时间的粒度调度运行一个任务。设置起来相对简单,它只需要一个配置文件。虽然配置过程有些深奥,但一般用户也可以使用。 然而,如果你的系统在需要执行的时间没有运行,那么 cron 会失败。 anacron 克服了“系统没有运行”的问题。它确保任务将在你的系统再次启动时执行。虽然它旨在给管理员使用,但有些系统允许普通用户访问 anacron。 但是,anacron 的执行频率不能低于每天一次。 cron 和 anacron 都存在执行上下文一致性的问题。必须注意任务运行时有效的环境与测试时使用的环境完全相同。必须提供相同的 shell、环境变量和路径。...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- SpringBoot2全家桶,快速入门学习开发网站教程
- CentOS关闭SELinux安全模块
- CentOS7编译安装Gcc9.2.0,解决mysql等软件编译问题
- CentOS7设置SWAP分区,小内存服务器的救世主
- CentOS6,7,8上安装Nginx,支持https2.0的开启
- Eclipse初始化配置,告别卡顿、闪退、编译时间过长
- CentOS7,CentOS8安装Elasticsearch6.8.6
- Windows10,CentOS7,CentOS8安装Nodejs环境
- CentOS8安装Docker,最新的服务器搭配容器使用
- CentOS7编译安装Cmake3.16.3,解决mysql等软件编译问题