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測驗一下：
- Session1啟動了一個事務，沒有提交，
- Session2執行添加列的操作，被阻塞，
- 更嚴重的是，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了，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/511101.html
標籤：其他