MySQL 入門（1）：查詢和更新的内部實作

MySQL 入門（1）：查詢和更新的内部實作

摘要

在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 重做日志

這裡要介紹一個很重要的日志子產品，稱為redo 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;舉例，如果我們把這一行資料所在的記憶體頁更新好了，并且寫入了redo log中，此時将傳回修改成功的提示。然後在redo log中表現為記錄了在某一個記憶體頁的更新記錄。

注意，此時在磁盤中，資料a未改變，在記憶體中，a改為了a+1，在redo log中記錄了這個記憶體頁的更新記錄，write_pos往後移動。

此時，如果要把check_point往後移，那麼他就應該把記錄中這個記憶體頁的更新持久化到磁盤中，也就是說要把a+1寫回磁盤，此時無論是磁盤還是記憶體，a的資料都是a+1。隻有成功的寫回了磁盤，check_point才可以往後移動。這個設計，使得redo 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：如果有其他的問題，也可以在公衆号找到作者。并且，所有文章第一時間會在公衆号更新，歡迎來找作者玩~

原文位址

https://www.cnblogs.com/hongjijun/p/12807980.html