MySQL 表引擎轉換是 DBA 常態操作。本文實測三大方案:ALTER TABLE 直改、mysqldump 導出導入、「建新表 + INSERT ... SELECT」分批遷移,並針對 1600 萬筆實測數據對比耗時、安全性、鎖表影響,給出大小表不同場景的最佳選型建議。
三大方案對比
| 方案 | 操作 | 1600萬筆實測耗時 | 鎖表影響 | 安全性 | 適用場景 |
| 1. ALTER TABLE 直改 | ALTER TABLE t ENGINE=InnoDB | 2 分 40 秒 | 全程鎖表,讀寫阻塞 | 中(過程不可中斷) | 小表 < 500M、維護窗口充足 |
| 2. mysqldump 導出導入 | 導出 SQL → 改引擎 → 導入 | 視導入速度 | 導入時鎖表 | 低(單點故障、易編碼錯) | 極不推薦生產環境 |
| 3. 建新表 + 分批遷移 ⭐ | 建同結構表 → 改引擎 → 分批 INSERT → 切換 | 總計 ~2 分 30 秒(含切換) | 僅切換瞬間短暫鎖表 | 高(可驗證、可回滾) | 大表、生產環境首選 |
方案三 詳細步驟(生產級 SOP)
-- 1. 建立同結構新表
CREATE TABLE my_tmp_table LIKE my_table;
ALTER TABLE my_tmp_table ENGINE=InnoDB;
-- 2. 分批遷移資料(建議加事務、限制主鍵範圍)
-- 例:每批 10 萬筆,按主鍵分段
INSERT INTO my_tmp_table SELECT * FROM my_table WHERE id BETWEEN 1 AND 100000;
-- 重複執行至全量遷移完成
-- 3. 驗證資料一致性
SELECT COUNT(*) FROM my_table;
SELECT COUNT(*) FROM my_tmp_table;
-- 抽樣比對關鍵欄位
-- 4. 切換表名(需短暫鎖表,秒級)
RENAME TABLE my_table TO my_table_old, my_tmp_table TO my_table;
-- 5. 觀察無誤後刪除舊表
DROP TABLE my_table_old;
大表分批遷移關鍵參數調優
-- 臨時調大緩衝區,加速排序與寫入(遷移完成後記得改回)
SET GLOBAL sort_buffer_size = 64*1024*1024;
SET GLOBAL tmp_table_size = 64*1024*1024;
SET GLOBAL read_buffer_size = 32*1024*1024;
SET GLOBAL read_rnd_buffer_size = 32*1024*1024;
SET GLOBAL innodb_buffer_pool_size = 80% 物理記憶體; -- InnoDB 專用
批次遷移腳本範例(Shell + SQL)
#!/bin/bash
# migrate.sh
TABLE=big_table
BATCH=50000
MAX_ID=$(mysql -N -e "SELECT MAX(id) FROM $TABLE")
for ((start=1; start<=MAX_ID; start+=BATCH)); do
end=$((start + BATCH - 1))
mysql -e "INSERT INTO ${TABLE}_new SELECT * FROM $TABLE WHERE id BETWEEN $start AND $end"
echo "Migrated $start - $end"
done
批量轉換多張表生成腳本
-- 生成 ALTER 語句
SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;')
FROM information_schema.tables
WHERE table_schema = 'my_db' AND engine = 'MyISAM';
-- 生成 CHECK TABLE 語句
SELECT CONCAT('CHECK TABLE `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'my_db';
MyISAM vs InnoDB 核心差異回顧
| 特性 | MyISAM | InnoDB |
| 事務 | 不支援 | 支援 (ACID) |
| 鎖粒度 | 表鎖 | 行鎖 |
| 外鍵 | 不支援 | 支援 |
| 崩潰恢復 | 需修復 | 自動恢復 (Redo Log) |
| 全文索引 | 5.6+ 支援 | 5.6+ 支援 |
| 空間佔用 | 較小 | 較大 (約 1.5~2x) |
| 純讀/寫效能 | 讀快、寫快(無事務) | 寫入稍慢、讀取穩定 |
避坑指南
- 外鍵:轉 InnoDB 前確認無孤兒資料,否則建立外鍵失敗
AUTO_INCREMENT:RENAME TABLE 保留自增值,TRUNCATE 會重置
- 觸發器/視圖:引擎轉換不影響,但需確認引擎特有功能(如全文索引語法差異)
- 主從複制:主庫轉換會同步到從庫,大表操作可能導致從庫延遲,建議從庫先轉或停止同步再轉
大表引擎轉換,核心原則是「業務不停、資料不丟、可驗證、可回滾」。方案三雖步驟多,但將風險控制在分鐘級切換窗口,是生產環境的唯一正解。
copyright ©
萬盛學電腦網 all rights reserved