MySQL 儲存引擎選型指南：MyISAM vs InnoDB 核心差異與決策矩陣

核心差異速查表

決策矩陣：5 個關鍵問題

1. 需要事務嗎？ 是 → InnoDB
2. 需要外鍵完整性？ 是 → InnoDB
3. 需要全文搜尋？ 是且版本 < 5.6 → MyISAM（或外掛 Sphinx/ES）
4. 查詢模式？ 以讀為主、少更新、大量 COUNT(*) → MyISAM 可考慮
5. 資料量多大？ 大數據集（>10GB）、高並發寫入 → InnoDB（恢復快、行鎖、MVCC）

深度差異解析

1. COUNT(*) 行為差異

-- MyISAM：直接讀取表元資料中的行數，極快
SELECT COUNT(*) FROM myisam_tbl;  -- 0.001s

-- InnoDB：需掃描聚簇索引（或次要索引）計算可見行數
SELECT COUNT(*) FROM innodb_tbl;  -- 100萬行約 0.5-2s
-- 有 WHERE 條件時兩者皆需掃描，差異縮小

對策：InnoDB 維護單獨計數表、Redis 計數器、或近似值 SHOW TABLE STATUS（Rows 欄位）。

2. AUTO_INCREMENT 差異

• InnoDB：主鍵必須包含自增欄位（單欄索引或複合索引最左前綴），重啟後自增值重置為 MAX(id)+1（5.7+ 持久化自增值）
• MyISAM：允許自增欄位在複合索引非最左位置

3. DELETE FROM table 行為

• MyISAM：重建表（快），釋放空間
• InnoDB：逐行標記刪除（慢），空間不釋放（需 OPTIMIZE TABLE 或 TRUNCATE）

4. LOAD TABLE FROM MASTER

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

5. 鎖升級風險

-- InnoDB 无法确定扫描范围时会锁表
UPDATE tbl SET num=1 WHERE name LIKE '%aaa%';  -- 全表鎖風險

對策：避免前導模糊查詢、建立全文索引、或應用層過濾。

InnoDB 效能關鍵參數

[mysqld]
# 核心：刷盤策略，1=最安全（每提交刷盤），0=最高效（每秒刷盤），2=折中
innodb_flush_log_at_trx_commit = 2  # 生產建議 1，高寫入可考慮 2

# 緩衝池：建議 物理記憶體 60-80%
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 8  # 5.5+ 多實例減少爭用

# 日誌檔案大小：總大小 ≈ 緩衝池 25%，單檔 256M-1G
innodb_log_file_size = 512M
innodb_log_files_in_group = 2

# 並發控制
innodb_thread_concurrency = 0  # 0=無限制，5.5+ 建議 0

# 獨立表空間（必開）
innodb_file_per_table = 1

# 避免 autocommit=1 導致單條提交開銷
# 應用層：START TRANSACTION; 批次 INSERT/UPDATE; COMMIT;
# 或會話級：SET autocommit=0;

實測數據參考（特定硬體環境，僅供相對比較）

結論：關閉 autocommit 批次提交 是 InnoDB 批量寫入效能的關鍵，innodb_flush_log_at_trx_commit 次之。

選型建議總結

查看與修改引擎

-- 查看表引擎
SHOW TABLE STATUS LIKE 'tbl_name';
SHOW CREATE TABLE tbl_name;

-- 修改引擎（會鎖表重建，大表謹慎）
ALTER TABLE tbl_name ENGINE=InnoDB;

-- 設定預設引擎
[mysqld]
default-storage-engine=InnoDB

遷移清單（MyISAM → InnoDB）

1. 評估表大小、寫入 QPS、事務需求
2. 測試環境 ALTER TABLE ... ENGINE=InnoDB 耗時與空間
3. 調整 innodb_buffer_pool_size 容納熱點資料
4. 應用層檢查：LOCK TABLES、COUNT(*)、自增欄位依賴、全文索引替代
5. 分批遷移（小表優先）、驗證資料一致性、監控鎖等待
6. 完成後移除 myisam_recover、調整備份策略為 xtrabackup