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

連接器

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

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

簡(jiǎn)單說(shuō)下長(zhǎng)連接：

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

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

查詢(xún)緩存

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

但在實(shí)際情況下，查詢(xún)緩存一般沒(méi)有必要設(shè)置。因?yàn)樵诓樵?xún)涉及到的表被更新時(shí)，緩存就會(huì)被清空。所以適用于靜態(tài)表。在 MySQL8.0 后，查詢(xún)緩存被廢除。

分析器

詞法分析：

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

語(yǔ)法分析：

判斷語(yǔ)句是否符合 MySQL 語(yǔ)法。

優(yōu)化器

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

執(zhí)行器

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

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

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

打開(kāi) profiling 分析語(yǔ)句執(zhí)行過(guò)程：

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í)行查詢(xún)語(yǔ)句：

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 | 打開(kāi)表| init | 0.000019 | 初始化| System lock | 0.000010 | 鎖系統(tǒng)| optimizing | 0.000004 | 優(yōu)化查詢(xún)| 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)

查詢(xún)具體的語(yǔ)句：

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 日志模塊

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

redo log

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

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

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

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

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

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

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

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

binlog

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

可以通過(guò) sync_binlog=1 開(kāi)啟 binlog 寫(xiě)入磁盤(pán)。

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

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

兩階段提交

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

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

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

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

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

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

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

再分析下兩階段提交的過(guò)程：

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

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

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

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

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

如何判斷 binlog 是否完整？

statement 格式 binlog，會(huì)有 COMMIT; 標(biāo)識(shí) row 格式的 binlog，會(huì)有 XID event. 標(biāo)識(shí) 在 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í)，會(huì)按順序掃描 redo log:

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

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

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

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

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

總結(jié)

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

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

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

以上就是MySQL 整體架構(gòu)介紹的詳細(xì)內(nèi)容，更多關(guān)于MySQL 整體架構(gòu)的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！