MySQL 儲存引擎深度對比：MyISAM vs InnoDB 在插入、查詢、事務、鎖粒度、崩潰恢復五大維度實測分析

選擇儲存引擎是 MySQL 架構設計的首要決策。MyISAM (非事務、表鎖、全文索引) 與 InnoDB (事務、行鎖、MVCC、崩潰安全) 代表兩種截然不同的設計哲學。本文基於實測數據，從插入效能、查詢特性、事務支援、鎖機制、崩潰恢復五大維度深度對比，並給出生產環境選型決策樹。

測試環境與表結構

-- 硬體: Windows, 單機
-- MySQL 5.0/5.1 預設配置
-- innodb_buffer_pool_size=159M, innodb_flush_log_at_trx_commit=1

CREATE TABLE myisam_tbl (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  content TEXT
) ENGINE=MyISAM DEFAULT CHARSET=gbk;

CREATE TABLE innodb_tbl (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  content TEXT
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

-- 測試資料: name='heiyeluren', content=長文本 (~1KB)

插入效能實測對比 (行/秒)

關鍵發現

1. InnoDB 預設 (flush=1) 遠超 MyISAM：10 萬行 2092 vs 29 行/秒，差距 72 倍。原因：MyISAM 每行獨立 fsync/鎖表，InnoDB 批次寫入 Redo Log + 群組提交
2. flush=0 時兩者持平：犧牲持久性換取速度，MyISAM 原本即非持久
3. 大緩衝池 + 批次提交 = InnoDB 最優：100 萬行 607 行/秒，生產環境推薦模式
4. MyISAM 大批次插入極慢：100 萬行未測完成，表鎖爭用嚴重

五大維度深度對比

生產環境選型決策樹

1. 是否需要事務/外鍵/崩潰安全？ → 是 → InnoDB (95%+ 核心業務)
2. 純唯讀/歸檔/日誌/報表表，極高插入/查詢比，無事務需求？ → 考慮 MyISAM 或 InnoDB COMPRESSED
3. 全文搜尋 (5.6 前)？ → MyISAM (或外接 Elasticsearch/Solr，現代架構首選)
4. 極簡計數器表 (高頻 COUNT(*))？ → MyISAM 或 Redis/應用層計數
5. 內存表/臨時高速快取？ → MEMORY (重啟丟失) / InnoDB + 大緩衝池

現代最佳實踐：InnoDB 一統江湖

MySQL 5.5+ 起 InnoDB 為預設引擎，5.6+ 完善全文索引，5.7+ 原生 GIS，8.0+ 瞬間 DDL、資源組、直方圖統計。除非極特殊場景，生產環境統一使用 InnoDB，以：

• innodb_buffer_pool_size = 專用機 70-80% 內存
• innodb_flush_log_at_trx_commit = 1 (核心業務) / 2 (可容忍 1 秒丟失)
• innodb_file_per_table = ON
• ROW_FORMAT=DYNAMIC (支援長字段溢出頁、壓縮)

遷移指南：MyISAM → InnoDB

-- 1. 評估表大小、索引、外鍵依賴
-- 2. 低峰期執行 (會重建表，鎖表時間 = 表大小/寫入速度)
ALTER TABLE myisam_tbl ENGINE=InnoDB;

-- 3. 或 mysqldump 導出導入 (可控制並發、驗證)
mysqldump --single-transaction db myisam_tbl | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' | mysql db

-- 4. 驗證：資料行數、索引定義、應用功能測試
-- 5. 監控：InnoDB 緩衝池命中率、鎖等待、磁碟 I/O

儲存引擎選擇是架構級決策。InnoDB 以事務、行鎖、MVCC、崩潰恢復四大核心優勢，已成為現代 MySQL 生產環境的**事實標準**。理解兩者差異，不為選擇 MyISAM，而是為了知道在極少數邊緣場景如何妥善處理遷移與共存。