1. 问题:怎么给线上表加字段?工作中最常遇到的问题,怎么给线上频繁使用的大表添加字段?
比如:给下面的用户表(user)添加年龄(age)字段 。
CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',`name` varchar(100) DEFAULT NULL COMMENT '姓名',PRIMARY KEY (`id`)) ENGINE=InnoDB COMMENT='用户表';
有同学会说,这还不简单,直接加不加完了,用下面的命令:
ALTER TABLE `user` ADD `age` int NOT NULL DEFAULT '0' COMMENT '年龄';
添加完,再查看一下表结构:
CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',`name` varchar(100) DEFAULT NULL COMMENT '姓名',`age` int NOT NULL DEFAULT '0' COMMENT '年龄',PRIMARY KEY (`id`)) ENGINE=InnoDB COMMENT='用户表';
这不是添加成功了吗?有什么呀!
是的 , 线下数据库怎么整都行,但是如果在线上数据库这样操作,整个服务都有宕机的风险!自己也离毕业不远了 。
不是危言耸听,我们找个case测试一下:
![线上服务宕机,码农试用期被毕业,原因竟是给MySQL加个字段](http://img.zhejianglong.com/231018/041K5G47-0.png)
文章插图
- Session1启动了一个事务 , 没有提交 。
- Session2执行添加列的操作 , 被阻塞 。
- 更严重的是,Session3执行简单查询的语句也被阻塞了 。
原因是在执行查询语句的时候,MySQL自动加了MDL锁(metadata lock,即元数据锁) 。
不行的话,我们可以再执行一下show processlist命令,查看有哪些正在执行的进程:
![线上服务宕机,码农试用期被毕业,原因竟是给MySQL加个字段](http://img.zhejianglong.com/231018/041K53H4-1.png)
文章插图
可以清楚的看到Session2和Session3的语句正在等待MDL锁,Waiting for table metadata lock 。
MDL锁的作用是什么?
为了保证并发操作下数据的一致性 。
如果一个事务正在执行中,另一个在这时修改了表结构,不但可能导致当前事务出现不可重复读的问题,还有可能连事务都无法提交 。
什么时候会加MDL锁?
MDL锁是MySQL自动隐式加锁,无需我们手动操作 。
在我们执行DDL语句的时候 , MySQL自动添加MDL读锁 。
在我们执行DML语句的时候,MySQL自动添加MDL写锁 。
读锁与读锁之间不互斥,读锁与写锁、写锁与写锁之间互斥 。
注意:MDL锁是表锁 , 会对整张表加锁 。
普及额外的小知识点,什么是DML和DDL:
DML(Data Manipulation Language)数据操纵语言:
适用范围:对表数据进行操作,比如 insert、delete、select、update等 。
DDL(Data Definition Language)数据定义语言:
适用范围:对表结构进行操作,比如create、drop、alter、rename、truncate等 。
3. 如何优雅的给线上表加字段既然修改表结构的时候,MySQL会自动添加表锁,并且是写锁 , 会阻塞后续的所有读写请求,造成非常严重的后果 。
还有没有办法能优雅的给线上表添加字段呢?
当然有,从MySQL5.6版本开始增加了Online DDL , 作用就是在执行DDL的时候,允许并发执行DML 。简单翻译就是修改表结构的时候,也能同时支持并发执行增删查改操作 。
从MySQL8.0版本开始又优化了Online DDL , 支持快速添加列,可以实现给大表秒级加字段 。
具体用法就是在DDL语句后面增加两个参数ALGORITHM和LOCK 。
比如下面这样:
ALTER TABLE `user` ADD `age` int NOT NULL DEFAULT '0' COMMENT '年龄',ALGORITHM=Inplace,LOCK=NONE;
这两个参数分别是干嘛用的?有哪些选项呢?ALGORITHM可以指定使用哪种算法执行DDL,可选项有:
- Copy:
拷贝方式,MySQL5.6 之前 DDL 的执行方式 , 过程就是先创建新表,修改新表结构,把旧表数据复制到新表 , 删除旧表,重命名新表 。执行过程非常耗时,产生大量的磁盘IO和占用CPU,还有使Buffer poll失效,而且需要锁住旧表 , 性能较差,现在基本很少使用 。
- Inplace:
原地修改,MySQL5.6开始引入的,优点是不会在Server层发生表数据拷贝,过程中允许并发执行DML操作 。过程就是先添加MDL写锁,执行初始化操作,然后降级为MDL读锁,执行DDL操作(比较耗时,允许并发执行DML操作),升级为MDL写锁,完成DDL操作 。
- Instant:
快速修改 , MySQL8.0开始引入的 , 可以实现快速给大表添加字段 。
推荐阅读
- .NET 6 跨服务器联表查询
- 一个C#开发者学习SpringCloud搭建微服务的心路历程
- 基于Netty的TCP服务框架
- 【.NET 6+Loki+Grafana】实现轻量级日志可视化服务功能
- 一篇文章带你掌握主流服务层框架——SpringMVC
- gin项目部署到服务器并后台启动
- 结合springboot实现,这里对接的是easy版本,工具用的是IDEA,WebStrom 支付宝沙箱服务
- gRPC+Protocol Buffer Go微服务实战 - 用户服务开发
- frp服务利用云主机实现Windows远程连接
- frp服务利用云主机docker服务实现Windows远程连接