MySQL 修改表存儲引擎三種方案對比：ALTER、導出導入、創建插入

方案對比速覽

方案一：直接 ALTER TABLE

ALTER TABLE my_table ENGINE=InnoDB;

• MySQL 5.6+ 支援 ALGORITHM=INPLACE、LOCK=NONE 線上 DDL，但引擎轉換通常仍需重建表
• 大表執行時間不可控，易導致主從延遲

方案二：mysqldump 導出導入

# 導出結構與數據
mysqldump -u root -p db_name my_table > table.sql
# 編輯 SQL 修改 ENGINE=InnoDB
# 導入
mysql -u root -p db_name < table.sql

風險：單點故障、無事務保護、導入失敗難回滾、需長時間鎖表保證一致性。

方案三：創建新表 + 事務分批插入（推薦）

步驟 1：創建目標表結構

CREATE TABLE my_tmp_table LIKE my_table;
ALTER TABLE my_tmp_table ENGINE=InnoDB;

步驟 2：分批事務遷移數據

-- 假設主鍵 id 連續，每批 10000 行
START TRANSACTION;
INSERT INTO my_tmp_table SELECT * FROM my_table WHERE id BETWEEN 1 AND 10000;
COMMIT;

START TRANSACTION;
INSERT INTO my_tmp_table SELECT * FROM my_table WHERE id BETWEEN 10001 AND 20000;
COMMIT;
-- ... 循環至結束

優勢：

• 每批提交釋放鎖，不阻塞業務讀寫
• 可驗證行數 SELECT COUNT(*) FROM my_tmp_table 對比
• 失敗僅影響當前批次，易重試
• 可結合 pt-online-schema-change 或 gh-ost 實現真正無鎖切換

步驟 3：原子切換

RENAME TABLE my_table TO my_table_old, my_tmp_table TO my_table;

RENAME TABLE 是原子操作，瞬間完成切換，舊表保留備份。

生產級工具推薦

• pt-online-schema-change (Percona Toolkit)：觸發器同步增量，支援任意 DDL
• gh-ost (GitHub)：無觸發器，基於 binlog 異步同步，更安全
• MySQL 8.0 Instant DDL：部分操作（如加列、修改默認值）真正秒級無鎖

總結

大表引擎遷移首選方案三 + 專業工具，小表可直接 ALTER。切記：變更前必備份，驗證後再釋放舊表空間。