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