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='用戶表';

有同學(xué)會(huì)說，這還不簡(jiǎn)單，直接加不加完了，用下面的命令：

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

添加完，再查看一下表結(jié)構(gòu)：

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='用戶表';

這不是添加成功了嗎？有什么呀！

是的，線下數(shù)據(jù)庫怎么整都行，但是如果在線上數(shù)據(jù)庫這樣操作，整個(gè)服務(wù)都有宕機(jī)的風(fēng)險(xiǎn)！自己也離畢業(yè)不遠(yuǎn)了。

不是危言聳聽，我們找個(gè)case測(cè)試一下：

1. Session1啟動(dòng)了一個(gè)事務(wù)，沒有提交。
2. Session2執(zhí)行添加列的操作，被阻塞。
3. 更嚴(yán)重的是，Session3執(zhí)行簡(jiǎn)單查詢的語句也被阻塞了。

2. 線上服務(wù)宕機(jī)的原因

為什么會(huì)出現(xiàn)這種情況呢？

原因是在執(zhí)行查詢語句的時(shí)候，MySQL自動(dòng)加了MDL鎖（metadata lock，即元數(shù)據(jù)鎖）。

不行的話，我們可以再執(zhí)行一下show processlist命令，查看有哪些正在執(zhí)行的進(jìn)程：

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

MDL鎖的作用是什么？

為了保證并發(fā)操作下數(shù)據(jù)的一致性。

如果一個(gè)事務(wù)正在執(zhí)行中，另一個(gè)在這時(shí)修改了表結(jié)構(gòu)，不但可能導(dǎo)致當(dāng)前事務(wù)出現(xiàn)不可重復(fù)讀的問題，還有可能連事務(wù)都無法提交。

什么時(shí)候會(huì)加MDL鎖？

MDL鎖是MySQL自動(dòng)隱式加鎖，無需我們手動(dòng)操作。

在我們執(zhí)行DDL語句的時(shí)候，MySQL自動(dòng)添加MDL讀鎖。

在我們執(zhí)行DML語句的時(shí)候，MySQL自動(dòng)添加MDL寫鎖。

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

注意：MDL鎖是表鎖，會(huì)對(duì)整張表加鎖。

普及額外的小知識(shí)點(diǎn)，什么是DML和DDL：

DML（Data Manipulation Language）數(shù)據(jù)操縱語言：

適用范圍：對(duì)表數(shù)據(jù)進(jìn)行操作，比如 insert、delete、select、update等。

DDL（Data Definition Language）數(shù)據(jù)定義語言：

適用范圍：對(duì)表結(jié)構(gòu)進(jìn)行操作，比如create、drop、alter、rename、truncate等。

3. 如何優(yōu)雅的給線上表加字段

既然修改表結(jié)構(gòu)的時(shí)候，MySQL會(huì)自動(dòng)添加表鎖，并且是寫鎖，會(huì)阻塞后續(xù)的所有讀寫請(qǐng)求，造成非常嚴(yán)重的后果。

還有沒有辦法能優(yōu)雅的給線上表添加字段呢？

當(dāng)然有，從MySQL5.6版本開始增加了Online DDL，作用就是在執(zhí)行DDL的時(shí)候，允許并發(fā)執(zhí)行DML。簡(jiǎn)單翻譯就是修改表結(jié)構(gòu)的時(shí)候，也能同時(shí)支持并發(fā)執(zhí)行增刪查改操作。

從MySQL8.0版本開始又優(yōu)化了Online DDL，支持快速添加列，可以實(shí)現(xiàn)給大表秒級(jí)加字段。

具體用法就是在DDL語句后面增加兩個(gè)參數(shù)ALGORITHM和LOCK。

比如下面這樣：

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

這兩個(gè)參數(shù)分別是干嘛用的？有哪些選項(xiàng)呢？

ALGORITHM可以指定使用哪種算法執(zhí)行DDL，可選項(xiàng)有：

• Copy：
• 拷貝方式，MySQL5.6 之前 DDL 的執(zhí)行方式，過程就是先創(chuàng)建新表，修改新表結(jié)構(gòu)，把舊表數(shù)據(jù)復(fù)制到新表，刪除舊表，重命名新表。執(zhí)行過程非常耗時(shí)，產(chǎn)生大量的磁盤IO和占用CPU，還有使Buffer poll失效，而且需要鎖住舊表，性能較差，現(xiàn)在基本很少使用。
• Inplace：
• 原地修改，MySQL5.6開始引入的，優(yōu)點(diǎn)是不會(huì)在Server層發(fā)生表數(shù)據(jù)拷貝，過程中允許并發(fā)執(zhí)行DML操作。過程就是先添加MDL寫鎖，執(zhí)行初始化操作，然后降級(jí)為MDL讀鎖，執(zhí)行DDL操作（比較耗時(shí)，允許并發(fā)執(zhí)行DML操作），升級(jí)為MDL寫鎖，完成DDL操作。
• Instant：
• 快速修改，MySQL8.0開始引入的，可以實(shí)現(xiàn)快速給大表添加字段。

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

LOCK可以指定執(zhí)行過程中，是否加鎖，可選項(xiàng)有：

• NONE
• 不加鎖，允許DML操作。
• SHARED
• 加讀鎖，允許讀操作，禁止DML操作。
• DEFAULT
• 默認(rèn)鎖模式，在滿足DDL操作前提下，默認(rèn)鎖模式會(huì)允許盡可能多的讀操作和DML操作。
• EXCLUSIVE
• 加寫鎖，禁止讀操作和DML操作。

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

像最常見的添加列就可以使用Instant，而像刪除列、重命名列、更改列數(shù)據(jù)類型就只能使用Inplace了。