目錄

1 MySQL 架構(gòu)說(shuō)明

2 連接層

3 核心業(yè)務(wù)層

3.1 查詢緩存

3.2 解析器

3.3 優(yōu)化器

3.4 執(zhí)行器

4 存儲(chǔ)引擎層

5 參考文檔

---

1 MySQL 架構(gòu)說(shuō)明

下圖是 MySQL 5.7?及其之前版本的邏輯架構(gòu)示意圖

MySQL 架構(gòu)大致可分為以下三層：

• 連接層：負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接
• 核心業(yè)務(wù)層：包括查詢緩存、解析器、優(yōu)化器、執(zhí)行器等，涵蓋 MySQL 的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)（如日期、時(shí)間、數(shù)學(xué)和加密函數(shù)等），所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)，比如存儲(chǔ)過(guò)程、觸發(fā)器、視圖等
• 存儲(chǔ)引擎層：負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。其架構(gòu)模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎。現(xiàn)在最常用的存儲(chǔ)引擎是 InnoDB，它從 MySQL 5.5.5 版本開(kāi)始成為了默認(rèn)存儲(chǔ)引擎

2 連接層

MySQL 連接層是 MySQL 數(shù)據(jù)庫(kù)架構(gòu)中的一個(gè)重要組成部分，它主要負(fù)責(zé)處理客戶端與數(shù)據(jù)庫(kù)服務(wù)器之間的連接和通信。

主要作用為：

• 連接管理：連接層負(fù)責(zé)監(jiān)聽(tīng)客戶端的連接請(qǐng)求，當(dāng)客戶端請(qǐng)求連接時(shí)，連接層會(huì)創(chuàng)建一個(gè)新的連接或者復(fù)用已有的空閑連接，以供客戶端使用。在客戶端使用完連接后，連接層會(huì)關(guān)閉連接以釋放資源。為了提高性能和響應(yīng)速度，連接層通常會(huì)實(shí)現(xiàn)一個(gè)線程池，線程池可以減少創(chuàng)建和銷毀線程的開(kāi)銷，因?yàn)榫€程可以被重復(fù)使用
• 通信協(xié)議處理：連接層處理客戶端與數(shù)據(jù)庫(kù)服務(wù)器之間的通信協(xié)議。它負(fù)責(zé)解析和處理客戶端發(fā)送的請(qǐng)求，并將請(qǐng)求轉(zhuǎn)換為數(shù)據(jù)庫(kù)服務(wù)器能夠理解的格式。同時(shí)，它也將數(shù)據(jù)庫(kù)服務(wù)器的響應(yīng)轉(zhuǎn)換為客戶端能夠理解的格式，并返回給客戶端
• 安全性管理：連接層負(fù)責(zé)處理數(shù)據(jù)庫(kù)的安全性。它驗(yàn)證客戶端的身份和權(quán)限，確保只有經(jīng)過(guò)授權(quán)的客戶端能夠訪問(wèn)數(shù)據(jù)庫(kù)。連接層還可以對(duì)客戶端發(fā)送的數(shù)據(jù)進(jìn)行加密和解密，以保護(hù)數(shù)據(jù)庫(kù)中的敏感信息
• 異常處理：連接層還負(fù)責(zé)處理數(shù)據(jù)庫(kù)連接過(guò)程中的異常情況。它可以檢測(cè)到連接超時(shí)、網(wǎng)絡(luò)中斷等異常情況，并采取相應(yīng)的措施，如重新連接或返回錯(cuò)誤信息給客戶端

查看連接狀態(tài)

通過(guò) show processlist 或 show full processlist?命令可以查看連接狀態(tài)

• Id：線程 id
• User：啟動(dòng)這個(gè)線程的用戶
• Host：發(fā)送請(qǐng)求的客戶端的 IP 和端口號(hào)
• db：當(dāng)前命令在哪個(gè)庫(kù)執(zhí)行
• Command：該線程正在執(zhí)行的操作命令
  • Create DB：正在創(chuàng)建庫(kù)操作
  • Drop DB：正在刪除庫(kù)操作
  • Execute：正在執(zhí)行一個(gè) Prepared Statement
  • Close Stmt：正在關(guān)閉一個(gè) Prepared Statement
  • Query：正在執(zhí)行一個(gè)語(yǔ)句
  • Sleep：正在等待客戶端發(fā)送語(yǔ)句
  • Quit：正在退出
  • Shutdown：正在關(guān)閉服務(wù)器
• Time：表示該線程處于當(dāng)前狀態(tài)的時(shí)間，單位是秒
• State：線程狀態(tài)
  • Updating：正在搜索匹配記錄，進(jìn)行修改
  • Sleeping：正在等待客戶端發(fā)送新請(qǐng)求
  • Starting：正在執(zhí)行請(qǐng)求處理
  • Checking table：正在檢查數(shù)據(jù)表
  • Closing table : 正在將表中數(shù)據(jù)刷新到磁盤中
  • Locked：被其他查詢鎖住了記錄
  • Sending Data：正在處理 Select 查詢，同時(shí)將結(jié)果發(fā)送給客戶端
• Info：一般記錄線程執(zhí)行的語(yǔ)句，默認(rèn)顯示前100個(gè)字符。想查看完整的使用 show full?processlist;

# 查看連接狀態(tài)
show processlist;

3 核心業(yè)務(wù)層

3.1 查詢緩存

? ? ? ? 當(dāng) MySQL 接收到一個(gè)查詢請(qǐng)求時(shí)，它首先會(huì)檢查查詢緩存中是否已經(jīng)存在與該查詢請(qǐng)求相同的查詢結(jié)果。之前執(zhí)行過(guò)的語(yǔ)句及其結(jié)果可能會(huì)以 key-value 對(duì)的形式，被直接緩存在內(nèi)存中。其中 key 是查詢的語(yǔ)句，value 是查詢的結(jié)果。如果存在，MySQL 會(huì)直接返回緩存中的結(jié)果，而不需要再執(zhí)行查詢語(yǔ)句。如果查詢緩存中沒(méi)有該查詢結(jié)果，MySQL 會(huì)執(zhí)行查詢語(yǔ)句并將結(jié)果存儲(chǔ)在查詢緩存中，以便下次相同的查詢可以直接從緩存中獲取結(jié)果。我們一般不使用查詢緩存，因?yàn)橐坏┎樵兩婕暗谋戆l(fā)生改變，該緩存會(huì)失效，會(huì)被查詢緩存清除。從 MySQL 8.0 版本開(kāi)始，MySQL 不再支持查詢緩存。

3.2 解析器

? ? ? ? MySQL 首先會(huì)進(jìn)行詞法分析 (Lexical scanner)，將 SQL 語(yǔ)句分成一個(gè)個(gè)的詞法單元 (Token) 。其中 Token 中包含 Keyword（也稱 symbol）和非 Keyword。之后根據(jù)語(yǔ)法分析?(Grammar rule module) 生成一棵對(duì)應(yīng)的“解析樹(shù)”。最后進(jìn)行語(yǔ)義分析，檢查“解析樹(shù)”中的元素是否在數(shù)據(jù)庫(kù)中有對(duì)應(yīng)的實(shí)體，并驗(yàn)證操作的合法性。例如會(huì)檢查數(shù)據(jù)表和數(shù)據(jù)列是否存在，還會(huì)解析名字和別名，看看它們是否有歧義，最后生成新的“解析樹(shù)”。

3.3 優(yōu)化器

? ? ? ? 優(yōu)化器會(huì)根據(jù)“解析樹(shù)”生成最優(yōu)的執(zhí)行計(jì)劃。這些執(zhí)行計(jì)劃可以分為兩類：靜態(tài)優(yōu)化（編譯時(shí)優(yōu)化）、動(dòng)態(tài)優(yōu)化（運(yùn)行時(shí)優(yōu)化）。

????????MySQL 能夠處理的優(yōu)化類型：

• 重新定義關(guān)聯(lián)表的順序
• 將外連接轉(zhuǎn)化為內(nèi)連接
• 使用等價(jià)變換規(guī)則簡(jiǎn)化并規(guī)范 SQL 語(yǔ)句，如（5 = 5 AND? a > 5) 會(huì)被改寫(xiě)成 a > 5
• 預(yù)估并轉(zhuǎn)化為常數(shù)表達(dá)式，當(dāng) MySQL 檢測(cè)到一個(gè)表達(dá)式可以轉(zhuǎn)化為常數(shù)時(shí)，會(huì)一直把該表達(dá)式作為常數(shù)進(jìn)行優(yōu)化處理，如在索引列執(zhí)行?COUNT()、MIN() 和 MAX()
• 子查詢優(yōu)化，減少數(shù)據(jù)訪問(wèn)次數(shù)
• 提前終止查詢，當(dāng)已經(jīng)滿足查詢需求時(shí)，MySQL 能夠立刻終止查詢，如使用 LIMIT 子句或條件不成立時(shí)，立刻終止后續(xù)查詢
• 等值傳播，如果兩個(gè)列的值通過(guò)等式關(guān)聯(lián)，MySQL 能夠把其中一個(gè)列的 WHERE 條件傳遞到另一個(gè)列上

select g1.id,g1.name
from goods g1
inner join goods g2 using(id)
where g1.id > 5;# where g1.id > 5 等價(jià)于 where g1.id > 5 and g2.id > 5
select g1.id,g1.name
from goods g1
inner join goods g2 using(id)
where g1.id > 5 and g2.id > 5;

? ?

• 列表 IN() 的比較，如 IN(2,1,3)，MySQL 會(huì) IN() 列表中的數(shù)據(jù)進(jìn)行排序，排序后為 IN(1,2,3)，然后可以通過(guò)二分查找的方式確定列表中的值是否滿足條件

3.4 執(zhí)行器

????????MySQL 執(zhí)行器是 MySQL 服務(wù)器中的一個(gè)組件，它負(fù)責(zé)執(zhí)行優(yōu)化器生成的執(zhí)行計(jì)劃，并返回查詢結(jié)果。

????????MySQL 執(zhí)行器的主要職責(zé)包括：

• 執(zhí)行查詢計(jì)劃：根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃，執(zhí)行器會(huì)執(zhí)行相應(yīng)的操作，如讀取數(shù)據(jù)、連接表、執(zhí)行排序、聚合等
• 處理數(shù)據(jù)：執(zhí)行器會(huì)從存儲(chǔ)引擎中讀取數(shù)據(jù)，并對(duì)數(shù)據(jù)進(jìn)行處理，如過(guò)濾、排序、分組等
• 返回結(jié)果：執(zhí)行器會(huì)將處理后的數(shù)據(jù)返回給客戶端，通常以行的形式返回
• 管理事務(wù)：執(zhí)行器會(huì)負(fù)責(zé)管理事務(wù)的提交和回滾，確保數(shù)據(jù)的一致性和完整性
• 處理錯(cuò)誤：如果在執(zhí)行過(guò)程中發(fā)生錯(cuò)誤，執(zhí)行器會(huì)捕獲錯(cuò)誤并將其返回給客戶端

執(zhí)行一條 SQL 語(yǔ)句的過(guò)程

4 存儲(chǔ)引擎層

????????MySQL 存儲(chǔ)引擎是 MySQL 數(shù)據(jù)庫(kù)的核心組件之一，它負(fù)責(zé)管理數(shù)據(jù)的存儲(chǔ)和訪問(wèn)。MySQL 支持多種存儲(chǔ)引擎，每個(gè)存儲(chǔ)引擎都有自己的特點(diǎn)和適用場(chǎng)景。常見(jiàn)的存儲(chǔ)引擎包括：

• InnoDB：InnoDB 是 MySQL 的默認(rèn)存儲(chǔ)引擎，它支持事務(wù)、熱備份（可以在不停機(jī)的情況下進(jìn)行數(shù)據(jù)備份）、行級(jí)鎖、外鍵約束等特性，適用于大多數(shù)應(yīng)用場(chǎng)景
• MyISAM：MyISAM 是 MySQL 的早期存儲(chǔ)引擎，它支持快速的讀取和插入操作，但不支持事務(wù)和外鍵約束
• Memory：Memory 存儲(chǔ)引擎將數(shù)據(jù)存儲(chǔ)在內(nèi)存中，適用于需要快速訪問(wèn)數(shù)據(jù)的場(chǎng)景，但數(shù)據(jù)在服務(wù)器重啟后會(huì)丟失
• CSV：CSV 存儲(chǔ)引擎將數(shù)據(jù)存儲(chǔ)為 CSV 文件，適用于需要導(dǎo)入和導(dǎo)出數(shù)據(jù)的場(chǎng)景
• Archive：Archive 存儲(chǔ)引擎用于存儲(chǔ)歸檔數(shù)據(jù)，支持快速的插入和壓縮操作，但不支持索引和查詢

存儲(chǔ)引擎層的主要職責(zé)包括：

• 管理數(shù)據(jù)存儲(chǔ)：存儲(chǔ)引擎負(fù)責(zé)將數(shù)據(jù)存儲(chǔ)在磁盤上，并提供高效的數(shù)據(jù)訪問(wèn)方式
• 支持索引：存儲(chǔ)引擎負(fù)責(zé)管理索引的創(chuàng)建、維護(hù)和查詢
• 處理事務(wù)：存儲(chǔ)引擎支持事務(wù)的原子性、一致性、隔離性和持久性
• 提供數(shù)據(jù)恢復(fù)：存儲(chǔ)引擎負(fù)責(zé)在服務(wù)器崩潰或意外關(guān)閉后，恢復(fù)數(shù)據(jù)的完整性
• 優(yōu)化查詢性能：存儲(chǔ)引擎會(huì)根據(jù)查詢條件和索引，選擇最優(yōu)的查詢計(jì)劃來(lái)提高查詢性能

5 參考文檔

《高性能MySQL》

深入淺出MySQL - 架構(gòu)與執(zhí)行 (https://www.cnblogs.com/novwind/p/17489997.html)

MySQL架構(gòu)原理(詳解) (https://cloud.tencent.com/developer/article/1981543)

MySQL架構(gòu)詳解 (https://mdnice.com/writing/43f52aeaee9644b1bc1373ff85f89972)