技術文章 » 我所理解的MySQL之一：基礎架構

我所理解的MySQL之一：基礎架構

2020-10-20 18:00:05

今天欄目為大家介紹我所理解的基礎架構。

作為一個正經的 CRUD 工程師，與資料庫的互動是日常工作中比重較大的內容，比如日常迭代的增刪改查、處理歷史資料、優化 SQL 效能等等。隨著專案資料量的增長，從前為了趕專案進度而埋下的深坑正慢慢顯露它們的威力，這也讓我不得不全面且深入的學習 MySQL，而不僅僅是停留在基礎的 CRUD 上。

MySQL 系列的第一篇，主要介紹 MySQL 的基礎架構以及各個組成部分的功能，包括 Server 層的 bin log 和 InnoDB 特有的 redo log 這兩種紀錄檔模組。

1. MySQL 架構簡介

根據 DB-Engines 釋出的最受歡迎的資料庫管理系統排行榜，MySQL 穩坐第二把交椅。

2020年9月最受歡迎的DBMS排行榜

作為最受歡迎的關係型資料庫管理系統之一，MySQL 採用的是C/S架構，即 Client & Server 架構。比如開發者使用 Navicat 連線到 MySQL，那麼前者就是使用者端，後者就是伺服器端。

同時，MySQL 也是單程序多執行緒的資料庫。這很好理解，正在執行的 MySQL 範例就是那個「單程序」，而在這個程序中會有很多個執行緒，比如主執行緒 Master Thread，IO Thread 等，這些執行緒被用於處理不同的任務。

2. MySQL 組成部分

前面說到 MySQL 採用的是C/S架構，使用者通過使用者端連線到 MySQL 伺服器，然後提交 SQL 語句到伺服器，然後伺服器就會把執行結果返回給客服端。

在這一小節的內容中，我們主要關注 MySQL 伺服器端的邏輯組成，先來看一張圖。

MySQL 邏輯架構圖

從上圖可以看到，與使用者端的互動中，MySQL 的伺服器端分別經過了聯結器、查詢快取、分析器、優化器、執行器和儲存引擎這幾部分。

下面就以一條簡單的查詢語句來描述 MySQL 伺服器端的各組成部分及它們所起的作用。

2.1 聯結器

在使用者端提交查詢語句之前，需要與伺服器端建立連線。所以最先來到的是聯結器，聯結器的作用就是負責與使用者端建立、管理連線，同時查詢使用者的許可權。

需要注意的是：

  • 聯結器只獲取使用者的許可權，並不做校驗，校驗是在查詢快取或執行器才進行。
  • 一旦建立連線同時獲取使用者的許可權之後，只有建立新的連線才會重新整理使用者許可權。
  • 對於長時間沒有傳送請求的使用者端，聯結器會自動斷開連線。這裡的「長時間」是由 wait_timeout 引數來決定的，它的預設值為8小時。

2.2 查詢快取

在經過聯結器的建立連線、獲取使用者許可權之後，接下來使用者可以提交查詢語句了。

最先經過的是查詢快取部分，由它的名字也能夠猜到，查詢快取的作用就是查詢 MySQL 是否執行過使用者端提交的查詢語句，如果這條 SQL 之前執行過，並且使用者對該表有執行該語句的許可權，就會直接返回之前執行的結果。

所以在某些時候，多次執行一句 SQL 並不能得到它的平均執行時間，因為查詢快取的關係，後面的執行時間往往比第一次執行要短。

如果你不想使用快取，可以在每次查詢後都用 update 語句更新表，當然這是非常麻煩並且憨的方法。MySQL也提供了相應的設定項—— query_cache_type，你可以在 my.cnf 檔案中將 query_cache_type 設定為0以關閉查詢快取。

需要注意的是：

  • 查詢快取部分是以 key-value 形式進行儲存的，key 為查詢語句，value 是查詢結果。
  • 當對資料表進行更新時，關於這張表的所有查詢快取都會失效，所以一般來說查詢快取的命中率是很低的。
  • 在 MySQL 8.0 的版本中，查詢快取的功能已經被刪除。

2.3 分析器

我使用的 MySQL 版本是5.7.21，所以使用者端提交的查詢語句會走查詢快取，如果沒有命中，那麼將繼續往下走，來到分析器。

分析器會對提交的語句進行詞法分析（解析語句）和語法分析（判斷語句是否符合 MySQL 的語法規則），所以分析器的作用就是解析 SQL 語句並檢查其合法性。

需要注意的是：

  • MySQL 在檢查 SQL 語句合法性時，僅會在最先不符合 MySQL 語法規則的地方提示錯誤，並不會將 SQL 語句中所有語法錯誤的地方全部展示。

舉個例子：

select * form user_info limit 1;複製程式碼

上面這句 SQL 有兩個錯誤，第一是 from 拼寫錯誤，第二是不存在 user_info 這張表，在執行之後，MySQL只會提醒一個錯誤，下面展示了三次執行 SQL 的結果資訊。

第一次的執行資訊：
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'form user_info limit 1' at line 1, Time: 0.000000s

修改為from後第二次的執行資訊：1146 - Table 'windfall.user_info' doesn't exist, Time: 0.000000s

修改為 user 表後第三次的執行資訊：
OK, Time: 0.000000s複製程式碼

2.4 優化器

在校驗了 SQL 語句的合法性之後，MySQL 已經知道使用者提交的語句是幹什麼的了，但是在真正執行之前，還需要經過非常「玄學」的優化器。

你相信玄學嗎

優化器的作用是為 SQL 語句生成最優的執行計劃。

之所以說優化器很「玄學」，是因為它在優化 SQL 語句的過程中可能會生成出乎使用者意料之外的執行計劃（索引選擇、多表關聯連線順序、隱式函數轉換等）。當然優化器有時候也會「選錯」索引，這與資料量、索引統計資訊等因素有關。

需要注意的是：

  • 如果你需要優化一條生產環境的 SQL，請儘量在本地還原與生產環境資料量相同的表，然後根據執行計劃進行優化。
  • 在寫查詢語句的時候，一定要考慮到索引的最左匹配原則（關於最左匹配原則的整理在索引篇再寫）。

關於 MySQL 優化器的工作流程，可以看看這篇部落格：MySQL 優化器原來是這樣工作的

MySQL 的執行計劃也是一項必須要掌握的技能，這篇部落格寫得非常詳細，值得一讀：不會看 Explain執行計劃，勸你簡歷別寫熟悉 SQL優化

2.5 執行器

在優化器生成了 MySQL 認為最優的執行計劃之後，最後來到了執行器，執行器的作用當然就是執行SQL語句了。

但是在執行之前，先要做許可權驗證，驗證使用者對錶是否有查詢許可權。然後再根據表定義的引擎型別，去使用相對應引擎提供的介面來對該表進行條件查詢，最後將該表所有滿足條件的資料行作為結果集返回使用者端，這樣整個 SQL 的執行就結束了。

需要注意的是：

  • 在執行器執行 SQL 語句前會做校驗：判斷使用者對錶是否具有操作許可權。

2.6 儲存引擎

MySQL 支援的儲存引擎有很多種，比如：InnoDB、MyISAM、Memory 等等。

2.6.1 InnoDB

InnoDB 是當下最常用的的 MySQL 儲存引擎，同時也是 MySQL 5.5 之後的預設儲存引擎。

InnoDB 支援事務、MVCC（多版本並行控制）、外來鍵、行級鎖和自增列。但是 InnoDB 不支援全文索引，同時它佔用的資料空間更大。

2.6.2 MyISAM

MyISAM 是 MySQL 5.1 及之前的預設儲存引擎，支援全文索引、壓縮、空間函數、表級鎖。

MyISAM 的資料以緊密格式儲存所以佔用空間更小，它擁有較高的插入和查詢速度，但是 MyISAM 不支援事務，且崩潰後無法安全恢復。

2.6.3 Memory

Memory 的所有資料都儲存的記憶體中，由於不需要磁碟 I/O，所以它的速度比 MyISAM 和 InnoDB 快了一個數量級。但如果資料庫關閉或重新啟動，Memory 引擎的資料就會消失。

Memory 支援 Hash 索引，但由於它使用表級鎖，因此並行寫入的效能比較低。

值得一提的是，MySQL 中的臨時表，一般是用 Memory 表儲存的，如果中間表資料量過大或含有 BLOB 型別或 TEXT 型別的欄位，就會使用 MyISAM 表。

關於儲存引擎，由於本人接觸的比較少，等看完《MySQL技術內幕：InnoDB儲存引擎》之後再整理，這裡只是簡單地提一下。

3. 紀錄檔模組

前面所說的執行流程主要是描述查詢語句，如果是更新語句還涉及到 MySQL 的紀錄檔模組。

從使用者端到執行器的之間的邏輯查詢語句和更新語句是相同的，只是在到執行器這一層的時候，更新語句會和 MySQL 的紀錄檔模組產生互動，這是查詢語句和更新語句不一樣的地方。

3.1 物理紀錄檔 redo log

3.1.1 redo log 中記錄的內容

對於 InnoDB 儲存引擎來說，它有一個特有的紀錄檔模組——物理紀錄檔（重做紀錄檔）redo log，它是 InnoDB 儲存引擎的紀錄檔，它所記錄的是資料頁的物理修改。

舉個例子，現在有一張 user 表，有一條主鍵 id=1，age=18 的資料，然後使用者提交了下面這條 SQL，執行器準備執行。

update user set age=age+1 where id=1;複製程式碼

對於這條 SQL，在 redo log 中記錄的內容大致是：將 user 表中主鍵 id=1 行的 age 欄位值修改為19。

3.1.2 WAL

MySQL 的更新持久化邏輯運用到了 WAL(Write-Ahead Logging，寫前紀錄檔記錄) 的思想：先寫紀錄檔，再寫磁碟。

需要注意的是這裡的寫紀錄檔也是寫到磁碟中，但由於紀錄檔是順序寫入的，所以速度很快。而如果沒有 redo log，直接更新磁碟中的資料，那麼首先需要找到那條記錄，然後再把新的值更新進入，由於查詢和讀寫I/O，就相對會慢一些。

最後，當 InnoDB 引擎空閒的時候，它會去執行 redo log 中的邏輯，將資料持久化到磁碟中。

3.1.3 redo log 紀錄檔檔案

redo log 紀錄檔檔案大小是固定的，我把它理解為一個迴圈連結串列，連結串列的每個節點都可以存放紀錄檔，在這個連結串列中有兩個指標：write（黑） 和 read（白）。

迴圈連結串列

最開始這兩個指標都指向同一個節點，且節點紀錄檔元素都為空，表示此時 redo log 為空。當使用者開始提交更新語句，write 節點開始往前移動，假設移動到3的位置。而此時的情況就是 redo log 中有1-3這三個紀錄檔元素需要被持久化到磁碟中，當 InnoDB 空閒時，read 指標往前移動，就代表著將 redo log 持久化到磁碟。

但這裡有一種特殊情況，就是 InnoDB 一直沒有空閒，write 指標一直在寫入紀錄檔，直到它寫到5的位置，再往前寫又回到了最開始1的位置（也就是上圖的位置，但不同的是連結串列節點中都存在紀錄檔資料）。

此時發現1的位置已經有紀錄檔資料了，同時 read 指標也在。那麼這時候 write 指標就會暫停寫入，InnoDB 引擎開始催動 read 指標移動，把 redo log 清空掉一部分之後再讓 write 指標寫入紀錄檔檔案。

3.1.4 redo log 的作用

我們已經知道，redo log 中記錄的是資料頁的物理修改，所以 redo log 能夠保證在資料庫發生異常重新啟動時，記錄尚未寫入磁碟，但是在重新啟動後可以通過 redo log 來「redo」，從而不會發生記錄丟失的情況，保證了事務的永續性。

這一能力也被稱作 crash-safe。

3.2 歸檔紀錄檔 bin log

前面說到 redo log 是 InnoDB 特有的紀錄檔，而 bin log 則是屬於 MySQL Server 層的紀錄檔，在預設的 Statement Level 下它記錄的是更新語句的原始邏輯，即 SQL 本身。

另外需要注意的是：

  • bin log 的紀錄檔檔案大小並不固定，它是「追加寫入」的模式，寫完一個檔案後會切換到下一個檔案寫入。
  • bin log 沒有 crash-safe 的能力。
  • bin log 是在事務最終提交前寫入的，而 redo log 是在事務執行中不斷寫入的。

3.2.1 bin log 的作用

與 redo log 不同的是，bin log 常用於恢復資料，比如說主從複製，從節點根據父節點的 bin log 來進行資料同步，實現主從同步。

3.3 兩階段提交

為了讓 redo log 和 bin log 的狀態保持一致，MySQL 使用兩階段提交的方式來寫入 redo log 紀錄檔。

在執行器呼叫 InnoDB 引擎的介面將寫入更新資料時，InnoDB 引擎會將本次更新記錄到 redo log 中，同時將 redo log 的狀態標記為 prepare，表示可以提交事務。

隨後執行器生成本次操作的 bin log 資料，並寫入 bin log 的紀錄檔檔案中。

最後執行器呼叫 InnoDB 的提交事務介面，儲存引擎把剛寫入的 redo log 記錄狀態修改為 commit，本次更新結束。

在這個過程中有三個步驟 add redo log and mark as prepare -> add bin log -> commit，即：

  1. 寫入 redo log 紀錄檔並標記為 prepare
  2. 寫入 bin log
  3. 提交事務

如果在第二個步驟，也就是寫入 bin log 之前系統崩潰或重新啟動，啟動後由於 bin log 中沒有記錄，會將 redo log 中的記錄回滾至執行本次更新語句前。

如果在第三個步驟前，也就是提交之前系統崩潰或重新啟動，即便沒有 commit 但是滿足 redo log 中記錄為 prepare 狀態並且 bin log 中也有完整記錄，在重新啟動後會自動 commit，並不會回滾。

4. 小結

本文主要介紹 MySQL 的基礎架構以及各個組成部分的功能，最後介紹了 MySQL Server 層的 bin log 和 InnoDB 特有的 redo log 這兩種紀錄檔模組。

5. 溫故知新

以下的幾個問題是對本文所描述內容的提問，鞏固知識，正所謂「溫故而知新，可以為師矣」。

  1. 如果查詢語句中欄位不存在、欄位有歧義、關鍵字拼寫錯誤，是由哪個部分報錯？
  2. 如果使用者對錶沒有查詢許可權，是哪個部分報錯？
  3. 為什麼 MySQL 的查詢快取會無效？
  4. 一條 select 查詢語句是如何執行的？
  5. MySQL 常用的儲存引擎有哪些？
  6. MySQL 的紀錄檔模組有哪些？分別起到什麼作用？
  7. redo log 寫滿了怎麼辦？
  8. 如何理解 redo log 的兩階段提交？

  9. redo log 和 bin log 的區別？

更多相關免費學習推薦：(視訊)

以上就是我所理解的MySQL之一：基礎架構的詳細內容，更多請關注TW511.COM其它相關文章！