技術文章 » 線上服務宕機，碼農試用期被畢業，原因竟是給MySQL加個欄位

線上服務宕機，碼農試用期被畢業，原因竟是給MySQL加個欄位

2022-10-08 18:01:52

1. 問題：怎麼給線上表加欄位？

工作中最常遇到的問題，怎麼給線上頻繁使用的大表新增欄位？

比如：給下面的使用者表（user）新增年齡（age）欄位。

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(100) DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='使用者表';

有同學會說，這還不簡單，直接加不加完了，用下面的命令：

ALTER TABLE `user` ADD `age` int NOT NULL DEFAULT '0' COMMENT '年齡';

新增完，再檢視一下表結構：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(100) DEFAULT NULL COMMENT '姓名',
  `age` int NOT NULL DEFAULT '0' COMMENT '年齡',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='使用者表';

這不是新增成功了嗎？有什麼呀！

是的，線下資料庫怎麼整都行，但是如果線上上資料庫這樣操作，整個服務都有宕機的風險！自己也離畢業不遠了。

不是危言聳聽，我們找個case測試一下：

  1. Session1啟動了一個事務，沒有提交。
  2. Session2執行新增列的操作，被阻塞。
  3. 更嚴重的是，Session3執行簡單查詢的語句也被阻塞了。

2. 線上服務宕機的原因

為什麼會出現這種情況呢？

原因是在執行查詢語句的時候，MySQL自動加了MDL鎖（metadata lock，即後設資料鎖）。

不行的話，我們可以再執行一下show processlist命令，檢視有哪些正在執行的程序：

可以清楚的看到Session2和Session3的語句正在等待MDL鎖，Waiting for table metadata lock。

MDL鎖的作用是什麼？

為了保證並行操作下資料的一致性。

如果一個事務正在執行中，另一個在這時修改了表結構，不但可能導致當前事務出現不可重複讀的問題，還有可能連事務都無法提交。

什麼時候會加MDL鎖？

MDL鎖是MySQL自動隱式加鎖，無需我們手動操作。

在我們執行DDL語句的時候，MySQL自動新增MDL讀鎖。

在我們執行DML語句的時候，MySQL自動新增MDL寫鎖。

讀鎖與讀鎖之間不互斥，讀鎖與寫鎖、寫鎖與寫鎖之間互斥。

注意：MDL鎖是表鎖，會對整張表加鎖。

普及額外的小知識點，什麼是DML和DDL：

DML（Data Manipulation Language）資料操縱語言：

適用範圍：對錶資料進行操作，比如 insert、delete、select、update等。

DDL（Data Definition Language）資料定義語言：

適用範圍：對錶結構進行操作，比如create、drop、alter、rename、truncate等。

3. 如何優雅的給線上表加欄位

既然修改表結構的時候，MySQL會自動新增表鎖，並且是寫鎖，會阻塞後續的所有讀寫請求，造成非常嚴重的後果。

還有沒有辦法能優雅的給線上表新增欄位呢？

當然有，從MySQL5.6版本開始增加了Online DDL，作用就是在執行DDL的時候，允許並行執行DML。簡單翻譯就是修改表結構的時候，也能同時支援並行執行增刪查改操作。

從MySQL8.0版本開始又優化了Online DDL，支援快速新增列，可以實現給大表秒級加欄位。

具體用法就是在DDL語句後面增加兩個引數ALGORITHM和LOCK。

比如下面這樣：

ALTER TABLE `user` ADD `age` int NOT NULL DEFAULT '0' COMMENT '年齡', 
ALGORITHM=Inplace, 
LOCK=NONE;

這兩個引數分別是幹嘛用的？有哪些選項呢？

ALGORITHM可以指定使用哪種演演算法執行DDL，可選項有：

  • Copy：

    拷貝方式，MySQL5.6 之前 DDL 的執行方式，過程就是先建立新表，修改新表結構，把舊錶資料複製到新表，刪除舊錶，重新命名新表。執行過程非常耗時，產生大量的磁碟IO和佔用CPU，還有使Buffer poll失效，而且需要鎖住舊錶，效能較差，現在基本很少使用。

  • Inplace：

    原地修改，MySQL5.6開始引入的，優點是不會在Server層發生表資料拷貝，過程中允許並行執行DML操作。過程就是先新增MDL寫鎖，執行初始化操作，然後降級為MDL讀鎖，執行DDL操作（比較耗時，允許並行執行DML操作），升級為MDL寫鎖，完成DDL操作。

  • Instant：

    快速修改，MySQL8.0開始引入的，可以實現快速給大表新增欄位。

效能依次是，Instant > Inplace > Copy。

LOCK可以指定執行過程中，是否加鎖，可選項有：

  • NONE

    不加鎖，允許DML操作。

  • SHARED

    加讀鎖，允許讀操作，禁止DML操作。

  • DEFAULT

    預設鎖模式，在滿足DDL操作前提下，預設鎖模式會允許儘可能多的讀操作和DML操作。

  • EXCLUSIVE

    加寫鎖，禁止讀操作和DML操作。

Online DDL並不是支援所有DDL操作，看一下到底支援哪些操作？

操作 Instant Inplace Rebuilds Table 允許並行DML 僅修改後設資料
新增列 Yes Yes No Yes No
刪除列 No Yes Yes Yes No
重新命名列 No Yes No Yes Yes
更改列順序 No Yes Yes Yes No
設定列預設值 Yes Yes No Yes Yes
更改列資料型別 No No Yes No No
設定VARCHAR列大小 No Yes No Yes Yes
刪除列預設值 Yes Yes No Yes Yes
更改自動增量值 No Yes No Yes No
設定列為null No Yes Yes Yes No
設定列not null No Yes Yes Yes No

像最常見的新增列就可以使用Instant，而像刪除列、重新命名列、更改列資料型別就只能使用Inplace了。