面試官：請分析一條SQL的執行

2022 年 4 月 9 日
筆記
MySQL, 手撕MySQL

關注公眾號【程序員白澤】，帶你走進一個不一樣的程序員/學生黨

前言

最近一直在寫《手撕MySQL系列》文章，我發現自己的切入點有一些問題，雖嘗試深入探究MySQL中的一些關鍵特性，但對於MySQL的知識掌握不太能夠形成較好的體系化的知識網絡。我感到在對全局了解不夠清晰的時候，去深究一個知識點往往會事倍功半。所以打算通過這篇文章，分析SQL語句從頭到尾的執行，串連一下MySQL當中的基礎知識點。

當然希望藉助一篇文章深入剖析MySQL所有的關鍵特性是不夠的，後面也會繼續更新《手撕MySQL》系列，只是可能會調整寫作的切入角度，儘可能幫助閱讀文章的同學建立體系化的知識網絡。

基礎架構

客戶端：Navicat是一款我們常用的數據庫操作工具，通過該數據庫客戶端軟件我們去建立數據庫連接，輸入SQL語句並提交執行命令。
服務端Server：首先要明確的是，客戶端運行時是一個進程，那麼發起連接，執行SQL等命令都有一個接收進程，那就是MySQL的服務端進程 （你剛開始學MySQL時總是聽到啟動MySQL服務就是指這個進程） ，藉助MySQL服務端進程去處理所有從客戶端發起的數據庫操作，並且最後將改動持久化到數據庫磁盤文件上。
存儲引擎內存池：將MySQL服務端拆解成兩個部分時因為存儲引擎是針對錶而言的，對於不同的表可以選擇不同的存儲引擎，並利用其相應的特性滿足對應的業務需求，我們在創建一張表的時候最後寫的engine=innodb就是在指定選擇的存儲引擎，從5.5版本開始，如果不指定存儲引擎，則默認使用InnoDB。
通用服務層：這一部分包含了MySQL中的通用核心服務，所有跨存儲引擎的功能都在這裡，包括連接器、查詢緩存、分析器、優化器、執行器、以及內置的函數表達式等等 （圖上還有很多沒畫出來，後面的文章中也會逐漸補充進來） 。
數據庫磁盤文件：這一部分的作用是持久化數據庫數據，服務端Server終究是一個運行的進程，所有的數據都是臨時存放在內存當中，而我們最終的目的自然是維護一份永久的數據庫文件。（當然不是說內存就不重要，相反，因為客戶端操作數據庫必然會頻繁修改磁盤上的文件，想要操作數據就得先將磁盤中的目標文件頁讀到內存中，在內存中操作完成之後，再把改動之後的數據頁刷新回磁盤，而磁盤IO性能較低，合理使用內存或者說緩存的技術可以減少磁盤IO次數，大大提高數據庫訪問的性能，這一部分將在後面逐漸介紹）。

一條查詢語句

接下來分析下面這條查詢語句的執行過程

select * from T where id = 1

連接器：首先通過客戶端如Navicat連接到這個數據庫服務進程 （需要輸入目標服務器的IP、端口、用戶名、密碼） ，而負責與建立連接的就是連接器，負責校驗用戶名密碼，以及獲取對應權限。
查詢緩存：以key-value形式存儲一條查詢語句對應的結果，如果當前輸入的SQL在查詢緩存中，可以直接返回查詢結果而不用重複執行，但是查詢緩存在MySQL8.0被廢棄，原因是一條查詢緩存對應的表如果發生了修改，則針對這個表的查詢緩存都將失效而被清除，如果表更新頻率比較高，則會大大提高查詢緩存的失效可能，緩存利用率很低，還會額外佔用內存開銷。
分析器：分析器只是一個概稱，它的工作是將SQL語句通過解析器成一顆對應的解析樹，然後交由預處理器進一步檢查解析樹的各個部分的語法是否合法，包括對應的表、字段是否存在、名稱是否合法等，不合法就拋出錯誤，通過分析器分析之後合法，則再交由優化器進行分析。
優化器：這裡先簡單理解成一條查詢語句涉及的表可能在不同的字段上建立了多個索引，也有可能涉及多個表，這裡需要優化器去分析得到一個最優的執行方案（效率最高），比如選擇走哪個索引，選擇多個表之間的連接順序等。
執行器：校驗是否有權限訪問SQL中涉及的表，然後配合對應的存儲引擎，根據優化器給出的執行方案執行一個SQL，最後返回查詢結果。

一條更新語句

看到這裡你大概對MySQL如何執行一條查詢語句的執行流程大概有了概念，也初步熟悉了其中會涉及到的一些 「功能組件」 ，但你還不太滿足，MySQL的redo log、bin log在哪呢？面試老愛問了！ （undo log這裡先不提）接下來分析下面這條更新語句的執行過程

update T set a = 0 where id = 1

與查詢語句相同，執行更新語句也要經過上面那張圖中從連接器到執行器的部分，這裡我再放一下。區別在於更新表對數據庫磁盤文件造成了變更，而查詢語句沒有。而且前面也提到，MySQL通過一些機制合理減少磁盤IO次數，提升數據庫訪問性能與可靠性。這裡就要介紹一下更新操作中涉及到的兩種物理日誌文件，redo log和bin log（MySQL服務端內存中也有對應着的日誌緩存）。

redo log

redo log是InnoDB引擎持有的日誌文件（bin log是MySQL通用層的日誌文件），也就是說一張表選擇InnoDB引擎，在執行更新語句時會同時產生redo和bin兩種物理日誌文件。這裡先介紹redo log：

前面說了，MySQL通過一些機制可以減少磁盤IO，以及提升數據庫可靠性。redo log功不可沒，在InnoDB引擎內存池中，維護着redo log。

具體來說，在執行上面那條更新語句的時候，InnoDB引擎會將涉及到的記錄讀取到內存中（只有對應記錄在內存中才可以開始更新），更新對應這條記錄的內存（此時磁盤中的這條記錄還沒更新，但內存中更新了），再將更新記錄到redo log緩存。之後redo log緩存會按照一些規則刷新到磁盤文件中的redo log物理文件。而那些在內存中與物理磁盤不同的記錄稱之為臟頁，臟頁會通過一種叫checkpoint的規則去刷新到磁盤上（此時才是真的完成了更新）。

上面大概描述了InnoDB引擎在更新時選擇先將更新日誌記錄下來，再最後修改磁盤（稱之為WAL技術—Write-Ahead Logging），這樣設計的作用是即使MySQL服務因為意外宕機時，之前的更新記錄依舊保存在redo log磁盤文件中 （如果只是單純依賴redo log緩存，則掉電後會遺失這部分數據，而不使用redo log則每次更新表的操作就得進行磁盤IO，無法優化，性能低下） 。

從上面我們可以看到重做日誌文件側重於數據庫崩潰時的數據恢復，以及涉及臟頁的刷新時機，因此InnoDB引擎對於redo log文件的設計是循環寫的，並沒有給予無限的增長空間，如下圖，如果有兩個大小為1G的redo log磁盤文件，則隨着redo log緩存逐漸刷新到磁盤上，這兩個文件會逐漸被填滿，並循環覆蓋。因此如果即將被覆蓋的redo log代表的操作（臟頁）還沒有刷新到磁盤，則會觸發checkpoint，刷新這些臟頁，只要磁盤完成修改，則對應的redo log磁盤文件可以被覆蓋掉（這是checkpoint的某一個觸發條件）。

bin log

bin log是很容易拿來與redo log進行比較的，它是MySQL通用層實現的，記錄對數據庫表的變更操作，不記錄查詢，而且由於歷史原因，InnoDB引擎是後來出現的，bin log被用於日誌歸檔（較長時間跨度的數據恢復/主從複製），而redo log則側重於崩潰時保留改動的數據。

下面給出幾個bin log與redo log的不同點：

redo log是物理日誌，記錄的是某條記錄發生了什麼改動；bin log是邏輯日誌，記錄的是語句的原始邏輯（bin log也可以選擇記錄日誌的模式）。
bin log稱為歸檔日誌（可能會根據需求保留過去一個月的數據庫變更），因此它是追加寫入的，沒有大小限制；redo log是循環寫入，有大小限制。（這主要是因為側重的功能不同）
redo log是InnoDB引擎層的，bin log是MySQL通用層的。

二階段提交

步驟

那麼對於使用InnoDB的表，執行上面那條update語句時，redo log和bin log是如何配合工作的呢？步驟簡化之後如下：

判斷表T的id=1的記錄是否在內存中
不在則先從磁盤讀入內存
在內存中，將id=1的這條記錄的a字段修改為0
將修改操作寫入磁盤redo log，此時redo log處於prepare狀態
將修改操作寫入磁盤bin log
提交事物，將redo log修改為commit狀態

二階段提交的由來是redo log的狀態經歷了從prepare到commit兩個階段的變化，而二階段提交的目的就是為了使bin log和redo log在配合使用時，在遇到宕機等情況時數據恢復能保持邏輯上的一致。

分析

如果不使用兩階段提交，只有單一的修改磁盤redo log和磁盤bin log則會有以下兩種問題：

先寫bin log，後寫redo log，在寫入bin log之後，服務器宕機，此時redo log未寫入，則本地磁盤中將丟失對於數據的更改（也丟失了修改的臟頁），而bin log歸檔文件中已經寫入了修改邏輯，那麼用這個bin log進行數據恢復或者主從複製會使得與當前數據庫表數據之間出現不同。
先寫redo log，後寫bin log，在寫入redo log之後，服務器宕機，此時bin log未寫入，則本地磁盤中將保留對數據的修改，但是bin log歸檔文件中沒有記錄這個修改邏輯。那麼用這個bin log進行數據恢復或者主從複製依舊會使得與當前數據庫表數據之間出現不同。

使用兩階段可以通過redo log的狀態判斷本次修改是否在bin log和redo log上都完成了記錄，結合回滾和補充提交機制，從而確保數據在兩種日誌文件中的邏輯一致性。

關注公眾號【程序員白澤】，帶你走進一個不一樣的程序員/學生黨，公眾號回復【簡歷】可以獲得我正在使用的簡歷模板，平時也會同步更新文章。希望大家都能收穫心儀的offer～

Tags: MySQL 手撕MySQL