MySQL MyISAM 轉 InnoDB 完整遷移指南：關鍵差異、注意事項與效能權衡

將既有 MyISAM 表轉為 InnoDB 可獲得事務支援、行級鎖、崩潰恢復與更高並發效能，但需注意結構與行為差異。本文系統梳理遷移前必檢項目、轉換語法、效能調優與回滾方案。

一、核心架構差異速查

二、遷移前必檢 5 大阻礙項

1. 自增欄位非主鍵首欄：MyISAM 允許複合主鍵中自增欄非第一欄 (如 PRIMARY KEY (type, id))，InnoDB 強制自增欄必為主鍵第一欄。
   → 若有「搶樓」類業務依賴此特性，需改用 Redis 計數器或單獨序列表。
2. 頻繁 COUNT(*) 無條件掃描：InnoDB 全表掃描較慢。
   → 改用觸發器維護計數表、或 SELECT COUNT(*) FROM t WHERE indexed_col > 0 走索引。
3. 依賴 MyISAM 全文索引：5.6 前 InnoDB 不支援。
   → 遷移至 Sphinx/Elasticsearch/Meilisearch 等專用搜尋引擎。
4. 大量批次匯入後續需加工：可先匯入 MyISAM 臨時表加工完成，再 INSERT SELECT 導入 InnoDB 正式表，利用 MyISAM 批次寫入快的特性。
5. 使用 LOAD TABLE FROM MASTER：InnoDB 不支援。
   → 改用 mysqldump --single-transaction 或 GTID 複製。

三、轉換操作步驟

-- 1. 備份
mysqldump -u root -p --single-transaction --routines --triggers db_name tbl_name > tbl_backup.sql

-- 2. 線上轉換 (MySQL 5.6+ 支援 Online DDL，僅短暫元數據鎖)
ALTER TABLE tbl_name ENGINE=InnoDB;

-- 3. 驗證
SHOW TABLE STATUS LIKE 'tbl_name'\G
-- Engine: InnoDB

-- 4. 重建索引/統計資訊 (可選)
ANALYZE TABLE tbl_name;
OPTIMIZE TABLE tbl_name;  -- 實際上會重建表

四、轉換後效能調優關鍵參數

[mysqld]
# 核心：緩衝池佔實體記憶體 60-80%
innodb_buffer_pool_size = 4G
# 日誌檔大小，減少 checkpoint 壓力
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
# 刷盤策略：1=每提交刷盤(最安全), 2=每秒刷盤(高效能)
innodb_flush_log_at_trx_commit = 2
# 並發控制
innodb_thread_concurrency = 0  # 0=不限制
# 檔案格式
innodb_file_format = Barracuda
innodb_file_per_table = 1

五、回滾方案

-- 若轉換後出現嚴重效能退化，可從備份還原
mysql -u root -p db_name < tbl_backup.sql
-- 或直接 ALTER TABLE tbl_name ENGINE=MyISAM; (需鎖表，慎用)

六、遷移清單

• [ ] 完整備份
• [ ] 檢查上述 5 大阻礙項並修正應用邏輯
• [ ] 非尖峰時段執行 ALTER TABLE ... ENGINE=InnoDB
• [ ] 觀察 Innodb_row_lock_waits、Innodb_buffer_pool_reads 等狀態
• [ ] 調整 innodb_buffer_pool_size 等核心參數並重啟
• [ ] 執行 ANALYZE TABLE 更新統計資訊
• [ ] 驗證應用功能與效能基準