萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 表引擎轉換 MyISAM ⇄ InnoDB 三大方案深度評測:大表推薦「建新表+分批遷移」

MySQL 表引擎轉換是 DBA 常態操作。本文實測三大方案:ALTER TABLE 直改、mysqldump 導出導入、「建新表 + INSERT ... SELECT」分批遷移,並針對 1600 萬筆實測數據對比耗時、安全性、鎖表影響,給出大小表不同場景的最佳選型建議。

三大方案對比

方案操作1600萬筆實測耗時鎖表影響安全性適用場景
1. ALTER TABLE 直改ALTER TABLE t ENGINE=InnoDB2 分 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 核心差異回顧

避坑指南

  • 外鍵:轉 InnoDB 前確認無孤兒資料,否則建立外鍵失敗
  • AUTO_INCREMENT:RENAME TABLE 保留自增值,TRUNCATE 會重置
  • 觸發器/視圖:引擎轉換不影響,但需確認引擎特有功能(如全文索引語法差異)
  • 主從複制:主庫轉換會同步到從庫,大表操作可能導致從庫延遲,建議從庫先轉或停止同步再轉
大表引擎轉換,核心原則是「業務不停、資料不丟、可驗證、可回滾」。方案三雖步驟多,但將風險控制在分鐘級切換窗口,是生產環境的唯一正解。
copyright © 萬盛學電腦網 all rights reserved
特性MyISAMInnoDB
事務不支援支援 (ACID)
鎖粒度表鎖行鎖
外鍵不支援支援
崩潰恢復需修復自動恢復 (Redo Log)
全文索引5.6+ 支援5.6+ 支援
空間佔用較小較大 (約 1.5~2x)
純讀/寫效能讀快、寫快(無事務)寫入稍慢、讀取穩定