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 小時）
• 建議使用長連接，但要注意記憶體洩漏問題

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 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

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

binlog（歸檔日誌）#

用於：

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

詳細的格式選擇與恢復細節見「04 日誌系統」章。

兩階段提交（概覽）#

更新語句的完整流程：

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

為什麼需要兩階段提交？ 保證 redo log 和 binlog 的邏輯一致性。完整的崩潰恢復規則與圖示見「04 日誌系統」章。

推薦設定：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