一條sql瞭解MYSQL的架構設計

来源:https://www.cnblogs.com/Jcloud/archive/2022/09/21/16714677.html
-Advertisement-
Play Games

對於一個服務端開發來說 MYSQL 可能是他使用最熟悉的資料庫工具,然而,大部分的Java工程師對MySQL的瞭解和掌握程度,大致就停留在這麼一個階段:它可以建庫、建表、建索引,然後就是對裡面的數據進行增刪改查,語句性能有點差?沒關係,在表裡建幾個索引或者調整一下查詢邏輯就可以了,一條sql,MYS... ...


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就可以拆解成如下幾個部分:

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

3.3 SQL優化器

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

  1. 直接定位到users表中id欄位等於10的一行數據,然後查出這行數據數據,然後設置name欄位為“zhangsan”;
  2. 也可以通過更新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開發者專區文檔:https://dev.mysql.com/blog-archive/mysql-8-0-retiring-support-for-the-query-cache/

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參數進行配置

  1. 配置為0:事務提交的時候不會把redolog buffer里的數據立即刷入磁碟,此時如果宕機則會導致已提交的數據修改丟失;
  2. 配置為1:則是事務提交的時候必須把redolog buffer里的數據刷入磁碟,以保證事務提交後操作數據日誌不丟;
  3. 配置為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執行成功。

5 總結

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

作者:陸慶林

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • mysql二進位安裝腳本部署 單實例 [[email protected] ~]# mkdir mysql //創建存放腳本目錄 [[email protected] ~]# ls anaconda-ks.cfg mysql [[email protected] ~]# cd mysql/ [[email protected] ...
  • 1.shell命令的執行機制:fork+exec執行命令(任何的shell都會執行) 2.shell中的用戶輸入處理 1 命令行參數:選項、參數 2 運行時輸入 3 read命令: 4 1.基本讀取、 5 2.超時處理-t選項、 6 3.隱藏方式讀取-s選項) 7 4.從文件中讀取 3.shell的 ...
  • 如果現在的我們離開了互聯網,生活會是什麼樣子? 互聯網++++,已經深刻滲透到人們的生活中。 不知道大家有沒有想過?每一個互聯網+結合的背後都是海量的存儲需求。你查看的每一個商品、組建的每一個戰隊、閱讀的每一篇文章,基於互聯網的每一個興趣愛好,都有它的key和value。 在 key-value 數 ...
  • 前言 在正式落地談技術之前,先花一些篇幅說說大數據技術的發展史。我們常說的大數據技術,其實起源於Google在2004年前後發表的三篇論文,分別是分散式文件系統GFS、大數據分散式計算框架MapReduce和NoSQL資料庫系統BigTable(如果大家需要可以留言給我,我可以專門解讀一下)。 一、 ...
  • 2022-09-21 (2)hash操作: ①設置多個hash值,(hmset): 查看說明: help hmset hmset person name A age 1 ②獲得多個hash值,(hmget): 查看說明: help hmget hmget person name age ③將hash ...
  • 摘要:帶你瞭解基於FusionInsight HD&MRS的5種kafka消費端性能優化方法。 本文分享自華為雲社區《FusionInsight HD&MRSkafka消費端性能優化方法》,作者: 穿夾克的壞猴子。 kafka消費端性能優化主要從下麵幾個方面優化: 1.介面使用方面優化: 舊版本hi ...
  • 摘要:RDS關係型資料庫是一種基於雲計算平臺的即開即用、穩定可靠、彈性伸縮、便捷管理的線上關係型資料庫服務。 本文分享自華為雲社區《一致性處理事務這下還是看RDS的吧【秋招特訓】》,作者:樣子的木偶。 什麼是RDS? RDS關係型資料庫是一種基於雲計算平臺的即開即用、穩定可靠、彈性伸縮、便捷管理的在 ...
  • 一、 登錄 1.1 登錄預設資料庫 首先切換到oracle用戶,用資料庫預設管理員登錄。 [[email protected] ~]# su – oracle [[email protected] ~]$ lsnrctl start #開啟監聽 [[email protected] ~]$ sqlplus / as s ...
一周排行
    -Advertisement-
    Play Games
  • Github / Gitee QQ群(1群) : 813100564 / QQ群(2群) : 579033769 視頻教學 介紹 MiniWord .NET Word模板引擎,藉由Word模板和數據簡單、快速生成文件。 Getting Started 安裝 nuget link : https:// ...
  • Array.Sort Array類中相當實用的我認為是Sort方法,相比起冗長的冒泡排序,它的出現讓排序更加的簡化 結果如下: 還可以聲明一個靜態方法用來專門調用指定數組排序,從名為 array 的一維數組中 a 索引處開始,到 b 元素 從小到大排序。 註意: a + b 不能大於 array 的 ...
  • 前言 在上一篇文章CLR類型系統概述里提到,當運行時掛起時, 垃圾回收會執行堆棧遍歷器(stack walker)去拿到堆棧上值類型的大小和堆棧根。這裡我們來翻譯BotR里一篇專門介紹Stackwalking的文章,希望能加深理解。 順便說一句,StackWalker在中文里似乎還沒有統一的翻譯,J ...
  • 使用過 nginx 的小伙伴應該都知道,這個中間件是可以設置跨域的,作為今天的主角,同樣的 反向代理中間件的 YARP 毫無意外也支持了跨域請求設置。 有些小伙伴可能會問了,怎樣才算是跨域呢? 在 HTML 中,一些標簽,例如 img、a 等,還有我們非常熟悉的 Ajax,都是可以指向非本站的資源的 ...
  • 什麼是Git Git 是一個開源的分散式版本控制系統,用於敏捷高效地處理任何或小或大的項目。 Git 是 Linus Torvalds 為了幫助管理 Linux 內核開發而開發的一個開放源碼的版本控制軟體。 Git 與常用的版本控制工具 CVS, Subversion 等不同,它採用了分散式版本庫的 ...
  • 首先CR3是什麼,CR3是一個寄存器,該寄存器內保存有頁目錄表物理地址(PDBR地址),其實CR3內部存放的就是頁目錄表的記憶體基地址,運用CR3切換可實現對特定進程記憶體地址的強制讀寫操作,此類讀寫屬於有痕讀寫,多數驅動保護都會將這個地址改為無效,此時CR3讀寫就失效了,當然如果能找到CR3的正確地址... ...
  • 說明 onlyoffice為一款開源的office線上編輯組件,提供word/excel/ppt編輯保存操作 以下操作均基於centos8系統,officeonly鏡像版本7.1.2.23 鏡像下載地址:https://yunpan.360.cn/surl_y87CKKcPdY4 (提取碼:1f92 ...
  • 二叉樹查找指定的節點 前序查找的思路 1.先判斷當前節點的no是否等於要查找的 2.如果是相等,則返回當前節點 3.如果不等,則判斷當前節點的左子節點是否為空,如果不為空,則遞歸前序查找 4.如果左遞歸前序查找,找到節點,則返回,否繼續判斷,當前的節點的右子節點是否為空,如果不為空,則繼續向右遞歸前 ...
  • ##Invalid bound statement (not found)出現原因和解決方法 ###前言: 想必各位小伙伴在碼路上經常會碰到奇奇怪怪的事情,比如出現Invalid bound statement (not found),那今天我就來分析以下出現此問題的原因。 其實出現這個問題實質就是 ...
  • ###一、背景知識 爬蟲的本質就是一個socket客戶端與服務端的通信過程,如果我們有多個url待爬取,只用一個線程且採用串列的方式執行,那隻能等待爬取一個結束後才能繼續下一個,效率會非常低。 需要強調的是:對於單線程下串列N個任務,並不完全等同於低效,如果這N個任務都是純計算的任務,那麼該線程對c ...