MySQL InnoDB 與 MyISAM 引擎差異深度解析

核心差異對比表

關鍵細節差異

1. COUNT(*) 效能

-- MyISAM: O(1) 直接讀取儲存的行數
SELECT COUNT(*) FROM myisam_tbl;

-- InnoDB: O(N) 全表掃描 (MVCC 可見性檢查)
SELECT COUNT(*) FROM innodb_tbl;
-- WHERE 條件下兩者皆需掃描

2. AUTO_INCREMENT 索引要求

• InnoDB：自增欄位**必須**為單獨索引或複合索引**最左列**。
• MyISAM：允許複合索引非最左列。

3. DELETE 全表行為

• MyISAM：重建表，極快，自增歸零。
• InnoDB：逐行刪除，產生 Undo Log，較慢。大量刪除建議 TRUNCATE 或分批 DELETE ... LIMIT。

4. LOAD TABLE FROM MASTER

僅支援 MyISAM。InnoDB 需先轉 MyISAM 匯入再轉回，但外鍵等特性會遺失。

5. 行鎖升級為表鎖

InnoDB 無法確定掃描範圍時 (如 WHERE name LIKE '%aaa%') 會鎖全表。

MyISAM 歷史演進 (ISAM → MyISAM)

• 二進制可移植性
• NULL 欄位可建索引
• 變長行碎片更少
• 支援大檔案 (>4GB)
• 索引壓縮更好
• 鍵值分佈統計更準
• auto_increment 處理更快

選型決策樹

1. 需要交易/外鍵/行鎖/崩潰安全 → InnoDB
2. 純唯讀/全文搜尋/日誌/資料倉庫 → MyISAM (或 InnoDB 5.6+ 全文)
3. 臨時表/極速查找/資料可丟失 → Memory
4. 海量歸檔/唯追加 → Archive

避坑指南

• 不要混用引擎做 JOIN，可能導致鎖爭用與優化器誤判。
• InnoDB 表慎用 COUNT(*)，改用計數表或 information_schema.TABLES.TABLE_ROWS 估算。
• 大批量寫入前 SET autocommit=0 + 批次 Commit，或 LOAD DATA INFILE。