核心差異一覽

特性	InnoDB	MyISAM
事務支援	✅ 完整 ACID	❌ 不支援
行級鎖	✅ 支援	❌ 僅表級鎖
外鍵約束	✅ 支援	❌ 不支援
全文索引	✅ 5.6+ 支援	✅ 早期支援
COUNT(*) 效能	需掃表/索引	O(1) 直接讀取
崩潰恢復	✅ Redo/Undo Log	❌ 易損壞需修復
索引結構	聚簇索引（資料與主鍵同存）	非聚簇（索引指向資料檔）
壓縮	頁面壓縮/表壓縮	myisampack 只讀壓縮

架構層面深度解析

1. 聚簇索引 vs 非聚簇索引

• InnoDB：資料檔即主鍵 B+ 樹葉節點（聚簇索引）。二級索引葉節點存主鍵值，查詢需「回表」。主鍵應盡量短、單調遞增（如自增 ID），否則導致頁分裂、碎片化。
• MyISAM：資料檔（.MYD）與索引檔（.MYI）分離。索引葉節點存資料檔物理位移。COUNT(*) 极快，因統計數儲存在 .MYI 表頭。

2. 鎖機制與並發

• InnoDB：行鎖基於索引。WHERE id = 1（主鍵）→ 行鎖；WHERE name LIKE '%abc%'（無索引）→ 升級為表鎖。間隙鎖防止幻讀。
• MyISAM：讀共享鎖、寫排他鎖，讀寫互斥、寫寫互斥。高並發寫入嚴重阻塞。

3. 事務與崩潰安全

InnoDB 透過 Redo Log（WAL） 保證持久性，Undo Log 實現 MVCC 與回滾。innodb_flush_log_at_trx_commit = 1 確保不丟資料。MyISAM 無日志，崩潰易導致索引損壞，需 REPAIR TABLE。

效能基準測試參考（典型場景）

場景	InnoDB	MyISAM	說明
純讀取（主鍵點查）	極快	極快	差異微小
高並發讀寫混合	✅ 優	❌ 差	行鎖優勢明顯
大量 INSERT 批次匯入	良好	✅ 更快	MyISAM 無事務開銷
全文搜尋	5.6+ 可用	成熟	大量中文分詞建議用 Elasticsearch
COUNT(*) 無條件	慢（掃描）	✅ 秒級	InnoDB 可用觸發器維護計數表

選型決策樹

需要事務/外鍵/高並發寫入/崩潰安全？
  ├─ 是 → InnoDB (預設最佳選擇)
  └─ 否 → 純讀取/日誌/報表/資料倉儲？
          ├─ 是 → MyISAM 可考慮（或 InnoDB 唯讀副本）
          └─ 否 → InnoDB

常見迷思破解

• ❌ 「MyISAM 讀快很多」→ 實測差距常 < 10%，並發下 InnoDB 反而更快
• ❌ 「InnoDB 不支援全文索引」→ 5.6+ 已支援，且支援中文分詞插件
• ❌「COUNT(*) 慢不能用 InnoDB」→ 維護計數表、快取、或改用 SELECT COUNT(1) FROM tbl WHERE id > 0 走二級索引

生產環境建議配置

[mysqld]
default-storage-engine = InnoDB
innodb_buffer_pool_size = 物理記憶體 70-80%
innodb_log_file_size = 512M (或更大)
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_read_io_threads = 8
innodb_write_io_threads = 8

遷移指南：MyISAM → InnoDB

ALTER TABLE tbl_name ENGINE=InnoDB;
-- 大表建議：
-- 1. 建立新表結構 ENGINE=InnoDB
-- 2. INSERT INTO new SELECT * FROM old
-- 3. RENAME TABLE old TO old_bak, new TO old
-- 4. 驗證無誤後 DROP old_bak

結語

MySQL 5.7/8.0 預設皆為 InnoDB，且持續優化讀取效能。除非極特殊的唯讀分析場景，**InnoDB 是 99% 應用的最佳選擇**。理解其鎖、索引、日志機制，配合合理的主鍵設計與 SQL 優化，才能發揮最大效能。