MySQL 的靈活性源於其獨特的**插件式存儲引擎架構**：將查詢處理（Server 層）與數據存儲/提取（Engine 層）解耦。本文深度拆解邏輯三層架構、鎖機制、事務實現（ACID、隔離級別、MVCC、死鎖、日誌）、以及兩大主流引擎 InnoDB/MyISAM 的核心差異與選型指南。
┌─────────────────────────────────────┐
│ 連接管理/線程池/認證授權 │ ← 第一層：連接層（網絡接口）
├─────────────────────────────────────┤
│ 查詢緩存 → 解析器 → 優化器 → 執行器 │ ← 第二層：Server 服務層（核心大腦）
├─────────────────────────────────────┤
│ 存儲引擎 API (InnoDB/MyISAM/...) │ ← 第三層：存儲引擎層（數據存取）
└─────────────────────────────────────┘user/host/password/plugin| 鎖類型 | 別名 | 兼容性 | 場景 |
|---|---|---|---|
| 共享鎖 | S 鎖、讀鎖 | S-S 兼容，S-X 互斥 | SELECT ... LOCK IN SHARE MODE |
| 排他鎖 | X 鎖、寫鎖 | X 與任何鎖互斥 | INSERT/UPDATE/DELETE、SELECT ... FOR UPDATE |
| 策略 | 粒度 | 併發度 | 開銷 | 引擎支持 |
|---|---|---|---|---|
| 表鎖 | 整張表 | 低 | 極小 | MyISAM、MEMORY、CSV |
| 行鎖 | 單行記錄 | 高 | 大（創建/檢測/釋放） | InnoDB |
| 頁鎖 | 數據頁 | 中 | 中 | BerkeleyDB（已淘汰） |
InnoDB 行鎖實現：基於索引記錄鎖定，無索引或索引失效時退化為表鎖。三種行鎖模式：
表級別標識：IS（意向共享）、IX（意向排他）。目的：快速判斷表級是否有行鎖衝突，避免掃描全表檢查行鎖。
| 特性 | 含義 | MySQL 實現 |
|---|---|---|
| 原子性 | 全做或全不做 | Undo Log 回滾 |
| 一致性 | 約束前後狀態合法 | 原子性+隔離性+持久性+約束共同保證 |
| 隔離性 | 並發事務互不干擾 | 鎖 + MVCC |
| 持久性 | 提交後永久生效 | Redo Log (WAL) + 雙寫緩衝 |
| 級別 | 髒讀 | 不可重複讀 | 幻讀 | MySQL 默認 |
|---|---|---|---|---|
| READ UNCOMMITTED | ✅ 可能 | ✅ 可能 | ✅ 可能 | 否 |
| READ COMMITTED | ❌ 不可能 | ✅ 可能 | ✅ 可能 | 否（Oracle 默認） |
| REPEATABLE READ | ❌ 不可能 | ❌ 不可能 | ❌ InnoDB 通過 MVCC+Gap Lock 解決 | ✅ 是 |
| SERIALIZABLE | ❌ 不可能 | ❌ 不可能 | ❌ 不可能 | 否（強制串行，性能極差） |
關鍵點：InnoDB RR 級別通過 **MVCC（快照讀）+ Next-Key Lock（當前讀）** 實質上達到了可串行化效果，且併發性能遠優於 SERIALIZABLE。
多事務循環等待資源形成死鎖。InnoDB 採用 **Wait-for Graph 死鎖檢測**，定期掃描，發現環路則**回滾持有行鎖最少的事務**（簡單啟發式）。
-- 典型死鎖場景
Session 1: UPDATE t SET v=1 WHERE id=1; -- 鎖 id=1
Session 2: UPDATE t SET v=2 WHERE id=2; -- 鎖 id=2
Session 1: UPDATE t SET v=3 WHERE id=2; -- 等待 Session 2
Session 2: UPDATE t SET v=4 WHERE id=1; -- 等待 Session 1 → DEADLOCK避免死鎖：固定訪問順序、縮短事務、避免長事務、適當降低隔離級別。
Redo Log（重做日誌）：循環寫入，順序 IO，崩潰恢復核心。
Undo Log（回滾日誌）：記錄反操作，支持回滾與 MVCC 多版本。
二階段提交：Redo Log Prepare → Binlog Write → Redo Log Commit，保證主備一致。
InnoDB 每行隱藏兩列：DB_TRX_ID（創建事務 ID）、DB_ROLL_PTR（回滾指鏈）。
DB_TRX_ID <= 當前事務 ID 且未被刪除的版本，無鎖、無阻塞SELECT ... LOCK IN SHARE MODE、FOR UPDATE、DML）：讀取最新版本，**加鎖**Purge 線程定期清理不可見的舊版本（Undo Log 回收）。
| 維度 | InnoDB（默認/推薦） | MyISAM（歷史/只讀） |
|---|---|---|
| 事務支持 | ✅ 完整 ACID | ❌ 無 |
| 鎖粒度 | 行鎖（高併發） | 表鎖（低併發） |
| MVCC | ✅ 支持 | ❌ 無 |
| 外鍵 | ✅ 支持 | ❌ 無 |
| 崩潰恢復 | ✅ 自動（Redo Log） | ❌ 需手動 REPAIR |
| 索引結構 | 聚簇索引（主鍵即數據） | 非聚簇（索引指向物理行） |
| 全文索引 | 5.6+ 支持 | 早期支持 |
| 壓縮表 | 頁壓縮/透明壓縮 | ✅ myisampack |
| 適用場景 | OLTP 核心業務 | 只讀分析、日志、非關鍵數據 |
除非極少數只讀/非關鍵場景，默認選 InnoDB。 MySQL 5.5+ 已將 InnoDB 設為默認引擎，這是經過生產環境億級驗證的最穩健選擇。
MySQL 架構設計精妙在於 **Server 層統一 SQL 處理 + Engine 層插件化存儲**。深入理解鎖粒度演進（表→行）、事務隔離實現（鎖+MVCC）、WAL 日誌機制、InnoDB 聚簇索引特性，是進行高性能架構設計、疑難排查、參數調優的理論基石。
