深入解讀MySQL InnoDB儲存引擎Update語句執行過程

參考b站up 戌米的論文筆記 https://www.bilibili.com/video/BV1Tv4y1o7tA/
書籍《mysql是怎樣執行的》
極客時間《mysql實戰45講》

系列文章目錄和關於我

一丶Mysql整體架構

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

1.Server 層

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

聯結器

聯結器負責跟使用者端建立連線、獲取許可權、維持和管理連線
查詢快取

對於查詢語句，mysql server層會將查詢語句和對應的結果，使用key - value的快取結構進行快取，但是一旦發生更新，那麼查詢快取就需要失效。因此查詢快取在高版本的mysql中已經被移除
分析器

分析器先會做「詞法分析」。你輸入的是由多個字串和空格組成的一條 SQL 語句，MySQL 需要識別出裡面的字串分別是什麼，代表什麼。

然後做「語法分析」。根據詞法分析的結果，語法分析器會根據語法規則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法。
優化器

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

MySQL 通過分析器知道了你要做什麼，通過優化器知道了該怎麼做，於是就進入了執行器階段，開始執行語句。開始執行的時候，要先判斷一下你對這個表 T 有沒有執行查詢的許可權，如果沒有，就會返回沒有許可權的錯誤，如果有許可權，就開啟表繼續執行。開啟表的時候，執行器就會根據表的引擎定義，去使用這個引擎提供的介面。

2.儲存引擎層

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

	MyISAM	InnoDB
儲存結構	Myisam 建立表後生成的檔案有三個，分別為： frm:建立表的語句 MYD:表裡面的資料檔案（myisam data） MYI:表裡面的索引檔案（myisam index）	Innodb 建立表後生成的檔案有兩個，分別為： frm:建立表的語句 idb:表裡面的資料+索引檔案
索引	`非聚集索引`，MyISAM 是非聚集索引，資料檔案是分離的，索引儲存的是資料檔案的指標。主鍵索引和輔助索引是獨立的。	`聚集索引`，聚集索引的檔案存放在主鍵索引的葉子節點上
事務支援	`不提供事務支援`	`提供事務支援`
鎖的粒度	`只支援表級鎖`	`支援行級鎖`。
儲存表的具體行數	儲存表的總行數，如果select count() from table;會直接取出出該值。	沒有儲存表的總行數，如果使用select count(*) from table；就會遍歷整個表，消耗相當大，但是在加了wehre條件後，myisam和innodb處理的方式都一樣。

以下的分析針對 update t set a='1' where 主鍵 = 1這條語句

二丶開啟事務

在mysql中，無論使用者是否手動開啟一個事務，sql都是在一個事務中進行的。我們可以使用start transaction開啟一個事務，commit提交事務，rollback回滾事務。

預設情況下，mysql存在自動提交(autocommit=1)，這時候即使我們沒有顯式開啟事務，直接執行update語句，那麼mysql會隱式的開啟一個事務，並在這條update執行結束後自動提交

如果set autocommit = 0 或者顯式開啟了一個事務，那麼update執行結束後不會自動提交，而是需要手動發起commit 或者rollback

無論式顯式事務，還是隱式事務，mysql都會在事務內部第一次執行增刪改操作的時候，給事務分配一個事務號

三丶Sql解析，查詢計劃生成

mysql伺服器層會從連線中讀取sql語句，然後進行詞法解析，語法解析，查詢優化（為什麼update語句需要查詢優化？不查出來怎麼知道修改哪些行資料暱）最終生成一個AST樹，這便是物理執行計劃，執行器會根據執行計劃，呼叫儲存引擎的介面，

四丶查詢需要修改的資料

Mysql InnoDB Buffer Pool

Mysql索引(究極無敵細節版

mysql innodb儲存引擎對磁碟的讀取是以頁為單位的，為了避免每次都從磁碟讀取資料，innodb存在buffer pool使用LRU連結串列維護最近存取到的頁，為了更快的從buffer pool中查詢到目標頁，innodb 還使用表空間號和頁號作為key，頁作為value，形成Hash表。如果我們目標頁已經在buffer pool中那麼直接返回目標頁，如果不在那麼需要進行磁碟io載入目標頁到記憶體，然後快取到buffer pool中

1.buffer pool是如何維護頁在記憶體中的

buffer poo中存在三個關鍵的連結串列結構

Free List 空閒連結串列，連結串列中將空閒緩衝頁的控制塊（控制塊中記錄了緩衝頁的位置）進行串聯，用於管理未被使用的緩衝池空間
LRU List，最近最少使用連結串列，利用LRU演演算法在buffer pool滿後淘汰冷門資料頁（innodb 為了應對預讀，全表掃描，對應LRU連結串列進行了改進，分成young區，和old區，解決預讀：innodb規定當磁碟某個頁面在初次載入到buffer pool中某個緩衝頁時，該緩衝頁對應的控制塊會放在old區域的頭部，這樣預讀到的且後續如果不進行後續存取的頁面會逐漸從old區移除，而不影響young區使用頻率高的緩衝頁。，解決全表掃描：nnodb規定對於某個處於old區的緩衝頁第一次存取時，就在其控制塊中記錄下存取時間，如果後續存取的時間和第一次存取的時間，在某個時間存取間隔內（innodb_old_blocks_time可以進行設定）那麼頁面不會從old區移動到young區，反之移動到young區中。這個時間間隔預設時1000ms，基本上多次存取同一個頁面中的多個記錄的時間不會超過1s，解決熱門資料經常需要移動到LRU鏈頭部的問題： innodb規定只有被存取的緩衝頁位於young區的前1/4範圍外，才會進行移動，所以前1/4的高熱度的資料，不會頻繁移動）
Flush List，髒鏈，維護在buffer pool中進行了修改，後續需要重新整理到磁碟的緩衝頁資訊，innodb修改後的頁不會立即刷盤，而是使用Flush list記錄，後臺存線上程定時進行刷髒

2.怎麼從16k的頁中找到目標資料

結合B+樹索引結構，執行引擎根據頁號找到根節點，然後根據根節點中的索引資料進行比較，找到子節點，重複此過程直至找到葉子節點所在的頁。

到了葉子節點所在的頁後，根據葉子節點頁中的Page Dictionary中的槽找到目標記錄所在的組，然後遍歷這一組中的記錄，找到目標記錄。如果是範圍查詢，還需要根據B+樹葉子節點間的雙向指標繼續查詢，直到找到不符合要求的記錄位置。（為了避免我們在遍歷B+樹的時候，其他執行緒修改了B+樹的結構，此過程還需要對B+樹進行加閂鎖）（詳細可看 Mysql索引(究極無敵細節版中的InnoDB索引方案一節）

五丶檢驗鎖和加鎖

Mysql 鎖

1.Mysql中的鎖

後設資料鎖MDL，mysql伺服器層的MDL主要是避免運算元據的同時存在另外執行緒修改表結構，實現二者的互斥
innodb表鎖
- 表級S鎖，X鎖
  
  使用Lock Tables t Read，innodb儲存引擎會對錶t加共用鎖
  
  使用Lock tables t write，innodb儲存引擎會對錶t加獨佔鎖
- 表級意向鎖：
  
  innodb儲存引擎中，當對錶中某些記錄加S鎖之前，會在表上加上一個IS鎖，同樣加X鎖之前會加表級IX鎖，這裡的I表示意向鎖，S or X表示共用還是互斥，表級意向鎖存在的目的是後續對錶加S鎖，X鎖的時候，可以快速判斷表中是否存在加鎖的記錄，避免遍歷每一個記錄檢視是否被加鎖。
innodb 行鎖
- Record Lock
官方名稱Lock_REC_NOT_GAP

記錄鎖有S鎖和X鎖，S型記錄鎖之間可以共用，X型記錄鎖和S型記錄鎖，X型記錄鎖互斥
- GAP Lock
innodb的可重複讀級別，使用詞鎖解決幻讀問題，前面我們說過，其難點在於，加鎖的時候幻影記錄還未出現。官方使用Lock_GAP實現如下操作

此處的gap鎖可以反之其他事務在number為8記錄前面的間隙插入新的記錄，在區間（3，8）內無法進行插入操作，當另外一個事務要插入number為4的記錄時，首先需要定位到該條記錄的下一條記錄，也就是number為8的記錄，此時number為8的記錄具備gap鎖，所以將阻塞插入操作，直到gap鎖被釋放，其他事務才能進行插入。
gap鎖出現的目的，就是為了防止插入幻影記錄，如果對記錄上gap鎖，並不會限制其他事務對記錄加記錄鎖。

innodb有兩個虛擬的記錄Infimum(虛擬最小)，Supermun(虛擬最大)當我們想在(xx,正無窮)範圍鎖住幻影記錄時就可以對Supermun加gap鎖。
- Next-Key Lock
Next-Key Lock = 記錄鎖 + gap鎖，既鎖住記錄，也鎖住記錄之前的間隙
- Insert Intention Lock
插入意向鎖，表示事務想在某個間隙插入新的記錄，但是當前處於等待狀態。

比如事務A持有（4，8）範圍內的gap鎖，事務B和C，想插入（4，8）範圍內的記錄，就會在記憶體中生成事務B，C對應的插入意向鎖，當前事務A釋放gap鎖的時候，將喚醒事務B和C，事務B和C可以同時獲取插入意向鎖，然後進行插入。插入意向鎖並不會阻止對記錄繼續上鎖。
- 隱式鎖
為事務生成記憶體中的鎖結構並不是一個0成本的事情，為了節省這個成本，提出隱式鎖的概念。

當一個事務插入語一條記錄A，其他事務
1. select xxx Lock in share mode讀取記錄A（獲取記錄A的S鎖），或者使用select xxx for update（獲取記錄A的X鎖）
2. 立即修改記錄A（獲取x鎖）
對於聚簇索引來說，有一個隱藏列trx_id此列儲存著最後更改記錄的事務id，在當前事務A插入記錄後，便是儲存著當前事務A的id，其他事務B企圖獲取x鎖，s鎖的時候，就需要下先看一下，trx_id隱藏列對應的事務是否存活，如果不是那麼正常獲取，反之需要為當前事務A建立一個x鎖記憶體結構，並標記is_waiting為false，然後事務B將為自己建立一個鎖結構，is_waiting 為true然後事務B進入等待狀態

對於二級索引來說，其不具備隱藏列trx_id但是在二級索引頁面的page header中的page_maxt_trx_id屬性，記錄了改動頁面最大的事務id，如果其屬性值小於當前最小的活躍事務id，那麼說明對頁面的改動事務已經提交，否則需要定位到二級索引記錄，然後回表對聚簇索引進行上述聚簇索引的操作。

一個事務對新插入的記錄不需要顯示的加鎖，由於事務id的存在相當於加了一個隱式鎖，別的事務需要加S鎖或者X鎖的時候，先幫之前的事務生成鎖結構，然後為自己生成鎖結構，再進入阻塞狀態。隱式鎖起到了延遲加鎖的作用，也許別的事務不會獲取於隱式鎖衝突的鎖，這時候可以減少記憶體中生成鎖結構。

2.一條Update語句涉及的鎖

2.1加共用後設資料鎖

為了避免當前事務操作的時候，存在另外的使用者對當前表進行DDL操作，mysql首先會為當前操作的表加共用後設資料鎖。這個過程可能存在阻塞的可能，如果當前事務企圖加共用後設資料鎖的時候，存在另外一個事務正在對錶進行DDL操作，這時候另外一個事務上了互斥後設資料鎖，這時候會出現當前事務阻塞的情況

2.2 加表級意向互斥鎖

此階段也可能存在阻塞，但是由於innodb支援行鎖，基本上很少有人給表上鎖。如果執行當前事務之前存在另外一個事務給表上了表記共用鎖，表記互斥鎖，那麼當前操作也會被阻塞。

加表記意向鎖的好處在於，若沒有意向鎖，那麼其他事務對錶加鎖的時候，需要遍歷表中所有記錄確保當前行中的記錄沒有被上鎖

2.3 行鎖

innodb中的行鎖，其實是在記憶體中，為當前行生成一個鎖結構，記錄事務id，索引資訊，鎖資訊，鎖型別等.如果當前事務加鎖的時候，記錄並沒有加鎖，那麼會生成一個鎖結構儲存於記憶體中。如果鎖已經被佔用那麼會掛起當前事務，直到鎖被釋放後喚醒當前事務。

六丶修改資料和生成紀錄檔

在成功上鎖之後，就可以放心的更新資料了，innodb將寫三部分內容

1.寫緩衝頁

如果修改前後這行資料的大小完全沒有發生改變，每一個欄位所佔用的大小和之前一樣，那麼進行就地更新
但凡存在任何一個欄位的大小發生了改變，那麼刪除舊記錄，將舊紀錄放入頁的垃圾連結串列中，插入新的記錄

不進行需要修改sql中指定的欄位，還需要更新trx_id=當前事務的id,roll_pointer = 指向undo log

buffer pool中髒頁的刷盤依賴於後臺定時任務執行緒定時進行重新整理，如果修改到此為止將存在資料丟失的問題，為此innodb儲存引擎還需要寫入以下兩種紀錄檔

2.寫undo log

Mysql InnoDB多版本並行控制MVCC

undo log是為了記錄行資料修改前的結果，用於回滾和mvcc。undo log 可以分為兩種——記錄insert undo log，和 update/delete undo log，生成的undo log會寫入到undo log buffer。

insert undo log 如何幫助回滾剛insert的一行資料

insert undo log實際上記錄了插入行資料的主鍵，回滾是隻需要根據主鍵進行刪除即可
update/delete undo log怎麼回滾update/delete的一行資料

update/delete操作的回滾需要記錄操作前資料的完整資訊

update/delete undo log中的trx_id,roll_pointer是為了支援mvcc，並且還需要記錄修改刪除前後的列資訊，便於回滾恢復記錄

2.1.mvcc

如圖多個版本的資料，在undo log中進行了記錄，並且使用roll_pointer,進行串聯，形成版本鏈。快照讀查詢語句執行前，或者使用start transaction with consistent snapshot（立即生成read view）會生成一個read view（一致性檢視，如下）

read view包含如下幾個欄位

m_ids:在生成read view時，當前系統中活躍的讀寫事務id列表
min_trx_id:生成read view時，當前系統中活躍的讀寫事務中最小事務id，也就是m_ids中的最小值
max_trx_id:生成read view時，系統應該分配給下一個事務的事務id值
creator_trx_id:生成該read view的事務的事務id

2.2如何利用一致性檢視判斷資料是否可見

如果被存取版本的trx_id和creator_trx_id相同，意味著當前事務在存取自己修改的記錄，自然可見
如果存取版本的trx_id屬性值小於read view中的min_trx_id 表明此版本是生成read view之前已經提交的事務，那麼自然可見
如果存取版本的trx_id,大於等於read view中的max_trx_id說明，當前版本資料是生成read view後開啟事務產生的，那麼自然不可見
如果存取版本的trx_id 介於min_trx_id和max_trx_id之間，需要判斷trx_id是否位於m_ids列表中，如果在說明建立read view時生成該版本的事務還是活躍的，那麼該版本，不可被存取，如果不在說明建立read view 時生成該版本的事務已經提交，可以被存取到

2.3Read Committed和 Repeatable Read的不同

Read Committed——每次讀取資料前都生成一個Read View

這樣可以保證生成Read view 中的m_ids是實時活躍事務id集合，也許第一次讀取的時候事務A沒提交，其id位於m_ids中，但是第二次讀取的時候事務A提交了，事務A將不位於m_ids中，這樣在第二次讀取的時候，通過m_ids判斷事務A是否提交的時候，可以得到事務A已經提交了，然後讓事務A版本產生的資料可見（見2.2.4中的內容）。
Repeatable Read——如果使用begin開啟事務那麼在第一次查詢的時候生成Read view，如果使用start transaction with consistent snapshot那麼執行的時候就會生成read view

這樣可以保證當前事務從頭到尾都是read view中記錄的內容是一致的，第一次讀取的時候事務A沒有提交，那麼不可見，但是第二次讀取的時候事務A提交了，但是read view的m_ids 和max_trx_id可以判斷事務A不可見，比如事務A事務id小於max_trx_id意味著生成read view是事務A啟動但是沒提交，即使第二次讀事務A提交了，但是m_ids中還是包含事務A，那麼不可見。如果事務A事務id大於max_trx_id，那麼自然第二次還是大於max_trx_id，也是不可見的，從而實現了可重複讀。

3.寫redo log

Mysql InnoDB Redo log

redo log 記錄事務修改了哪個表空間（space id屬性），哪個頁（page number 屬性），修改後的值（data屬性）

即使是非常簡單的一條變更sql，往往涉及到多出的改動，比如需改sql資料的字元數發生了變更，需要先刪除，後插入。並且需要對上一條行記錄的next_record 屬性進行修改，頁中行資料的修改，往往同樣需要修改page header，page dictionary等內容，並且可能伴隨著B+樹節點分裂和合並。為了解決存在多種不同修改的問題，innodb存在多種型別的redo log。

3.1 mini-transaction

innodb 把一次變更分為多個mini-transaction（MTR）一個MTR包含一組redo log，這一組redo log以一個特殊型別的redo log作為型別，恢復的時候，這一組redo log具備原子性，只有檢測到特殊型別的redo log才任何一組redo log是完整的才會進行恢復（B+樹葉子節點的分裂，不能說分裂一半）

3.2 log buffer

生成redo log，會寫入到log buffer，log buffer是一塊連續的記憶體空間，由一個個大小為512B的log block組成，預設16mb大小。生成的redo log會找最小的一個redo log block 順序寫入

buf_next_to_write 標記redo log已經落盤的位置
buf_free 是標記buffer pool 剩下的空閒空間

3.3 redo log 刷盤的時機：

事務提交
log buffer 空間低於50%
後臺執行緒週期性刷盤
mysql服務正常關閉
做checkpoint

3.4 redo log 進行崩潰恢復

從checkpoint_lsn位置開始讀取redo log，來恢復髒頁和undo log，然後通過undo log把所有未提交的事務的髒頁進行回滾

七丶本地提交

提交階段 innodb儲存引擎需要落盤redo log，mysql伺服器層需要落盤binlog

1.binlog

二進位制邏輯紀錄檔，在邏輯備份和主備複製中發揮重要作用，具備三種格式

statement

每一條會修改資料的 SQL 都會記錄在 binlog 中。

Statement 模式只記錄執行的 SQL，不需要記錄每一行資料的變化，因此極大的減少了 binlog 的紀錄檔量，避免了大量的 IO 操作，提升了系統的效能。

但是，正是由於 Statement 模式只記錄 SQL，而如果一些 SQL 中包含了函數，那麼可能會出現執行結果不一致的情況。比如說 uuid() 函數，每次執行的時候都會生成一個隨機字串，在 master 中記錄了 uuid，當同步到 slave 之後，再次執行，就獲取到另外一個結果了。

所以使用 Statement 格式會出現一些資料一致性問題。
row

Row 格式不記錄 SQL 語句上下文相關資訊，僅僅只需要記錄某一條記錄被修改成什麼樣子了。

Row 格式的紀錄檔內容會非常清楚的記錄下每一行資料修改的細節，這樣就不會出現 Statement 中存在的那種資料無法被正常複製的情況。

不過 Row 格式也有一個很大的問題，那就是紀錄檔量太大了，特別是批次 update、整表 delete、alter 表等操作，由於要記錄每一行資料的變化，此時會產生大量的紀錄檔，大量的紀錄檔也會帶來 IO 效能問題
mixed

Row 格式不記錄 SQL 語句上下文相關資訊，僅僅只需要記錄某一條記錄被修改成什麼樣子了。

Row 格式的紀錄檔內容會非常清楚的記錄下每一行資料修改的細節，這樣就不會出現 Statement 中存在的那種資料無法被正常複製的情況。

不過 Row 格式也有一個很大的問題，那就是紀錄檔量太大了，特別是批次 update、整表 delete、alter 表等操作，由於要記錄每一行資料的變化，此時會產生大量的紀錄檔，大量的紀錄檔也會帶來 IO 效能問題

2.怎麼保證binlog 和redo log狀態一致

mysql採用了內部XA事務的機制保證binlog，和redo log的狀態順序一致，通過兩階段提交的方式實現，兩階段提交存在一個協調者和多個參與者，在mysql中binlog是協調者，redo log是參與者

2.1mysql的兩階段提交

prepare階段
- innodb刷redo log到磁碟，redo log刷盤完成後，修改事務狀態為TRX_PREPARED
- prepare如果失敗，那麼事務會回滾，而prepare成功那麼進入commit階段
commit階段
- mysql伺服器層寫入binlog，寫入完成後，修改事務狀態為TRX_NOT_STARTED,表示事務已經成功提交

2.2宕機的處理

事務轉換若為TRX_ACTIVE那麼回滾事務
事務狀態為TRX_NOT_STARTED 那麼說明redo log 和binlog都成功落盤，這時候任務事務已經提交
恢復的時候如果發現事務狀態為TRX_PREPARED,根據binlog的狀態判斷是提交還是回滾。
- 若binlog 寫入失敗，那麼回滾
- 若binlog寫入成功那麼提交併修改事務為TRX_NOT_STARTED

3.redo log 和binlog 物理落盤策略

3.1 innodb_flush_log_at_trx_commit空置redo log的落盤

0表示每秒進行一次重新整理
1表示每次事務提交時落盤
2表示每次事務提交都只寫redolog緩衝寫道作業系統快取中，由作業系統決定何時刷盤

3.2 sync_binlog控制binlog的落盤

0 表示當事務提交之後，MySQL不做fsync之類的磁碟同步指令重新整理binlog_cache中的資訊到磁碟，而讓Filesystem自行決定什麼時候來做同步，或者cache滿了之後才同步到磁碟。
n表示當每進行n次事務提交之後，MySQL將進行一次fsync之類的磁碟同步指令來將binlog_cache中的資料強制寫入磁碟。
1表示每次事務提交都刷盤

八丶主備複製

主庫寫入binlog之後，備庫的io執行緒會讀取主庫的binlog，並轉存為原生的中繼紀錄檔relay log，備庫上的sql執行緒讀取relay log並在本地執行

1.主備複製的策略

非同步複製：主庫寫完binlog後即可返回提交成功，無需等待備庫響應
半同步複製：主庫接受指定數量的備機轉儲relay log成功的ACK後可返回提交成功（還支援超時時間，超時沒有返回那麼主庫返回成功）
同步複製：主庫等備庫回放relay log執行完，事務之後才能返回提交成功

不同的策略，其效能和一致性要求不同，也影響到主庫能否返回

九丶返回提交成功

至此mysql會給使用者端返回成功

十丶髒頁刷盤

innodb後臺有專門的執行緒負責將buffer pool中的髒頁重新整理到磁碟

從LRU連結串列中的冷資料重新整理一部分頁面到磁碟

後臺執行緒定時從LRU連結串列尾部掃描一些頁面，掃描的頁面數量可以通過innodb_lru_scan_depth指定，如果在LRU中發現髒頁，那麼重新整理到磁碟
從flush連結串列重新整理一部分頁面到磁碟

後臺執行緒也會定時從flush連結串列中重新整理一部分頁面到磁碟，重新整理速率取決於系統是否繁忙

如果後臺執行緒重新整理的很慢，且有新的頁面需要進行快取，這時候會從LRU連結串列尾部看看是否有可以直接釋放的非髒頁，如果不存在那麼需要刷盤然後快取新的頁。