1 前言

對于一個服務(wù)端開發(fā)來說 MYSQL 可能是他使用最熟悉的數(shù)據(jù)庫工具，然而，大部分的Java工程師對MySQL的了解和掌握程度，大致就停留在這么一個階段:它可以建庫、建表、建索引，然后就是對里面的數(shù)據(jù)進(jìn)行增刪改查，語句性能有點差？沒關(guān)系，在表里建幾個索引或者調(diào)整一下查詢邏輯就可以了，一條sql，MYSQL是如何處理的，為我們做了什么，完全是個黑盒。本文主要通過sql執(zhí)行的過程打破這樣一個黑盒的認(rèn)知，來了解MYSQL的邏輯架構(gòu)。

MYSQL的邏輯架構(gòu)可分為3層:應(yīng)用層、服務(wù)層、存儲引擎層。其中存儲引擎是MYSQL最有特色的地方，MySQL區(qū)別于其他數(shù)據(jù)庫的最重要特點是其插件式的表存儲引擎，本文也將著重聊聊最常用的innoDB存儲引擎的架構(gòu)設(shè)計原理，假設(shè)現(xiàn)有如下sql：

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

作為一個java服務(wù)端工程師，見到這樣一個sql，本能的腦海中立刻就浮現(xiàn)出如下信息：

• 一個表名為users的表
• 有兩個字段 id、name，id是主鍵
• 把users表里的id=10的這個用戶名修改為“zhangsan”

那么MYSQL是如何處理這樣一個sql呢？帶著這個問題，我們來看一下MYSQL是如何通過一個個組件來處理這個sql，來了解MYSQL的整體架構(gòu)

2 應(yīng)用層

2.1 連接線程處理

當(dāng)MYSQL面對上面的sql，首先應(yīng)該做什么呢？是如何解析？如何選擇索引？如何提交事務(wù)？當(dāng)然不是，首先應(yīng)該解決的是怎么把sql語句傳給它。大家都知道，如果我們要訪問數(shù)據(jù)庫，那么，首先就需要和數(shù)據(jù)庫建立連接，那么這個連接由誰來建呢，答案就是MYSQL驅(qū)動，下面這段maven配置大家應(yīng)該都很熟悉

java程序就是通過這個驅(qū)動包來與數(shù)據(jù)庫建立網(wǎng)絡(luò)連接。

下圖示意：

從圖中可以看到這樣一個場景：java程序很多個線程并發(fā)請求執(zhí)行上述sql，我們都知道數(shù)據(jù)庫連接是非常占用資源的，尤其是在高并發(fā)的情況下，如果每次都去建立數(shù)據(jù)庫連接就會有性能問題，也會影響一個應(yīng)用程序的延展性，針對這個問題，連接池出現(xiàn)了。

下圖示意：

從圖中可見網(wǎng)絡(luò)連接交由線程3監(jiān)聽和讀取sql請求，至此MYSQL已經(jīng)收到我們的請求，當(dāng)然MYSQL在建立連接時還做了用戶鑒權(quán),鑒權(quán)依據(jù)是: 用戶名，客戶端主機(jī)地址和用戶密碼;在獲取連接后，處理請求時還會做sql請求的安全校驗，根據(jù)用戶的權(quán)限判斷用戶是否可以執(zhí)行這條sql。

3 服務(wù)層

3.1 SQL 接口

從上圖中我們知道線程3負(fù)責(zé)監(jiān)聽并讀取sql，拿到這個sql之后，如何執(zhí)行是一項極其復(fù)雜的任務(wù)，所以MYSQL提供了SQL接口這么一個組件，線程3會將sql轉(zhuǎn)交給SQL接口來執(zhí)行如下圖：

SQL接口具體處理功能有：DDL、DML、存儲過程、視圖、觸發(fā)器等。

3.2 SQL解析器

接著問題來了，SQL接口如何執(zhí)行本文sql呢？，數(shù)據(jù)庫怎么理解本文這個sql呢？相信懂sql語法的人立馬就能知道什么意思，但是MYSQL是個系統(tǒng)不是人，它無法直接理解sql的意思，這個時候關(guān)鍵的組件出場了，SQL解析器的作用主要就是是解析sql語句，最終生成語法樹，比如本文sql就可以拆解成如下幾個部分：

• 需要從users表里更新數(shù)據(jù)
• 需要更新id字段是10的那行數(shù)據(jù)
• 需要把這行數(shù)據(jù)的name字段的值改為 “zhangsan”

3.3 SQL優(yōu)化器

當(dāng)通過SQL 解析器理解了sql語句要干什么之后，該如何實現(xiàn)呢，以本文的更新語句為例，我們可以有以下兩種實現(xiàn)方式：

• 直接定位到users表中id字段等于10的一行數(shù)據(jù)，然后查出這行數(shù)據(jù)數(shù)據(jù)，然后設(shè)置name字段為“zhangsan”；
• 也可以通過更新name字段索引的方式在name索引上遍歷id等于10的索引值，然后設(shè)置name字段為“zhangsan”。

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

3.4 執(zhí)行器

通過SQL優(yōu)化器我們得到一套執(zhí)行計劃，那么，這個計劃怎么執(zhí)行呢？這個時候就不得不提MYSQL存儲引擎，我們都知道MySQL和其他關(guān)系型數(shù)據(jù)庫不一樣的地方在于它的彈性以及可以通過插件形式提供不同種類的存儲引擎，類似java接口的多實現(xiàn)，MYSQL肯定會有一套標(biāo)準(zhǔn)的存儲引擎接口，而執(zhí)行器就是按照執(zhí)行計劃一步一步的調(diào)用存儲引擎接口完成sql執(zhí)行而已，如下圖：

上圖專門將binlog標(biāo)出來是為了和下文innodb存儲引擎的undo log、redo log做區(qū)分，強(qiáng)調(diào)binlog是server層的日志，后續(xù)binlog 和redo log的兩階段方式完成事務(wù)的提交會再次提到。

3.5 查詢緩存

MYSQL服務(wù)層為追求高效也引入了QUERY BUFFER 這個組件，但是這個組件比較雞肋，緩存不僅需要sql全字匹配命中，而且對基礎(chǔ)表的任何修改都會導(dǎo)致這些表的所有緩存失效，既不符合現(xiàn)在用戶變量的開發(fā)模式，大部分時候也不高效。

MYSQL從5.7開始不推薦使用默認(rèn)關(guān)閉，8.0中不再支持，詳細(xì)原因如下圖：

截圖來源MYSQL開發(fā)者專區(qū)文檔：

4 存儲引擎層

4.1 概述

上文執(zhí)行器拿到執(zhí)行計劃后，調(diào)用存儲引擎的接口來完成sql的執(zhí)行，那么存儲引擎如何幫助我們?nèi)ピL問、操作內(nèi)存以及磁盤上的數(shù)據(jù)呢？我們都知道MYSQL的存儲引擎有很多，實現(xiàn)方式各一，下面讓我們繼續(xù)通過上文的sql來初步了解我們常用的Innodb存儲引擎的核心原理和架構(gòu)設(shè)計

重溫一下本文sql：

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

4.2 緩沖池（buffer pool）

InnoDB存儲引擎中有一個非常重要的放在內(nèi)存里的組件，就是緩沖池（Buffer Pool），這里面會緩存很多的數(shù)據(jù)，以便于以后在查詢的時候，萬一你要是內(nèi)存緩沖池里有數(shù)據(jù)，就可以不用去查磁盤了，如下圖：

緩沖池（buffer pool）在Innodb中的地位類似于我們現(xiàn)在系統(tǒng)設(shè)計中redis的地位，在Innodb中引入這一組件的就是為了高效的存取，我們都知道MYSQL查詢數(shù)據(jù)很快，究其原因不止是索引查詢，深層次的原因就是所有的增刪改查都是在buffer pool這塊內(nèi)存上操作的，相比于操作磁盤，效率不言自明。

4.2.1 數(shù)據(jù)頁、緩存頁和臟頁

還是拿我們的sql舉例，更新id=10的這條記錄，難道從磁盤里只拉取id=10數(shù)據(jù)進(jìn)入內(nèi)存中嗎？很明顯不是，畢竟加入內(nèi)存的記錄不止這一張表，而且單表每行記錄也不一樣，內(nèi)存管理會非常困難的，所以，MYSQL對數(shù)據(jù)抽象出來的一個叫數(shù)據(jù)頁的邏輯概念，每頁固定大小默認(rèn)16KB，可以存多條數(shù)據(jù)，并且buffer pool里的存儲結(jié)構(gòu)和數(shù)據(jù)頁一致，這樣內(nèi)存管理就會簡單的多，數(shù)據(jù)頁注冊元數(shù)據(jù)后加載進(jìn)內(nèi)存后就是緩存頁。

從圖中可以看到在緩存頁在sql更新完還未刷回硬盤時數(shù)據(jù)和磁盤中的數(shù)據(jù)頁是不一致的，這個時候我們稱這種緩存頁為臟頁。至于后續(xù)臟頁如何落盤暫時不提。

4.2.2 元數(shù)據(jù)

從上圖我們看到buffer pool中除了緩存頁，還多了一個元數(shù)據(jù)內(nèi)存結(jié)構(gòu)，這個可以簡單的理解為登記，比如因為疫情外地人回家過年會被當(dāng)?shù)卣M(jìn)行登記，記錄從哪來、到哪去等信息，便于管理，buffer pool也是這樣做的；但是元數(shù)據(jù)可不止記錄緩存頁的磁盤地址和內(nèi)存地址這么簡單，buffer pool核心原理都是通過元數(shù)據(jù)來實現(xiàn)的

4.2.3 free鏈表

buffer pool在MYSQL初始化的時候，就根據(jù)配置在內(nèi)存中申請了一塊連續(xù)的空間，申請過后就按數(shù)據(jù)頁的大小和元數(shù)據(jù)的大小進(jìn)行合理的劃分出很多個連續(xù)的、空的緩存頁,當(dāng)需要查詢數(shù)據(jù)的時候就會從磁盤讀入數(shù)據(jù)頁放入到緩存頁當(dāng)中,但是由于臟頁的存在，數(shù)據(jù)還未刷盤不能使用，那么數(shù)據(jù)頁加載進(jìn)哪個緩存頁就是個問題。為了解決哪些緩存頁是空閑的，MYSQL團(tuán)隊為Buffer pool設(shè)計了一個free鏈表，它是一個雙向鏈表的數(shù)據(jù)結(jié)構(gòu)，這個free鏈表里每個節(jié)點就是一個空閑的緩存頁的元數(shù)據(jù)塊地址，也就是說只要一個緩存頁是空閑的，那么他的元數(shù)據(jù)塊就會放入這個free鏈表中,這樣加載數(shù)據(jù)頁是只需要從free鏈表中找空閑的緩存頁即可。

從圖中即可看出鏈表的大致結(jié)構(gòu)，那么現(xiàn)在我們要更新users表中id=10的記錄，首先要知道id=10這條記錄的數(shù)據(jù)頁有沒有在緩存頁當(dāng)中，然后在決定是否是加載數(shù)據(jù)頁還是直接使用緩存頁，所以，buffer pool里還有左下角這種hash表，用表空間+數(shù)據(jù)頁號作為key，緩存頁地址為value，可以快速判斷數(shù)據(jù)頁是否被緩存。

4.2.4 flush鏈表

本文sql執(zhí)行更新后，這樣就導(dǎo)致內(nèi)存中的數(shù)據(jù)和磁盤上的數(shù)據(jù)不一致，這就表明這個緩存頁是臟頁，臟頁是需要刷新到磁盤文件的。但是不可能所有緩存頁都刷回磁盤，比如有的緩存頁可能只是查詢的時候用到了，沒有別更新過，所以數(shù)據(jù)庫就引入flush鏈表，flush鏈表和free鏈表的實現(xiàn)方式一樣，都是在元數(shù)據(jù)中增加兩個指針做成雙向鏈表，用來標(biāo)記鏈表上的都是臟頁，需要刷回磁盤，后續(xù)IO線程異步刷盤就是將flush鏈表的數(shù)據(jù)刷盤，然后把緩存頁移除flush鏈表，加入free鏈表當(dāng)中。

4.2.5 LRU鏈表

隨著不停的把磁盤上的數(shù)據(jù)頁加載到空閑的緩存頁里去，free鏈表中空閑的緩存頁越來越少，如果free鏈表空了，這時候就無法從磁盤加載數(shù)據(jù)頁了，這時候就需要淘汰掉一些緩存頁，首先想到的就是把修改過的緩存頁刷新回磁盤上，然后清空這個緩存頁

具體選擇哪個緩存頁進(jìn)行清空呢，數(shù)據(jù)庫引入LRU鏈表,結(jié)構(gòu)和free鏈表基本一致,最近訪問的緩存頁都會被移動到LRU鏈表的頭部,這樣尾部的就是少訪問的數(shù)據(jù)，但是這樣的LRU有個問題，就是MYSQL的預(yù)讀機(jī)制，會把不常訪問或者不訪問的數(shù)據(jù)連帶著加載到內(nèi)存，這樣就把這一部分也放在了LRU頭結(jié)點上，很明顯不合理,同理，全表掃描也有這個問題。

從上面可以看出，如果此時需要淘汰緩存頁，就可能把熱點數(shù)據(jù)提前淘汰掉。對于這種不合理的LRU算法MYSQL基于冷熱數(shù)據(jù)分離的方法對LRU算法進(jìn)行如下優(yōu)化：LRU鏈表被拆分為兩個部分，一部分熱數(shù)據(jù)，一部分冷數(shù)據(jù)，數(shù)據(jù)頁第一次加載到緩存的時候是放在冷數(shù)據(jù)表頭，在1s后再次訪問這個緩存頁，就很有可能是熱數(shù)據(jù)，就會把它挪到熱數(shù)據(jù)表頭區(qū)域，這樣設(shè)計防止了剛加載就訪問造成的假熱現(xiàn)象。

冷熱區(qū)域緩存頁移動規(guī)則如下：

• 冷數(shù)據(jù) -> 熱數(shù)據(jù)：冷數(shù)據(jù)區(qū)的緩存頁是在 1s 后再被訪問到就移動到熱數(shù)據(jù)區(qū)的鏈表頭部

• 熱數(shù)據(jù) -> 冷數(shù)據(jù)：能留在熱數(shù)據(jù)區(qū)域的緩存頁，證明都是緩存命中率比較高的，會經(jīng)常被訪問到。如果每個緩存頁被訪問都移動到鏈表頭部，那這個操作將會非常的頻繁。所以 InnoDB 存儲引擎做了一個優(yōu)化，只有在熱數(shù)據(jù)區(qū)域的后 3/4 的緩存頁被訪問了，才會移動到鏈表頭部；如果是熱數(shù)據(jù)區(qū)域的前 1/4 的緩存頁被訪問到，它是不會被移動到鏈表頭部去的。這樣盡可能的減少鏈表中節(jié)點的移動了

4.2.6 小結(jié)

現(xiàn)在我們了解了更新數(shù)據(jù)會先把數(shù)據(jù)加載進(jìn)buffer pool在進(jìn)行，了解buffer pool是如何通過冷熱數(shù)據(jù)分離的機(jī)制優(yōu)化LRU鏈表，為系統(tǒng)設(shè)計中緩存過期淘汰策略提供的新的解決思路。既然，數(shù)據(jù)更新是把數(shù)據(jù)載入buffer pool中修改，那么更新完緩存頁之后數(shù)據(jù)庫是如何保證事務(wù)提交、如何保證數(shù)據(jù)頁和緩存頁數(shù)據(jù)一致的呢

4.3 undo log

說到事務(wù)就不得不提事務(wù)是如何回滾的，innodb是引入了undo log的日志組件來實現(xiàn)事務(wù)回滾的，以本文sql為例, 在數(shù)據(jù)加載進(jìn)緩存頁后，修改之前，會將執(zhí)行的sql取反保存在undo log中，邏輯類似sql：

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

當(dāng)然如果是insert語句與之對應(yīng)的就是delete語句，delete語句也就對應(yīng)的insert 語句，這也就明白為什么delete的數(shù)據(jù)是可以回滾，而truncate數(shù)據(jù)之后無法回滾的根本原因，在于truncate無法生成undo log。

上圖是本問sql執(zhí)行的大致步驟，至于加入buffer pool這塊上文已經(jīng)詳細(xì)了解過了，就不在贅述。從圖中可以看出因為log直接刷盤比較損耗性能，所以引入log buffer進(jìn)行緩存，然后在通過異步的方式把數(shù)據(jù)刷入磁盤既然數(shù)據(jù)更新之前的數(shù)據(jù)記錄下來并成功刷入磁盤，則事務(wù)的回滾就不難實現(xiàn)了。

當(dāng)然undo log 除了提供回滾功能，還為多版本并發(fā)控制（MVCC）提供了實現(xiàn)基礎(chǔ)，實現(xiàn)了MYSQL的非阻塞讀寫，提高了系統(tǒng)的并發(fā)性。本文也不再深入

4.4 redo log

下面來了解一下innodb是如何保證buffer pool緩存的數(shù)據(jù)一致性問題，數(shù)據(jù)更新值內(nèi)存后并不會立即刷新至磁盤數(shù)據(jù)頁，而是一致以臟頁的形式保存在buffer pool當(dāng)中，這樣做有兩個原因會導(dǎo)致效率很差，一個是內(nèi)存向磁盤寫數(shù)據(jù)本身效率就慢，另一個就是隨機(jī)IO會寫磁盤的時間上附加上很多磁頭尋址的時間，所以立即刷數(shù)據(jù)頁效率很低。

Innodb是如何規(guī)避上述問題的呢，正常情況下，異步刷盤就已經(jīng)可以解決了刷磁盤慢的問題，但是，假如MYSQL系統(tǒng)崩潰、宕機(jī)，這時候臟頁還未及時刷盤，那么緩存頁期間所有改動數(shù)據(jù)豈不是丟了，所以，Innodb引入了另一個組件redo log，專門記錄數(shù)據(jù)被緩存期間做過的修改記錄，然后立即寫入redo log磁盤文件，相比于緩存頁刷盤，redo log刷盤的數(shù)據(jù)了小多了，并且寫redo log是順序IO，而緩存頁刷盤是隨機(jī)IO。

下圖示意：

這樣當(dāng)數(shù)據(jù)庫異常宕機(jī)時，即使緩存頁丟失數(shù)據(jù)也不會丟失，因為redo log已經(jīng)落盤，數(shù)據(jù)庫重啟的時候會更近redo log把磁盤上歷史的數(shù)據(jù)頁重新載入內(nèi)存，重新按redo log的修改記錄操作一遍就能將緩存頁中的數(shù)據(jù)恢復(fù)至宕機(jī)前的狀態(tài)。

如果系統(tǒng)宕機(jī)時，redo log還沒落盤數(shù)據(jù)豈不是丟了，對，這種情況下數(shù)據(jù)會丟，這種redo log丟數(shù)據(jù)分兩中情況：

第一種情況，MYSQL有三種刷盤策略，通過innodb_flush_log_at_trx_commit參數(shù)進(jìn)行配置

• 配置為0：事務(wù)提交的時候不會把redolog buffer里的數(shù)據(jù)立即刷入磁盤，此時如果宕機(jī)則會導(dǎo)致已提交的數(shù)據(jù)修改丟失；
• 配置為1：則是事務(wù)提交的時候必須把redolog buffer里的數(shù)據(jù)刷入磁盤，以保證事務(wù)提交后操作數(shù)據(jù)日志不丟；
• 配置為2：則表示只是把數(shù)據(jù)交給操作系統(tǒng)進(jìn)行刷盤，操作系統(tǒng)刷沒刷成功則不管，理論上操作系統(tǒng)刷盤是先要經(jīng)過os cache內(nèi)存緩存的，就是說數(shù)據(jù)會先在os chache里沒有真正的落盤，這種模式下也可能導(dǎo)致數(shù)據(jù)丟失

這第一種情況如果產(chǎn)生丟數(shù)據(jù)，是真的丟失，所以，如果對數(shù)據(jù)庫丟失數(shù)據(jù)零容忍，建議配置策略為1

第二種情況，就是未寫commit標(biāo)記日志的情況，即下圖第9步丟失的情況，但是這種情況系統(tǒng)認(rèn)為事務(wù)提交失敗，所以丟失了并不影響數(shù)據(jù)一致性。

圖中7、8、9三個步驟是事務(wù)提交commit的時候才做的（本文只用一個sql來講解，默認(rèn)事務(wù)自動提交），redo log記錄更新記錄之后，執(zhí)行器會把修改記錄寫在server層的binlog當(dāng)中，很明顯這是兩個文件，如果出現(xiàn)上述宕機(jī)等異常情況，這兩個文件的數(shù)據(jù)一致性是不能保證的，所以，為了保證兩個文件的數(shù)據(jù)一致性，innodb會在binlog寫完之后在redo log中補(bǔ)上一個commit標(biāo)記告訴redo log事務(wù)成功。事務(wù)執(zhí)行成功后操作redo log刷入磁盤，至此本文sql執(zhí)行成功。

5 總結(jié)

通過一條update的sql的更新流程，清晰的看到MYSQL的整體架構(gòu)設(shè)計，對Innodb存儲引擎的幾大核心組件如何相互協(xié)作、配合以實現(xiàn)高效的數(shù)據(jù)庫系統(tǒng)有了更清晰的認(rèn)識；核心組件buffer pool的冷熱數(shù)據(jù)分離的緩存淘汰機(jī)制也為以后系統(tǒng)的架構(gòu)設(shè)計提供了新的解決思路。

到此這篇關(guān)于一條sql詳解MYSQL的架構(gòu)設(shè)計詳情的文章就介紹到這了,更多相關(guān)MYSQL架構(gòu)設(shè)計內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！