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

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了。