MySQL 儲存引擎深度解析：InnoDB vs MyISAM、索引結構（B+Tree）、聚集與非聚集索引
身為 Java 開發者，MySQL 是日常核心組件。深入理解 InnoDB 與 MyISAM 兩大引擎架構差異、索引底層資料結構（B+Tree）、聚集/非聚集索引機制，對 SQL 優化、架構選型至關重要。
一、InnoDB 引擎特性
- ACID 事務支援：完整實現四大隔離級別（READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE）
- 行級鎖：高併發寫入性能優於表鎖
- 外鍵約束：資料完整性保障
- MVCC（多版本併發控制）：非鎖定讀，讀寫不阻塞
- 自適應哈希索引：熱點頁自動建立哈希加速
- 緩衝池：
innodb_buffer_pool 缓存資料與索引頁
- 不支援 FULLTEXT（5.6+ 已支援）
- 無儲存行數：
SELECT COUNT(*) 需全表掃描
二、MyISAM 引擎特性
- 無事務支援：不適合核心業務
- 表級鎖：寫入鎖全表，併發差
- 無外鍵、無行鎖、無 MVCC
- 儲存行數：
SELECT COUNT(*) 極快（O(1)）
- 支援 FULLTEXT 索引（5.6 前 InnoDB 不支援）
- 壓縮表：
myisampack 僅讀壓縮
三、引擎選擇指南
| 場景 | 推薦引擎 | 理由 |
|---|
| 核心交易、高併發寫入、需事務 | InnoDB | 行鎖+MVCC+ACID |
| 唯讀/極少寫入、大量 COUNT(*)、全文搜尋 | MyISAM | 計數快、FULLTEXT |
| 大批量 INSERT（批次多行） | MyISAM | 表鎖下批次寫入快 |
| 大批量 UPDATE/DELETE、高併發 | InnoDB | 行鎖減少阻塞 |
| 主鍵查詢為主 | InnoDB | 聚集索引主鍵查詢極快 |
現代最佳實踐：預設選 InnoDB（5.5+ 預設），除非明確場景需 MyISAM。
四、索引資料結構：B-Tree 與 B+Tree
B-Tree（多路平衡搜尋樹）
- 每節點最多
d 個分支（度），d-1 個鍵值
- 鍵值分佈在所有節點（含葉子與非葉子）
- 查找複雜度
O(log_d N)，d 越大樹高越低
B+Tree（B-Tree 變體，MySQL 索引實際採用）
- 非葉子節點僅存鍵值+指針，不存實際資料
- 葉子節點存完整資料記錄（InnoDB）或資料指標（MyISAM）
- 葉子節點雙向鏈表串聯，支援範圍掃描、全表掃描極快
- 所有查找路徑長度固定（根到葉），性能穩定
五、MyISAM 索引結構：非聚集索引
- 索引檔（
.MYI）與資料檔（.MYD）分離
- B+Tree 葉子節點存資料檔物理位址（指標）
- 主索引與輔助索引結構相同，皆為非聚集
- 查找流程：索引 → 取得位址 → 讀取資料檔
六、InnoDB 索引結構：聚集索引
- 索引即資料，資料即索引：
.ibd 檔案即為主索引（聚集索引）
- B+Tree 葉子節點存完整行資料
- 主鍵即為聚集索引鍵：
- 有主鍵 → 主鍵為聚集索引
- 無主鍵 → 首個非空唯一索引
- 皆無 → 隱式 6 字節 row_id
- 輔助索引葉子節點存主鍵值而非位址
→ 查找需「回表」：輔助索引 → 主鍵 → 聚集索引 → 行資料
七、InnoDB 設計最佳實踐
- 主鍵盡量短、遞增：
BIGINT UNSIGNED AUTO_INCREMENT 理想；避免 UUID、長字串、複合主鍵 → 導致輔助索引膨脹、頁分裂頻繁。
- 避免業務欄位做主鍵：如身分證、手機號、訂單號（非單調遞增）。
- 輔助索引覆蓋查詢：
INDEX (a,b,c) 可滿足 SELECT a,b,c FROM t WHERE a=? 避免回表。
- 合理設定
innodb_buffer_pool_size：熱點索引與資料常駐記憶體。
八、總結對比表
| 特性 | InnoDB | MyISAM |
|---|
| 事務 | 支援 | 不支援 |
| 鎖粒度 | 行鎖 | 表鎖 |
| 外鍵 | 支援 | 不支援 |
| MVCC | 支援 | 不支援 |
| COUNT(*) | 全表掃描 | O(1) 直接讀取 |
| 索引類型 | 聚集索引 | 非聚集索引 |
| 輔助索引葉子 | 主鍵值 | 資料位址 |
| 崩潰恢復 | Redo Log 快速 | 慢、易損壞 |
| 壓縮 | 頁面壓縮 | myisampack 僅讀 |
