# 一條 sql 瞭解 MYSQL 的架構設計

### 1 前言

最近我從cnaaa.com購買了雲伺服器。

對於一個服務端開發來說 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 配置大家應該都很熟悉

![img](https://pic.imgdb.cn/item/632c035916f2c2beb1129c36.png)

java 程式就是透過這個驅動包來與資料庫建立網路連線。
下圖示意：

![img](https://pic.imgdb.cn/item/632c035916f2c2beb1129c3a.png)

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

![img](https://pic.imgdb.cn/item/632c035916f2c2beb1129c42.png)

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

### 3 服務層

#### 3.1 SQL 介面

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

![img](https://pic.imgdb.cn/item/632c035916f2c2beb1129c4a.png)

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

#### 3.2 SQL 解析器

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

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

![img](https://pic.imgdb.cn/item/632c035916f2c2beb1129c57.png)

#### 3.3 SQL 最佳化器

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

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

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

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129d5e.png)

#### 3.4 執行器

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

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129d63.png)

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

#### 3.5 查詢快取

MYSQL 服務層為追求高效也引入了 QUERY BUFFER 這個元件，但是這個元件比較雞肋，快取不僅需要 sql 全字匹配命中，而且對基礎表的任何修改都會導致這些表的所有快取失效，既不符合現在使用者變數的開發模式，大部分時候也不高效。MYSQL 從 5.7 開始不推薦使用預設關閉，8.0 中不再支援，詳細原因如下圖：

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129d70.png)

截圖來源 MYSQL 開發者專區文件：https://dev.mysql.com/blog-archive/mysql-8-0-retiring-support-for-the-query-cache/

### 4 儲存引擎層

#### 4.1 概述

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

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129d80.png)

重溫一下本文 sql：

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

#### 4.2 緩衝池（buffer pool）

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

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129d88.png)

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

4.2.1 資料頁、快取頁和髒頁

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

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129e23.png)

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

4.2.2 後設資料

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

4.2.3 free 連結串列

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

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129e33.png)

從圖中即可看出連結串列的大致結構，那麼現在我們要更新 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 頭結點上，很明顯不合理，同理，全表掃描也有這個問題。

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129e4a.png)

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

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129e59.png)

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

- 冷資料 -> 熱資料
  冷資料區的快取頁是在 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。

![img](https://pic.imgdb.cn/item/632c035a16f2c2beb1129e63.png)

上圖是本問 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。下圖示意：

![img](https://pic.imgdb.cn/item/632c035b16f2c2beb112a052.png)

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

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

第一種情況，MYSQL 有三種刷盤策略，透過 innodb_flush_log_at_trx_commit 引數進行配置

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

![img](https://pic.imgdb.cn/item/632c035b16f2c2beb112a05c.png)

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

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

![img](https://pic.imgdb.cn/item/632c035b16f2c2beb112a068.png)

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

### 5 總結

透過一條 update 的 sql 的更新流程，清晰的看到 MYSQL 的整體架構設計，對 Innodb 儲存引擎的幾大核心元件如何相互協作、配合以實現高效的資料庫系統有了更清晰的認識；核心元件 buffer pool 的冷熱資料分離的快取淘汰機制也為以後系統的架構設計提供了新的解決思路。

一條 sql 瞭解 MYSQL 的架構設計

相關文章