MySQL 資料表儲存引擎轉換三大方法詳解：ALTER TABLE、匯出匯入、建表複制

MySQL 表引擎轉換三大實戰方法：優缺點對比與操作指南

業務增長常需將表從 MyISAM 轉為 InnoDB 以支援交易/行鎖，或反向轉換利用 MyISAM 全文索引。本文詳細說明三種轉換方法的適用場景、操作步驟、風險與優化技巧。

方法一：ALTER TABLE (最簡單、鎖表久、大表慎用)

ALTER TABLE my_table ENGINE = InnoDB;

• 優點：單一指令、語法通用、無需額外空間
• 缺點：逐行複製 (Row-by-Row)、全程鎖表 (READ LOCK)、大表耗時極長、I/O 飆升
• 風險：轉換期間寫入阻塞、外鍵/全文索引等引擎專屬特性**遺失**
• 適用：小表 (<100萬行)、維護窗口、測試環境

方法二：mysqldump 匯出匯入 (可控、需雙倍空間、可修改結構)

# 1. 匯出結構與資料
mysqldump -u root -p --single-transaction --routines --triggers db_name table_name > table_dump.sql

# 2. 編輯 SQL 檔：修改 ENGINE=InnoDB、調整索引、更名表名
sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' table_dump.sql
# 重要：更改表名避免衝突，如 CREATE TABLE `table_name_new` ...

# 3. 匯入新表
mysql -u root -p db_name < table_dump.sql

# 4. 驗證無誤後重命名
RENAME TABLE table_name TO table_name_old, table_name_new TO table_name;

• 優點：可離線操作、可順便優化結構、不影響線上表
• 缺點：需雙倍磁碟空間、匯入慢、DROP TABLE 預設包含在 dump 中需小心
• 適用：中型表、需結構調整、有維護窗口

方法三：CREATE TABLE ... LIKE + INSERT SELECT (平衡速度與安全、推薦大表)

-- 1. 複製結構 (含索引、約束)
CREATE TABLE new_table LIKE old_table;

-- 2. 修改新表引擎
ALTER TABLE new_table ENGINE = InnoDB;

-- 3. 分批複製資料 (避免巨大交易、Undo Log 膨脹)
-- 假設 id 為主鍵遞增
SET @batch_size = 10000;
SET @max_id = (SELECT MAX(id) FROM old_table);
SET @start = 1;

WHILE @start <= @max_id DO
    START TRANSACTION;
    INSERT INTO new_table SELECT * FROM old_table
    WHERE id >= @start AND id < @start + @batch_size;
    COMMIT;
    SET @start = @start + @batch_size;
    -- 可選：SLEEP(0.1) 降低主從延遲壓力
END WHILE;

-- 4. 驗證行數一致
SELECT COUNT(*) FROM old_table;
SELECT COUNT(*) FROM new_table;

-- 5. 切換 (需短暫鎖表)
LOCK TABLES old_table WRITE, new_table WRITE;
-- 處理增量 (若有)
INSERT INTO new_table SELECT * FROM old_table WHERE id > @max_id;
RENAME TABLE old_table TO old_table_backup, new_table TO old_table;
UNLOCK TABLES;

• 優點：線上可執行、分批提交不阻塞久、可驗證後再切換
• 缺點：需雙倍空間、應用層需處理切換瞬間寫入 (雙寫或暫停)
• 適用：大表 (千萬行以上)、生產環境零停機需求

三方法對比總結

關鍵提醒

1. 轉換前**完整備份**
2. InnoDB 表**必須有主鍵** (隱含主鍵效能差)
3. 轉換後執行 ANALYZE TABLE 更新統計資訊
4. 主從環境建議**從庫先轉、驗證無誤再轉主庫**
5. 使用 pt-online-schema-change (Percona Toolkit) 可實現真正線上無鎖轉換