MySQL 體系架構深度解析：從連接器到儲存引擎的完整圖譜

架構總覽

MySQL 採用**分層架構**，清晰分離「服務層」與「儲存引擎層」，支援插件式儲存引擎，是其靈活高效的核心基因。

+-------------------+
|   客戶端連接器     |  ← JDBC/ODBC/.NET/PHP/Python/C API
+-------------------+
|   連接池/線程管理  |  ← 認證、連線限制、線程池、Prepared Statement 快取
+-------------------+
|      SQL 介面      |  ← 接收 SQL、回傳結果集
+-------------------+
|      解析器        |  ← 詞法/語法分析、AST 生成 (Lex + YACC)
+-------------------+
|      優化器        |  ← 邏輯重寫、成本估算、執行計畫選擇、索引合併、MRR、ICP
+-------------------+
|      執行器        |  ← 呼叫儲存引擎 API、處理事務、鎖、Savepoint
+-------------------+
|   查詢快取 (已移除)|  ← MySQL 8.0 移除，歷史遺留
+-------------------+
|   儲存引擎 API     |  ← 抽象介面：handler::ha_* 虛函數
+---------+---------+
          |
    +-----+-----+-----+-----+
    |           |           |
 InnoDB      MyISAM      Memory      ... (30+ 插件引擎)
    |           |           |
  叢集索引    堆表/全文    記憶體表
  MVCC/事務   表鎖/快速    Hash 索引

各層詳細職責

1. 連接器

• 支援協定：TCP/IP、Unix Socket、Named Pipe、Shared Memory
• 認證：用戶名/密碼、SSL、LDAP、PAM、Windows 原生
• 線程模型：One Thread Per Connection (預設)，可選 Thread Pool (Enterprise/Percona/MariaDB)
• 連線控制：`max_connections`、`max_user_connections`、`wait_timeout`、`interactive_timeout`

2. SQL 介面

• 接收客戶端發送的 SQL 文本 (COM_QUERY、COM_STMT_PREPARE/EXECUTE)
• 回傳結果集 (Row-based Protocol / Binary Protocol)
• 處理 `SHOW`、`DESCRIBE`、`EXPLAIN` 等非標準查詢

3. 解析器

• 技術：Lex (詞法分析) + YACC/Bison (語法分析)
• 輸出：**AST (Abstract Syntax Tree)**，後續所有處理基於此結構
• 驗證：語法正確性、關鍵字保留字、識別符長度、註釋處理

4. 優化器 —— 核心大腦

策略：選擇-投影-聯接 (Select-Project-Join)

• 邏輯重寫：子查詢轉聯接、外連接轉內連接、條件下推、常數摺疊
• 存取路徑選擇：Full Scan vs Index Scan vs Index Merge vs Range Scan
• 聯接順序優化：DP (Dynamic Programming) + Greedy 啟發式，`optimizer_search_depth` 控制
• 成本模型：基於統計資料 (樣本頁、直方圖) 估算 IO/CPU 成本
• 先進特性：
  • ICP (Index Condition Pushdown)：將 WHERE 條件下推至儲存引擎過濾
  • MRR (Multi-Range Read)：批次讀取減少隨機 IO
  • BKA (Batched Key Access)：聯接優化
  • Derived Table Merge / Materialization：衍生表優化

5. 執行器

• 按執行計畫逐步呼叫儲存引擎 `handler` 介面
• 事務管理：`START TRANSACTION`、`COMMIT`、`ROLLBACK`、`SAVEPOINT`
• 鎖管理：表鎖 (MDL)、行鎖 (InnoDB)、意向鎖、間隙鎖、Next-Key Lock
• 觸發器、外鍵、檢查約束執行

6. 儲存引擎層 —— 插件式架構精髓

關鍵差異：InnoDB vs MyISAM

• 叢集索引：InnoDB 主鍵即資料順序，主鍵查詢極快；MyISAM 主鍵指向資料檔位置
• MVCC：InnoDB 多版本並發控制，讀不阻塞寫；MyISAM 表鎖讀寫互斥
• 崩潰恢復：InnoDB Redo/Undo Log 保證耐久性；MyISAM 需 `REPAIR TABLE`
• 外鍵：InnoDB 原生支援；MyISAM 不支援

同一資料庫混用多引擎

**完全支援**：`CREATE TABLE t1 (...) ENGINE=InnoDB; CREATE TABLE t2 (...) ENGINE=MyISAM;`

• 跨引擎聯接：優化器自動處理，但無法利用跨引擎索引優化
• 跨引擎事務：不支援 (除非使用 XA 分散式事務)
• 遷移：`ALTER TABLE t ENGINE=InnoDB;` (線上 DDL 5.6+)

MySQL 8.0 重要架構變更

1. 移除 Query Cache：鎖爭用嚴重、命中率低、不支援 Prepared Statement
2. 資料字典遷移至 InnoDB：`.frm` 檔案消失，元資料存於 `mysql.ibd`，支援原子 DDL
3. 直方圖統計資料：`ANALYZE TABLE` 產生柱狀圖，優化器成本估算更精準
4. CTE / Window Functions：優化器支援遞歸查詢、分析函數
5. Instant DDL：`ADD COLUMN`、`DROP COLUMN` 秒級完成 (僅修改元資料)
6. 預設 utf8mb4、utf8mb4_0900_ai_ci：完整 Unicode 支援

效能調優視角的架構認知

深入理解 MySQL 分層架構與儲存引擎插件機制，是從「會用 SQL」進階為「資料庫架構師」的必修課。架構決定上限，細節決定成敗。