暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Mysql Online DDL Support for Column Operations

Mysql Online DDL Support for Column Operations

扫地僧的故事 2020-08-06
1489
今天为什么会写这个呢？是因为前段时间，有个朋友问我，"我有个字段是timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，现在想把它改成timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,不需要自动更新时间，表有200多w条记录吧，这个操作会不会锁表？会锁很久吗？会不会引发数据库性能问题？"
每当说到ddl的时候，总是有点敬畏之心。。在我的理解中，这只是一个修改表定义的操作，只需要拿到mdl锁，改下表定义就OK了。测试了下，果然0.00sec就执行完成了。
所以，今天想起来这个事儿，又特意去翻了翻书，看了看官方文档，梳理下知识点。

上面这个表格，其实已经很清楚详细的说明了。（但也不能啥都不写，就这么结束了吧。。）
在Mysql 5.5及以前的版本中，很多DDL操作的代价都很高，因为很多场景在处理时都是一个copy的过程，并且这个过程是全程加锁的，如果对一张大表做DDL，那可以说是一场灾难。。（其实我接触的Mysql最老的版本也就5.6。）
从Mysql 5.6版本开始，推出了online DDL，用于支持DDL执行期间DML的并行操作，提高数据库的吞吐量。
在online DDL中，是官方在内部自定义线程来实现的。主要原理是把整个过程分为了基线和增量两个部分。其中会开启一个线程来变更基线数据，同时将增量数据写入row-log，在基线变更结束后，回放row-log，实现增量同步。
在实现中是分成三个阶段：prepare，ddl和commit。其中prepare阶段会获取快照，生成相应的.frm,.ibd文件，同时持有EXCLUSIVE-MDL锁，禁止读写。在DDL阶段会降级EXCLUSIVE-MDL锁，这个时候会允许读写，同时不断处理增量数据，使得数据尽可能保持同步。在commit阶段会升级为EXCLUSIVE-MDL锁，禁止读写，处理最新的增量数据，然后更新数据字段，使得schema配置生效。  （摘自《MYSQL DBA 工作笔记》）
Online DDL的两种算法：copy 和 inplace。
一、copy(ALGORITHM=COPY)
Mysql5.6版本前的DDL执行方法，这其实是offline ddl了，在DDL执行期间其他DML不能并行执行。其间生成临时表，用于写入原表修改后的数据，同时在原表路径下会生成临时表的.frm和.ibd文件。在innodb中，不支持inplace的操作都会自动使用copy方式执行，而MyISAM表只能用copy的方式。
二、inplace(ALGORITHM=INPLACE)
所有操作都是在innodb引擎层完成的，不需要经过临时表的中转。除了创建fulltext索引和spatial索引，其他以inpace方式执行的操作都是online的，执行期间，其他DML操作可以并行。
其中，又以是否重建表分为两个部分rebuild 和 no-rebuild。

  • rebuild部分涉及表的重建，在原表路径下创建新的.frm和.ibd文件，消耗的IO会较多。期间（原表可以修改）会申请row log空间记录DDL执行期间的DML操作，这部分操作会在DDL提交阶段应用新的表空间中。

  •  no-rebuild部分由于不涉及表的重建，除创建添加索引，会产生部分二级索引的写入操作外，其余操作均只修改元数据项，即只在原表路径下产生.frm文件，不会申请row log，不会消耗过多的IO，速度通常很快。 

所以，再看上面那张图，Mysql中所有列操作场景中，只有修改字段类型不是inplace的操作，这个在生产中，绝对要谨慎处理！
网上看到的一张图，供大家参考：

为什么要改这个字段呢，那就继续讲一个Mysql中比较有趣的参数。explicit_defaults_for_timestamp=OFF

Mysql的CREATE TABLE语句中，timestamp列可以用下面的任何一种方式声明：

  • 如果定义时DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP子句都有，列值为默认使用当前的时间戳，并且自动更新;

  • 如果不使用DEFAULT或ON UPDATE子句，那么它等同于DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

  • 如果只有DEFAULT CURRENT_TIMESTAMP子句，而没有ON UPDATE子句，列值默认为当前时间戳但不自动更新;

  • 如果没用DEFAULT子句，但有ON UPDATE CURRENT_TIMESTAMP子句，列默认为0并自动更新;

  • 如果有一个常量值DEFAULT，该列会有一个默认值，而且不会自动初始化为当前时间戳。如果该列还有一个ON UPDATE CURRENT_TIMESTAMP子句，这个时间戳会自动更新，否则该列有一个默认的常量但不会自动更新。

好像经常有人掉这个坑，Oracle中是没有这个功能的，我想没有在Mysql中掉过坑的Oracle DBA应该很难想到这个特性。。不过8.0以后这个参数默认为ON啦。

🔚

mysql
文章转载自扫地僧的故事，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论