有一次在线上提了一个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

 

• 还没有人评论，欢迎说说您的想法！