一條sql詳解MYSQL的架構設計詳情

2022-09-26 14:06:31

1 前言

對於一個伺服器端開發來說 MYSQL 可能是他使用最熟悉的資料庫工具，然而，大部分的Java工程師對MySQL的瞭解和掌握程度，大致就停留在這麼一個階段:它可以建庫、建表、建索引，然後就是對裡面的資料進行增刪改查，語句效能有點差？沒關係，在表裡建幾個索引或者調整一下查詢邏輯就可以了，一條sql，MYSQL是如何處理的，為我們做了什麼，完全是個黑盒。本文主要通過sql執行的過程打破這樣一個黑盒的認知，來了解MYSQL的邏輯架構。

MYSQL的邏輯架構可分為3層:應用層、服務層、儲存引擎層。其中儲存引擎是MYSQL最有特色的地方，MySQL區別於其他資料庫的最重要特點是其外掛式的表儲存引擎，本文也將著重聊聊最常用的innoDB儲存引擎的架構設計原理，假設現有如下sql：

update users set name=’zhangsan’ where id = 10

作為一個java伺服器端工程師，見到這樣一個sql，本能的腦海中立刻就浮現出如下資訊：

一個表名為users的表
有兩個欄位 id、name，id是主鍵
把users表裡的id=10的這個使用者名稱修改為“zhangsan”

那麼MYSQL是如何處理這樣一個sql呢？帶著這個問題，我們來看一下MYSQL是如何通過一個個元件來處理這個sql，來了解MYSQL的整體架構

2 應用層

2.1 連線執行緒處理

當MYSQL面對上面的sql，首先應該做什麼呢？是如何解析？如何選擇索引？如何提交事務？當然不是，首先應該解決的是怎麼把sql語句傳給它。大家都知道，如果我們要存取資料庫，那麼，首先就需要和資料庫建立連線，那麼這個連線由誰來建呢，答案就是MYSQL驅動，下面這段maven設定大家應該都很熟悉

java程式就是通過這個驅動包來與資料庫建立網路連線。

下圖示意：

從圖中可以看到這樣一個場景：java程式很多個執行緒並行請求執行上述sql，我們都知道資料庫連線是非常佔用資源的，尤其是在高並行的情況下，如果每次都去建立資料庫連線就會有效能問題，也會影響一個應用程式的延展性，針對這個問題，連線池出現了。

下圖示意：

從圖中可見網路連線交由執行緒3監聽和讀取sql請求，至此MYSQL已經收到我們的請求，當然MYSQL在建立連線時還做了使用者鑑權,鑑權依據是: 使用者名稱，使用者端主機地址和使用者密碼;在獲取連線後，處理請求時還會做sql請求的安全校驗，根據使用者的許可權判斷使用者是否可以執行這條sql。

3 服務層

3.1 SQL 介面

從上圖中我們知道執行緒3負責監聽並讀取sql，拿到這個sql之後，如何執行是一項極其複雜的任務，所以MYSQL提供了SQL介面這麼一個元件，執行緒3會將sql轉交給SQL介面來執行如下圖：

SQL介面具體處理功能有：DDL、DML、儲存過程、檢視、觸發器等。

3.2 SQL解析器

接著問題來了，SQL介面如何執行本文sql呢？，資料庫怎麼理解本文這個sql呢？相信懂sql語法的人立馬就能知道什麼意思，但是MYSQL是個系統不是人，它無法直接理解sql的意思，這個時候關鍵的元件出場了，SQL解析器的作用主要就是是解析sql語句，最終生成語法樹，比如本文sql就可以拆解成如下幾個部分：

需要從users表裡更新資料
需要更新id欄位是10的那行資料
需要把這行資料的name欄位的值改為 “zhangsan”

3.3 SQL優化器

當通過SQL 解析器理解了sql語句要幹什麼之後，該如何實現呢，以本文的更新語句為例，我們可以有以下兩種實現方式：

直接定位到users表中id欄位等於10的一行資料，然後查出這行資料資料，然後設定name欄位為“zhangsan”；
也可以通過更新name欄位索引的方式在name索引上遍歷id等於10的索引值，然後設定name欄位為“zhangsan”。

上面兩種途徑都能實現最終結果，顯然第一種路徑更好一些，所以,SQL優化器就是從眾多實現路徑中選則一條最優的路徑出來，也就是我們常說的執行計劃。

3.4 執行器

通過SQL優化器我們得到一套執行計劃，那麼，這個計劃怎麼執行呢？這個時候就不得不提MYSQL儲存引擎，我們都知道MySQL和其他關係型資料庫不一樣的地方在於它的彈性以及可以通過外掛形式提供不同種類的儲存引擎，類似java介面的多實現，MYSQL肯定會有一套標準的儲存引擎介面，而執行器就是按照執行計劃一步一步的呼叫儲存引擎介面完成sql執行而已，如下圖：

上圖專門將binlog標出來是為了和下文innodb儲存引擎的undo log、redo log做區分，強調binlog是server層的紀錄檔，後續binlog 和redo log的兩階段方式完成事務的提交會再次提到。

3.5 查詢快取

MYSQL服務層為追求高效也引入了QUERY BUFFER 這個元件，但是這個元件比較雞肋，快取不僅需要sql全字匹配命中，而且對基礎表的任何修改都會導致這些表的所有快取失效，既不符合現在使用者變數的開發模式，大部分時候也不高效。

MYSQL從5.7開始不推薦使用預設關閉，8.0中不再支援，詳細原因如下圖：

截圖來源MYSQL開發者專區檔案：

4 儲存引擎層

4.1 概述

上文執行器拿到執行計劃後，呼叫儲存引擎的介面來完成sql的執行，那麼儲存引擎如何幫助我們去存取、操作記憶體以及磁碟上的資料呢？我們都知道MYSQL的儲存引擎有很多，實現方式各一，下面讓我們繼續通過上文的sql來初步瞭解我們常用的Innodb儲存引擎的核心原理和架構設計

重溫一下本文sql：

update users set name='zhangsan' where id = 10 —-歷史name = ‘lisi'

4.2 緩衝池（buffer pool）

InnoDB儲存引擎中有一個非常重要的放在記憶體裡的元件，就是緩衝池（Buffer Pool），這裡面會快取很多的資料，以便於以後在查詢的時候，萬一你要是記憶體緩衝池裡有資料，就可以不用去查磁碟了，如下圖：

緩衝池（buffer pool）在Innodb中的地位類似於我們現在系統設計中redis的地位，在Innodb中引入這一元件的就是為了高效的存取，我們都知道MYSQL查詢資料很快，究其原因不止是索引查詢，深層次的原因就是所有的增刪改查都是在buffer pool這塊記憶體上操作的，相比於操作磁碟，效率不言自明。

4.2.1 資料頁、快取頁和髒頁

還是拿我們的sql舉例，更新id=10的這條記錄，難道從磁碟裡只拉取id=10資料進入記憶體中嗎？很明顯不是，畢竟加入記憶體的記錄不止這一張表，而且單表每行記錄也不一樣，記憶體管理會非常困難的，所以，MYSQL對資料抽象出來的一個叫資料頁的邏輯概念，每頁固定大小預設16KB，可以存多條資料，並且buffer pool裡的儲存結構和資料頁一致，這樣記憶體管理就會簡單的多，資料頁註冊後設資料後載入進記憶體後就是快取頁。

從圖中可以看到在快取頁在sql更新完還未刷回硬碟時資料和磁碟中的資料頁是不一致的，這個時候我們稱這種快取頁為髒頁。至於後續髒頁如何落盤暫時不提。

4.2.2 後設資料

從上圖我們看到buffer pool中除了快取頁，還多了一個後設資料記憶體結構，這個可以簡單的理解為登記，比如因為疫情外地人回家過年會被當地政府進行登記，記錄從哪來、到哪去等資訊，便於管理，buffer pool也是這樣做的；但是後設資料可不止記錄快取頁的磁碟地址和記憶體地址這麼簡單，buffer pool核心原理都是通過後設資料來實現的

4.2.3 free連結串列

buffer pool在MYSQL初始化的時候，就根據設定在記憶體中申請了一塊連續的空間，申請過後就按資料頁的大小和後設資料的大小進行合理的劃分出很多個連續的、空的快取頁,當需要查詢資料的時候就會從磁碟讀入資料頁放入到快取頁當中,但是由於髒頁的存在，資料還未刷盤不能使用，那麼資料頁載入進哪個快取頁就是個問題。為了解決哪些快取頁是空閒的，MYSQL團隊為Buffer pool設計了一個free連結串列，它是一個雙向連結串列的資料結構，這個free連結串列裡每個節點就是一個空閒的快取頁的後設資料塊地址，也就是說只要一個快取頁是空閒的，那麼他的後設資料塊就會放入這個free連結串列中,這樣載入資料頁是隻需要從free連結串列中找空閒的快取頁即可。

從圖中即可看出連結串列的大致結構，那麼現在我們要更新users表中id=10的記錄，首先要知道id=10這條記錄的資料頁有沒有在快取頁當中，然後在決定是否是載入資料頁還是直接使用快取頁，所以，buffer pool裡還有左下角這種hash表，用表空間+資料頁號作為key，快取頁地址為value，可以快速判斷資料頁是否被快取。

4.2.4 flush連結串列

本文sql執行更新後，這樣就導致記憶體中的資料和磁碟上的資料不一致，這就表明這個快取頁是髒頁，髒頁是需要重新整理到磁碟檔案的。但是不可能所有快取頁都刷回磁碟，比如有的快取頁可能只是查詢的時候用到了，沒有別更新過，所以資料庫就引入flush連結串列，flush連結串列和free連結串列的實現方式一樣，都是在後設資料中增加兩個指標做成雙向連結串列，用來標記連結串列上的都是髒頁，需要刷回磁碟，後續IO執行緒非同步刷盤就是將flush連結串列的資料刷盤，然後把快取頁移除flush連結串列，加入free連結串列當中。

4.2.5 LRU連結串列

隨著不停的把磁碟上的資料頁載入到空閒的快取頁裡去，free連結串列中空閒的快取頁越來越少，如果free連結串列空了，這時候就無法從磁碟載入資料頁了，這時候就需要淘汰掉一些快取頁，首先想到的就是把修改過的快取頁重新整理回磁碟上，然後清空這個快取頁

具體選擇哪個快取頁進行清空呢，資料庫引入LRU連結串列,結構和free連結串列基本一致,最近存取的快取頁都會被移動到LRU連結串列的頭部,這樣尾部的就是少存取的資料，但是這樣的LRU有個問題，就是MYSQL的預讀機制，會把不常存取或者不存取的資料連帶著載入到記憶體，這樣就把這一部分也放在了LRU頭結點上，很明顯不合理,同理，全表掃描也有這個問題。

從上面可以看出，如果此時需要淘汰快取頁，就可能把熱點資料提前淘汰掉。對於這種不合理的LRU演演算法MYSQL基於冷熱資料分離的方法對LRU演演算法進行如下優化：LRU連結串列被拆分為兩個部分，一部分熱資料，一部分冷資料，資料頁第一次載入到快取的時候是放在冷資料表頭，在1s後再次存取這個快取頁，就很有可能是熱資料，就會把它挪到熱資料表頭區域，這樣設計防止了剛載入就存取造成的假熱現象。

冷熱區域快取頁移動規則如下：

冷資料 -> 熱資料：冷資料區的快取頁是在 1s 後再被存取到就移動到熱資料區的連結串列頭部
熱資料 -> 冷資料：能留在熱資料區域的快取頁，證明都是快取命中率比較高的，會經常被存取到。如果每個快取頁被存取都移動到連結串列頭部，那這個操作將會非常的頻繁。所以 InnoDB 儲存引擎做了一個優化，只有在熱資料區域的後 3/4 的快取頁被存取了，才會移動到連結串列頭部；如果是熱資料區域的前 1/4 的快取頁被存取到，它是不會被移動到連結串列頭部去的。這樣儘可能的減少連結串列中節點的移動了

4.2.6 小結

現在我們瞭解了更新資料會先把資料載入進buffer pool在進行，瞭解buffer pool是如何通過冷熱資料分離的機制優化LRU連結串列，為系統設計中快取過期淘汰策略提供的新的解決思路。既然，資料更新是把資料載入buffer pool中修改，那麼更新完快取頁之後資料庫是如何保證事務提交、如何保證資料頁和快取頁資料一致的呢

4.3 undo log

說到事務就不得不提事務是如何回滾的，innodb是引入了undo log的紀錄檔元件來實現事務回滾的，以本文sql為例, 在資料載入進快取頁後，修改之前，會將執行的sql取反儲存在undo log中，邏輯類似sql：

update users set name='lisi' where id = 10

當然如果是insert語句與之對應的就是delete語句，delete語句也就對應的insert 語句，這也就明白為什麼delete的資料是可以回滾，而truncate資料之後無法回滾的根本原因，在於truncate無法生成undo log。

上圖是本問sql執行的大致步驟，至於加入buffer pool這塊上文已經詳細瞭解過了，就不在贅述。從圖中可以看出因為log直接刷盤比較損耗效能，所以引入log buffer進行快取，然後在通過非同步的方式把資料刷入磁碟既然資料更新之前的資料記錄下來併成功刷入磁碟，則事務的回滾就不難實現了。

當然undo log 除了提供回滾功能，還為多版本並行控制（MVCC）提供了實現基礎，實現了MYSQL的非阻塞讀寫，提高了系統的並行性。本文也不再深入

4.4 redo log

下面來了解一下innodb是如何保證buffer pool快取的資料一致性問題，資料更新值記憶體後並不會立即重新整理至磁碟資料頁，而是一致以髒頁的形式儲存在buffer pool當中，這樣做有兩個原因會導致效率很差，一個是記憶體向磁碟寫資料本身效率就慢，另一個就是隨機IO會寫磁碟的時間上附加上很多磁頭定址的時間，所以立即刷資料頁效率很低。

Innodb是如何規避上述問題的呢，正常情況下，非同步刷盤就已經可以解決了刷磁碟慢的問題，但是，假如MYSQL系統崩潰、宕機，這時候髒頁還未及時刷盤，那麼快取頁期間所有改動資料豈不是丟了，所以，Innodb引入了另一個元件redo log，專門記錄資料被快取期間做過的修改記錄，然後立即寫入redo log磁碟檔案，相比於快取頁刷盤，redo log刷盤的資料了小多了，並且寫redo log是順序IO，而快取頁刷盤是隨機IO。

下圖示意：

這樣當資料庫異常宕機時，即使快取頁丟失資料也不會丟失，因為redo log已經落盤，資料庫重啟的時候會更近redo log把磁碟上歷史的資料頁重新載入記憶體，重新按redo log的修改記錄操作一遍就能將快取頁中的資料恢復至宕機前的狀態。

如果系統宕機時，redo log還沒落盤資料豈不是丟了，對，這種情況下資料會丟，這種redo log丟資料分兩中情況：

第一種情況，MYSQL有三種刷盤策略，通過innodb_flush_log_at_trx_commit引數進行設定

設定為0：事務提交的時候不會把redolog buffer裡的資料立即刷入磁碟，此時如果宕機則會導致已提交的資料修改丟失；
設定為1：則是事務提交的時候必須把redolog buffer裡的資料刷入磁碟，以保證事務提交後運算元據紀錄檔不丟；
設定為2：則表示只是把資料交給作業系統進行刷盤，作業系統刷沒刷成功則不管，理論上作業系統刷盤是先要經過os cache記憶體快取的，就是說資料會先在os chache裡沒有真正的落盤，這種模式下也可能導致資料丟失

這第一種情況如果產生丟資料，是真的丟失，所以，如果對資料庫丟失資料零容忍，建議設定策略為1

第二種情況，就是未寫commit標記紀錄檔的情況，即下圖第9步丟失的情況，但是這種情況系統認為事務提交失敗，所以丟失了並不影響資料一致性。

圖中7、8、9三個步驟是事務提交commit的時候才做的（本文只用一個sql來講解，預設事務自動提交），redo log記錄更新記錄之後，執行器會把修改記錄寫在server層的binlog當中，很明顯這是兩個檔案，如果出現上述宕機等異常情況，這兩個檔案的資料一致性是不能保證的，所以，為了保證兩個檔案的資料一致性，innodb會在binlog寫完之後在redo log中補上一個commit標記告訴redo log事務成功。事務執行成功後操作redo log刷入磁碟，至此本文sql執行成功。