一、環(huán)境

為支持用戶賬號(hào)刪除功能，需要在 user 表上加一個(gè)字段 deleted。

數(shù)據(jù)庫(kù)：Mysql5.6

被 操作表 user：數(shù)量級(jí)為100w，外鍵200多個(gè)

操作：alter table user add deleted boolean NOT NULL default false comment '用戶注銷標(biāo)識(shí)' , algorithm=inplace, lock=none;

二、執(zhí)行過(guò)程分析

在Mysql5.6之后，mysql支持 Online DDL 操作。

Online DDL Support for Column Operations

如圖所示，所執(zhí)行的添加列操作整個(gè)過(guò)程為：

• 初始化：為創(chuàng)建臨時(shí)表的表結(jié)構(gòu)，獲取MDL的排他鎖
• 執(zhí)行：根據(jù)參數(shù) algorithm=inplace, lock=none ，MDL鎖降級(jí)為共享鎖進(jìn)行數(shù)據(jù)拷貝
• 提交：由于涉及到增量備份和臨時(shí)表的重命名，MDL鎖需要升級(jí)為排他鎖

分析后認(rèn)為，整個(gè)過(guò)程只有在初始化和提交的極短過(guò)程內(nèi)需要用到MDL排他鎖（影響線上），故而就直接在線上進(jìn)行操作嘗試。

三、遇到的問(wèn)題

在初始化和提交階段需要用到MDL的排他鎖，而如果DDL操作一直沒(méi)獲取MDL鎖（默認(rèn)獲取MDL鎖超時(shí)時(shí)間為一年），那么就會(huì)造成 Waiting for table metadata lock 狀態(tài)，也會(huì)阻塞后面所有對(duì) user 表的操作（包括select）。后面會(huì)看到連接占滿，服務(wù)502：

在遇到這樣的問(wèn)題后，為不影響線上，于是后面進(jìn)行了工具的嘗試。

四、工具嘗試

工具涉及到 pt-online-schema-change、gh-ost和阿里云無(wú)鎖DDL。三個(gè)工具大同小異，均為使用臨時(shí)表，將原表數(shù)據(jù)拷貝到臨時(shí)表，最后將臨時(shí)表重命名替換掉原表。區(qū)別是在增量同步方面，一個(gè)用的觸發(fā)器、一個(gè)用的binlog日志。

但是在處理外鍵方面，pt-online-schema-change用的刪除、重建外鍵，gh-ost和阿里云無(wú)鎖DDL則是不支持主表外鍵的變更。eg：阿里云無(wú)鎖DDL的失敗嘗試

故使用工具進(jìn)行 DDL 操作也不適合。

五、Online DDL 嘗試

在本地測(cè)試30w數(shù)據(jù)新增列只需440ms后，嘗試選擇了 online ddl 的操作：

設(shè)置 session 級(jí)別獲取 MDL 鎖等待時(shí)間時(shí)間，避免長(zhǎng)時(shí)間阻塞其他線程

$ set lock_wait_timeout=10;
# 在10s內(nèi)獲取不到MDL鎖，直接退出 alter 操作
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

kill 掉對(duì)應(yīng)的線程以及事務(wù)

$ 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)

執(zhí)行 DDL 操作：

$ alter table user add deleted boolean NOT NULL default false comment '用戶注銷標(biāo)識(shí)' , algorithm=inplace, lock=none;

結(jié)果：

以上是在停服后操作的結(jié)果，其中 2 操作在停服后，沒(méi)有需要 kill 的 ID。

參考文獻(xiàn)

InnoDB and Online DDL

以上就是MySql Online DDL操作記錄詳解的詳細(xì)內(nèi)容，更多關(guān)于MySql Online DDL操作的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論