MySqlOnlineDDL操作记录详解
一、环境
为支持用户账号删除功能，需要在 user 表上加一个字段 deleted。
数据库：Mysql5.6
被 操作表 user：数量级为100w，外键200多个
操作：alter table user add deleted boolean NOT NULL default false comment '用户注销标识' , algorithm=inplace, lock=none;
二、执行过程分析
在Mysql5.6之后，mysql支持 Online DDL 操作。
Online DDL Support for Column Operations
| Operation | In Place | Rebuilds Table | Permits Concurrent DML | Only Modifies Metadata |
|---|---|---|---|---|
| Adding a column | Yes | Yes | Yes* | No |
| Dropping a column | Yes | Yes | Yes | No |
| Renaming a column | Yes | No | Yes* | Yes |
| Reordering columns | Yes | Yes | Yes | No |
| Setting a column default value | Yes | No | Yes | Yes |
| Changing the column data type | No | Yes | No | No |
| Dropping the column default value | Yes | No | Yes | Yes |
| Changing the auto-increment value | Yes | No | Yes | No* |
| Making a column NULL | Yes | Yes* | Yes | No |
| Making a column NOT NULL | Yes* | Yes* | Yes | No |
| Modifying the definition of an ENUM or SET column | Yes | No | Yes | Yes |
如图所示，所执行的添加列操作整个过程为：
- 初始化：为创建临时表的表结构，获取MDL的排他锁
- 执行：根据参数 algorithm=inplace, lock=none ，MDL锁降级为共享锁进行数据拷贝
- 提交：由于涉及到增量备份和临时表的重命名，MDL锁需要升级为排他锁
分析后认为，整个过程只有在初始化和提交的极短过程内需要用到MDL排他锁（影响线上），故而就直接在线上进行操作尝试。
三、遇到的问题
在初始化和提交阶段需要用到MDL的排他锁，而如果DDL操作一直没获取MDL锁（默认获取MDL锁超时时间为一年），那么就会造成 Waiting for table metadata lock 状态，也会阻塞后面所有对 user 表的操作（包括select）。后面会看到连接占满，服务502：
在遇到这样的问题后，为不影响线上，于是后面进行了工具的尝试。
四、工具尝试
工具涉及到 pt-online-schema-change、gh-ost和阿里云无锁DDL。三个工具大同小异，均为使用临时表，将原表数据拷贝到临时表，最后将临时表重命名替换掉原表。区别是在增量同步方面，一个用的触发器、一个用的binlog日志。
但是在处理外键方面，pt-online-schema-change用的删除、重建外键，gh-ost和阿里云无锁DDL则是不支持主表外键的变更。eg：阿里云无锁DDL的失败尝试
故使用工具进行 DDL 操作也不适合。
五、Online DDL 尝试
在本地测试30w数据新增列只需440ms后，尝试选择了 online ddl 的操作：
设置 session 级别获取 MDL 锁等待时间时间，避免长时间阻塞其他线程
$ set lock_wait_timeout=10;
# 在10s内获取不到MDL锁，直接退出 alter 操作
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionkill 掉对应的线程以及事务
$ select group_concat(stat separator ' ') from (select concat('kill query ',id,';') as stat from information_schema.processlist where command != 'Sleep' and Time > 5 order by Time desc) as stats;
+-------------------------------------+
| group_concat(stat separator ' ') |
+-------------------------------------+
| kill query 42510; kill query 42514; |
+-------------------------------------+
1 row in set (0.00 sec)
$ select group_concat(stat separator ' ') from (select concat('kill ',trx_mysql_thread_id,';') as stat from information_schema.innodb_trx order by trx_started desc) as stats;
+-------------------------------------------------------------------------+
| group_concat(stat separator ' ') |
+-------------------------------------------------------------------------+
| kill 42436; kill 42435; kill 42521; kill 42511; kill 42510; kill 42483; |
+-------------------------------------------------------------------------+
1 row in set (0.01 sec)执行 DDL 操作：
$ alter table user add deleted boolean NOT NULL default false comment '用户注销标识' , algorithm=inplace, lock=none;结果：
以上是在停服后操作的结果，其中 2 操作在停服后，没有需要 kill 的 ID。
参考文献
InnoDB and Online DDL
以上就是MySql Online DDL操作记录详解的详细内容，更多关于MySql Online DDL操作的资料请关注编程网其它相关文章！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
