MySQL 架構與執行原理#

理解 MySQL 的關鍵在於理解「一條 SQL 是如何執行的」。本章將 MySQL 拆解開來，看看裡面的各個「零件」如何協同工作。

MySQL 整體架構#

MySQL 可分為 Server 層和存儲引擎層兩部分：

flowchart TB
    subgraph Server[Server 層]
        A[連接器] --> B[查詢緩存]
        B --> C[分析器]
        C --> D[優化器]
        D --> E[執行器]
    end

    subgraph Engine[存儲引擎層]
        F[InnoDB]
        G[MyISAM]
        H[Memory]
        I[...]
    end

    E --> Engine

Server 層：連接器、查詢緩存、分析器、最佳化器、執行器，涵蓋大多數核心功能 存儲引擎層：負責資料存儲和提取，插件式架構，InnoDB 是預設引擎（MySQL 5.5.5 起）

不同的存儲引擎共用一個 Server 層。引擎的選擇影響事務支援、鎖粒度、崩潰恢復等特性。

查詢語句執行流程#

以 SELECT * FROM T WHERE ID=10 為例：

1. 連接器#

負責建立連接、驗證身份、獲取權限。

mysql -h$ip -P$port -u$user -p

關鍵點：

  • 權限在連接建立時讀取，之後修改權限不影響已有連接
  • 空閒連接超時由 wait_timeout 控制（默認 8 小時）
  • 建議使用長連接，但要注意內存洩漏問題
長連接內存問題解決方案

長連接會導致 MySQL 內存持續增長，因為執行過程中的臨時內存綁定在連接對象上。

解決方案：

  1. 定期斷開長連接，執行大查詢後主動重連
  2. MySQL 5.7+ 可用 mysql_reset_connection 重置連接資源（無需重連）

2. 查詢緩存#

不建議使用查詢緩存。任何對表的更新都會清空該表所有緩存，命中率極低。MySQL 8.0 已徹底移除此功能。

3. 分析器#

詞法分析：識別關鍵字、表名、列名 語法分析：檢查 SQL 語法是否正確

-- 語法錯誤示例
mysql> elect * from t where ID=1;
ERROR 1064: ... near 'elect * from t where ID=1'

語法錯誤提示會指向第一個出錯的位置，關注 use near 後面的內容。

4. 最佳化器#

決定：

  • 使用哪個索引
  • 多表 JOIN 的連接順序
SELECT * FROM t1 JOIN t2 USING(ID) WHERE t1.c=10 AND t2.d=20;
-- 最佳化器決定：先查 t1.c=10 還是先查 t2.d=20

5. 執行器#

  1. 檢查權限
  2. 調用存儲引擎接口執行查詢
  3. 返回結果集

無索引執行流程：全表掃描，逐行判斷 有索引執行流程：調用「取滿足條件的第一行/下一行」接口

慢查詢日誌中的 rows_examined 字段記錄執行器呼叫引擎的次數，但不一定等於引擎實際掃描的行數。

更新語句執行流程#

更新語句除了經歷查詢的流程外，還涉及兩個重要的日誌模塊。

兩大日誌系統#

特性redo logbinlog
所屬層InnoDB 引擎特有Server 層，所有引擎可用
日誌類型物理日誌（資料頁修改）邏輯日誌（SQL 邏輯）
寫入方式循環寫，固定大小追加寫，文件切換
作用crash-safe歸檔、主從複製、恢復

redo log（重做日誌）#

採用 WAL（Write-Ahead Logging） 技術：先寫日誌，再寫磁盤。

flowchart LR
    subgraph RedoLog["redo log（循環寫）"]
        direction LR
        F0["file0"] --> F1["file1"] --> F2["file2"] --> F3["file3"]
        F3 -.->|循環| F0
    end

    CP["checkpoint"] -.-> F0
    WP["write pos"] -.-> F2

    style CP fill:#e8f5e9
    style WP fill:#fff3e0
位置關係說明
checkpoint → write pos 之間可寫空間
write pos 追上 checkpoint 時需要刷盤，釋放日誌空間

有了 redo log，即使資料庫異常重啟，之前提交的記錄也不會丟失（crash-safe）。

binlog（歸檔日誌）#

用於：

  • 資料恢復（恢復到任意時間點）
  • 主從複製
  • 審計

兩階段提交#

更新語句的完整流程：

1. 執行器呼叫引擎取 ID=2 這一行
2. 執行器將值 +1，呼叫引擎寫入新資料
3. 引擎更新內存，寫 redo log（prepare 狀態）
4. 執行器寫 binlog
5. 執行器呼叫引擎提交事務，redo log 改為 commit 狀態

為什麼需要兩階段提交？

保證 redo log 和 binlog 的邏輯一致性。

場景問題
先 redo log 後 binlog，中間 crash資料恢復了，但 binlog 沒記錄，主從不一致
先 binlog 後 redo log，中間 crashbinlog 有記錄，但資料沒恢復，主從不一致

推薦組態：innodb_flush_log_at_trx_commit = 1（每次事務 redo log 持久化）+ sync_binlog = 1（每次事務 binlog 持久化）

本章小結#

flowchart TD
    A[SQL 語句] --> B[連接器<br/>建立連接、驗證身份]
    B --> C[查詢緩存<br/>8.0 已移除]
    C --> D[分析器<br/>詞法分析、語法分析]
    D --> E[優化器<br/>選擇索引、確定執行計劃]
    E --> F[執行器<br/>調用存儲引擎接口]
    F --> G[存儲引擎<br/>InnoDB: redo log + 兩階段提交]

    style C fill:#ffebee
  • Server 層：連接管理、SQL 解析、最佳化、執行
  • 存儲引擎：資料存取，InnoDB 提供事務和 crash-safe
  • redo log：保證崩潰恢復
  • binlog：保證資料可恢復、可複製
  • 兩階段提交：保證兩份日誌的一致性
Backward 資料庫核心 索引原理與優化 Forward