共计 2535 个字符，预计需要花费 7 分钟才能阅读完成。
记录一次 Online DDL 操作
为反对用户账号删除性能，须要在 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 transaction -
kill 掉对应的线程以及事务
$ 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
