因为一条DDL,差点搞挂整个系统,这次真的长了教训
有一次在线上提了一个sql变更,就是下面这条,
-- 修改字段的数据类型由varchar(500)变更为text
ALTER TABLE t MODIFY COLUMN name text;
提完之后,上级审批人给我打来了电话,说不允许进行字段类型的变更,要变更的话需要找大领导审批,一想还是算了,不要打扰领导了。最后把varchar的长度变更为1000,才把这个事情解决了。后来查阅资料才明白原来一条普通的DDL却暗藏玄机。什么玄机呐今天细细说来。
要了解DDL的执行原理,必须区分mysql的版本,不同的版本DDL执行原理是不一样的。
一、DDL执行原理(5.6之前)
在mysql5.6版本之前,执行一条DDL语句,mysql内部会使用两种方式执行,分别是copy和inplace。
1.1、copy
所谓copy就是在执行过程中需要copy table,看下其具体步骤,
- 新建跟原表格一致的临时表,并在该临时表上执行DDL语句;
- 锁原表,不允许执行DML,仅允许查询;
- 逐行把数据从原表拷贝到临时表(无排序);
- 拷贝结束后,原表禁止读操作,也就是原表此时不提供读写服务;
- 进行rename操作,完成DDL过程;
可以看到在copy这种方式下,执行DDL语句的时候会锁表,且无法执行DML语句;再看下inplace的方式,
1.2、inplace方式(仅针对索引创建、删除)
这种方式仅对索引的创建、删除有效,其他类型的DDL还是使用copy的方式,其步骤如下,
- 新建frm临时文件;
- 锁住原表,不允许DDL,允许查询;
- 按照聚集索引的顺序查询数据,找到需要的索引列数据,排序后插入到新的索引中;
- 原表禁止读操作,也就是原表此时不提供读写服务;
- 进行rename操作,替换frm文件,完成DDL;
可以看到inplace这种方式依然需要锁表,且无法执行DML。
copy和inplace两种都会阻止DML语句的执行,也就是insert/update/delete操作,只能执行select操作。相对于copy的需要拷贝全表的数据外,inplace只需要拷贝索引数据,就好很多,但inplace只支持索引新增、删除。
在5.6版本之前的mysql在执行DDL的时候,一定要注意选择业务低峰期,同时做好影响范围的预测,以为在执行DDL的时候是无法执行DML的。
在5.6及之后,mysql推出了online DDL的方式。很好的解决了无法执行DML的问题。
二、online DDL
online DDL是mysql在5.6版本推出的执行DDL的方式,可以解决执行DDL时无法执行DML的情况。online DDL有自己的语法,在传统的DDL语句后加相应的参数,当然参数可以省略,省略的话mysql则会选择一种适合的方式执行。
2.1、online DDL语法
标准的online DDL写法如下,
-- 修改字段的数据类型由varchar(500)变更为text
ALTER TABLE t MODIFY COLUMN name text,algorithm=default|copy|inplace|instant,lock=none|shared|default|exclusive;
在algorithm参数中有四个值,
default,默认的,由系统决定
copy,和早期的copy方式一致;
inplace,和早期的inplace方式一致;
instant,mysql8.0新增的。只会修改数据字典中的元数据,会短暂的占用元数据上的排它锁,操作是即时的,允许并发DML;
lock参数有四个值,其限制级别由少到多,
none,允许并发查询和MDL语句,
shared,允许并发查询,但阻止DML
default,允许尽可能多的并发查询、DML。省略lock和default是一样的。
exclusive,阻止并发查询和DML,
2.2、online DDL执行过程
mysql将online DDL的执行过程分为三步,
初始化(initialization)
在这个阶段,服务器根据存储引擎、语句中指定的选项等来确定允许的并发,使用共享的可升级元数据锁来保护当前表定义。
执行(execution)
语句被准备和执行,元数据锁是否升级为排它锁取决于初始化阶段的评估,如果需要独占元数据锁,只在语句准备期间短暂使用。
提交(commit table definition)
元数据锁升级为排它锁,退出旧的表定义并提交新表定义,元数据锁持续时间很短。
2.3、常用的DDL
总结了常用的DDL的执行方式,
需要特别注意的是对于varchar的长度变化,其使用的算法是不一样的。
有个很有趣的点,平时定义的varchar(50),这里的50是字节数还是字符数吗?
其实在mysql5.0之后varchar(50),代表的是50个字符,在5.0之前是50个字节;
按照UTF8编码,一个字符3个字节;按照GBK编码一个字符2个字节;
了解了上面的知识后,还需要了解字符串的长度是怎么存储的,当小于256字节时使用1个字节存储,当大于256字节小于65535字节时,使用2个字节存储;varchar的最大长度是65535字节。
varchar类型字符长度的变化带来的是字节的变化,同时会引起存储字节长度的变化,也就是使用1个字节还是2个字节存储其长度。
增加
以UTF8编码为例,也就是一个字符3个字节。
1、如果字节的变化在256以内,也就是存储长度使用1个字节则使用inplace,如,
varchar(10)-->varchar(50)
varchar(50)-->varchar(80)
2、如果字节的变化跨越了256,也就是存储长度由1个字节变成2个字节则使用copy,如
varchar(80)-->varchar(90)
3、如果字节的变化超过256,也就是存储长度使用2个字节则使用inplace,如
varchar(90)-->varchar(250)
varchar(250)-->varcahr(1000)
减少
对varchar的长度减少统一是copy方式。
下面总结了各种DDL语句使用的算法及是否允许并发DML,是否需要重建表等,可参考。
需要特别注意下面这些不允许并发DML的DDL,其均使用copy方式:
1、改变列的数据类型;
2、删除主键;
3、变更表字符集;
4、varchar长度变短;
5、varchar长度边长,存储字节超过255;
三、总结
在mysql中执行DDL语句是很正常的,很多时候并不会想到会锁表或者阻止DML的执行,因为DDL执行的太快了,相对于大表则要格外注意,尤其是线上业务高峰期,千万不要执行DDL,在业务低峰期也要进行评估;
1、关注表的数据量;
2、确定mysql的版本;
3、关注CPU及内存使用情况;
4、做好应急措施;
参考:
https://www.cnblogs.com/hankyoon/p/15128334.html
https://www.cnblogs.com/xinysu/p/6732646.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-column-operations