MySQL 存儲引擎遷移指南：從 MyISAM 到 InnoDB 的關鍵差異與遷移策略

MySQL 存儲引擎遷移指南：從 MyISAM 到 InnoDB 的關鍵差異與遷移策略

隨著 MySQL 5.5+ 將 InnoDB 設為默認引擎，MyISAM 逐漸退出主流舞台。本文梳理遷移前必須評估的 5 大風險點、4 大收益，並提供工程化遷移建議。

⚠️ 遷移前必須評估的 5 大風險點

1. 自增列位置限制：MyISAM 允許聯合主鍵中非第一列為 AUTO_INCREMENT (如 Discuz「搶樓」功能)；InnoDB 強制自增列必須為主鍵或唯一索引第一列。涉及此類業務需重構 (如改用 Redis 實現計數器)。
2. 全表 COUNT(*) 性能差異：MyISAM 維護行計數器，SELECT COUNT(*) 瞬間返回；InnoDB 需全表掃描 (MVCC 可見性檢查)，大表極慢。對策：業務層緩存計數、觸發器維護計數表、或接受近似值 SHOW TABLE STATUS。帶 WHERE 走索引時兩者差異微小。
3. 全文索引支持：MyISAM 原生支持；InnoDB 5.6+ 才支持且中文分詞效果一般。建議：全文搜索外接 Elasticsearch / Sphinx / Meilisearch，勿依賴數據庫內建。
4. 大批量導入中間態：一次性導入大量數據後續需加工，可先導入 MyISAM 表 (無事務開銷、索引構建快)，加工完成後 ALTER TABLE ... ENGINE=InnoDB 或 INSERT INTO innodb_tbl SELECT * FROM myisam_tbl。
5. LOAD TABLE FROM MASTER 不支持：InnoDB 不支持此舊式複製語法 (極少使用，可忽略)。

✅ 遷移後獲得的 4 大核心收益

1. 完整 ACID 事務：支持 Commit/Rollback、Savepoint、行級鎖，高並發 TPS 遠超 MyISAM 表鎖。
2. 崩潰自動恢復：實例異常重啟後，InnoDB 通過 Redo Log 自動前滾、Undo Log 回滾，秒級恢復；MyISAM 需 REPAIR TABLE 手動修復，耗時 4-5 倍且易損壞索引。
3. 數據+索引雙緩存：InnoDB Buffer Pool 同時緩存數據頁與索引頁；MyISAM 僅緩存索引，數據依賴 OS Cache，隨機讀取性能弱。
4. 外鍵約束：InnoDB 唯一支持外鍵 (雖生產環境常禁用，改由應用層維護完整性)。

🔬 兩大引擎架構本質對比

🛠️ 遷移操作清單

1. 全量審計：SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='db' AND ENGINE='MyISAM';
2. 風險評估：逐表核對上述 5 大風險點，制定重構方案。
3. 停機窗口遷移：

   ALTER TABLE tbl_name ENGINE=InnoDB;
   -- 或大表使用 pt-online-schema-change 零停機遷移
   

4. 參數調優：innodb_buffer_pool_size 設為物理內存 60-70%；innodb_flush_log_at_trx_commit=1；innodb_file_per_table=ON。
5. 驗證回歸：核對行數、索引基數、關鍵查詢 QPS、事務提交延遲。

遷移不是目的，釋放 InnoDB 事務與並發優勢才是核心價值。建議新建表默認 InnoDB，存量 MyISAM 表按業務優先級分批遷移。