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

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

前言

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

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

基礎架構

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

一條查詢語句

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

select * from T where id = 1

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

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

二階段提交

步驟

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

1. 判斷表T的id=1的記錄是否在記憶體中
2. 不在則先從磁碟讀入記憶體
3. 在記憶體中，將id=1的這條記錄的a欄位修改為0
4. 將修改操作寫入磁碟redo log，此時redo log處於prepare狀態
5. 將修改操作寫入磁碟bin log
6. 提交事物，將redo log修改為commit狀態

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

分析

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

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

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

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