MySQL 在整體架構(gòu)上分為 Server 層和存儲引擎層。其中 Server 層，包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，存儲過程、觸發(fā)器、視圖和內(nèi)置函數(shù)都在這層實(shí)現(xiàn)。數(shù)據(jù)引擎層負(fù)責(zé)數(shù)據(jù)的存儲和提取，如 InnoDB、MyISAM、Memory 等引擎。在客戶端連接到 Server 層后，Server 會調(diào)用數(shù)據(jù)引擎提供的接口，進(jìn)行數(shù)據(jù)的變更。

連接器

負(fù)責(zé)和客戶端建立連接，獲取用戶權(quán)限以及維持和管理連接。

通過 show processlist; 來查詢連接的狀態(tài)。在用戶建立連接后，即使管理員改變連接用戶的權(quán)限，也不會影響到已連接的用戶。默認(rèn)連接時(shí)長為 8 小時(shí)，超過時(shí)間后將會被斷開。

簡單說下長連接：

優(yōu)勢：在連接時(shí)間內(nèi)，客戶端一直使用同一連接，避免多次連接的資源消耗。

劣勢：在 MySQL 執(zhí)行時(shí)，使用的內(nèi)存被連接對象管理，由于長時(shí)間沒有被釋放，會導(dǎo)致系統(tǒng)內(nèi)存溢出，被系統(tǒng)kill. 所以需要定期斷開長連接，或執(zhí)行大查詢后，斷開連接。MySQL 5.7 后，可以通過 mysql_rest_connection 初始化連接資源，不需要重連或者做權(quán)限驗(yàn)證。

查詢緩存

當(dāng)接受到查詢請求時(shí)，會現(xiàn)在查詢緩存中查詢（key/value保存），是否執(zhí)行過。沒有的話，再走正常的執(zhí)行流程。

但在實(shí)際情況下，查詢緩存一般沒有必要設(shè)置。因?yàn)樵诓樵兩婕暗降谋肀桓聲r(shí)，緩存就會被清空。所以適用于靜態(tài)表。在 MySQL8.0 后，查詢緩存被廢除。

分析器

詞法分析：

如識別 select，表名，列名，判斷其是否存在等。

語法分析：

判斷語句是否符合 MySQL 語法。

優(yōu)化器

確定索引的使用，join 表的連接順序等，選擇最優(yōu)化的方案。

執(zhí)行器

在具體執(zhí)行語句前，會先進(jìn)行權(quán)限的檢查，通過后使用數(shù)據(jù)引擎提供的接口，進(jìn)行查詢。如果設(shè)置了慢查詢，會在對應(yīng)日志中看到 rows_examined 來表示掃描的行數(shù)。在一些場景下（索引），執(zhí)行器調(diào)用一次，但在數(shù)據(jù)引擎中掃描了多行，所以引擎掃描的行數(shù)和 rows_examined 并不完全相同。

不預(yù)先檢查權(quán)限的原因：如像觸發(fā)器等情況，需要在執(zhí)行器階段才能確定權(quán)限，在優(yōu)化器階段無法驗(yàn)證。

使用 profiling 查看 SQL 執(zhí)行過程

打開 profiling 分析語句執(zhí)行過程：

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|      0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

執(zhí)行查詢語句：

mysql> SELECT * FROM s limit 10;
+------+--------+-----+-----+
| s_id | s_name | age | sex |
+------+--------+-----+-----+
|  1 | z   | 12 |  1 |
|  2 | s   | 14 |  0 |
|  3 | c   | 14 |  1 |
+------+--------+-----+-----+
3 rows in set (0.00 sec)

獲取 profiles;

mysql> show profiles;
+----------+------------+--------------------------+
| Query_ID | Duration  | Query          |
+----------+------------+--------------------------+
|    1 | 0.00046600 | SELECT * FROM s limit 10 |
+----------+------------+--------------------------+

mysql> show profile;
+----------------------+----------+
| Status        | Duration |
+----------------------+----------+
| starting       | 0.000069 |
| checking permissions | 0.000008 | 權(quán)限檢查
| Opening tables    | 0.000018 | 打開表
| init         | 0.000019 | 初始化
| System lock     | 0.000010 | 鎖系統(tǒng)
| optimizing      | 0.000004 | 優(yōu)化查詢
| statistics      | 0.000013 |
| preparing      | 0.000094 | 準(zhǔn)備
| executing      | 0.000016 | 執(zhí)行
| Sending data     | 0.000120 |
| end         | 0.000010 |
| query end      | 0.000015 |
| closing tables    | 0.000014 |
| freeing items    | 0.000032 |
| cleaning up     | 0.000026 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

查詢具體的語句：

mysql> show profile for query 1;
+----------------------+----------+
| Status        | Duration |
+----------------------+----------+
| starting       | 0.000069 |
| checking permissions | 0.000008 |
| Opening tables    | 0.000018 |
| init         | 0.000019 |
| System lock     | 0.000010 |
| optimizing      | 0.000004 |
| statistics      | 0.000013 |
| preparing      | 0.000094 |
| executing      | 0.000016 |
| Sending data     | 0.000120 |
| end         | 0.000010 |
| query end      | 0.000015 |
| closing tables    | 0.000014 |
| freeing items    | 0.000032 |
| cleaning up     | 0.000026 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

MySQL 日志模塊

如前面所說，MySQL 整體分為 Server 層和數(shù)據(jù)引擎層，而每層也對應(yīng)了自己的日志文件。如果選用的是 InnoDB 引擎，對應(yīng)的是 redo log 文件。Server 層則對應(yīng)了 binlog 文件。至于為什么存在了兩種日志系統(tǒng)，咱們往下看。

redo log

redo log 是 InnoDB 特有日志，為什么要引入 redo log 呢，想象這樣一個(gè)場景，MySQL 為了保證持久性是需要把數(shù)據(jù)寫入磁盤文件的。我們知道，在寫入磁盤時(shí)，會進(jìn)行文件的 IO，查找操作，如果每次更新操作都這樣的話，整體的效率就會特別低，根本沒法使用。

既然直接寫入磁盤不行，解決方法就是先寫進(jìn)內(nèi)存，在系統(tǒng)空閑時(shí)再更新到磁盤就可以了。但光更新內(nèi)存不行，假如系統(tǒng)出現(xiàn)異常宕機(jī)和重啟，內(nèi)存中沒有被寫入磁盤的數(shù)據(jù)就會被丟掉，數(shù)據(jù)的一致性就出現(xiàn)問題了。這時(shí) redo log 就發(fā)揮了作用，在更新操作發(fā)生時(shí)，InnoDb 會先寫入 redo log 日志（記錄了數(shù)據(jù)發(fā)生了怎么樣的改變），然后更新內(nèi)存，最后在適當(dāng)?shù)臅r(shí)間再寫入磁盤，一般是找系統(tǒng)空閑的時(shí)間做。先寫日志，在寫磁盤的操作，就是常說到的 WAL （Write-Ahead- Logging）技術(shù)。

redo log 的出現(xiàn)，除了在效率上有了很大的改善，還保證了 MySQL 具有了 crash-safe 的能力，在發(fā)生異常情況下，不會丟失數(shù)據(jù)。

在具體實(shí)現(xiàn)上 redo log 的大小是固定的，可配置一組為 4 個(gè)文件，每個(gè)文件 1GB，更新時(shí)對四個(gè)文件進(jìn)行循環(huán)寫入。

write pos 記錄當(dāng)前寫入的位置，寫完就后移，當(dāng)?shù)趯懭氲?4 個(gè)文件的末尾時(shí)，從第 0 號位置重新寫入。

check point 表示當(dāng)前可以擦除的位置，當(dāng)數(shù)據(jù)更新到磁盤時(shí)，check point 就向后移動。

write pos 和 check point 之間的位置，就是可以記錄更新操作的空間。當(dāng) write pos 追上 check point ，不在能執(zhí)行新的操作，先讓 check point 去寫入一些數(shù)據(jù)。

可以將 innodb_flush_log_at_trx_commit 設(shè)置成 1，開啟 redo log 持久化的能力。

binlog

binlog 則是 Server 層的日志，主要用于歸檔，在備份，主備同步，恢復(fù)數(shù)據(jù)時(shí)發(fā)揮作用，常見的日志格式有 row, mixed, statement 三種。具體的使用方法可以參見 Binlog 恢復(fù)日志這篇。

可以通過 sync_binlog=1 開啟 binlog 寫入磁盤。

這里對 binlog 和 redo 進(jìn)行下區(qū)分：

1. 所有者不同，binlog 是 Server 層，所有引擎都可使用。redo log 是 InnoDB 特有的。
2. 類型不同，binlog 是邏輯日志，記錄的是語句的原始邏輯（比 statement）。redo log 是物理日志，記錄某個(gè)數(shù)據(jù)頁被做了怎樣的修改。
3. 數(shù)據(jù)寫入的方式不同，binog 日志會一直追加，而 redo log 是循環(huán)寫入。
4. 功能不同，binlog 用于歸檔，而 redo log 用于保證 crash-safe.
   

兩階段提交

下面執(zhí)行器和 InnoDB 執(zhí)行 Update 時(shí)內(nèi)部流程：

以更新 update T set c=c+1 where ID=2; 語句為例：

1. 執(zhí)行器通過 InooDB 引擎去 ID 所在行，ID 為主鍵。引擎通過樹搜索找到該行，如果該行所在數(shù)據(jù)頁在內(nèi)存中，返回給執(zhí)行器。否則先從磁盤讀入內(nèi)存，然后再返回。
2. 執(zhí)行器拿到引擎給的數(shù)據(jù)，將 C 值加 1，等到新的一行，然后通過引擎接口重新寫入新數(shù)據(jù)。
3. 引擎將該行更新到內(nèi)存中，同時(shí)將該更新操作記錄到 redo log 中，并更改 redo log 的狀態(tài)為 prepare 狀態(tài)。然后告知執(zhí)行器，在合適的時(shí)間提交事務(wù)。
4. 執(zhí)行器生成這個(gè)操作的 binlog，并將 binlog 寫入磁盤。
5. 執(zhí)行器調(diào)用引擎到的提交事務(wù)接口，將剛剛寫入的 redo log 改成 commit 狀態(tài)，更新完成。

淺色為執(zhí)行器執(zhí)行，深色為引擎執(zhí)行。

在更新內(nèi)存后，將寫入 redo log 拆分了成兩個(gè)步驟：prepare 和 commit，就是常說的兩階段提交。用于保證當(dāng)有意外情況發(fā)生時(shí)，數(shù)據(jù)的一致性。

這里假設(shè)下，如果不采用兩階段提交會發(fā)生什么？

1. 先寫 redo log 后寫 binlog. 假設(shè)在寫入 redo log 后，MySQL 發(fā)生異常重啟，此時(shí) binlog 沒有寫入。在重啟后，由于 redolog 已經(jīng)寫入，此時(shí)數(shù)據(jù)庫的內(nèi)容是沒有問題的。但此時(shí)，如果想要拿 binlog 進(jìn)行備份或恢復(fù)，發(fā)現(xiàn)會少了最后一條的更新邏輯，導(dǎo)致數(shù)據(jù)不一致。
2. 先寫 binlog 后寫 redo log. binlog 寫入后，MySQL 異常重啟，redo log 沒有寫入。此時(shí)重啟后，發(fā)現(xiàn) redo log 沒有成功寫入，認(rèn)為這個(gè)事務(wù)無效，而此時(shí) binlog 卻多了一條更新語句，拿去恢復(fù)后自然數(shù)據(jù)也是不一致的。

再分析下兩階段提交的過程：

1.在寫 redo log prepare 階段奔潰，時(shí)刻 A 的位置。重啟后，發(fā)現(xiàn) redo log 沒寫入，回滾此次事務(wù)。

2.如果在寫 binlog 時(shí)奔潰，重啟后，發(fā)現(xiàn) binlog 未被寫入，回滾操作。

3.binlog 寫完，但在提交 redo log 的 commit 狀態(tài)時(shí)發(fā)生 crash

• 如果 redo log 中事務(wù)完整，有了 commit 標(biāo)識，直接提交。
• 如果 redo log 中只有完整的 prepare, 判斷對應(yīng) binlog 是否完整。

完整，提交事務(wù)
不完整，回滾事務(wù)。

如何判斷 binlog 是否完整？

• statement 格式 binlog，會有 COMMIT; 標(biāo)識
• row 格式的 binlog，會有 XID event. 標(biāo)識
• 在 5.6 后，還有 binlog-checksum 參數(shù)，驗(yàn)證 binlog 正確性。
  

如何將 redo log 和 binlog 關(guān)聯(lián)表示同一個(gè)操作？

結(jié)構(gòu)中有一個(gè)共同的數(shù)據(jù)字段，XID. 在崩潰恢復(fù)時(shí)，會按順序掃描 redo log:

• 如果有 prepare，又有 commit 的 redo log，直接提交。
• 如果只有 prepare，沒有 commit 的 redo log, 拿 XID 去 binlog 找對應(yīng)的事務(wù)做判斷。
  

數(shù)據(jù)寫入后，最終落盤和 redo log 有無關(guān)系？

• 對于正常運(yùn)行的 instance 來說，內(nèi)存中頁被修改后，和磁盤的數(shù)據(jù)頁不一致，稱為臟頁。而落盤的過程，是把內(nèi)存中的數(shù)據(jù)頁寫入磁盤。
• 對于 crash 場景，InnoDB 判斷一個(gè)數(shù)據(jù)頁是否丟失了更新，會將其讀到內(nèi)存，然后讓 redo log 更新內(nèi)存內(nèi)容。更新完成后，內(nèi)存頁就變成臟頁，然后回到第一種情況的狀態(tài)。

redo log buffer 和 redo log 的關(guān)系？

在一個(gè)事務(wù)的更新過程中，存在多個(gè) SQL 語句，所以是要寫多次日志的。
但在寫的過程中，生產(chǎn)的日志要先保存起來，但在 commit 前，不能直接寫到 redo log 中。
所以通過內(nèi)存中 redo log buffer 先存 redo log 的日志。在 commit 時(shí)，將 buffer 中的內(nèi)容寫入 redo log.

總結(jié)

在文章開始部分，說明了 MySQL 的整體架構(gòu)分為 Server 層和引擎層，并簡要說明了一條語句的執(zhí)行過程。接著 MySQL 在 5.5 后選用 InnoDB 作為默認(rèn)的引擎，就是因?yàn)楸仍?MyISAM 多了事務(wù)以及 crash-safe 的能力。

而 crash-safe 就是由 redo log 實(shí)現(xiàn)的。與 redo log 類似的日志文件還有 binlog，是 Server 引擎的日志，用于歸檔和備份數(shù)據(jù)。

最后提到了，為了保證數(shù)據(jù)的一致性，將 redo log 和 binlog 放入相同的事務(wù)中，也就是常提到的兩階段提交操作。

以上就是MySQL 整體架構(gòu)介紹的詳細(xì)內(nèi)容，更多關(guān)于MySQL 整體架構(gòu)的資料請關(guān)注腳本之家其它相關(guān)文章！