MYSQL的架構(gòu)設(shè)計(jì)是什么

這篇文章主要介紹了MYSQL的架構(gòu)設(shè)計(jì)是什么的相關(guān)知識，內(nèi)容詳細(xì)易懂，操作簡單快捷，具有一定借鑒價(jià)值，相信大家閱讀完這篇MYSQL的架構(gòu)設(shè)計(jì)是什么文章都會有所收獲，下面我們一起來看看吧。

1 前言

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

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

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

• 一個(gè)表名為users的表

• 有兩個(gè)字段 id、name，id是主鍵

• 把users表里的id=10的這個(gè)用戶名修改為“zhangsan”

那么MYSQL是如何處理這樣一個(gè)sql呢？帶著這個(gè)問題，我們來看一下MYSQL是如何通過一個(gè)個(gè)組件來處理這個(gè)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ù)庫建立連接，那么這個(gè)連接由誰來建呢，答案就是MYSQL驅(qū)動，下面這段maven配置大家應(yīng)該都很熟悉

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

下圖示意：

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

下圖示意：

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

3 服務(wù)層

3.1 SQL 接口

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

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

3.2 SQL解析器

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

• 需要從users表里更新數(shù)據(jù)

• 需要更新id字段是10的那行數(shù)據(jù)

• 需要把這行數(shù)據(jù)的name字段的值改為 “zhangsan”

3.3 SQL優(yōu)化器

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

• 直接定位到users表中id字段等于10的一行數(shù)據(jù)，然后查出這行數(shù)據(jù)數(shù)據(jù)，然后設(shè)置name字段為“zhangsan”；

• 也可以通過更新name字段索引的方式在name索引上遍歷id等于10的索引值，然后設(shè)置name字段為“zhangsan”。

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

3.4 執(zhí)行器

通過SQL優(yōu)化器我們得到一套執(zhí)行計(jì)劃，那么，這個(gè)計(jì)劃怎么執(zhí)行呢？這個(gè)時(shí)候就不得不提MYSQL存儲引擎，我們都知道MySQL和其他關(guān)系型數(shù)據(jù)庫不一樣的地方在于它的彈性以及可以通過插件形式提供不同種類的存儲引擎，類似java接口的多實(shí)現(xiàn)，MYSQL肯定會有一套標(biāo)準(zhǔn)的存儲引擎接口，而執(zhí)行器就是按照執(zhí)行計(jì)劃一步一步的調(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 這個(gè)組件，但是這個(gè)組件比較雞肋，緩存不僅需要sql全字匹配命中，而且對基礎(chǔ)表的任何修改都會導(dǎo)致這些表的所有緩存失效，既不符合現(xiàn)在用戶變量的開發(fā)模式，大部分時(shí)候也不高效。

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

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

4 存儲引擎層

4.1 概述

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

重溫一下本文sql：

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

4.2 緩沖池（buffer pool）

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

緩沖池（buffer pool）在Innodb中的地位類似于我們現(xiàn)在系統(tǒng)設(shè)計(jì)中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ù)抽象出來的一個(gè)叫數(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í)數(shù)據(jù)和磁盤中的數(shù)據(jù)頁是不一致的，這個(gè)時(shí)候我們稱這種緩存頁為臟頁。至于后續(xù)臟頁如何落盤暫時(shí)不提。

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

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

4.2.3 free鏈表

buffer pool在MYSQL初始化的時(shí)候，就根據(jù)配置在內(nèi)存中申請了一塊連續(xù)的空間，申請過后就按數(shù)據(jù)頁的大小和元數(shù)據(jù)的大小進(jìn)行合理的劃分出很多個(gè)連續(xù)的、空的緩存頁,當(dāng)需要查詢數(shù)據(jù)的時(shí)候就會從磁盤讀入數(shù)據(jù)頁放入到緩存頁當(dāng)中,但是由于臟頁的存在，數(shù)據(jù)還未刷盤不能使用，那么數(shù)據(jù)頁加載進(jìn)哪個(gè)緩存頁就是個(gè)問題。為了解決哪些緩存頁是空閑的，MYSQL團(tuán)隊(duì)為Buffer pool設(shè)計(jì)了一個(gè)free鏈表，它是一個(gè)雙向鏈表的數(shù)據(jù)結(jié)構(gòu)，這個(gè)free鏈表里每個(gè)節(jié)點(diǎn)就是一個(gè)空閑的緩存頁的元數(shù)據(jù)塊地址，也就是說只要一個(gè)緩存頁是空閑的，那么他的元數(shù)據(jù)塊就會放入這個(gè)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ù)不一致，這就表明這個(gè)緩存頁是臟頁，臟頁是需要刷新到磁盤文件的。但是不可能所有緩存頁都刷回磁盤，比如有的緩存頁可能只是查詢的時(shí)候用到了，沒有別更新過，所以數(shù)據(jù)庫就引入flush鏈表，flush鏈表和free鏈表的實(shí)現(xiàn)方式一樣，都是在元數(shù)據(jù)中增加兩個(gè)指針做成雙向鏈表，用來標(biāo)記鏈表上的都是臟頁，需要刷回磁盤，后續(xù)IO線程異步刷盤就是將flush鏈表的數(shù)據(jù)刷盤，然后把緩存頁移除flush鏈表，加入free鏈表當(dāng)中。

4.2.5 LRU鏈表

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

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

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

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

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

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

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è)計(jì)中緩存過期淘汰策略提供的新的解決思路。既然，數(shù)據(jù)更新是把數(shù)據(jù)載入buffer pool中修改，那么更新完緩存頁之后數(shù)據(jù)庫是如何保證事務(wù)提交、如何保證數(shù)據(jù)頁和緩存頁數(shù)據(jù)一致的呢

4.3 undo log

說到事務(wù)就不得不提事務(wù)是如何回滾的，innodb是引入了undo log的日志組件來實(shí)現(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ì)了解過了，就不在贅述。從圖中可以看出因?yàn)閘og直接刷盤比較損耗性能，所以引入log buffer進(jìn)行緩存，然后在通過異步的方式把數(shù)據(jù)刷入磁盤既然數(shù)據(jù)更新之前的數(shù)據(jù)記錄下來并成功刷入磁盤，則事務(wù)的回滾就不難實(shí)現(xiàn)了。

當(dāng)然undo log 除了提供回滾功能，還為多版本并發(fā)控制（MVCC）提供了實(shí)現(xiàn)基礎(chǔ)，實(shí)現(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)中，這樣做有兩個(gè)原因會導(dǎo)致效率很差，一個(gè)是內(nèi)存向磁盤寫數(shù)據(jù)本身效率就慢，另一個(gè)就是隨機(jī)IO會寫磁盤的時(shí)間上附加上很多磁頭尋址的時(shí)間，所以立即刷數(shù)據(jù)頁效率很低。

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

下圖示意：

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

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

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

• 配置為0：事務(wù)提交的時(shí)候不會把redolog buffer里的數(shù)據(jù)立即刷入磁盤，此時(shí)如果宕機(jī)則會導(dǎo)致已提交的數(shù)據(jù)修改丟失；

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

關(guān)于“MYSQL的架構(gòu)設(shè)計(jì)是什么”這篇文章的內(nèi)容就介紹到這里，感謝各位的閱讀！相信大家對“MYSQL的架構(gòu)設(shè)計(jì)是什么”知識都有一定的了解，大家如果還想學(xué)習(xí)更多知識，歡迎關(guān)注億速云行業(yè)資訊頻道。