MySQL 兩大主流存儲引擎 MyISAM 與 InnoDB 在架構設計、事務支援、鎖粒度、適用場景上存在本質差異。本文從底層實現到業務選型進行系統對比。
一、架構與文件存儲差異
| 維度 | MyISAM | InnoDB |
|---|
| 文件結構 | 三文件：.frm(結構)、.MYD(數據)、.MYI(索引) | 表空間模式：共享/獨立表空間（.ibd），受限於 OS 文件大小（通常 2TB） |
| 可移植性 | 二進制級跨平台複製 | 需邏輯導出/導入或傳輸表空間 |
| 壓縮支援 | 支援 myisampack 唯讀壓縮 | 支援頁面壓縮（Transparent Page Compression） |
| 全文索引 | 原生支援（5.6+） | 5.6+ 開始支援 |
二、核心功能對比
| 特性 | MyISAM | InnoDB |
|---|
| 事務支援 | ❌ 不支援，操作不可回滾 | ✅ 完整 ACID，支援 Commit/Rollback |
| 外鍵約束 | ❌ | ✅ |
| 鎖粒度 | 表級鎖 | 行級鎖（意向鎖 + 記錄鎖 + 間隙鎖） |
| MVCC | ❌ | ✅ 多版本併發控制，讀寫不阻塞 |
| 崩潰恢復 | 依賴 myisamchk 修復，可能損壞 | Redo/Undo Log + Doublewrite Buffer，自動恢復 |
| 聚簇索引 | ❌ 堆表，主鍵非聚簇 | ✅ 主鍵即聚簇索引，數據與索引同存 |
三、鎖機制深度解析
MyISAM 表級鎖
- 共享讀鎖（Read Lock）：多讀不阻塞，寫等待。
- 獨佔寫鎖（Write Lock）：讀寫皆阻塞。
- 並發插入：
concurrent_insert 參數控制尾部追加並發。
InnoDB 行級鎖
- Record Lock：鎖定單行記錄。
- Gap Lock：鎖定間隙，防止幻讀（RR 隔離級別）。
- Next-Key Lock：Record + Gap 組合。
- 注意：無索引或索引失效時退化為表鎖（如
WHERE name LIKE '%aaa%'）。
四、DML 性能特徵
| 操作 | MyISAM | InnoDB |
|---|
| 大量 SELECT | ✅ 優勢明顯（全表掃描快、壓縮表） | 良好，覆蓋索引可媲美 |
| 大量 INSERT/UPDATE | 表鎖爭用嚴重 | ✅ 行鎖 + 變更緩衝，高併發優勢 |
| DELETE 全表 | 重建表極快 | 逐行刪除，較慢；建議 TRUNCATE 或分區 |
| AUTO_INCREMENT | 需單列索引 | 允許聯合索引首列 |
五、InnoDB 關鍵優化參數
- innodb_flush_log_at_trx_commit = 2：備庫可設 2，主庫建議 1 保證持久性。
- autocommit：批量寫入時顯式
BEGIN; ... COMMIT; 避免每行 fsync。
- innodb_buffer_pool_size：設為物理記憶體 60-80%，核心性能參數。
六、選型決策樹
- 需要事務/外鍵/高併發寫入 → InnoDB（預設首選）。
- 純讀密集、無事務、需全文索引（舊版本）、空間資料 → MyISAM。
- 日誌/審計/統計類寫入極少、查詢極多 → 可評估 MyISAM 或 InnoDB 壓縮頁。
- 混合負載 → 統一 InnoDB，利用覆蓋索引、分區、讀寫分離優化讀性能。
七、遷移注意事項
LOAD DATA FROM MASTER 僅支援 MyISAM，InnoDB 需先轉換再導入再轉回（外鍵表不可用）。- MyISAM → InnoDB：
ALTER TABLE t ENGINE=InnoDB; 需鎖表，大表建議 pt-online-schema-change。
- 檢查
ROW_FORMAT、字符集、大小寫敏感設置一致性。
