一丶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，其他事務
select xxx Lock in share mode讀取記錄A（擷取記錄A的S鎖），或者使用select xxx for update（擷取記錄A的X鎖）
立即修改記錄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_PREPAREDprepare如果失敗，那麼事務會復原，而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連結清單尾部看看是否有可以直接釋放的非髒頁，如果不存在那麼需要刷盤然後緩存新的頁。

原文連結：https://www.chuangkit.com/designtools/designindex

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