关于mysql:记录一次-Online-DDL-操作

记录一次 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