MySQL 兩大核心儲存引擎深度對比：InnoDB vs MyISAM 選型指南

核心結論先行

MySQL 5.5+ 版本預設且強烈推薦使用 InnoDB。 MyISAM 僅適用於極少數唯讀、全文搜尋（5.6 前）、空間索引等特殊場景。理解兩者差異，是效能調優與架構設計的基石。

架構級差異總覽

深度剖析：為什麼 InnoDB 是預設首選？

1. 行級鎖 + MVCC = 高並發王者

-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 鎖住 id=1 這一行

-- Session B (並發執行)
SELECT * FROM accounts WHERE id = 2;  -- 完全不受阻塞，讀取 id=2
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 鎖住 id=2，互不干擾

MyISAM 表級鎖會導致 Session B 完全阻塞，並發效能呈指數級下降。

2. 叢集索引設計：主鍵查詢零回表

• 資料按主鍵順序物理儲存，主鍵查詢直接定位葉子節點
• 二級索引葉子節點儲存主鍵值，查詢需「回表」
• 設計鐵律： 主鍵應為單調遞增（自增 ID/雪花算法），避免頁分裂與碎片

3. WAL 機制保證持久性

innodb_flush_log_at_trx_commit = 1 時，事務提交強制 Redo Log 刷盤，崩潰零數據丟失。配合 Doublewrite Buffer 防止半寫頁。

MyISAM 僅存活場景（遺留系統維護）

• 唯讀/靜態資料： 報表快照、日誌歸檔、配置表
• 全文搜尋 (MySQL < 5.6)： 歷史系統未遷移時
• 極度空間受限嵌入式： 僅讀、無事務需求、空間極其敏感

遷移清單：MyISAM → InnoDB

ALTER TABLE tbl_name ENGINE=InnoDB;
-- 注意事項：
-- 1. 鎖表時間與資料量成正比，大表建議 pt-online-schema-change
-- 2. 主鍵必須存在（無主鍵 InnoDB 會隱式生成 6 字節 row_id）
-- 3. 全文索引需重建 (5.6+)
-- 4. 空間索引需重建 (5.7+)
-- 5. 記憶體配置調整：key_buffer_size ↓, innodb_buffer_pool_size ↑

其他引擎簡覽（特定場景神器）

總結：選型決策樹

1. 需要事務/外鍵/高並發寫入/崩潰安全？→ InnoDB（99% 場景）
2. 唯讀、全文搜尋 (舊版)、空間受限？→ MyISAM（<1% 場景）
3. 特殊場景（OLAP、日誌、快取、GIS）？→ 選擇專用引擎

記住：儲存引擎是表級屬性，同一資料庫可混用，但跨引擎事務不支援。新建表預設 ENGINE=InnoDB，除非有極強理由否則不改。