mysql超大表加字段(MySQL 大表添加一列的实现)
mysql超大表加字段
MySQL 大表添加一列的实现问题参考自: https://www.zhihu.com/question/440231149 ,mysql中,一张表里有3亿数据,未分表,要求是在这个大表里添加一列数据。数据库不能停,并且还有增删改操作。请问如何操作?答案为个人原创
以前老版本 MySQL 添加一列的方式:
|
ALTER TABLE 你的表 ADD COLUMN 新列 char (128); |
会造成锁表,简易过程如下:
- 新建一个和 Table1 完全同构的 Table2
- 对表 Table1 加写锁
-
在表 Table2 上执行
ALTER TABLE 你的表 ADD COLUMN 新列 char(128)
- 将 Table1 中的数据拷贝到 Table2
- 将 Table2 重命名为 Table1 并移除 Table1,释放所有相关的锁
如果数据量特别特别大,那么锁表时间很长,期间所有表更新都会阻塞,线上业务不能正常执行。
针对 MySQL 5.6(不包含)之前的版本,通过触发器将一个表的更新在另一个表上重复,并进行数据同步,当数据同步完成时,业务上修改表名为新表并发布。业务不会暂停。触发器设置类似于:
|
create trigger person_trigger_update AFTER UPDATE on 原有表 for each row begin set @x = "trigger UPDATE" ; Replace into 新表 SELECT * from 原有表 where 新表.id = 原有表.id; END IF; end ; |
MySQL 5.6(包含) 以后的版本引入了在线 DDL 的功能:
|
Alter table 你的表 , ALGORITHM [=] { DEFAULT |INSTANT|INPLACE|COPY}, LOCK [=] { DEFAULT | NONE| SHARED| EXCLUSIVE } |
其中的参数:
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.htmlMySQL
5.7:https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.htmlMySQL
8.0:https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
可以通过:
|
ALTER TABLE 你的表 ADD COLUMN 新列 char (128), ALGORITHM=INSTANT, LOCK=NONE; |
类似的语句,实现在线增加字段。最好还是明确 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 时就会出现复制错误的现象。
如果当前有很多事务在执行,并且有那种包含大查询的事务,例如:
|
START TRANSACTION ; select count (*) from 你的表 |
这样类似的会执行较长时间的事务,也会阻塞。
所以,原则上:
- 避免大事务
- 在业务低峰去做表结构变化
到此这篇关于MySQL 大表添加一列的实现的文章就介绍到这了,更多相关MySQL 大表添加一列内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://blog.csdn.net/zhxdick/article/details/112857581
- mysql 死锁查询(MySQL slave 延迟一列 外键检查和自增加锁)
- 2021-10-18 11:58:15
- mysql如何定义外键(MySQL外键设置的方法实例)
- 如何正常查看mysql存储数据文件(Mysql文件存储图文详解)
- mysql5.7.36详细安装(CenOS6.7下mysql 8.0.22 安装配置方法图文教程)
- mysql双向同步原理(详解MySQL的半同步)
- mysql中查询数据合并(Mysql合并结果接横向拼接字段的实现步骤)
- docker搭建mysql服务(Docker部署Mysql集群的实现)
- mysql存储过程limit参数(MySQL存储过程in、out和inout参数示例和总结)
- mysql字符串的表示方法(详解mysql中的字符集和校验规则)
- 如何排查mysql存储过程的问题(Mysql修改存储过程相关权限问题)
- mysql 安装阿里云(详解如何在阿里云服务器安装Mysql数据库)
- mysql语句性能分析(聊聊MySQL的COUNT*的性能)
- mysql8.0.19.0正确安装图解(MySQL 8.0.23 主要更新一览新特征解读)
- mysqltruncate分区自定义参数(MySQL truncate table语句的使用)
- mysql的索引及其介绍总结(浅析MysQL B-Tree 索引)
- 仙女们的私藏鲜法大PK 鲜香切块牛肉(仙女们的私藏鲜法大PK)
- 天热没胃口 这道菜开胃又下饭,2个小技巧新手一学就会(这道菜开胃又下饭)
- 指天椒紫苏爆炒牛肉(指天椒紫苏爆炒牛肉)
- 谷雨前,吃牛羊肉别忘了吃河鲜,除湿还清热,加紫苏一炒特解馋(吃牛羊肉别忘了吃河鲜)
- 紫苏牛肉锅里滚一滚,香的鼻子都要掉了(紫苏牛肉锅里滚一滚)
- 每天都吃水果的好处(每天吃水果的好处与功效)
热门推荐
- sql查询优化最快的方法(必备 SQL 查询优化技巧提升网站访问速度)
- canvas如何保存当前的图片(canvas如何实现多张图片编辑的图片编辑器)
- iis7.5怎么安装(IIS7.5打开启用GZip压缩功能的设置教程方法)
- 小程序ui设计样式(AmazeUI 平滑滚动效果的示例代码)
- python去除字符串中间的空格(Python去除字符串前后空格的几种方法)
- 正则表达式中/i,/g,/ig,/gi,/m的含义
- windows 10中如何安装numpy(windows下numpy下载与安装图文教程)
- python中可以改变的数据类型(Python常见数据类型转换操作示例)
- css3边框讲解(六种css3实现的边框过渡效果)
- vmware16虚拟机的安装教程(最新虚拟机VMware 14安装教程)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9