[資料庫]MySQL總結三部曲（一）

加瓦一枚發表於2019-02-21
資料庫MySql

基礎架構：一條 SQL 查詢語句是如何執行的？

MySQL 的基本架構示意圖：

MySQL 可以分為 Server 層和儲存引擎層兩部分。

Server 層包括聯結器、查詢快取、分析器、優化器、執行器等，涵蓋 MySQL 的大多數核心服務功能，以及所有的內建函式（如日期、時間、數學和加密函式等），所有跨儲存引擎的功能都在這一層實現，比如儲存過程、觸發器、檢視等。

儲存引擎層負責資料的儲存和提取。其架構模式是外掛式的，支援 InnoDB、MyISAM、Memory 等多個儲存引擎。現在最常用的儲存引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了預設儲存引擎。

不同儲存引擎的表資料存取方式不同，支援的功能也不同。不同的儲存引擎共用一個Server 層，也就是從聯結器到執行器的部分。

聯結器

聯結器負責跟客戶端建立連線、獲取許可權、維持和管理連線。

查詢快取

MySQL 拿到一個查詢請求後，會先到查詢快取看看，之前是不是執行過這條語句。之前執行過的語句及其結果可能會以 key-value 對的形式，被直接快取在記憶體中。key 是查詢的語句，value 是查詢的結果。如果你的查詢能夠直接在這個快取中找到 key，那麼這個 value 就會被直接返回給客戶端。

但是大多數情況下我會建議你不要使用查詢快取，為什麼呢？因為查詢快取往往弊大於利。

查詢快取的失效非常頻繁，只要有對一個表的更新，這個表上所有的查詢快取都會被清空。因此很可能你費勁地把結果存起來，還沒使用呢，就被一個更新全清空了。對於更新壓力大的資料庫來說，查詢快取的命中率會非常低。除非你的業務就是有一張靜態表，很長時間才會更新一次。比如，一個系統配置表，那這張表上的查詢才適合使用查詢快取。

MySQL 8.0 版本直接將查詢快取的整塊功能刪掉了，也就是說 8.0 開始徹底沒有這個功能了。

分析器

如果沒有命中查詢快取，就要開始真正執行語句了。

分析器先會做“詞法分析”。做完了這些識別以後，就要做“語法分析”。

優化器

優化器是在表裡面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯（join）的時候，決定各個表的連線順序。

優化器階段完成後，這個語句的執行方案就確定下來了，然後進入執行器階段。

執行器

開啟表的時候，執行器就會根據表的引擎定義，去使用這個引擎提供的介面。

你會在資料庫的慢查詢日誌中看到一個 rows_examined 的欄位，表示這個語句執行過程中掃描了多少行。這個值就是在執行器每次呼叫引擎獲取資料行的時候累加的。

在有些場景下，執行器呼叫一次，在引擎內部則掃描了多行，因此引擎掃描行數跟 rows_examined 並不是完全相同的。

我給你留一個問題吧，如果表 T 中沒有欄位 k，而你執行了這個語句 select * from T where k=1, 那肯定是會報“不存在這個列”的錯誤： “Unknown column ‘k’ in ‘where clause’”。你覺得這個錯誤是在我們上面提到的哪個階段報出來的呢？

答案是分析器。

日誌系統：一條 SQL 更新語句是如何執行的？

與查詢流程不一樣的是，更新流程還涉及兩個重要的日誌模組，它們正是我們今天要討論的主角：redo log（重做日誌）和 binlog（歸檔日誌）。

重要的日誌模組：redo log

MySQL 裡經常說到的 WAL 技術，WAL 的全稱是 Write-Ahead Logging，它的關鍵點就是先寫日誌，再寫磁碟。

 

具體來說，當有一條記錄需要更新的時候，InnoDB 引擎就會先把記錄寫到 redo log（粉板）裡面，並更新記憶體，這個時候更新就算完成了。同時，InnoDB 引擎會在適當的時候，將這個操作記錄更新到磁碟裡面，而這個更新往往是在系統比較空閒的時候做。

與此類似，InnoDB 的 redo log 是固定大小的，比如可以配置為一組 4 個檔案，每個檔案的大小是 1GB，那麼這塊“粉板”總共就可以記錄 4GB 的操作。從頭開始寫，寫到末尾就又回到開頭迴圈寫。

write pos 是當前記錄的位置，一邊寫一邊後移，寫到第 3 號檔案末尾後就回到 0 號檔案開頭。checkpoint 是當前要擦除的位置，也是往後推移並且迴圈的，擦除記錄前要把記錄更新到資料檔案。

write pos 和 checkpoint 之間的是“粉板”上還空著的部分，可以用來記錄新的操作。如果 write pos 追上 checkpoint，表示“粉板”滿了，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把 checkpoint 推進一下。

有了 redo log，InnoDB 就可以保證即使資料庫發生異常重啟，之前提交的記錄都不會丟失，這個能力稱為crash-safe。

重要的日誌模組：binlog

redo log 是 InnoDB 引擎特有的日誌，而 Server 層也有自己的日誌，稱為 binlog（歸檔日誌）。

為什麼會有兩份日誌呢？

因為最開始 MySQL 裡並沒有 InnoDB 引擎。MySQL 自帶的引擎是 MyISAM，但是 MyISAM 沒有 crash-safe 的能力，binlog 日誌只能用於歸檔。而 InnoDB 是另一個公司以外掛形式引入 MySQL 的，既然只依靠 binlog 是沒有 crash-safe 能力的，所以 InnoDB 使用另外一套日誌系統——也就是 redo log 來實現 crash-safe 能力。

這兩種日誌有以下三點不同。

  • 1 redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實現的，所有引擎都可以使用。

  • 2 redo log 是物理日誌，記錄的是“在某個資料頁上做了什麼修改”；binlog 是邏輯日誌，記錄的是這個語句的原始邏輯，比如“給 ID=2 這一行的 c 欄位加 1 ”。

  • 3 redo log 是迴圈寫的，空間固定會用完；binlog 是可以追加寫入的。“追加寫”是指 binlog 檔案寫到一定大小後會切換到下一個，並不會覆蓋以前的日誌。

有了對這兩個日誌的概念性理解，我們再來看執行器和 InnoDB 引擎在執行這個簡單的 update 語句時的內部流程。

  • 1 執行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜尋找到這一行。如果 ID=2 這一行所在的資料頁本來就在記憶體中，就直接返回給執行器；否則，需要先從磁碟讀入記憶體，然後再返回。

  • 2 執行器拿到引擎給的行資料，把這個值加上 1，比如原來是 N，現在就是 N+1，得到新的一行資料，再呼叫引擎介面寫入這行新資料。

  • 3 引擎將這行新資料更新到記憶體中，同時將這個更新操作記錄到 redo log 裡面，此時 redo log 處於 prepare 狀態。然後告知執行器執行完成了，隨時可以提交事務。

  • 4 執行器生成這個操作的 binlog，並把 binlog 寫入磁碟。

  • 5 執行器呼叫引擎的提交事務介面，引擎把剛剛寫入的 redo log 改成提交（commit）狀態，更新完成。

update 語句執行流程：

將 redo log 的寫入拆成了兩個步驟：prepare 和 commit，這就是"兩階段提交"。

兩階段提交

前面我們說過了，binlog 會記錄所有的邏輯操作，並且是採用“追加寫”的形式。

由於 redo log 和 binlog 是兩個獨立的邏輯如果不使用“兩階段提交”，那麼資料庫的狀態就有可能和用它的日誌恢復出來的庫的狀態不一致。

小結

redo log 用於保證 crash-safe 能力。innodbflushlogattrx_commit 這個引數設定成 1 的時候，表示每次事務的 redo log 都直接持久化到磁碟。這個引數我建議你設定成 1，這樣可以保證 MySQL 異常重啟之後資料不丟失。

sync_binlog 這個引數設定成 1 的時候，表示每次事務的 binlog 都持久化到磁碟。這個引數我也建議你設定成 1，這樣可以保證 MySQL 異常重啟之後 binlog 不丟失。

事務隔離：為什麼你改了我還看不見？

簡單來說，事務就是要保證一組資料庫操作，要麼全部成功，要麼全部失敗。在 MySQL 中，事務支援是在引擎層實現的。你現在知道，MySQL 是一個支援多引擎的系統，但並不是所有的引擎都支援事務。比如 MySQL 原生的 MyISAM 引擎就不支援事務，這也是 MyISAM 被 InnoDB 取代的重要原因之一。

隔離性與隔離級別

在談隔離級別之前，你首先要知道，你隔離得越嚴實，效率就會越低。SQL 標準的事務隔離級別包括：讀未提交（read uncommitted）、讀提交（read committed）、可重複讀（repeatable read）和序列化（serializable ）。SQL 標準的事務隔離級別包括：

  • 讀未提交是指，一個事務還沒提交時，它做的變更就能被別的事務看到。

  • 讀提交是指，一個事務提交之後，它做的變更才會被其他事務看到。

  • 可重複讀是指，一個事務執行過程中看到的資料，總是跟這個事務在啟動時看到的資料是一致的。當然在可重複讀隔離級別下，未提交變更對其他事務也是不可見的。

  • 序列化，顧名思義是對於同一行記錄，“寫”會加“寫鎖”，“讀”會加“讀鎖”。當出現讀寫鎖衝突的時候，後訪問的事務必須等前一個事務執行完成，才能繼續執行。

  • 若隔離級別是“讀未提交”， 則 V1 的值就是 2。這時候事務 B 雖然還沒有提交，但是結果已經被 A 看到了。因此，V2、V3 也都是 2。

  • 若隔離級別是“讀提交”，則 V1 是 1，V2 的值是 2。事務 B 的更新在提交後才能被 A 看到。所以， V3 的值也是 2。

  • 若隔離級別是“可重複讀”，則 V1、V2 是 1，V3 是 2。之所以 V2 還是 1，遵循的就是這個要求：事務在執行期間看到的資料前後必須是一致的。

  • 若隔離級別是“序列化”，則在事務 B 執行“將 1 改成 2”的時候，會被鎖住。直到事務 A 提交後，事務 B 才可以繼續執行。所以從 A 的角度看，V1、V2 值是 1，V3 的值是 2。

在實現上，資料庫裡面會建立一個檢視，訪問的時候以檢視的邏輯結果為準。在“可重複讀”隔離級別下，這個檢視是在事務啟動時建立的，整個事務存在期間都用這個檢視。在“讀提交”隔離級別下，這個檢視是在每個 SQL 語句開始執行的時候建立的。這裡需要注意的是，“讀未提交”隔離級別下直接返回記錄上的最新值，沒有檢視概念；而“序列化”隔離級別下直接用加鎖的方式來避免並行訪問。

Oracle 資料庫的預設隔離級別其實就是“讀提交”。

事務啟動時的檢視可以認為是靜態的，不受其他事務更新的影響。

事務隔離的實現

在 MySQL 中，實際上每條記錄在更新的時候都會同時記錄一條回滾操作。記錄上的最新值，通過回滾操作，都可以得到前一個狀態的值。

同一條記錄在系統中可以存在多個版本，就是資料庫的多版本併發控制（MVCC）。

你一定會問，回滾日誌總不能一直保留吧，什麼時候刪除呢？答案是，在不需要的時候才刪除。也就是說，系統會判斷，當沒有事務再需要用到這些回滾日誌時，回滾日誌會被刪除。

什麼時候才不需要了呢？就是當系統裡沒有比這個回滾日誌更早的 read-view 的時候。

長事務意味著系統裡面會存在很老的事務檢視。由於這些事務隨時可能訪問資料庫裡面的任何資料，所以這個事務提交之前，資料庫裡面它可能用到的回滾記錄都必須保留，這就會導致大量佔用儲存空間。

在 MySQL 5.5 及以前的版本，回滾日誌是跟資料字典一起放在 ibdata 檔案裡的，即使長事務最終提交，回滾段被清理，檔案也不會變小。

除了對回滾段的影響，長事務還佔用鎖資源。

事務的啟動方式

MySQL 的事務啟動方式有以下幾種：

  • 1 顯式啟動事務語句， begin 或 start transaction。配套的提交語句是 commit，回滾語句是 rollback。

  • 2 set autocommit=0，這個命令會將這個執行緒的自動提交關掉。

有些客戶端連線框架會預設連線成功後先執行一個 set autocommit=0 的命令。這就導致接下來的查詢都在事務中，如果是長連線，就導致了意外的長事務。

因此，我會建議你總是使用 set autocommit=1, 通過顯式語句的方式來啟動事務。

但是有的開發同學會糾結“多一次互動”的問題。對於一個需要頻繁使用事務的業務，第二種方式每個事務在開始時都不需要主動執行一次 “begin”，減少了語句的互動次數。

深入淺出索引

索引的出現其實就是為了提高資料查詢的效率，就像書的目錄一樣。

索引的常見模型

索引的出現是為了提高查詢效率，但是實現索引的方式卻有很多種，所以這裡也就引入了索引模型的概念。簡單的資料結構，它們分別是雜湊表、有序陣列和搜尋樹。

雜湊表是一種以鍵 - 值（key-value）儲存資料的結構，我們只要輸入待查詢的值即 key，就可以找到其對應的值即 Value。

不可避免地，多個 key 值經過雜湊函式的換算，會出現同一個值的情況。處理這種情況的一種方法是，拉出一個連結串列。

有序陣列索引只適用於靜態儲存引擎。

二叉搜尋樹的特點是：每個節點的左兒子小於父節點，父節點又小於右兒子。

當然為了維持 O(log(N)) 的查詢複雜度，你就需要保持這棵樹是平衡二叉樹。為了做這個保證，更新的時間複雜度也是 O(log(N))。

樹可以有二叉，也可以有多叉。多叉樹就是每個節點有多個兒子，兒子之間的大小保證從左到右遞增。二叉樹是搜尋效率最高的，但是實際上大多數的資料庫儲存卻並不使用二叉樹。其原因是，索引不止存在記憶體中，還要寫到磁碟上。

為了讓一個查詢儘量少地讀磁碟，就必須讓查詢過程訪問儘量少的資料塊。那麼，我們就不應該使用二叉樹，而是要使用“N 叉”樹。這裡，“N 叉”樹中的“N”取決於資料塊的大小。

以 InnoDB 的一個整數字段索引為例，這個 N 差不多是 1200。這棵樹高是 4 的時候，就可以存 1200 的 3 次方個值，這已經 17 億了。

N 叉樹由於在讀寫上的效能優點，以及適配磁碟的訪問模式，已經被廣泛應用在資料庫引擎中了。

在 MySQL 中，索引是在儲存引擎層實現的，所以並沒有統一的索引標準，即不同儲存引擎的索引的工作方式並不一樣。而即使多個儲存引擎支援同一種型別的索引，其底層的實現也可能不同。

InnoDB 的索引模型

在 InnoDB 中，表都是根據主鍵順序以索引的形式存放的，這種儲存方式的表稱為索引組織表。InnoDB 使用了 B+ 樹索引模型，所以資料都是儲存在 B+ 樹中的。

 

每一個索引在 InnoDB 裡面對應一棵 B+ 樹。索引型別分為主鍵索引和非主鍵索引。

主鍵索引的葉子節點存的是整行資料。在 InnoDB 裡，主鍵索引也被稱為聚簇索引（clustered index）。

 

非主鍵索引的葉子節點內容是主鍵的值。在 InnoDB 裡，非主鍵索引也被稱為二級索引（secondary index）。

基於主鍵索引和普通索引的查詢有什麼區別？

  • 如果語句是 select * from T where ID=500，即主鍵查詢方式，則只需要搜尋 ID 這棵 B+ 樹；

  • 如果語句是 select * from T where k=5，即普通索引查詢方式，則需要先搜尋 k 索引樹，得到 ID 的值為 500，再到 ID 索引樹搜尋一次。這個過程稱為回表。

也就是說，基於非主鍵索引的查詢需要多掃描一棵索引樹。

索引維護

而更糟的情況是，如果 R5 所在的資料頁已經滿了，根據 B+ 樹的演算法，這時候需要申請一個新的資料頁，然後挪動部分資料過去。這個過程稱為頁分裂。

當相鄰兩個頁由於刪除了資料，利用率很低之後，會將資料頁做合併。合併的過程，可以認為是分裂過程的逆過程。

假設你的表中確實有一個唯一欄位，比如字串型別的身份證號，那應該用身份證號做主鍵，還是用自增欄位做主鍵呢？

由於每個非主鍵索引的葉子節點上都是主鍵的值。如果用身份證號做主鍵，那麼每個二級索引的葉子節點佔用約 20 個位元組，而如果用整型做主鍵，則只要 4 個位元組，如果是長整型（bigint）則是 8 個位元組。

顯然，主鍵長度越小，普通索引的葉子節點就越小，普通索引佔用的空間也就越小。

有沒有什麼場景適合用業務欄位直接做主鍵的呢？還是有的。比如，有些業務的場景需求是這樣的：

  • 1 只有一個索引；

  • 2 該索引必須是唯一索引。

你一定看出來了，這就是典型的 KV 場景。

由於沒有其他索引，所以也就不用考慮其他索引的葉子節點大小的問題。

回到主鍵索引樹搜尋的過程，我們稱為回表。

覆蓋索引

如果執行的語句是 select ID from T where k between 3 and 5，這時只需要查 ID 的值，而 ID 的值已經在 k 索引樹上了，因此可以直接提供查詢結果，不需要回表。也就是說，在這個查詢裡面，索引 k 已經“覆蓋了”我們的查詢需求，我們稱為覆蓋索引。

由於覆蓋索引可以減少樹的搜尋次數，顯著提升查詢效能，所以使用覆蓋索引是一個常用的效能優化手段。

最左字首原則

B+ 樹這種索引結構，可以利用索引的“最左字首”，來定位記錄。索引項是按照索引定義裡面出現的欄位順序排序的。

在建立聯合索引的時候，如何安排索引內的欄位順序。

第一原則是，如果通過調整順序，可以少維護一個索引，那麼這個順序往往就是需要優先考慮採用的。

索引下推

在 MySQL 5.6 之前，只能從 ID3 開始一個個回表。到主鍵索引上找出資料行，再對比欄位值。

MySQL 5.6 引入的索引下推優化（index condition pushdown)， 可以在索引遍歷過程中，對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

圖3

圖4

在圖 3 和 4 這兩個圖裡面，每一個虛線箭頭表示回表一次。

圖 3 中，在 (name,age) 索引裡面我特意去掉了 age 的值，這個過程 InnoDB 並不會去看 age 的值，只是按順序把“name 第一個字是’張’”的記錄一條條取出來回表。因此，需要回表 4 次。

圖 4 跟圖 3 的區別是，InnoDB 在 (name,age) 索引內部就判斷了 age 是否等於 10，對於不等於 10 的記錄，直接判斷並跳過。在我們的這個例子中，只需要對 ID4、ID5 這兩條記錄回表取資料判斷，就只需要回表 2 次。

全域性鎖和表鎖：給表加個欄位怎麼有這麼多阻礙？

資料庫鎖設計的初衷是處理併發問題。當出現併發訪問的時候，資料庫需要合理地控制資源的訪問規則。而鎖就是用來實現這些訪問規則的重要資料結構。

根據加鎖的範圍，MySQL 裡面的鎖大致可以分成全域性鎖、表級鎖和行鎖三類。

全域性鎖

顧名思義，全域性鎖就是對整個資料庫例項加鎖。MySQL 提供了一個加全域性讀鎖的方法，命令是 Flush tables with read lock (FTWRL)。當你需要讓整個庫處於只讀狀態的時候，可以使用這個命令，之後其他執行緒的以下語句會被阻塞：資料更新語句（資料的增刪改）、資料定義語句（包括建表、修改表結構等）和更新類事務的提交語句。

全域性鎖的典型使用場景是，做全庫邏輯備份。

但是讓整庫都只讀，聽上去就很危險：

  • 如果你在主庫上備份，那麼在備份期間都不能執行更新，業務基本上就得停擺；

  • 如果你在從庫上備份，那麼備份期間從庫不能執行主庫同步過來的 binlog，會導致主從延遲。

一致性讀是好，但前提是引擎要支援這個隔離級別。比如，對於 MyISAM 這種不支援事務的引擎，如果備份過程中有更新，總是隻能取到最新的資料，那麼就破壞了備份的一致性。這時，我們就需要使用 FTWRL 命令了。

如果有的表使用了不支援事務的引擎，那麼備份就只能通過 FTWRL 方法。這往往是 DBA 要求業務開發人員使用 InnoDB 替代 MyISAM 的原因之一。

業務的更新不只是增刪改資料（DML)，還有可能是加欄位等修改表結構的操作（DDL）。不論是哪種方法，一個庫被全域性鎖上以後，你要對裡面任何一個表做加欄位操作，都是會被鎖住的。

表級鎖

MySQL 裡面表級別的鎖有兩種：一種是表鎖，一種是後設資料鎖（meta data lock，MDL)。

表鎖的語法是 lock tables … read/write。與 FTWRL 類似，可以用 unlock tables 主動釋放鎖，也可以在客戶端斷開的時候自動釋放。需要注意，lock tables 語法除了會限制別的執行緒的讀寫外，也限定了本執行緒接下來的操作物件。

舉個例子, 如果在某個執行緒 A 中執行 lock tables t1 read, t2 write; 這個語句，則其他執行緒寫 t1、讀寫 t2 的語句都會被阻塞。同時，執行緒 A 在執行 unlock tables 之前，也只能執行讀 t1、讀寫 t2 的操作。連寫 t1 都不允許，自然也不能訪問其他表。

而對於 InnoDB 這種支援行鎖的引擎，一般不使用 lock tables 命令來控制併發，畢竟鎖住整個表的影響面還是太大。

另一類表級的鎖是 MDL（metadata lock)。MDL 不需要顯式使用，在訪問一個表的時候會被自動加上。MDL 的作用是，保證讀寫的正確性。你可以想象一下，如果一個查詢正在遍歷一個表中的資料，而執行期間另一個執行緒對這個表結構做變更，刪了一列，那麼查詢執行緒拿到的結果跟表結構對不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL，當對一個表做增刪改查操作的時候，加 MDL 讀鎖；當要對錶做結構變更操作的時候，加 MDL 寫鎖。

  • 讀鎖之間不互斥，因此你可以有多個執行緒同時對一張表增刪改查。

  • 讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性。因此，如果有兩個執行緒要同時給一個表加欄位，其中一個要等另一個執行完才能開始執行。

你肯定知道，給一個表加欄位，或者修改欄位，或者加索引，需要掃描全表的資料。在對大表操作的時候，你肯定會特別小心，以免對線上服務造成影響。

我們可以看到 session A 先啟動，這時候會對錶 t 加一個 MDL 讀鎖。由於 session B 需要的也是 MDL 讀鎖，因此可以正常執行。

 

之後 session C 會被 blocked，是因為 session A 的 MDL 讀鎖還沒有釋放，而 session C 需要 MDL 寫鎖，因此只能被阻塞。

 

如果只有 session C 自己被阻塞還沒什麼關係，但是之後所有要在表 t 上新申請 MDL 讀鎖的請求也會被 session C 阻塞。前面我們說了，所有對錶的增刪改查操作都需要先申請 MDL 讀鎖，就都被鎖住，等於這個表現在完全不可讀寫了。

事務中的 MDL 鎖，在語句執行開始時申請，但是語句結束後並不會馬上釋放，而會等到整個事務提交後再釋放。

如何安全地給小表加欄位？

首先我們要解決長事務，事務不提交，就會一直佔著 MDL 鎖。

小結

全域性鎖主要用在邏輯備份過程中。對於全部是 InnoDB 引擎的庫，我建議你選擇使用–single-transaction 引數，對應用會更友好。

表鎖一般是在資料庫引擎不支援行鎖的時候才會被用到的。如果你發現你的應用程式裡有 lock tables 這樣的語句，你需要追查一下，比較可能的情況是：

  • 要麼是你的系統現在還在用 MyISAM 這類不支援事務的引擎，那要安排升級換引擎；

  • 要麼是你的引擎升級了，但是程式碼還沒升級。我見過這樣的情況，最後業務開發就是把 lock tables 和 unlock tables 改成 begin 和 commit，問題就解決了。

MDL 會直到事務提交才釋放，在做表結構變更的時候，你一定要小心不要導致鎖住線上查詢和更新。

我給你留一個問題吧，備份一般都會在備庫上執行，你在用–single-transaction 方法做邏輯備份的過程中，如果主庫上的一個小表做了一個 DDL，比如給一個表上加了一列。這時候，從備庫上會看到什麼現象呢？


 

參考答案如下：

如果在 Q4 語句執行之前到達，現象：沒有影響，備份拿到的是 DDL 後的表結構。

如果在“時刻 2”到達，則表結構被改過，Q5 執行的時候，報 Table definition has changed, please retry transaction，現象：mysqldump 終止；

如果在“時刻 2”和“時刻 3”之間到達，mysqldump 佔著 t1 的 MDL 讀鎖，binlog 被阻塞，現象：主從延遲，直到 Q6 執行完成。

從“時刻 4”開始，mysqldump 釋放了 MDL 讀鎖，現象：沒有影響，備份拿到的是 DDL 前的表結構。

行鎖功過：怎麼減少行鎖對效能的影響？

MySQL 的行鎖是在引擎層由各個引擎自己實現的。但並不是所有的引擎都支援行鎖，比如 MyISAM 引擎就不支援行鎖。不支援行鎖意味著併發控制只能使用表鎖。InnoDB 是支援行鎖的，這也是 MyISAM 被 InnoDB 替代的重要原因之一。（innodb行級鎖是通過鎖索引記錄實現的。）

顧名思義，行鎖就是針對資料表中行記錄的鎖。這很好理解，比如事務 A 更新了一行，而這時候事務 B 也要更新同一行，則必須等事務 A 的操作完成後才能進行更新。

從兩階段鎖說起

在下面的操作序列中，事務 B 的 update 語句執行時會是什麼現象呢？假設欄位 id 是表 t 的主鍵。

你可以驗證一下：實際上事務 B 的 update 語句會被阻塞，直到事務 A 執行 commit 之後，事務 B 才能繼續執行。

知道了這個答案，你一定知道了事務 A 持有的兩個記錄的行鎖，都是在 commit 的時候才釋放的。

也就是說，在 InnoDB 事務中，行鎖是在需要的時候才加上的，但並不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖協議。

死鎖和死鎖檢測

當併發系統中不同執行緒出現迴圈資源依賴，涉及的執行緒都在等待別的執行緒釋放資源時，就會導致這幾個執行緒都進入無限等待的狀態，稱為死鎖。

這時候，事務 A 在等待事務 B 釋放 id=2 的行鎖，而事務 B 在等待事務 A 釋放 id=1 的行鎖。 事務 A 和事務 B 在互相等待對方的資源釋放，就是進入了死鎖狀態。當出現死鎖以後，有兩種策略：

  • 一種策略是，直接進入等待，直到超時。這個超時時間可以通過引數 innodblockwait_timeout 來設定。

  • 另一種策略是，發起死鎖檢測，發現死鎖後，主動回滾死鎖鏈條中的某一個事務，讓其他事務得以繼續執行。將引數 innodbdeadlockdetect 設定為 on，表示開啟這個邏輯。

在 InnoDB 中，innodblockwait_timeout 的預設值是 50s，意味著如果採用第一個策略，當出現死鎖以後，第一個被鎖住的執行緒要過 50s 才會超時退出，然後其他執行緒才有可能繼續執行。對於線上服務來說，這個等待時間往往是無法接受的。

所以，超時時間設定太短的話，會出現很多誤傷。

所以，正常情況下我們還是要採用第二種策略，即：主動死鎖檢測，而且 innodbdeadlockdetect 的預設值本身就是 on。主動死鎖檢測在發生死鎖的時候，是能夠快速發現並進行處理的，但是它也是有額外負擔的。

每個新來的被堵住的執行緒，都要判斷會不會由於自己的加入導致了死鎖，這是一個時間複雜度是 O(n) 的操作。假設有 1000 個併發執行緒要同時更新同一行，那麼死鎖檢測操作就是 100 萬這個量級的。雖然最終檢測的結果是沒有死鎖，但是這期間要消耗大量的 CPU 資源。

問題的癥結在於，死鎖檢測要耗費大量的 CPU 資源。

一種頭痛醫頭的方法，就是如果你能確保這個業務一定不會出現死鎖，可以臨時把死鎖檢測關掉。

另一個思路是控制併發度。根據上面的分析，你會發現如果併發能夠控制住，比如同一行同時最多隻有 10 個執行緒在更新，那麼死鎖檢測的成本很低，就不會出現這個問題。一個直接的想法就是，在客戶端做併發控制。但是，你會很快發現這個方法不太可行，因為客戶端很多。我見過一個應用，有 600 個客戶端，這樣即使每個客戶端控制到只有 5 個併發執行緒，彙總到資料庫服務端以後，峰值併發數也可能要達到 3000。

因此，這個併發控制要做在資料庫服務端。

小結

調整語句順序並不能完全避免死鎖。所以我們引入了死鎖和死鎖檢測的概念，以及提供了三個方案，來減少死鎖對資料庫的影響。減少死鎖的主要方向，就是控制訪問相同資源的併發事務量。

我給你留一個問題吧，如果你要刪除一個表裡面的前 10000 行資料，有以下三種方法可以做到：

  • 第一種，直接執行 delete from T limit 10000;

  • 第二種，在一個連線中迴圈執行 20 次 delete from T limit 500;

  • 第三種，在 20 個連線中同時執行 delete from T limit 500。

你會選擇哪一種方法呢？為什麼呢？

確實是這樣的，第二種方式是相對較好的。

第一種方式（即：直接執行 delete from T limit 10000）裡面，單個語句佔用時間長，鎖的時間也比較長；而且大事務還會導致主從延遲。

第三種方式（即：在 20 個連線中同時執行 delete from T limit 500），會人為造成鎖衝突。

事務到底是隔離的還是不隔離的？


 

begin/start transaction 命令並不是一個事務的起點，在執行到它們之後的第一個操作 InnoDB 表的語句，事務才真正啟動。如果你想要馬上啟動一個事務，可以使用 start transaction with consistent snapshot 這個命令。

在這個例子中，事務 C 沒有顯式地使用 begin/commit，表示這個 update 語句本身就是一個事務，語句完成的時候會自動提交。事務 B 在更新了行之後查詢 ; 事務 A 在一個只讀事務中查詢，並且時間順序上是在事務 B 的查詢之後。

這時，如果我告訴你事務 B 查到的 k 的值是 3，而事務 A 查到的 k 的值是 1。

在 MySQL 裡，有兩個“檢視”的概念：

  • 一個是 view。它是一個用查詢語句定義的虛擬表，在呼叫的時候執行查詢語句並生成結果。建立檢視的語法是 create view … ，而它的查詢方法與表一樣。

  • 另一個是 InnoDB 在實現 MVCC 時用到的一致性讀檢視，即 consistent read view，用於支援 RC（Read Committed，讀提交）和 RR（Repeatable Read，可重複讀）隔離級別的實現。

它沒有物理結構，作用是事務執行期間用來定義“我能看到什麼資料”。

“快照”在 MVCC 裡是怎麼工作的？

在可重複讀隔離級別下，事務在啟動的時候就“拍了個快照”。注意，這個快照是基於整庫的。

 

InnoDB 裡面每個事務有一個唯一的事務 ID，叫作 transaction id。它是在事務開始的時候向 InnoDB 的事務系統申請的，是按申請順序嚴格遞增的。

而每行資料也都是有多個版本的。每次事務更新資料的時候，都會生成一個新的資料版本，並且把 transaction id 賦值給這個資料版本的事務 ID，記為 row trx_id。同時，舊的資料版本要保留，並且在新的資料版本中，能夠有資訊可以直接拿到它。

也就是說，資料表中的一行記錄，其實可能有多個版本 (row)，每個版本有自己的 row trx_id。

圖中虛線框裡是同一行資料的 4 個版本，當前最新版本是 V4，k 的值是 22，它是被 transaction id 為 25 的事務更新的，因此它的 row trx_id 也是 25。

實際上，圖 2 中的三個虛線箭頭，就是 undo log；而 V1、V2、V3 並不是物理上真實存在的，而是每次需要的時候根據當前版本和 undo log 計算出來的。比如，需要 V2 的時候，就是通過 V4 依次執行 U3、U2 算出來。

按照可重複讀的定義，一個事務啟動的時候，能夠看到所有已經提交的事務結果。但是之後，這個事務執行期間，其他事務的更新對它不可見。

因此，一個事務只需要在啟動的時候宣告說，“以我啟動的時刻為準，如果一個資料版本是在我啟動之前生成的，就認；如果是我啟動以後才生成的，我就不認，我必須要找到它的上一個版本”。

當然，如果“上一個版本”也不可見，那就得繼續往前找。

在實現上， InnoDB 為每個事務構造了一個陣列，用來儲存這個事務啟動瞬間，當前正在“活躍”的所有事務 ID。“活躍”指的就是，啟動了但還沒提交。

陣列裡面事務 ID 的最小值記為低水位，當前系統裡面已經建立過的事務 ID 的最大值加 1 記為高水位。

這個檢視陣列和高水位，就組成了當前事務的一致性檢視（read-view）。

而資料版本的可見性規則，就是基於資料的 row trx_id 和這個一致性檢視的對比結果得到的。

這樣，對於當前事務的啟動瞬間來說，一個資料版本的 row trx_id，有以下幾種可能：

如果落在綠色部分，表示這個版本是已提交的事務或者是當前事務自己生成的，這個資料是可見的；

如果落在紅色部分，表示這個版本是由將來啟動的事務生成的，是肯定不可見的；

如果落在黃色部分，那就包括兩種情況：

  • a. 若 row trx_id 在陣列中，表示這個版本是由還沒提交的事務生成的，不可見；

  • b. 若 row trx_id 不在陣列中，表示這個版本是已經提交了的事務生成的，可見。

因為之後的更新，生成的版本一定屬於上面的 2 或者 3(a) 的情況，而對它來說，這些新的資料版本是不存在的，所以這個事務的快照，就是“靜態”的了。

所以你現在知道了，InnoDB 利用了“所有資料都有多個版本”的這個特性，實現了“秒級建立快照”的能力。

這裡，我們不妨做如下假設：

  • 事務 A 開始前，系統裡面只有一個活躍事務 ID 是 99；

  • 事務 A、B、C 的版本號分別是 100、101、102，且當前系統裡只有這四個事務；

  • 三個事務開始前，(1,1）這一行資料的 row trx_id 是 90。

這樣，事務 A 的檢視陣列就是 [99,100], 事務 B 的檢視陣列是 [99,100,101], 事務 C 的檢視陣列是 [99,100,101,102]。

從圖中可以看到，第一個有效更新是事務 C，把資料從 (1,1) 改成了 (1,2)。這時候，這個資料的最新版本的 row trx_id 是 102，而 90 這個版本已經成為了歷史版本。

第二個有效更新是事務 B，把資料從 (1,2) 改成了 (1,3)。這時候，這個資料的最新版本（即 row trx_id）是 101，而 102 又成為了歷史版本。

你可能注意到了，在事務 A 查詢的時候，其實事務 B 還沒有提交，但是它生成的 (1,3) 這個版本已經變成當前版本了。但這個版本對事務 A 必須是不可見的，否則就變成髒讀了。

好，現在事務 A 要來讀資料了，它的檢視陣列是 [99,100]。當然了，讀資料都是從當前版本讀起的。所以，事務 A 查詢語句的讀資料流程是這樣的：

  • 找到 (1,3) 的時候，判斷出 row trx_id=101，比高水位大，處於紅色區域，不可見；

  • 接著，找到上一個歷史版本，一看 row trx_id=102，比高水位大，處於紅色區域，不可見；

  • 再往前找，終於找到了（1,1)，它的 row trx_id=90，比低水位小，處於綠色區域，可見。

這樣執行下來，雖然期間這一行資料被修改過，但是事務 A 不論在什麼時候查詢，看到這行資料的結果都是一致的，所以我們稱之為一致性讀。

一個資料版本，對於一個事務檢視來說，除了自己的更新總是可見以外，有三種情況：

  • 版本未提交，不可見；

  • 版本已提交，但是是在檢視建立後提交的，不可見；

  • 版本已提交，而且是在檢視建立前提交的，可見。

現在，我們用這個規則來判斷圖 4 中的查詢結果，事務 A 的查詢語句的檢視陣列是在事務 A 啟動的時候生成的，這時候：

  • (1,3) 還沒提交，屬於情況 1，不可見；

  • (1,2) 雖然提交了，但是是在檢視陣列建立之後提交的，屬於情況 2，不可見；

  • (1,1) 是在檢視陣列建立之前提交的，可見。

更新邏輯

你看圖 5 中，事務 B 的檢視陣列是先生成的，之後事務 C 才提交，不是應該看不見 (1,2) 嗎，怎麼能算出 (1,3) 來？

是的，如果事務 B 在更新之前查詢一次資料，這個查詢返回的 k 的值確實是 1。

但是，當它要去更新資料的時候，就不能再在歷史版本上更新了，否則事務 C 的更新就丟失了。因此，事務 B 此時的 set k=k+1 是在（1,2）的基礎上進行的操作。

所以，這裡就用到了這樣一條規則：更新資料都是先讀後寫的，而這個讀，只能讀當前的值，稱為“當前讀”（current read）。

因此，在更新的時候，當前讀拿到的資料是 (1,2)，更新後生成了新版本的資料 (1,3)，這個新版本的 row trx_id 是 101。

其實，除了 update 語句外，select 語句如果加鎖，也是當前讀。

再往前一步，假設事務 C 不是馬上提交的，而是變成了下面的事務 C’，會怎麼樣呢？

事務 C’的不同是，更新後並沒有馬上提交，在它提交前，事務 B 的更新語句先發起了。前面說過了，雖然事務 C’還沒提交，但是 (1,2) 這個版本也已經生成了，並且是當前的最新版本。那麼，事務 B 的更新語句會怎麼處理呢？

事務 C’沒提交，也就是說 (1,2) 這個版本上的寫鎖還沒釋放。而事務 B 是當前讀，必須要讀最新版本，而且必須加鎖，因此就被鎖住了，必須等到事務 C’釋放這個鎖，才能繼續它的當前讀。

事務的可重複讀的能力是怎麼實現的？

可重複讀的核心就是一致性讀（consistent read）；而事務更新資料的時候，只能用當前讀。如果當前的記錄的行鎖被其他事務佔用的話，就需要進入鎖等待。

而讀提交的邏輯和可重複讀的邏輯類似，它們最主要的區別是：

  • 在可重複讀隔離級別下，只需要在事務開始的時候建立一致性檢視，之後事務裡的其他查詢都共用這個一致性檢視；

  • 在讀提交隔離級別下，每一個語句執行前都會重新算出一個新的檢視。

那麼，我們再看一下，在讀提交隔離級別下，事務 A 和事務 B 的查詢語句查到的 k，分別應該是多少呢？

下面是讀提交時的狀態圖，可以看到這兩個查詢語句的建立檢視陣列的時機發生了變化，就是圖中的 read view 框。

這時，事務 A 的查詢語句的檢視陣列是在執行這個語句的時候建立的，時序上 (1,2)、(1,3) 的生成時間都在建立這個檢視陣列的時刻之前。但是，在這個時刻：

  • (1,3) 還沒提交，屬於情況 1，不可見；

  • (1,2) 提交了，屬於情況 3，可見。

所以，這時候事務 A 查詢語句返回的是 k=2。

顯然地，事務 B 查詢結果 k=3。

小結

InnoDB 的行資料有多個版本，每個資料版本有自己的 row trxid，每個事務或者語句有自己的一致性檢視。普通查詢語句是一致性讀，一致性讀會根據 row trxid 和一致性檢視確定資料版本的可見性。

  • 對於可重複讀，查詢只承認在事務啟動前就已經提交完成的資料；

  • 對於讀提交，查詢只承認在語句啟動前就已經提交完成的資料；

而當前讀，總是讀取已經提交完成的最新版本。

當然，MySQL 8.0 已經可以把表結構放在 InnoDB 字典裡了，也許以後會支援表結構的可重複讀。

相關文章