一、引擎家族譜系
| 引擎 | 血緣 | 支援交易 | 支援外鍵 | 鎖粒度 | 主要定位 |
|---|
| ISAM | 原生舊格式 | ❌ | ❌ | 表鎖 | 已淘汰，僅歷史相容 |
| MyISAM | ISAM 增強版 (5.1 前預設) | ❌ | ❌ | 表鎖 | 唯讀密集、全文檢索、只讀報表 |
| MEMORY (HEAP) | 記憶體表 | ❌ | ❌ | 表鎖 | 極速暫存、查找表、Session 存儲 |
| InnoDB | MySQL++ API 衍生 (5.5+ 預設) | ✅ ACID | ✅ | 行鎖 (MVCC) | 核心交易、高併發寫入、關鍵業務 |
| BDB (BerkeleyDB) | Sleepycat 嵌入 | ✅ | ✅ | 頁鎖 | 已移除 (5.1+)，歷史參考 |
二、ISAM：歷史產物
- 優點：讀取極快、資源佔用極低
- 缺點：無交易、無容錯 (崩潰資料不可恢復)、無並發寫入能力
- 現狀：已移除 (5.1+)，僅極舊遷移專案會遇到
三、MyISAM：讀多寫少的經典選擇
核心特性
- 表鎖：並發寫入效能差，讀取並發佳
- 全文索引 (5.6 前唯一支援)
- 壓縮表 (
myisampack) 唯讀極致壓縮
- 獨立檔案：
.frm 結構、.MYD 資料、.MYI 索引，易備份/遷移單表
維護成本
- 頻繁
DELETE/UPDATE 產生碎片 → 需定期 OPTIMIZE TABLE
- 崩壞風險高 → 需
myisamchk / REPAIR TABLE
適用場景
- 日誌/審計表 (僅追加、極少更新)
- 資料倉庫維度表 (批次載入、唯讀查詢)
- 全文檢索 (5.6 前)、地理空間索引 (舊版)
- 虛擬主機限制只能用 MyISAM 的環境
四、MEMORY (HEAP)：極速揮發性儲存
特性
- 資料 100% 駐留記憶體，速度比磁碟引擎快 10-100 倍
- 支援 HASH 索引 (預設) 與 BTREE 索引
- 固定長度行格式：
VARCHAR 實際存為 CHAR、不支援 BLOB/TEXT
- 重啟資料全失失，結構保留
適用場景
- 查找表：郵遞區號、字典、配置參數
- 中間結果暫存：複雜報表多步驟 JOIN
- Session 存儲 (配合 TTL 清理)
- 高頻計數器 (配合定期落地持久化)
關鍵限制
- 記憶體上限受
max_heap_table_size 限制 (預設 16MB)
- 不支援交易、外鍵、全文、空間索引
- 並發寫入仍為表鎖，高併發寫入成為瓶頸
五、InnoDB：現代 MySQL 核心
核心優勢
- ACID 交易：Redo Log (持久) + Undo Log (回滾 + MVCC)
- 行級鎖 + MVCC：高併發讀寫不阻塞，一致性非鎖定讀
- 外鍵約束：資料完整性保障
- 崩潰恢復：啟動自動 Redo Log 前滾 + Undo Log 回滾
- 自適應雜湊索引、插入緩衝、讀前預讀：效能自優化
架構
- 共享表空間 (
ibdata1) + 獨立表空間 (file_per_table)
- Buffer Pool (資料+索引+鎖+插入緩衝+自適應雜湊) — 核心記憶體區
- Redo Log 循環寫入、Doublewrite Buffer 防止部分寫入
六、選型決策矩陣
| 業務特徵 | 推薦引擎 | 備註 |
|---|
| 核心交易 (訂單、帳戶、支付) | InnoDB | 不可妥協 |
| 高併發讀寫混合 | InnoDB | 行鎖+MVCC |
| 唯讀報表、資料倉庫維度表 | MyISAM / InnoDB | MyISAM 壓縮表省空間 |
| 日誌、審計、追加寫入為主 | MyISAM / InnoDB | InnoDB 更安全 |
| 極速查找、暫存、可容忍丟失 | MEMORY | 配合持久化落地 |
| 全文檢索 (5.6+) | InnoDB | ngram parser 中文支援 |
| 地理空間 | InnoDB (5.7+) | R-tree 索引 |
七、混合部署實戰
-- 建表時指定
CREATE TABLE logs (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
msg TEXT
) ENGINE=MyISAM;
CREATE TABLE sessions (
sess_id CHAR(32) PRIMARY KEY,
data BLOB,
expiry INT UNSIGNED
) ENGINE=MEMORY;
-- 線上修改引擎 (鎖表重建)
ALTER TABLE tbl_name ENGINE=InnoDB;
同一資料庫可混用多種引擎，按表業務特性選型。
八、遷移建議
- MyISAM → InnoDB：
ALTER TABLE 重建、或 mysqldump | sed 's/MyISAM/InnoDB/' | mysql
- 遷移前必測：交易邏輯、鎖衝突、索引選擇性、磁碟空間 (InnoDB 通常大 20-30%)
- 5.5+ 新建表預設 InnoDB，除非有極強理由否則不要指定 MyISAM
