記錄一次 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

記錄一次 Online DDL 操作