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

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

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

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

1. MySQL 架構簡介

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

作為最受歡迎的關聯式資料庫管理系統之一，MySQL 採用的是C/S架構，也就是 Client & Server 架構。例如開發者使用 Navicat 連線到 MySQL，那麼前者就是客戶端，後者就是服務端。

同時，MySQL 也是單一行程多執行緒的資料庫。這很好理解，正在運行的MySQL 實例就是那個“單一進程”，而這個進程中會有很多個線程，例如主線程Master Thread，IO Thread 等，這些線程被用來處理不同的任務。

2. MySQL 組成部分

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

在這一小節的內容中，我們主要關注 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 日志文件大小是固定的，我把它理解为一个我所理解的MySQL之一：基礎架構，链表的每个节点都可以存放日志，在这个链表中有两个指针：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. 為什麼 MySQL 的查詢快取會無效？
3. 一條 select 查詢語句是如何執行的？
4. MySQL 常用的儲存引擎有哪些？
5. MySQL 的日誌模組有哪些？分別起到什麼作用？
6. redo log 寫滿了怎麼辦？
7. 如何理解 redo log 的兩階段提交？
9. redo log 和 bin log 的差別？

更多相關免費學習推薦：mysql教學(影片)

以上是我所理解的MySQL之一：基礎架構的詳細內容。更多資訊請關注PHP中文網其他相關文章！