您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL InnoDB 與 MyISAM 深度比較：架構、效能、功能差異與選型指南

編輯：MySQL 綜合教學

前言

InnoDB 與 MyISAM 是 MySQL 歷史上最核心的兩大儲存引擎。雖然 MySQL 5.5+ 預設為 InnoDB，但理解兩者架構差異、效能特徵、功能邊界，對存量系統遷移、特殊場景選型仍至關重要。本文深度比較並提供決策指南。

一、架構核心差異

維度	InnoDB	MyISAM
儲存結構	表空間 (ibdata1 + .ibd) / 檔案分表	三檔案 (.frm, .MYD, .MYI)
索引結構	聚簇索引 (主鍵即資料) + 二級索引存主鍵	非聚簇索引 (索引存資料指標)
鎖粒度	行級鎖 (Record Lock, Gap Lock, Next-Key Lock)	表級鎖 (共享讀鎖、獨佔寫鎖)
交易支援	完整 ACID、MVCC、Redo/Undo Log	不支援
外鍵	支援	不支援
崩潰恢復	Redo Log 雙寫緩衝、Checksum、自動恢復	易損壞、需 myisamchk 修復
MVCC	支援 (非阻塞讀取)	不支援
全文索引	5.6+ 支援	早期支援、成熟
COUNT(*)	全表掃描 (MVCC 可見性)	O(1) 直接讀取儲存行數
壓縮	頁面壓縮、表壓縮 (Transparent)	myisampack 唯讀壓縮
熱備份	xtrabackup/ibbackup 線上熱備	檔案複製 (需鎖表/停服務)

二、效能特徵對比

讀取效能

單行主鍵查找：InnoDB 聚簇索引極快 (1 次 B+ Tree)，MyISAM 需 2 次 (索引→資料指標→資料)
範圍掃描：InnoDB 葉節點雙向鏈表順序讀取高效
全表掃描：MyISAM 略快 (無 MVCC 開銷、資料緊湊)
覆蓋索引：InnoDB 二級索引葉節點含完整索引欄位，覆蓋索引效果更好

寫入效能

INSERT：InnoDB 需維護 Redo/Undo Log、雙寫緩衝、主鍵順序影響大；MyISAM 追加寫入快 (但表鎖併發差)
UPDATE/DELETE：InnoDB 行鎖併發高、MVCC 讀不阻塞寫；MyISAM 表鎖序列化、併發極差
大批次匯入：MyISAM 關閉索引 ALTER TABLE ... DISABLE KEYS 後匯入再啟用，極快；InnoDB 建議按主鍵順序匯入

併發效能

高併發讀寫混合：InnoDB 完勝 (行鎖 + MVCC)
純讀/多讀少寫：MyISAM 可接受
唯讀報表/資料倉儲：MyISAM 全表掃描略快、COUNT(*) 秒級

三、關鍵功能差異細節

1. AUTO_INCREMENT 差異

InnoDB：計數器只存在記憶體，重啟後重新計算 MAX(id)+1 (5.7+ 持久化到 redo log)
MyISAM：儲存在 .MYI，重啟不變
InnoDB 組合索引中 AUTO_INCREMENT 欄位**必須為索引首欄**

2. 索引長度限制

引擎	單欄索引	組合索引	行大小限制
InnoDB (Redundant/Compact)	767 bytes	767 bytes	~8KB (半頁)
InnoDB (Dynamic/Compressed, 5.7.7+)	3072 bytes	3072 bytes	~8KB
MyISAM	1000 bytes	1000 bytes	65,535 bytes

3. 外鍵約束

InnoDB：ON DELETE CASCADE/SET NULL/RESTRICT/NO ACTION
MyISAM：語法接受但**忽略不執行**

4. 表鎖升級

InnoDB：無鎖升級，永遠行級 (除非無索引 WHERE 導致全表掃描鎖)
MyISAM：永遠表級

四、遷移與轉換實戰

MyISAM → InnoDB

-- 線上轉換 (大表用 pt-online-schema-change)
ALTER TABLE t ENGINE = InnoDB;

-- 關鍵調整
-- 1. 主鍵：確保有單調遞增主鍵 (避免 UUID)
-- 2. 索引：檢查組合索引順序 (最左前綴)
-- 3. 參數：innodb_buffer_pool_size = 70-80% 記憶體
-- 4. 檢查：外鍵、觸發程序、分區表相容性

InnoDB → MyISAM (極少見)

ALTER TABLE t ENGINE = MyISAM;
-- 風險：失去交易、外鍵、行鎖、崩潰恢復
-- 僅適用：唯讀歸檔表、全文搜尋 (5.6 前)、資料倉儲

五、選型決策樹 (2024 版)

需要交易 (ACID)？ → InnoDB (無腦選)
需要外鍵/行級鎖/MVCC？ → InnoDB
高併發讀寫混合？ → InnoDB
資料安全性要求高 (崩潰恢復)？ → InnoDB
純唯讀/多讀少寫/資料倉儲？ → 考慮 MyISAM (全表掃描快、COUNT(*) 秒級、myisampack 壓縮)
全文搜尋 (MySQL 5.6 前)？ → MyISAM
需跨平台直接複製檔案部署？ → MyISAM (三檔案獨立)
嵌入式/唯讀媒體 (CD/DVD)？ → MyISAM

預設建議：**99% 場景選 InnoDB**。MyISAM 僅作為特定唯讀/歸檔場景的優化選項。

六、常見誤區澄清

誤區	真相
MyISAM 總是比 InnoDB 快	僅在「純讀、全表掃描、COUNT(*)」場景；併發寫入 InnoDB 完勝
InnoDB 不支援全文索引	5.6+ 完整支援，且支援中文 ngram parser
轉 InnoDB 只需 ALTER TABLE	需配合參數調優、主鍵設計、索引重構、應用驗證
MyISAM 表損壞無法恢復	myisamchk -r 可修復大多數，但需停服務、風險高
InnoDB 表空間不可縮減	5.7+ 支援 `innodb_file_per_table` + `OPTIMIZE TABLE` / `ALTER TABLE ... ENGINE=InnoDB` 回收空間

七、混合部署策略

-- 核心交易系統
CREATE TABLE orders (...) ENGINE = InnoDB;
CREATE TABLE users (...) ENGINE = InnoDB;

-- 唯讀商品目錄 (全文搜尋)
CREATE TABLE products (...) ENGINE = MyISAM;

-- 審計日誌歸檔
CREATE TABLE audit_log_2023 (...) ENGINE = Archive;

-- Session 快取
CREATE TABLE sessions (...) ENGINE = Memory;