MySQL學習筆記-如何安全的給小表加字段

如果要給一個大表加字段，你一般都會非常謹慎小心，以免對線上業務造成影響，但實際上給一個小表加字段不慎操作也會導致線上業務出問題，這篇文章主要學習一下MySQL中MDL鎖對性能的影響，防止采坑。

1.筆記圖

2.全局鎖

• 全局鎖就是對整個數據庫實例加鎖
• 命令：

Flush tables with read lock (FTWRL)

• 全局鎖現象：數據更新語句（數據的增刪改）、數據定義語句（包括建表、修改表結構等）和更新類事務的提交語句會被阻塞

3.數據邏輯備份的方式

• 加全局鎖(MyISAM)：
  • 通過FTWRL確保不會有其他線程對數據庫做更新，然后對整個庫做備份
  • 如果在主庫上備份，備份期間都不能執行更新
  • 如果在從庫上備份，備份期間從庫不能執行主庫同步過來的binlog，會導致主從延遲
  • 備份為什么要加鎖？：
    • 假設現在要維護購買系統，用戶賬戶余額表和用戶課程表
    • 發起一個邏輯備份，不加鎖的話，備份系統備份的得到的庫不是一個邏輯時間點
• 事務(InnoDB)：
  • 方自帶的邏輯備份工具是mysqldump
  • mysqldump使用single-transaction參數，導數據之前就會啟動一個事務，拿到一致性視圖
  • 由于MVCC的支持，這個過程中數據是可以正常更新的

4.表級鎖

• 表鎖：
  • 加表鎖的語法是lock tables … read/write
    • 如果在某個線程A中執行lock tables t1 read, t2 write這個語句，則其他線程寫t1、讀寫t2的語句都會被阻塞
    • 同時，線程A在執行unlock tables之前，也只能執行讀t1、讀寫t2的操作，不能訪問其他表
    • 對于InnoDB這種支持行鎖的引擎，一般不使用lock tables命令來控制并發
    • 可以用unlock tables主動釋放鎖，也可以在客戶端斷開的時候自動釋放
• 元數據鎖（meta data lock，MDL)
  • 如果一個查詢正在遍歷一個表中的數據，執行期間另一個線程對這個表結構做變更，刪了一列，查詢線程拿到的結果跟表結構對不上，肯定是不行的
  • 在訪問一個表的時候會被自動加上MDL鎖，其作用是保證讀寫的正確性
  • 當對一個表做增刪改查操作的時候，加MDL讀鎖，讀鎖之間不互斥，可以有多個線程同時對一張表增刪改查
  • 當要對表做結構變更操作的時候，加MDL寫鎖，讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性

5.給一個小表加個字段，導致整個庫掛了

• 現象：給一個小表加個字段，導致整個庫掛了

• 給一個表加字段，或者修改字段，或者加索引，需要掃描全表的數據，一般在對大表操作的時候，會特別小心，以免對線上服務造成影響，實際上，即使是小表，操作不慎也會出問題
  

• session A先啟動，這時候會對表t加一個MDL讀鎖

• session B需要的也是MDL讀鎖，因此可以正常執行

• session A的MDL讀鎖還沒有釋放，session C需要MDL寫鎖，因此只能被阻塞

• 之后所有要在表t上新申請MDL讀鎖的請求也會被session C阻塞，所有對表的增刪改查操作都需要先申請MDL讀鎖，這個表此時完全不可讀寫

6.如何安全地給小表加字段？

• 事務中的MDL鎖，在語句執行開始時申請，但是語句結束后并不會馬上釋放，而會等到整個事務提交后再釋放
• 首先要解決長事務，事務不提交，會一直占著MDL鎖，如果要做DDL變更的表剛好有長事務在執行，要考慮先暫停DDL，或者kill掉這個長事務
• 如果要變更的表是一個熱點表，請求很頻繁，不得不加個字段，該怎么做呢？
  • 在 alter table 語句里面設定等待時間，在這個指定的等待時間里面能夠拿到MDL寫鎖最好，拿不到也不要阻塞后面的業務語句，先放棄
  • 再通過重試命令重復這個過程
  • MariaDB已經合并了AliSQL的這個功能，所以這兩個開源分支目前都支持DDL NOWAIT/WAIT n這個語法

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

7.課后問題

當備庫用single-transaction做邏輯備份的時候，如果從主庫的binlog傳來一個DDL語句會怎么樣？

• 從庫備份過程中幾個關鍵的語句：

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 時刻 1 */
Q4:show create table `t1`;
/* 時刻 2 */
Q5:SELECT * FROM `t1`;
/* 時刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 時刻 4 */
/* other tables */

Tips：Q1表示為了確保RR（可重復讀）隔離級別，設置RR隔離級別(Q1)，Q2表示WITH CONSISTENT SNAPSHOT這個語句執行完就可以得到一個一致性視圖（Q2)，Q3表示設置一個保存點，Q4表示show create是為了拿到表結構，Q5表示正式導數據，Q6表示回滾到SAVEPOINT sp，作用是釋放t1的MDL鎖。

• 如果主庫DDL命令在Q4之前到達從庫，沒有影響
• 如果主庫DDL在時刻2到達從庫，此時會有MDL寫鎖，會造成Q5執行失敗，備份過程被終止
• 如果主庫DDL在時刻3到達從庫，由于備份過程占用讀鎖，會導致binlog復制被阻塞，會造成主從延遲，直到備份完成Q6執行之后從庫才能繼續復制主庫binlog
• 如果主庫DDL在時刻4到達從庫，沒有影響