MySQL 修改表存儲引擎的三種方案：ALTER TABLE、導出導入、建表複製

MySQL 修改表存儲引擎的三種方案：ALTER TABLE、導出導入、建表複製

MySQL 支援多種存儲引擎（InnoDB、MyISAM、Memory 等），業務演進中常需變更引擎。本文對比三種主流方案優劣，助你按場景選型。

方案一：ALTER TABLE —— 最簡單，但鎖表久

ALTER TABLE mytable ENGINE = InnoDB;

適用：小表、維護窗口、非核心業務。

方案二：mysqldump 導出導入 —— 可控，但需雙倍空間

# 1. 導出結構+數據
mysqldump -u user -p db_name table_name > table.sql

# 2. 編輯 table.sql：修改 ENGINE=InnoDB，並改表名（如 mytable_new）
vi table.sql

# 3. 導入新表
mysql -u user -p db_name < table.sql

# 4. 驗證無誤後：RENAME TABLE mytable TO mytable_old, mytable_new TO mytable;

適用：需調整表結構、字符集、或對鎖表極度敏感但可接受停機窗口的場景。

方案三：建新表 + INSERT...SELECT —— 平衡效率與安全（推薦）

-- 1. 結構克隆
CREATE TABLE innodb_table LIKE myisam_table;

-- 2. 修改引擎（此時表空，極快）
ALTER TABLE innodb_table ENGINE = InnoDB;

-- 3. 分批複製數據（避免長事務、減輕鎖壓力）
INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN 1 AND 10000;
-- 循環分批，最後 RENAME 切換

進階：結合 pt-online-schema-change（Percona Toolkit）可實現真正的 Online DDL，無鎖遷移。

方案選型決策樹

1. 表 < 100 萬行、可停機窗口 → ALTER TABLE 最省事。
2. 需同步調整字符集、分區、列屬性 → mysqldump 導出導入 可全控制。
3. 核心大表、要求零停機、可接受分批複製 → 建新表 + INSERT...SELECT (+ pt-osc) 首選。

避坑指南

• 引擎轉換會丟失引擎專屬特性：外鍵、全文索引（MyISAM）、空間索引等，遷移前務必清單核對。
• ALTER TABLE ... ENGINE=... 在 5.6+ 支援 ALGORITHM=INPLACE 部分場景，但跨引擎仍需 COPY，鎖表不可避免。
• 生產環境必須先在測試庫驗證 數據完整性、索引可用性、應用兼容性。

總結

沒有銀彈，只有權衡。理解三種方案的鎖行為、空間佔用、特性保留差異，結合業務 SLA 選型，才是專業 DBA 的基本功。