由于业务随着时间不停的改变,起初的表结构设计已经满足不了如今的需求,这时你是不是想那就加字段呗!加字段也是个艺术活,接下来由本文的主人咔咔给你吹。
试想一下这个场景
事务A在执行一个非常大的查询
事务B毫不犹豫的执行了DDL操作
接下来会发生什么,你如果已经知道答案的话恭喜你又掌握了一个MySQL中重要的知识点。
事务A执行查询时会持有MDL锁,而事务B同样也需要MDL锁,但事务A在进行大查询,所以导致事务B后的所有操作都会被堵塞。
这时你应该知道了在MySQL中所有对表的增删改查都需要申请MDL读锁。
接下来聊聊如何安全的给表加个字段
最新文章
死磕MySQL系列总目录
MySQL统计总数就用count(*),别花里胡哨的《死磕MySQL系列十》
为什么MySQL字符串不加引号索引失效?《死磕MySQL系列十一》
打开orderby的大门,一探究竟《死磕MySQL系列十二》
重重封锁,让你一条数据都拿不到《死磕MySQL系列十三》
一、为什么不建议你在生产环境下进行DDL
上期文章跟大家聊过的MDL锁的知识还记得吧!不记得也没事,那就在这篇文章中再进行复习。
MDL锁不需要显示调用,当对一个表做增删改查时会默认加MDL读锁,而执行修改表结构时会默认加MDL写锁。
这也就是文章开头给大家说的事务B是不能在事务A没释放MDL读锁之前执行。
事务B需要的是MDL写锁,MDL读锁与写锁是互斥关系,因此事务B的DDL操作会一直等待事务A提交并释放MDL锁
但你有没有想过一个问题,此时的事务B执行的在线DDL操作,需要的是MDL写锁,上文也说了对一个表做增删改查时会默认加MDL读锁,这不就意味着后续对这个表的所有操作都会堵塞吗?
所以说坚决不要在生产环境进行在线DDL,现在的客户端都有会重试机制,当堵塞的语句超时后会再起一个新的事务在请求,这张表假设是一个热表,MySQL库的线程会非常快就爆满,等待的结果就是用户那边迟迟响应不了结果。
这里给大家一个方案,当你十分紧急需要添加一个字段时,可以给语句设置一个时间,如果在这个设定的时间内能拿到MDL写锁最好,拿不到也会阻塞后续的业务语句。
当设置的这个时间超过后,这个指令就结束了,之后可以再次重复执行这个指令即可。
执行语法为altertabletable_namwait10addcloumn
这个方案也是在你的表不大的情况下才可以进行执行的,假设你的表就看第二种方案哈!要不你会死的很惨。
咔咔在一张近W数据的开发表上做过一次DDL操作,大概用了23s这样一个时间,这要是在线上想想都害怕
二、如何安全给表加个字段
目前咔咔知道的第三方工具有gh-ost和pt-online-schema-change,咔咔所在的公司使用的是后者,对于这两个插件后者的使用还是多点。
还记得在前几期文章中提到了表数据都删完了,但表空间依然没有缩小,在那期就简单的提了一下。
想要缩小表空间可以新建一模一样的表结构,然后根据主键ID的顺序把数据从就旧表中逐行插入新表,这样就可以减少表空洞的问题。
同理今天要说的pt-online-schema-change这个插件的工作流程大致如下
新建一模一样的表,表名可以起为_new后缀
接着在这个新表执行更改字段操作
接着在原表上加三个触发器,分别为delete、update、insert,将原表中要执行的语句也在新表中执行
最后将原表的数据拷贝到新表中,替换掉原表
接下来咔咔将亲自实战一下此操作,可以跟着咔咔的步骤一起来
三、使用pt-online-schema-change
安装步骤
yum-yinstallperlperl-DBIperl-DBD-MySQLperl-Time-HiResperl-IO-Socket-SSLperl-Digest-MD5wget