參考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儲存引擎對磁碟的讀取是以頁為單位的，為了避免每次都從磁碟讀取資料，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索引方案一節）
五丶檢驗鎖和加鎖
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
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
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連結串列尾部看看是否有可以直接釋放的非髒頁，如果不存在那麼需要刷盤然後快取新的頁。