MySQL 儲存引擎深度對比：InnoDB vs MyISAM 核心機制與選型指南
MySQL 5.5+ 預設引擎為 InnoDB，5.1 及之前為 MyISAM。理解兩者核心差異是架構選型與效能調優的基礎。
InnoDB：事務型引擎王者
核心特性
- 支援 ACID 事務：Commit/Rollback、崩潰恢復
- 行級鎖 (Row-level Locking)：高並發寫入效能
- MVCC (多版本並發控制)：非阻塞讀取、四大隔離級別(預設 REPEATABLE READ)
- 聚簇索引：主鍵即資料，主鍵查詢極快
- 外鍵約束：資料完整性保障
- 自動崩潰恢復：Redo Log + Undo Log + Doublewrite Buffer
架構細節
- 表空間：`innodb_file_per_table=ON` 獨立 `.ibd` 檔案，或共享 `ibdata1`
- 二級索引包含主鍵值：主鍵過大會膨脹所有二級索引 → 建議主鍵用自增整數
- 間隙鎖/Next-Key Lock：防止幻讀，但可能導致鎖衝突
適用場景
- ✅ 絕大多數 OLTP 業務（電商、金融、社交）
- ✅ 需要事務、行鎖、外鍵、崩潰安全恢復
- ✅ 高並發讀寫混合負載
MyISAM：輕量唯讀/日誌型引擎
核心特性
- 不支援事務：無 Commit/Rollback
- 表級鎖：讀寫互斥，寫鎖優先級高
- 全文索引 (5.6+ InnoDB 也支援)：早期優勢
- 壓縮表 (myisampack)：唯讀壓縮，節省空間
- GIS 空間索引：地理資料查詢
- 崩潰不安全：需 `REPAIR TABLE` 修復，可能資料遺失
檔案結構
- `tbl_name.frm`：表定義
- `tbl_name.MYD` (MYData)：資料檔
- `tbl_name.MYI` (MYIndex)：索引檔
並發插入機制
`concurrent_insert` 變數控制：
- 0：禁用
- 1 (預設)：無空洞時表尾並發插入
- 2：無論空洞皆允許表尾並發插入
鎖調度優化
- `low-priority-updates=1`：降低寫優先級
- `max_write_lock_count`：讀鎖達閾值降低寫優先級
- 避免長查詢阻塞寫入
適用場景
- ✅ 唯讀/極少寫入：報表表、日誌表、配置表
- ✅ 全文搜尋 (若不升級 5.6+)
- ✅ 壓縮唯讀歷史資料
- ❌ 任何需要事務、高併發寫入、資料完整性保障的業務
核心差異對比表
| 維度 | InnoDB | MyISAM |
|---|
| 事務支援 | ✅ 完整 ACID | ❌ 無 |
| 鎖粒度 | 行級 (含意向鎖、間隙鎖) | 表級 |
| 並發效能 | 高 (寫不鎖讀、MVCC) | 低 (讀寫互斥) |
| 崩潰恢復 | ✅ 自動 (Redo/Undo Log) | ❌ 手動 REPAIR，可能遺失 |
| 外鍵 | ✅ 支援 | ❌ 不支援 |
| 全文索引 | ✅ 5.6+ | ✅ 早期優勢 |
| 壓縮表 | ✅ 透明壓縮/頁面壓縮 | ✅ myisampack (唯讀) |
| 索引結構 | 聚簇索引 (B+Tree) | 非聚簇 (B+Tree) |
| 主鍵要求 | 強烈建議有 | 可無 |
| 資料檔大小 | 較大 (系統欄位、MVCC) | 較小 |
| 預設版本 | 5.5+ | 5.1 及之前 |
選型決策樹
- 需要事務嗎？→ 是 → InnoDB
- 高併發寫入嗎？→ 是 → InnoDB
- 需要外鍵/資料完整性？→ 是 → InnoDB
- 純唯讀/日誌/報表/全文搜尋(舊版)？→ 可考慮 MyISAM
- 不確定？→ 預設選 InnoDB (5.5+ 預設)
遷移建議
-- 線上轉換 (會鎖表重建，大表慎用)
ALTER TABLE tbl_name ENGINE=InnoDB;
-- 大表零停機遷移工具
pt-online-schema-change --alter "ENGINE=InnoDB" D=db,t=tbl --execute
