1. MySQL 基本架構(gòu)

圖片

總體來(lái)說(shuō)，MySQL大體分為兩部分，分別是Server 層和存儲(chǔ)引擎層。

Server 層

它包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等。比如存儲(chǔ)過(guò)程，觸發(fā)器，視圖都是在這一層實(shí)現(xiàn)的。

• 連接器Connection Manager）：負(fù)責(zé)處理客戶端與服務(wù)器之間的連接。它接受來(lái)自客戶端的請(qǐng)求，并進(jìn)行身份驗(yàn)證和權(quán)限檢查，建立和管理連接。
• 查詢緩存（Query Cache）：在舊版 MySQL 中有，但在較新的版本中已不推薦使用。它能夠緩存查詢和對(duì)應(yīng)的結(jié)果，以提高查詢性能。然而，在高并發(fā)和大型數(shù)據(jù)庫(kù)中，它反而可能成為性能瓶頸，因?yàn)樗谀承┣闆r下會(huì)引起鎖和不必要的開銷。
• 分析器（Parser）：負(fù)責(zé)分析 SQL 查詢語(yǔ)句，驗(yàn)證其語(yǔ)法和語(yǔ)義，確保查詢的正確性。它將 SQL 語(yǔ)句轉(zhuǎn)換成內(nèi)部數(shù)據(jù)結(jié)構(gòu)供優(yōu)化器和執(zhí)行器使用。
• 優(yōu)化器（Optimizer）：接收來(lái)自分析器的查詢請(qǐng)求，并決定如何最有效地執(zhí)行查詢。優(yōu)化器的目標(biāo)是找到最佳的執(zhí)行路徑，選擇合適的索引、連接順序和訪問(wèn)方法，以提高查詢性能。
• 執(zhí)行器（Executor）：負(fù)責(zé)執(zhí)行優(yōu)化器生成的執(zhí)行計(jì)劃，獲取存儲(chǔ)引擎返回的數(shù)據(jù)，并處理客戶端請(qǐng)求。它與存儲(chǔ)引擎交互，執(zhí)行查詢并返回結(jié)果給用戶。
• 存儲(chǔ)引擎層: 它負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。Mysql支持InnoDB、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎。我們?nèi)粘ｉ_發(fā)中，一般用的存儲(chǔ)引擎就是InnoDB。從 MySQL 5.5 版本開始，InnoDB 就成為了默認(rèn)的存儲(chǔ)引擎。

介紹完MySQL基本架構(gòu)，帶大家看一下，每個(gè)組件，一條寫入SQL,它主要做什么事~~

2.連接器

我們要執(zhí)行寫入SQL，一般在MySQL客戶端, 需要輸入連接命令，連接到MySQL服務(wù)端。在MySQL服務(wù)端，就是連接器負(fù)責(zé)跟你的客戶端建立連接、獲取權(quán)限、維持和管理連接。

連接命令如下：

mysql -h(ip地址) -P(端口) -u(用戶名) -p

輸入完連接命令之后，我們接著輸入正確的密碼，經(jīng)過(guò)經(jīng)典的TCP握手之后，就可以成功連接到MySQL服務(wù)器啦，如下：

C:\MySQL\MySQL Server 8.0\bin>mysql -h 127.0.0.1 -P 3306 -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

如果輸入密碼錯(cuò)誤，則會(huì)收到一個(gè) Access denied的錯(cuò)誤信息，如下：

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -h 127.0.0.1 -P 3306 -u root -p  
Enter password: *****  
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

連接成功之后，大家就可以直接輸入寫入SQL，就可以看到結(jié)果啦。

mysql> insert into user_score_tab(user_id,score) values(888,10);
Query OK, 1 row affected (0.02 sec)

3. 查詢緩存（Query Cache）

在5.6 及更早的MySQL版本中，連接成功后，會(huì)提供查詢緩存，來(lái)優(yōu)化查詢SQL。如果你查詢的表進(jìn)行更新、插入的時(shí)候，會(huì)清空緩存的。因此，當(dāng)你執(zhí)行寫入SQL，會(huì)清空緩存。

其實(shí)，MySQL比較新的版本，如8.0 已經(jīng)徹底廢棄了查詢緩存。因?yàn)樵诟卟l(fā)和大型數(shù)據(jù)庫(kù)環(huán)境下，查詢緩存可能導(dǎo)致性能問(wèn)題，并且在實(shí)際測(cè)試中發(fā)現(xiàn)，禁用查詢緩存可能會(huì)提高整體性能和可伸縮性。

4. 分析器

你扔個(gè)寫入SQL給MySQL服務(wù)器，它肯定需要先解析，才知道這個(gè)SQL是做什么的，對(duì)吧。

它會(huì)派出分析器，先做詞法分析。你提交過(guò)來(lái)的寫入SQL是由很多個(gè)字符串和空格組成的，MySQL會(huì)先解析出這些字符串表示什么意思。比如這個(gè)插入SQL:

insert into user_score_tab(user_id,score) values(888,10);

它會(huì)把關(guān)鍵字insert into解析出來(lái)，然后把user_score_tab解析成表，user_id、score解析成列名。做完詞法分析之后，開始做語(yǔ)法分析。語(yǔ)法分析主要就是判斷，你的SQL是否滿足MYSQL的語(yǔ)法。

如果你的SQL寫錯(cuò)了，語(yǔ)法分析就會(huì)報(bào)錯(cuò)誤提示：ERROR 1064 (42000): You have an error in your SQL syntax;

mysql> inser into user_score_tab(user_id,score) value(888,10);
ERROR 1064 (42000): 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 'inser into user_score_tab(user_id,score) value(888,10)' at line 1

平時(shí)大家看到這個(gè)錯(cuò)誤的時(shí)候，只需要，關(guān)注關(guān)鍵詞 syntax to use near 就可以快速知道哪里寫錯(cuò)啦。比如這個(gè)例子，就是我的insert寫錯(cuò)了，少了t。

做完詞法分析和語(yǔ)法分析解析，就知道這是一條插入SQL。

5. 優(yōu)化器

對(duì)于簡(jiǎn)單的插入SQL語(yǔ)句，優(yōu)化器并不會(huì)執(zhí)行復(fù)雜的查詢計(jì)劃生成工作。

優(yōu)化器會(huì)處理索引的選擇與維護(hù)，但并不涉及復(fù)雜的查詢優(yōu)化。如果表中存在主鍵或索引，優(yōu)化器會(huì)確保索引的更新以保證數(shù)據(jù)一致性，并在插入數(shù)據(jù)時(shí)檢查約束條件。

INSERT語(yǔ)句也會(huì)生成執(zhí)行計(jì)劃，它詳細(xì)描述了數(shù)據(jù)庫(kù)如何訪問(wèn)數(shù)據(jù)、使用哪些索引、以及數(shù)據(jù)的處理順序等

6. 執(zhí)行器

執(zhí)行器負(fù)責(zé)執(zhí)行具體的 SQL 操作，是數(shù)據(jù)庫(kù)系統(tǒng)的核心執(zhí)行模塊。對(duì)于INSERT語(yǔ)句，執(zhí)行器會(huì)負(fù)責(zé)實(shí)際的數(shù)據(jù)寫入過(guò)程。

• 確定插入位置：根據(jù)優(yōu)化器的執(zhí)行計(jì)劃，執(zhí)行器會(huì)決定將數(shù)據(jù)插入表的具體位置，比如根據(jù)主鍵或唯一索引找到插入點(diǎn)。
• 加載數(shù)據(jù)頁(yè)：如果要插入的數(shù)據(jù)頁(yè)在內(nèi)存（Buffer Pool）中，則直接使用；如果不在內(nèi)存中，則需要從磁盤加載對(duì)應(yīng)的數(shù)據(jù)頁(yè)到內(nèi)存。
• 更新索引：如果表中有索引（如主鍵、唯一索引或其他索引），執(zhí)行器也會(huì)相應(yīng)更新這些索引。

7. Buffer Pool

Buffer Pool 是 MySQL InnoDB 存儲(chǔ)引擎中的一塊內(nèi)存區(qū)域，專門用來(lái)緩存數(shù)據(jù)庫(kù)表的數(shù)據(jù)頁(yè)、索引頁(yè)等內(nèi)容。它的主要目的是提高數(shù)據(jù)讀寫性能，減少磁盤 I/O 操作.

• 數(shù)據(jù)寫入內(nèi)存中的數(shù)據(jù)頁(yè)：執(zhí)行器將新數(shù)據(jù)插入到Buffer Pool中的相應(yīng)數(shù)據(jù)頁(yè)。這是一個(gè)內(nèi)存操作，而不是直接修改磁盤上的文件。

8. undo log

生成 Undo Log 在真正插入數(shù)據(jù)之前，InnoDB 會(huì)生成 undo log。對(duì)于插入操作，undo log 記錄的是如何刪除當(dāng)前插入的記錄（這用于事務(wù)回滾時(shí)撤銷插入操作）。為什么生成 undo log？

在事務(wù)回滾時(shí)，MySQL 需要撤銷未提交的操作。通過(guò) undo log，MySQL 能夠刪除已經(jīng)插入但未提交的記錄，確保事務(wù)的原子性。

9. Redo Log

執(zhí)行器在插入數(shù)據(jù)后，立即將這個(gè)操作記錄在 redo log 中。

• 寫入 redo log：為了確保數(shù)據(jù)的可靠性，MySQL采用了預(yù)寫日志（Write-Ahead Logging, WAL）機(jī)制。在數(shù)據(jù)真正寫入磁盤前，首先會(huì)將這個(gè)操作記錄在redo log中。

過(guò)程是怎樣的呢？

MySQL首先將操作寫入redo log，并標(biāo)記為預(yù)提交（prepare）狀態(tài)。這意味著如果崩潰，MySQL可以通過(guò)redo log將操作重做，從而恢復(fù)數(shù)據(jù)。

10. 寫入 Binlog

• 寫入 binlog：在寫入 redo log 的同時(shí)，MySQL還會(huì)將這次操作寫入binlog，用于數(shù)據(jù)庫(kù)復(fù)制和災(zāi)難恢復(fù)。

binlog是 MySQL 的邏輯日志，記錄了SQL操作細(xì)節(jié)，比如（INSERT INTO）。不同于redo log的物理日志。

11. 事務(wù)提交（兩階段提交）

在兩階段提交機(jī)制下，MySQL會(huì)在事務(wù)提交時(shí)更新 redo log 為commit狀態(tài)。

為什么需要兩階段提交？

確保 binlog 和 redo log 的一致性。如果系統(tǒng)崩潰，MySQL可以通過(guò) redo log 重做操作，并通過(guò) binlog 進(jìn)行恢復(fù)。

12. 數(shù)據(jù)刷入磁盤

執(zhí)行器不會(huì)立即將內(nèi)存中的臟頁(yè)同步到磁盤。后臺(tái)線程會(huì)根據(jù)一定的策略（如定時(shí)刷新等），異步地將 Buffer Pool 中的臟頁(yè)刷到磁盤上的表空間文件中。- 這樣可以避免頻繁的磁盤 I/O 提高性能。