線上服務宕機，碼農試用期被畢業，原因竟是給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測試一下：

image

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

2. 線上服務宕機的原因

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

原因是在執行查詢語句的時候，MySQL自動加了MDL鎖（metadata lock，即元數據鎖）。

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

image

可以清楚的看到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了。

image

Tags:

VirMach 便宜 VPS

QNews

QNews