MySQL 入門（1）：查詢和更新的內部實現

2020 年 4 月 30 日
筆記

摘要

在MySQL中，簡單的CURD是很容易上手的。

但是，理解CURD的背後發生了什麼，卻是一件特別困難的事情。

在這一篇的內容中，我將簡單介紹一下MySQL的架構是什麼樣的，分別有什麼樣的功能。然後再簡單介紹一下在我們執行簡單的查詢和更新指令的時候，背後到底發生了什麼。

1 MySQL結構

在這一小節中，我會先簡單的介紹一下各個部分的功能。隨後，將在第二、第三節中詳細介紹。

先來看一張圖：

簡單的來講一講：

1.1 連接器

連接器負責跟客戶端建立連接、獲取權限、維持和管理連接。

在客戶端輸入了賬號密碼之後，如果此時賬號密碼驗證通過，連接器將會和客戶端建立一條TCP連接。這個連接將會在長時間無請求後被連接器自動斷開（默認是8小時）。

此外，在連接建立後，如果管理員修改了這個賬戶的權限，也不會對當前的連接有任何的影響，當前連接所擁有的權限還是之前未修改前的權限。

1.2 分析器

分析器有兩個功能：詞法分析、語法分析。

對於一個 SQL 語句，分析器首先進行詞法分析，對sql語句進行拆分，識別出各個字符串代表的含義。

然後就是語法分析，分析器根據定義的語法規則判斷sql語句是否滿足 MySQL 語法。

所以，如果我們看到You have an error in your SQL syntax這麼一段話，就可以知道這個錯誤是由分析器返回的。

1.3 緩存

這裡的緩存會保存之前的sql查詢語句和結果。你可以理解為這是一個map：key是查詢的sql語句，value是查詢的結果。

並且，在官方手冊中，有這麼一句話：

Queries must be exactly the same (byte for byte) to be seen as identical.

也就是說，查詢語句必須得和之前完全一致，每一個位元組都一樣，大小寫敏感，甚至不能多一個空格。

但是，這裡的緩存是非常容易失效的。為了保證查詢的冪等性，當某一張表有數據更新後，這個表的緩存也將失效。

所以，對於更新壓力大的數據庫來說，查詢緩存的命中率會非常低。建議只在讀多寫少的數據庫開啟緩存。

但是，在MySQL8.0以後，已經刪除了緩存功能。

1.4 優化器

查詢優化器的任務是發現執行SQL查詢的最佳方案。大多數查詢優化器，包括MySQL的查詢優化器，總或多或少地在所有可能的查詢評估方案中搜索最佳方案。

簡單來說，優化器就是尋找一個最快能夠查詢到數據的策略。

1.5 執行器

在通過了上述的過程後，Server層已經解析出了需要處理的數據是什麼，應該怎麼做。

隨後會進行權限的判斷，如果當前的連接擁有目標表的權限，則會調用存儲引擎開放的接口，處理需要處理的數據。

到這裡MySQL的基本架構就講完了。但是因為我省略了大部分的細節，只講了這麼一小部分，可能會導致你的疑問增加了。

不過沒關係，我們接着往下看，用實際的例子來解釋這裡的每一部分，可能會更容易理解。

2 查詢

我們從這麼一條sql講起：

select * from T where ID = 1；

2.1 查找緩存

首先，會調用分析器，進行詞法分析。

此時，詞法分析發現這條sql語句是以select開頭的，並且在這條語句中沒有任何不確定的數據，所以會去緩存中查找是否保存了這條語句的結果作為緩存。

但是關於上面的說法，有我個人推測的部分。我沒有在官方文檔中找到MySQL是何時查找緩存的，到底是在分析器之前還是分析器之後。

但是在《高性能MySQL》這本書中提到了「通過檢查sql語句是否以select」開頭，所以我推測查找緩存是需要先經過簡單的詞法分析的。

只有經過了詞法分析分析，MySQL才能知道這段語句是否是select語句，也能知道這條語句中有無一些不確定的數據（如當前時間等）。

2.2 緩存未命中

此時，如果緩存未命中，則繼續使用分析器進行語法分析。然後，根據這顆語法樹，來判斷這條sql語句是否符合MySQL語法的。

注意，關於詞法分析和語法分析，如果你感興趣的話，可以看一看編譯原理相關的內容。

然後來到了優化器。優化器就是在有多種查找方式的時候，自行選擇一個更好的查詢方式。

例如，如果此時sql語句裏面有多個索引，會選擇一個合適的索引；又或者在關聯查詢的時候，選擇一個更好的方案。

這一部分的內容我想在以後的文章中介紹，這裡我想重點講講下面的內容，關於MySQL中數據的結構。

2.3 數據的結構

在我們利用最後一步的執行器去進行數據的讀取和寫入的時候，其實是調用了MySQL中的存儲引擎進行數據的讀寫和寫入。

回到我們的例子，我們要找的是在表T中ID為1的數據。但是，存儲引擎並不會返回這麼一條具體的數據，他返回的是包含這條數據的數據頁。

這裡我補充一點點知識：

數據庫使用頁管理，和我們操作系統是一樣的。因為我們現在的機器是馮諾依曼結構的，這是是一種將程序指令存儲器和數據存儲器合併在一起的存儲器結構。

在這種結構中，具有一個特性，叫局部性原理。

時間局部性（Temporal Locality）：如果一個信息項正在被訪問，那麼在近期它很可能還會被再次訪問。程序循環、堆棧等是產生時間局部性的原因。
空間局部性（Spatial Locality）：在最近的將來將用到的信息很可能與正在使用的信息在空間地址上是臨近的。
順序局部性（Order Locality）：在典型程序中，除轉移類指令外，大部分指令是順序進行的。順序執行和非順序執行的比例大致是5:1。此外，對大型數組訪問也是順序的。指令的順序執行、數組的連續存放等是產生順序局部性的原因。

簡單的來解釋就是如果一行數據被讀取了或者一條指令被執行了，那麼很大概率接下來CPU會繼續讀取或執行這個地址或者這個地址後面的數據和指令。

在MySQL中也是一樣的，如果一次性讀取一個頁，那麼可能在接下來的讀寫中所操作的數據也在這個數據頁內，這樣可以使得磁盤IO的次數更少。

回到我們剛剛說的內容，至於引擎是如何找到這個頁的，我想在後面索引相關的文章中再詳細解釋。這裡我們先簡單的理解為引擎能夠快速的找到這一行數據所在的頁，然後這一頁返回給執行器。

此時，這一頁數據還會被保存在內存中。在之後還需要用到這些數據的時候，將會直接在內存中進行處理，並且MySQL的內存空間中可以存放很多個這樣的數據頁。也就是說，這個時候無論是查找還是修改，都可以在內存中進行，而不需要每次都進行磁盤IO。

最後，會在合適的時候將這一頁數據寫回磁盤。至於是在什麼時候如何寫回磁盤的，我們接着往下看。

3 更新

在說完了如何查找數據之後，我們已經知道了一行數據是如何以頁的形式保存在內存中了。我們現在要解決的問題是：

update語句是如何執行
如何將執行後的新數據持久化在磁盤中

這是一個很有意思的問題，我們來假設兩種情境：

假設MySQL在更新之後只更新內存中的數據就返回，然後再某一時刻進行IO將數據頁持久化。這樣所有操作都是在內存中，可以想像此時的MySQL性能是特別高的。但是，如果在更新完內存又還沒有進行持久化的這段時間，MySQL宕機了，那麼我們的數據就丟失了。

再來看另外一種情況：每次MySQL將內存中的頁更新好後，立刻進行IO，只有數據落盤後才返回。此時我們可以保證數據一定是正確的。但是，每一次的操作，都要進行IO，此時MySQL的效率變得非常低。

所以我們來看看MySQL是如何做到保證性能的情況下，還保證數據不丟的。

現在回到這條語句：

update T set a = a + 1 where ID = 0;

假設這條sql語句是正確的，存在名為ID，a的列在表T中，且存在ID為0的數據。

此時經過連接器，分析器，分析器發現這是一條update語句，於是繼續語法分析，優化器，執行器。執行器判斷有權限，然後開表，引擎找到了包含了ID為0這行數據的數據頁，將這一頁數據保存在內存中。

你可以發現，update語句，同樣也走了這麼一遍流程。

然後重點來了，我們要介紹一下MySQL是如何保證數據一致性的。

3.1 重做日誌

這裡要介紹一個很重要的日誌模塊，稱為todo log（重做日誌）。

注意，重做日誌是InnoDB引擎特有的。

重做日誌在更新數據的時候，會記錄在哪個數據頁更新了什麼數據，並且只要成功的在重做日誌記錄了這次更新，不需要將內存中的數據頁寫回磁盤，就可以認為這次更新已經完成了。

MySQL里有一個名詞，叫WAL技術，WAL的全稱是Write-Ahead-Logging，它的關鍵點就是先寫日誌，再寫磁盤，也就是說只要保證了日誌的落盤，數據就一定正確。此時只要保存了日誌，就算此時MySQL宕機了，沒有將數據頁寫回磁盤，也可以在之後利用日誌進行恢復。

但是，InnoDB的redo log是固定大小的，比如可以配置為一組4個文件，每個文件的大小是1GB。固定大小也就造成了一個問題，redo log是會被寫滿的。

所以，InnoDB採取了循環寫的方式。注意看，這裡有兩個指針。write_pos表示當前寫的位置，只要有記錄更新了，write_pos就會往後移動。而check_point表示檢查點，只要InnoDB將check_point指向的修改記錄更新到了磁盤中，check_point將會往後移動。

換句話說，拿我們剛剛的update T set a = a + 1 where ID = 0;舉例，如果我們把這一行數據所在的內存頁更新好了，並且寫入了todo log中，此時將返回修改成功的提示。然後在todo log中表現為記錄了在某一個內存頁的更新記錄。

注意，此時在磁盤中，數據a未改變，在內存中，a改為了a+1，在todo log中記錄了這個內存頁的更新記錄，write_pos往後移動。

此時，如果要把check_point往後移，那麼他就應該把記錄中這個內存頁的更新持久化到磁盤中，也就是說要把a+1寫回磁盤，此時無論是磁盤還是內存，a的數據都是a+1。只有成功的寫回了磁盤，check_point才可以往後移動。這個設計，使得todo log是可以無限重複使用的。

那麼問題來了，我們現在只是知道了write_pos會在數據更新之後往後移動，那麼check_point會在什麼時候移動呢？

這裡涉及到了innodb_io_capacity這個參數，這個參數會告訴InnoDB你的磁盤讀寫速度怎麼樣，然後由他來控制check_point的移動。至於如何調優，我想在以後的文章中來介紹，在本文你就理解為，他會按照一定的速度，不斷推進。

然後問題又來了，如果此時數據庫有大量的更新操作，而check_point推進的速度又是恆定的，那麼write_pos不斷往前推進，就一定會寫滿。這種情況是InnoDB要盡量避免的。因為出現這種情況的時候，整個系統就不能再接受更新了，所有的更新都會被堵住。如果你從監控上看，這時候更新數會跌為0。至於如何避免這種情況，我想等到調優的時候再來聊，這裡我們只是知道會有這麼一種情況。

除此之外還有一種情況我想聊一聊，同樣是大量的更新操作。我們在前面已經提到過了，所有的操作都會在內存中完成，也就是說如果此時我要操作的數據，他們分佈到了不同的數據頁中，那麼此時內存中就存儲了非常多的數據頁。這個時候，內存可能不足了。

我們這裡補充一個概念，乾淨頁和臟頁。乾淨頁指的是從磁盤讀到內存中，沒有被修改過，你可以理解為只被查詢而沒有被更新過的數據頁。而臟頁是和磁盤中數據不一樣的數據頁，他被修改過。如果此時有大量的查詢或更新操作，那麼就需要有大量的內存空間，而此時內存空間已經有各種各樣的數據頁了。那麼我們應該怎麼辦呢？

如果還有空閑空間，則直接將需要的數據頁讀取並存到空間空間內。
如果沒有空閑空間了，則淘汰最近最少使用的乾淨頁，也就是說把這個乾淨頁的空間給用了。
如果連乾淨頁也沒有了，那麼需要淘汰最近最少使用的臟頁。要怎麼淘汰呢，把臟頁寫回磁盤，也就是說更新臟頁的數據，使他變成了乾淨頁。

然後問題又雙叕來了，如果此時我們因為內存空間不足而將這個臟頁寫回了磁盤，但是對這個臟頁的更新卻記錄在了redo log的不同位置，那麼在redo log需要更新這個頁的時候，怎麼辦呢？我們需不需要在刷新臟頁的時候，在redo log中也把對應的記錄刪掉或者怎麼樣呢？

這個問題我希望你能思考一下，如果有了這個疑問我想你就理解了上面我說的關於redo log和臟頁的問題了。答案是在更新臟頁的時候，是不需要修改redo log的。redo log在check_point往前推進的時候，如果發現這個頁已經被刷回磁盤了，將會跳過這條記錄。

3.2 歸檔日誌

說了這麼多重做日誌，我們再來聊聊歸檔日誌。

有幾個原因，redo log是循環使用的，也就是說新數據一定會覆蓋舊數據，我們沒辦法拿他來恢復太長時間的記錄。

第二個原因是因為redo log是InnoDB引擎特有的，在別的引擎中，就沒有重做日誌了。

所以在這裡我們聊聊引擎層必有的歸檔日誌binlog。

歸檔日誌是追加寫的，在一個文件寫滿後就會切換到下一個文件繼續寫，會記錄每一條語句更改了什麼內容。

也就是說，在進行故障恢復的時候，可以使用binlog一條一條的恢復記錄。

那我們要怎麼保證binlog一定能保證數據一致性呢，我們來聊聊MySQL中的兩階段提交。

還是以update T set a = a + 1 where ID = 0;為例：

解釋一下：一直到更新內存中的數據頁，在上面都已經提到過了。然後是將數據頁的更新寫入redo log中。

注意，這裡寫的redo log，並不是寫入了redo log的文件中，而是寫入了名為redo log的buffer中，也就是說此時並沒有使用磁盤IO，不會造成性能的降低。

然後，進入了名為prepare的階段。

然後，寫入bin log。注意，這裡說的寫入bin log，也同樣沒有持久化，也是寫入了buffer中。

只有當這兩者都寫入成功了，才會到提交事務的階段。

然後，有兩個參數很重要。

這兩個參數決定了是否等待直到將redo log和bin log持久化之後再返回。

sync_binlog和innodb_flush_log_at_trx_commit。

先說說innodb_flush_log_at_trx_commit：

當設置參數為1時，（默認為1），表示事務提交時必須調用一次 fsync 操作，最安全的配置，保障持久性。
當設置參數為2時，則在事務提交時只做 write 操作，只保證將redo log buffer寫到系統的頁面緩存中，不進行fsync操作，因此如果MySQL數據庫宕機時，不會丟失事務，但操作系統宕機則可能丟失事務。
當設置參數為0時，表示事務提交時不進行寫入redo log操作，這個操作僅在master thread 中完成，而在master thread中每1秒進行一次重做日誌的fsync操作，因此實例 crash 最多丟失1秒鐘內的事務。（master thread是負責將緩衝池中的數據異步刷新到磁盤，保證數據的一致性）。

也就是說，如果我們設置為了1，在最後提交的時候，會調用fsync等待redo log持久化，才返回。

再說說sync_binlog：

sync_binlog=0的時候，表示每次提交事務都只write，不fsync。
sync_binlog=1的時候，表示每次提交事務都會執行fsync。
sync_binlog=N(N>1)的時候，表示每次提交事務都write，但累積N個事務後才fsync。但如果宕機了可能會丟失最後的N條語句。
也就是說，如果我們設置為了1，最後提交的時候會和上面說到的一樣，等待系統的fsync。

那麼，我們為什麼需要兩階段提交來保證數據的一致性呢？

我們假設現在寫完了redo log，進入了prepare階段，但是還沒有寫bin log，此時數據庫宕機，那麼重啟後事務會回滾，不影響數據。

再做一個假設，我們已經寫完了bin log，宕機了，再重啟後MySQL會判斷redo log是否已經有了commit標識，如果有，則提交；否則的話，去判斷bin log是否完整，如果是完整的，則提交，否則回滾。

那麼，如果我們沒有將階段提交，會怎麼樣呢？

假設我們先提交redo log，再提交bin log，此時邏輯和兩階段提交一樣，但是沒有了兩次驗證。那麼如果我們在redo log提交完了宕機了，那麼我們重啟後，可以根據redo log來恢複數據。但是因為我們在bin log中沒有更新，所以在未來如果使用bin log進行恢復，或者同步從庫的時候，將會導致數據不一致。（主從同步問題在以後的文章解釋）

再做一個假設，先提交bin log，再提交redo log。那麼在恢復的時候這個數據是沒有被更新的，但是在未來使用bin log的時候，會發現這裡的數據不一致。

所以說，兩階段提交是為了保證這兩個日誌是可以一致的。

寫在最後

首先，謝謝你能看到這裡。

希望這篇文章能夠給你帶來幫助，讓你對MySQL的了解可以加深一些。當然了，文章篇幅有限，作者水平也有限，文章中很多地方的細節沒有展開講。很多知識點會在今後的文章中不斷進行補充。另外，如果你發現了作者不對的地方，還請不吝指正，謝謝你！

其次，要特別感謝雄哥，給了我很多的幫助！另外，也特別感謝丁奇老師，我是以《MySQL實戰45講》作為主線進行學習的。

PS：如果有其他的問題，也可以在公眾號找到作者。並且，所有文章第一時間會在公眾號更新，歡迎來找作者玩~