快速對照表
| 維度 | MyISAM | InnoDB |
|---|
| 檔案結構 | .frm(定義) .MYD(資料) .MYI(索引) | 共享表空間 或 獨立表空間 + Redo/Undo Log |
| 交易支援 | ❌ 不支援 | ✅ ACID 完整支援 |
| 鎖粒度 | 表鎖 | 行鎖（含間隙鎖、Next-Key Lock） |
| 外鍵 | ❌ | ✅ |
| MVCC | ❌ | ✅ 多版本並發控制 |
| 崩潰恢復 | 需修復 | 自動恢復 |
| 全文索引 | 5.6 前唯一支援 | 5.6+ 支援 |
| COUNT(*) | O(1) 直接讀取統計 | O(N) 掃描計算 |
| 適用場景 | 唯讀/重讀輕寫、全文搜尋 | OLTP、高併發寫入、需交易場景 |
詳細差異解析
1. 檔案結構與儲存
- MyISAM： 三個獨立檔案，易於跨平台複製、備份單表
- InnoDB： 預設共享表空間
ibdata1，或啟用 innodb_file_per_table=1 產生 .ibd 獨立檔案。大小受 OS 檔案限制（通常 2TB+）
2. 交易與資料完整性
- MyISAM 無交易，寫入中斷易導致資料損壞，需
REPAIR TABLE
- InnoDB 支援 Commit/Rollback、雙重寫緩衝、Redo/Undo Log 保證耐久性
3. SELECT/INSERT/UPDATE/DELETE 效能特徵
- 大量 SELECT： MyISAM 略快（無鎖開銷、壓縮表支援）
- 大量 INSERT/UPDATE： InnoDB 明顯優勢（行鎖、變更緩衝、自適應雜湊索引）
- DELETE： InnoDB 標記刪除+ Purge 線程非同步清理；MyISAM 立即重組
- LOAD DATA： InnoDB 需先轉 MyISAM 匯入再轉回（或用
innodb_flush_log_at_trx_commit=2 暫緩刷盤）
4. AUTO_INCREMENT 處理
- MyISAM： 計數器存在檔案頭，重啟不變，支援複合索引最後一列為自增
- InnoDB： 計數器僅在記憶體，重啟後以
MAX(id)+1 初始化，單列索引必須為主鍵或首列
5. COUNT(*) 差異
- MyISAM 維護行數統計，
SELECT COUNT(*) 瞬間返回
- InnoDB 需掃描聚簇索引或輔助索引，大表極慢
→ 解法：維護計數表、使用 INFORMATION_SCHEMA.TABLES.TABLE_ROWS 估值、或觸發器同步計數
6. 鎖機制
- MyISAM： 表鎖，讀鎖共享、寫鎖獨佔，讀寫互斥，併發極差
- InnoDB： 行鎖（基於索引），支援
SELECT ... LOCK IN SHARE MODE、FOR UPDATE、一致性非鎖定讀（MVCC）
⚠️ 無索引條件更新會退化為表鎖：UPDATE t SET x=1 WHERE name LIKE '%abc%'
選型決策樹
- 需要交易/外鍵/高併發寫入？ → InnoDB
- 純唯讀/日誌/報表/全文搜尋？ → MyISAM（或 InnoDB + 全文索引）
- 不確定？ → 預設 InnoDB（MySQL 5.7+ 預設引擎）
遷移建議
-- MyISAM 轉 InnoDB（線上 DDL，5.6+）
ALTER TABLE t ENGINE=InnoDB;
-- 大表建議用 pt-online-schema-change 避免鎖表