MySQL 儲存引擎 InnoDB 與 MyISAM 六大核心差異深度解析
選擇正確的儲存引擎直接決定資料庫的效能、可靠性與功能支援。本文從檔案結構、事務、SQL 操作、自增主鍵、行數統計、鎖機制六大維度深度對比，助你依業務場景做最佳選擇。
一、磁盤檔案結構差異
| 特性 | MyISAM | InnoDB |
|---|
| 檔案組成 | 三個獨立檔案：
.frm（表定義）、.MYD（資料）、.MYI（索引） | 表空間共享檔案：
ibdata1（系統表空間）或 .ibd（獨立表空間）+ Redo Log |
| 大小限制 | 受限於作業系統單一檔案大小（通常 2TB~16TB） | 受限於作業系統檔案大小，支援 64TB 單表 |
| 可移植性 | 直接複製三個檔案即可遷移 | 需透過 innodb_file_per_table + DISCARD/IMPORT TABLESPACE 或邏輯備份遷移 |
二、事務與資料完整性
- MyISAM：不支援事務，操作不可回滾，崩潰易導致資料損壞，需
REPAIR TABLE 修復。
- InnoDB：完整支援 ACID 事務、外鍵約束、崩潰恢復（Redo/Undo Log）、MVCC 多版本並發控制，適合金融、訂單等強一致性場景。
三、SQL 操作效能特徵
| 操作類型 | MyISAM | InnoDB |
|---|
| 大量 SELECT（唯讀/報表） | ✅ 極快（全表掃描無鎖開銷） | 稍慢（MVCC、Buffer Pool 管理開銷） |
| 大量 INSERT/UPDATE/DELETE | ❌ 慢（表鎖導致併發極低） | ✅ 快（行鎖、Change Buffer、Group Commit） |
| DELETE FROM table | 重建表，極快 | 逐行刪除，產生 Undo Log，較慢 |
| LOAD TABLE FROM MASTER | 支援 | 不支援（需先轉 MyISAM 再轉回） |
四、AUTO_INCREMENT 自增主鍵行為
| 特性 | MyISAM | InnoDB |
|---|
| 索引要求 | 可建立複合索引將自增列置於最後 | **必須**擁有唯一索引（通常為主鍵） |
| 計數器儲存 | 寫入 .MYI 索引檔，持久化 | 僅存在記憶體，重啟後重新計算 MAX(id)+1 |
| 重用已刪除最大值 | 不重用（複合索引例外） | 不重用（記憶體計數器單調遞增） |
| 重置方式 | ALTER TABLE ... AUTO_INCREMENT=N 或 myisamchk | ALTER TABLE ... AUTO_INCREMENT=N |
注意：InnoDB 記憶體計數器重啟後重新掃描 MAX(id)，若有大量刪除導致空洞，不會自動回收。
五、精確行數統計 COUNT(*)
- MyISAM：在表元資料中維護精確行數，
SELECT COUNT(*) FROM table **無 WHERE 條件時瞬間返回**，無需掃描。
- InnoDB：不儲存精確行數（MVCC 導致可見行數隨事務變化），
COUNT(*) 需全表掃描聚合，大表極慢。
- 解決方案：維護獨立計數表、使用 Redis 緩存、或
SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES 取得估算值。
六、鎖機制與併發能力
| 鎖類型 | MyISAM | InnoDB |
|---|
| 預設鎖粒度 | 表鎖（Table Lock） | 行鎖（Row Lock） |
| 讀寫阻塞 | 讀阻塞寫、寫阻塞讀、寫阻塞寫 | 讀不阻塞寫、寫不阻塞讀（MVCC），寫阻塞寫 |
| 死鎖檢測 | 無 | 自動檢測並回滾受害者 |
| 全表掃描升級 | 無索引可用時維持表鎖 | 無法確定掃描範圍時（如 LIKE '%abc%'）退化為表鎖 |
選型決策樹
- 需要事務、外鍵、崩潰安全 → InnoDB（預設最佳選擇）。
- 純唯讀/報表、極少寫入、需精確 COUNT(*)、全文搜尋（5.6 前） → MyISAM 可考慮。
- 混合負載、高併發寫入、行級鎖需求 → InnoDB。
- 嵌入式、唯讀媒體、極簡部署 → MyISAM 輕量優勢明顯。
MySQL 5.5+ 起 InnoDB 為預設引擎，除非有明確理由，新建表一律建議使用 InnoDB。
- 上一篇文章： Linux 環境下 MySQL 效能調優完整指南
- 下一篇文章： MySQL 查詢效能優化基礎教程：緩存、索引、排序與不可達查詢
Copyright ©
程式碼之家 All Rights Reserved
