MySQL 基礎知識（十）之 MySQL 架構(gòu)

這篇具有很好參考價值的文章主要介紹了MySQL 基礎知識（十）之 MySQL 架構(gòu)。希望對大家有所幫助。如果存在錯誤或未考慮完全的地方，請大家不吝賜教，您也可以點擊"舉報違法"按鈕提交疑問。

1 MySQL 架構(gòu)說明

2 連接層

3 核心業(yè)務層

3.1 查詢緩存

3.2 解析器

3.3 優(yōu)化器

3.4 執(zhí)行器

4 存儲引擎層

5 參考文檔

1 MySQL 架構(gòu)說明

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

MySQL 基礎知識（十）之 MySQL 架構(gòu),MySQL,mysql,數(shù)據(jù)庫

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

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

2 連接層

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

主要作用為：

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

查看連接狀態(tài)

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

Id：線程 id

User：啟動這個線程的用戶

Host：發(fā)送請求的客戶端的 IP 和端口號

db：當前命令在哪個庫執(zhí)行

Command：該線程正在執(zhí)行的操作命令

Create DB：正在創(chuàng)建庫操作

Drop DB：正在刪除庫操作

Execute：正在執(zhí)行一個 Prepared Statement

Close Stmt：正在關(guān)閉一個 Prepared Statement

Query：正在執(zhí)行一個語句

Sleep：正在等待客戶端發(fā)送語句

Quit：正在退出

Shutdown：正在關(guān)閉服務器

Time：表示該線程處于當前狀態(tài)的時間，單位是秒

State：線程狀態(tài)

Updating：正在搜索匹配記錄，進行修改

Sleeping：正在等待客戶端發(fā)送新請求

Starting：正在執(zhí)行請求處理

Checking table：正在檢查數(shù)據(jù)表

Closing table : 正在將表中數(shù)據(jù)刷新到磁盤中

Locked：被其他查詢鎖住了記錄

Sending Data：正在處理 Select 查詢，同時將結(jié)果發(fā)送給客戶端

Info：一般記錄線程執(zhí)行的語句，默認顯示前100個字符。想查看完整的使用 show full?processlist;

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

MySQL 基礎知識（十）之 MySQL 架構(gòu),MySQL,mysql,數(shù)據(jù)庫

3 核心業(yè)務層

3.1 查詢緩存

? ? ? ? 當 MySQL 接收到一個查詢請求時，它首先會檢查查詢緩存中是否已經(jīng)存在與該查詢請求相同的查詢結(jié)果。之前執(zhí)行過的語句及其結(jié)果可能會以 key-value 對的形式，被直接緩存在內(nèi)存中。其中 key 是查詢的語句，value 是查詢的結(jié)果。如果存在，MySQL 會直接返回緩存中的結(jié)果，而不需要再執(zhí)行查詢語句。如果查詢緩存中沒有該查詢結(jié)果，MySQL 會執(zhí)行查詢語句并將結(jié)果存儲在查詢緩存中，以便下次相同的查詢可以直接從緩存中獲取結(jié)果。我們一般不使用查詢緩存，因為一旦查詢涉及的表發(fā)生改變，該緩存會失效，會被查詢緩存清除。從 MySQL 8.0 版本開始，MySQL 不再支持查詢緩存。

3.2 解析器

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

3.3 優(yōu)化器

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

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

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

select g1.id,g1.name
from goods g1
inner join goods g2 using(id)
where g1.id > 5;

# where g1.id > 5 等價于 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;

MySQL 基礎知識（十）之 MySQL 架構(gòu),MySQL,mysql,數(shù)據(jù)庫 ? ?

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

3.4 執(zhí)行器

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

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

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

執(zhí)行一條 SQL 語句的過程

MySQL 基礎知識（十）之 MySQL 架構(gòu),MySQL,mysql,數(shù)據(jù)庫

4 存儲引擎層

????????MySQL 存儲引擎是 MySQL 數(shù)據(jù)庫的核心組件之一，它負責管理數(shù)據(jù)的存儲和訪問。MySQL 支持多種存儲引擎，每個存儲引擎都有自己的特點和適用場景。常見的存儲引擎包括：

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

存儲引擎層的主要職責包括：