引擎架構核心差異
| 維度 | MyISAM | InnoDB |
|---|
| 檔案結構 | 分離：.frm(定義) .MYD(資料) .MYI(索引) | 共用表空間 ibdata1 或獨立 .ibd (file_per_table=ON) |
| 鎖粒度 | 表鎖 | 行鎖 (意向鎖 + 記錄鎖 + 間隙鎖) |
| 事務 | 不支援 | ACID 完整支援 |
| 外鍵 | 不支援 | 支援 |
| 全文索引 | 5.6 前唯一支援 | 5.6+ 支援 |
| 崩潰恢復 | myisamchk 掃描修復，慢 | Redo/Undo Log 快速恢復 |
| COUNT(*) | O(1) 直接讀取統計 | 需掃描/估算 |
| 主鍵查詢 | 非叢集，需回表 | 叢集索引，極快 |
| 空間佔用 | 索引壓縮，較小 | 約 1.5-2 倍 |
建表指定引擎
CREATE TABLE t_myisam (id INT PRIMARY KEY, v INT) ENGINE=MyISAM;
CREATE TABLE t_innodb (id INT PRIMARY KEY, v INT) ENGINE=InnoDB;
-- 修改現有表引擎
ALTER TABLE t_myisam ENGINE=InnoDB;
-- 批量轉換腳本
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ENGINE=InnoDB;')
FROM information_schema.TABLES WHERE TABLE_SCHEMA='mydb' AND ENGINE='MyISAM';
效能實測（38 萬筆測試表，WinXP + P4 1.6G + 1G RAM）
| 操作 | MyISAM (s) | InnoDB (s) | 倍數差異 |
|---|
| COUNT(*) | 0.0008 | 3.016 | 3760x |
| PK 查詢 | 0.0057 | 0.157 | 27x |
| 非 PK 查詢 | 24.01 | 80.37 | 3.3x |
| PK 更新 | 0.0081 | 0.818 | 100x |
| 非 PK 更新 | 0.0041 | 0.026 | 6.3x |
| INSERT | 0.0042 | 0.369 | 88x |
加索引後：MyISAM 查詢提速 420,000x，InnoDB 提速 510,000x；但更新分別變慢 2x、30x。
空間佔用實測
| 表規模 | 引擎 | 資料 | 索引 | 總計 |
|---|
| 38 萬行 | MyISAM | 53.9 MB | 13.6 MB | 67.6 MB |
| 38 萬行 | InnoDB | 59.0 MB | 21.1 MB | 80.0 MB |
| 176 萬行 | MyISAM | 56.2 MB | 67.1 MB | 123.3 MB |
| 176 萬行 | InnoDB | 90.7 MB | 88.8 MB | 179.6 MB |
選型決策矩陣
| 場景特徵 | 推薦引擎 | 理由 |
|---|
| 核心交易、需事務/外鍵、高併發寫入 | InnoDB | ACID、行鎖、快速恢復 |
| 海量日誌/統計、讀多寫少、COUNT(*)/ORDER BY 高頻 | MyISAM | 讀快、COUNT O(1)、空間省、檔案易分發 |
| 全文搜尋（不想上 ES） | MyISAM / InnoDB 5.6+ | 內建 FULLTEXT |
| 臨時中間表、極高速讀寫、資料可丟失 | MEMORY (HEAP) | 純記憶體，重啟消失 |
InnoDB 關鍵調優參數
innodb_buffer_pool_size = 物理記憶體 60-80%
innodb_flush_log_at_trx_commit = 1 (安全) / 2 (高效折中)
innodb_file_per_table = ON
innodb_log_file_size = 512M - 1G
innodb_log_buffer_size = 64M
innodb_thread_concurrency = 0 (自動) 或 CPU 核心 * 2
MyISAM 關鍵調優參數
key_buffer_size = 物理記憶體 20-30%
myisam_sort_buffer_size = 256M
bulk_insert_buffer_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
線上引擎轉換最佳實踐
- 大表必須用
pt-online-schema-change 或 gh-ost，避免長時間鎖表。
- 轉換前備份，驗證從庫同步正常。
- 轉換後觀察
Innodb_buffer_pool_reads、Key_read_requests 等指標微調參數。
總結
沒有最好的引擎，只有最適合業務的引擎。現代架構常見混合部署：核心交易 InnoDB、海量日誌 MyISAM、熱點緩存 Redis、全文搜尋 Elasticsearch。選型前先問五個問題（外鍵、事務、全文、查詢模式、資料量），再查決策矩陣。
