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

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

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

1. MySQL 架構簡介

根據(jù) DB-Engines 發(fā)布的最受歡迎的數(shù)據(jù)庫管理系統(tǒng)排行榜，MySQL 穩(wěn)坐第二把交椅。

作為最受歡迎的關系型數(shù)據(jù)庫管理系統(tǒng)之一，MySQL 采用的是C/S架構，即 Client & Server 架構。比如開發(fā)者使用 Navicat 連接到 MySQL，那么前者就是客戶端，后者就是服務端。

同時，MySQL 也是單進程多線程的數(shù)據(jù)庫。這很好理解，正在運行的 MySQL 實例就是那個“單進程”，而在這個進程中會有很多個線程，比如主線程 Master Thread，IO Thread 等，這些線程被用于處理不同的任務。

2. MySQL 組成部分

前面說到 MySQL 采用的是C/S架構，用戶通過客戶端連接到 MySQL 服務器，然后提交 SQL 語句到服務器，然后服務器就會把執(zhí)行結果返回給客服端。

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

從上圖可以看到，與客戶端的交互中，MySQL 的服務端分別經過了連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器和存儲引擎這幾部分。

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

2.1 連接器

在客戶端提交查詢語句之前，需要與服務端建立連接。所以最先來到的是連接器，連接器的作用就是負責與客戶端建立、管理連接，同時查詢用戶的權限。

需要注意的是：

• 連接器只獲取用戶的權限，并不做校驗，校驗是在查詢緩存或執(zhí)行器才進行。
• 一旦建立連接同時獲取用戶的權限之后，只有建立新的連接才會刷新用戶權限。
• 對于長時間沒有發(fā)送請求的客戶端，連接器會自動斷開連接。這里的「長時間」是由 wait_timeout 參數(shù)來決定的，它的默認值為8小時。

2.2 查詢緩存

在經過連接器的建立連接、獲取用戶權限之后，接下來用戶可以提交查詢語句了。

最先經過的是查詢緩存部分，由它的名字也能夠猜到，查詢緩存的作用就是查詢 MySQL 是否執(zhí)行過客戶端提交的查詢語句，如果這條 SQL 之前執(zhí)行過，并且用戶對該表有執(zhí)行該語句的權限，就會直接返回之前執(zhí)行的結果。

所以在某些時候，多次執(zhí)行一句 SQL 并不能得到它的平均執(zhí)行時間，因為查詢緩存的關系，后面的執(zhí)行時間往往比第一次執(zhí)行要短。

如果你不想使用緩存，可以在每次查詢后都用 update 語句更新表，當然這是非常麻煩并且憨的方法。MySQL也提供了相應的配置項—— query_cache_type，你可以在 my.cnf 文件中將 query_cache_type 設置為0以關閉查詢緩存。

需要注意的是：

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

2.3 分析器

我使用的 MySQL 版本是5.7.21，所以客戶端提交的查詢語句會走查詢緩存，如果沒有命中，那么將繼續(xù)往下走，來到分析器。

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

需要注意的是：

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

舉個例子：

select * form user_info limit 1;復制代碼

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

第一次的執(zhí)行信息： 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后第二次的執(zhí)行信息：1146 - Table 'windfall.user_info' doesn't exist, Time: 0.000000s  修改為 user 表后第三次的執(zhí)行信息： OK, Time: 0.000000s復制代碼

2.4 優(yōu)化器

在校驗了 SQL 語句的合法性之后，MySQL 已經知道用戶提交的語句是干什么的了，但是在真正執(zhí)行之前，還需要經過非?！靶W”的優(yōu)化器。

優(yōu)化器的作用是為 SQL 語句生成最優(yōu)的執(zhí)行計劃。

之所以說優(yōu)化器很“玄學”，是因為它在優(yōu)化 SQL 語句的過程中可能會生成出乎用戶意料之外的執(zhí)行計劃（索引選擇、多表關聯(lián)連接順序、隱式函數(shù)轉換等）。當然優(yōu)化器有時候也會“選錯”索引，這與數(shù)據(jù)量、索引統(tǒng)計信息等因素有關。

需要注意的是：

• 如果你需要優(yōu)化一條生產環(huán)境的 SQL，請盡量在本地還原與生產環(huán)境數(shù)據(jù)量相同的表，然后根據(jù)執(zhí)行計劃進行優(yōu)化。
• 在寫查詢語句的時候，一定要考慮到索引的最左匹配原則（關于最左匹配原則的整理在索引篇再寫）。

關于 MySQL 優(yōu)化器的工作流程，可以看看這篇博客：MySQL 優(yōu)化器原來是這樣工作的

MySQL 的執(zhí)行計劃也是一項必須要掌握的技能，這篇博客寫得非常詳細，值得一讀：不會看 Explain執(zhí)行計劃，勸你簡歷別寫熟悉 SQL優(yōu)化

2.5 執(zhí)行器

在優(yōu)化器生成了 MySQL 認為最優(yōu)的執(zhí)行計劃之后，最后來到了執(zhí)行器，執(zhí)行器的作用當然就是執(zhí)行SQL語句了。

但是在執(zhí)行之前，先要做權限驗證，驗證用戶對表是否有查詢權限。然后再根據(jù)表定義的引擎類型，去使用相對應引擎提供的接口來對該表進行條件查詢，最后將該表所有滿足條件的數(shù)據(jù)行作為結果集返回客戶端，這樣整個 SQL 的執(zhí)行就結束了。

需要注意的是：

• 在執(zhí)行器執(zhí)行 SQL 語句前會做校驗：判斷用戶對表是否具有操作權限。

2.6 存儲引擎

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

2.6.1 InnoDB

InnoDB 是當下最常用的的 MySQL 存儲引擎，同時也是 MySQL 5.5 之后的默認存儲引擎。

InnoDB 支持事務、MVCC（多版本并發(fā)控制）、外鍵、行級鎖和自增列。但是 InnoDB 不支持全文索引，同時它占用的數(shù)據(jù)空間更大。

2.6.2 MyISAM

MyISAM 是 MySQL 5.1 及之前的默認存儲引擎，支持全文索引、壓縮、空間函數(shù)、表級鎖。

MyISAM 的數(shù)據(jù)以緊密格式存儲所以占用空間更小，它擁有較高的插入和查詢速度，但是 MyISAM 不支持事務，且崩潰后無法安全恢復。

2.6.3 Memory

Memory 的所有數(shù)據(jù)都保存的內存中，由于不需要磁盤 I/O，所以它的速度比 MyISAM 和 InnoDB 快了一個數(shù)量級。但如果數(shù)據(jù)庫關閉或重啟，Memory 引擎的數(shù)據(jù)就會消失。

Memory 支持 Hash 索引，但由于它使用表級鎖，因此并發(fā)寫入的性能比較低。

值得一提的是，MySQL 中的臨時表，一般是用 Memory 表保存的，如果中間表數(shù)據(jù)量過大或含有 BLOB 類型或 TEXT 類型的字段，就會使用 MyISAM 表。

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

3. 日志模塊

前面所說的執(zhí)行流程主要是描述查詢語句，如果是更新語句還涉及到 MySQL 的日志模塊。

從客戶端到執(zhí)行器的之間的邏輯查詢語句和更新語句是相同的，只是在到執(zhí)行器這一層的時候，更新語句會和 MySQL 的日志模塊產生交互，這是查詢語句和更新語句不一樣的地方。

3.1 物理日志 redo log

3.1.1 redo log 中記錄的內容

對于 InnoDB 存儲引擎來說，它有一個特有的日志模塊——物理日志（重做日志）redo log，它是 InnoDB 存儲引擎的日志，它所記錄的是數(shù)據(jù)頁的物理修改。

舉個例子，現(xiàn)在有一張 user 表，有一條主鍵 id=1，age=18 的數(shù)據(jù)，然后用戶提交了下面這條 SQL，執(zhí)行器準備執(zhí)行。

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，直接更新磁盤中的數(shù)據(jù)，那么首先需要找到那條記錄，然后再把新的值更新進入，由于查詢和讀寫I/O，就相對會慢一些。

最后，當 InnoDB 引擎空閑的時候，它會去執(zhí)行 redo log 中的邏輯，將數(shù)據(jù)持久化到磁盤中。

3.1.3 redo log 日志文件

redo log 日志文件大小是固定的，我把它理解為一個循環(huán)鏈表，鏈表的每個節(jié)點都可以存放日志，在這個鏈表中有兩個指針：write（黑） 和 read（白）。

最開始這兩個指針都指向同一個節(jié)點，且節(jié)點日志元素都為空，表示此時 redo log 為空。當用戶開始提交更新語句，write 節(jié)點開始往前移動，假設移動到3的位置。而此時的情況就是 redo log 中有1-3這三個日志元素需要被持久化到磁盤中，當 InnoDB 空閑時，read 指針往前移動，就代表著將 redo log 持久化到磁盤。

但這里有一種特殊情況，就是 InnoDB 一直沒有空閑，write 指針一直在寫入日志，直到它寫到5的位置，再往前寫又回到了最開始1的位置（也就是上圖的位置，但不同的是鏈表節(jié)點中都存在日志數(shù)據(jù)）。

此時發(fā)現(xiàn)1的位置已經有日志數(shù)據(jù)了，同時 read 指針也在。那么這時候 write 指針就會暫停寫入，InnoDB 引擎開始催動 read 指針移動，把 redo log 清空掉一部分之后再讓 write 指針寫入日志文件。

3.1.4 redo log 的作用

我們已經知道，redo log 中記錄的是數(shù)據(jù)頁的物理修改，所以 redo log 能夠保證在數(shù)據(jù)庫發(fā)生異常重啟時，記錄尚未寫入磁盤，但是在重啟后可以通過 redo log 來“redo”，從而不會發(fā)生記錄丟失的情況，保證了事務的持久性。

這一能力也被稱作 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 是在事務執(zhí)行中不斷寫入的。

3.2.1 bin log 的作用

與 redo log 不同的是，bin log 常用于恢復數(shù)據(jù)，比如說主從復制，從節(jié)點根據(jù)父節(jié)點的 bin log 來進行數(shù)據(jù)同步，實現(xiàn)主從同步。

3.3 兩階段提交

為了讓 redo log 和 bin log 的狀態(tài)保持一致，MySQL 使用兩階段提交的方式來寫入 redo log 日志。

在執(zhí)行器調用 InnoDB 引擎的接口將寫入更新數(shù)據(jù)時，InnoDB 引擎會將本次更新記錄到 redo log 中，同時將 redo log 的狀態(tài)標記為 prepare，表示可以提交事務。

隨后執(zhí)行器生成本次操作的 bin log 數(shù)據(jù)，并寫入 bin log 的日志文件中。

最后執(zhí)行器調用 InnoDB 的提交事務接口，存儲引擎把剛寫入的 redo log 記錄狀態(tài)修改為 commit，本次更新結束。

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

1. 寫入 redo log 日志并標記為 prepare
2. 寫入 bin log
3. 提交事務

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

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

4. 小結

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

5. 溫故知新

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

1. 如果查詢語句中字段不存在、字段有歧義、關鍵字拼寫錯誤，是由哪個部分報錯？
2. 如果用戶對表沒有查詢權限，是哪個部分報錯？
3. 為什么 MySQL 的查詢緩存會無效？
4. 一條 select 查詢語句是如何執(zhí)行的？
5. MySQL 常用的存儲引擎有哪些？
6. MySQL 的日志模塊有哪些？分別起到什么作用？
7. redo log 寫滿了怎么辦？
8. 如何理解 redo log 的兩階段提交？

9. redo log 和 bin log 的區(qū)別？