MySQL 儲存引擎深度解析：InnoDB、MyISAM、Memory 選型指南
核心引擎對比總覽
| 特性 | InnoDB | MyISAM | Memory (HEAP) | Archive |
|---|
| 事務支援 (ACID) | ✅ 完整支援 | ❌ 不支援 | ❌ 不支援 | ❌ 不支援 |
| 行級鎖 | ✅ | ❌ (表鎖) | ❌ (表鎖) | ❌ |
| 外鍵約束 | ✅ | ❌ | ❌ | ❌ |
| 全文索引 | ✅ (5.6+) | ✅ | ❌ | ❌ |
| 資料壓縮 | ✅ | ✅ (myisampack) | ❌ | ✅ (極高) |
| 崩潰恢復 | ✅ (Redo/Undo Log) | ❌ (需修復) | ❌ (資料遺失) | ❌ |
| MVCC 多版本並發 | ✅ | ❌ | ❌ | ❌ |
| 預設引擎 (5.5+) | ✅ | ❌ | ❌ | ❌ |
InnoDB：核心業務首選
架構特點
- 聚簇索引：主鍵即資料，主鍵查詢極快
- Buffer Pool：緩存資料+索引，核心效能參數
innodb_buffer_pool_size
- Redo Log (WAL)：保證持久性，
innodb_flush_log_at_trx_commit=1 最安全
- Undo Log：支援 MVCC、回滾、一致性讀
- Change Buffer：輔助索引寫入優化
適用場景
- 核心交易系統 (訂單、帳戶、庫存)
- 高並發讀寫混合負載
- 需事務、外鍵、行級鎖的業務
- 大多數生產環境預設選擇
關鍵調優參數
[mysqld]
innodb_buffer_pool_size = 物理記憶體 60-80%
innodb_log_file_size = 1G (總 redo log 容量)
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_read_io_threads = 8
innodb_write_io_threads = 8
MyISAM：唯讀/日誌/非關鍵資料
特點
- 表級鎖：讀鎖共享、寫鎖排他，讀寫互斥，並發差
- 無事務：崩潰易損壞，需
myisamchk -r 修復
- 儲存格式：定長/動態/壓縮 (
myisampack 唯讀壓縮)
- 計數器快：
SELECT COUNT(*) 無 WHERE 瞬時返回
- 全文索引成熟 (5.6 前 InnoDB 不支援)
適用場景
- 唯讀/極少寫入：報表表、日誌歸檔、配置表
- 全文搜尋 (舊版本遷移)
- 非關鍵、可重建資料
Memory (HEAP)：極速暫存
特點
- 資料全在記憶體，重啟資料消失 (結構保留)
- 預設 Hash 索引 (精確查詢 O(1))，可指定 B-Tree (範圍查詢)
- 固定長度儲存 (VARCHAR 當 CHAR 處理)
- 表級鎖，不支援 BLOB/TEXT
- 大小限制：
max_heap_table_size (預設 16MB)
適用場景
- 臨時中間結果表 (複雜查詢分解)
- 查找表/字典表 (郵遞區號、編碼對照)
- Session 暫存 (配合定期持久化)
- 單位測試、快速原型
其他特殊引擎
| 引擎 | 用途 | 特點 |
|---|
| Archive | 海量日誌歸檔 | 僅支援 INSERT/SELECT，極高壓縮 (Zlib)，無索引 |
| CSV | 外部工具交換 | 以 CSV 檔案儲存，無索引，可直接用 Excel 開啟 |
| Federated | 跨實例查詢 | 映射遠端 MySQL 表，網路開銷大，慎用 |
| Blackhole | 測試/複製過濾 | 寫入即丟棄，Binlog 記錄事件，用於複製鏈路過濾 |
引擎選型決策樹
需要事務/外鍵/行級鎖/崩潰安全？
├─ 是 → InnoDB (95% 場景)
└─ 否
資料須持久化？
├─ 否 → Memory (暫存/查找)
└─ 是
唯讀/極少寫/需 COUNT(*) 快/全文搜尋？
├─ 是 → MyISAM
└─ 否
海量唯寫日誌/歸檔？
├─ 是 → Archive
└─ 否 → InnoDB (通用最優)
混合部署最佳實踐
-- 建表時指定引擎
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
...
) ENGINE=InnoDB;
CREATE TABLE access_log (
id BIGINT AUTO_INCREMENT,
url VARCHAR(255),
...
) ENGINE=Archive;
-- 修改現有表引擎 (需重建表，鎖表時間長)
ALTER TABLE t ENGINE=InnoDB;
-- 或使用 pt-online-schema-change 零停機變更
避坑指南
- ❌ 混用引擎導致分散式事務不支援 (XA 事務除外)
- ❌ MyISAM 表頻繁 UPDATE 導致表鎖爭用嚴重
- ❌ Memory 表存放關鍵資料未持久化，重啟丟失
- ❌ Archive 表嘗試 UPDATE/DELETE/索引 → 報錯
- ✅ 生產環境統一 InnoDB，特殊場景才用其他引擎
- ✅ 監控
Innodb_buffer_pool_reads / Innodb_buffer_pool_pages_free 調整 Buffer Pool
