MySQL MyISAM 轉 InnoDB 一鍵遷移腳本與生產環境注意事項

MyISAM 不支援交易、行鎖、外鍵，隨著業務增長遷移至 InnoDB 成為必經之路。本文提供經驗證的資訊庫查詢生成法，並詳述遷移前評估、執行策略與驗證清單。

一鍵生成遷移腳本（資訊庫法）

SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ENGINE=InnoDB;')
FROM information_schema.TABLES
WHERE ENGINE = 'MyISAM'
  AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
INTO OUTFILE '/tmp/alter_innodb.sql';

• 輸出檔案 /tmp/alter_innodb.sql 包含所有非系統庫 MyISAM 表的轉換語句。
• 執行：SOURCE /tmp/alter_innodb.sql; 或 mysql -u root -p < /tmp/alter_innodb.sql
• 完成後清理：RM /tmp/alter_innodb.sql

遷移前核心評估（必做）

線上遷移工具對比（大表必用）

標準遷移執行流程（中小表可直刷）

1. 備份： mysqldump --single-transaction --routines --triggers db > backup.sql
2. 離峰時段執行： 降低業務影響。
3. 執行轉換： SOURCE /tmp/alter_innodb.sql;
4. 驗證：

   SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES 
   WHERE TABLE_SCHEMA='your_db' AND ENGINE='MyISAM';

   結果應為空集。
5. 調整 InnoDB 參數： innodb_buffer_pool_size 設為實體記憶體 60-80%。
6. 重建索引統計： ANALYZE TABLE tbl_name;
7. 應用驗證： 核心業務流程回歸測試。

常見問題與對策

• 磁碟空間暴增： InnoDB 索引結構較大，預留 1.5-2 倍空間。
• 從庫複寫延遲： 大表 ALTER 產生大量 Binlog，考慮 SET SESSION sql_log_bin=0; 主庫執行，再手動同步從庫（需極謹慎）。
• 死鎖頻發： 檢查應用索引設計，確保更新走索引而非全表掃描。
• 外鍵建立失敗： 父表需先轉 InnoDB，且資料滿足引用完整性。

驗收清單

• [ ] 所有目標表 ENGINE=InnoDB
• [ ] 核心查詢 EXPLAIN 走索引（無 type=ALL）
• [ ] 寫入 QPS / 延遲達標
• [ ] 從庫同步正常（Seconds_Behind_Master=0）
• [ ] 備份還原演練通過

MyISAM → InnoDB 遷移是架構演進關鍵一步。**資訊庫生成腳本 + pt-online-schema-change/gh-ost 線上遷移 + 完整驗收**，讓遷移可控、可逆、零停機。