因为一条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,看下其具体步骤,

  1. 新建跟原表格一致的临时表,并在该临时表上执行DDL语句;
  2. 锁原表,不允许执行DML,仅允许查询;
  3. 逐行把数据从原表拷贝到临时表(无排序);
  4. 拷贝结束后,原表禁止读操作,也就是原表此时不提供读写服务;
  5. 进行rename操作,完成DDL过程;

可以看到在copy这种方式下,执行DDL语句的时候会锁表,且无法执行DML语句;再看下inplace的方式,

1.2、inplace方式(仅针对索引创建、删除)

这种方式仅对索引的创建、删除有效,其他类型的DDL还是使用copy的方式,其步骤如下,

  1. 新建frm临时文件;
  2. 锁住原表,不允许DDL,允许查询;
  3. 按照聚集索引的顺序查询数据,找到需要的索引列数据,排序后插入到新的索引中;
  4. 原表禁止读操作,也就是原表此时不提供读写服务;
  5. 进行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

 

posted @ 2023-07-17 17:09  良工说技术  阅读(2894)  评论(4编辑  收藏  举报