背景與動機

早期業務表多為 MyISAM，隨著並發增長頻繁出現**表級鎖阻塞**（長查詢阻塞寫入、寫入阻塞讀取），導致 502 超時。InnoDB **行級鎖、MVCC、事務、崩潰恢復** 完美解決此痛點。本文記錄生產環境 MyISAM → InnoDB 全量遷移標準化流程。

核心命令（單表）

ALTER TABLE tbl_name ENGINE=InnoDB;

• 原地重建表（Online DDL，5.6+），過程中允許並發 DML（需 ALGORITHM=INPLACE，默認）。
• 大表耗時久、產生臨時文件佔磁盤、Binlog 膨脹，需低峰期操作。

批量遷移標準化流程（推薦：導出結構→改引擎→導入數據）

步驟 1：導出結構（不含數據）

mysqldump -d -u root -p --databases db1 > db1_struct.sql
# -d / --no-data 僅導出 DDL

步驟 2：批量替換引擎關鍵字

sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' db1_struct.sql
# 兼容大小寫：sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/gi'
# 同時替換 TYPE=MyISAM (舊版本語法)
sed -i 's/TYPE=MyISAM/ENGINE=InnoDB/gi' db1_struct.sql

步驟 3：建立新庫並導入結構

mysql -u root -p -e "CREATE DATABASE db1_new CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"
mysql -u root -p db1_new < db1_struct.sql
# 驗證
mysql -u root -p -e "SHOW TABLE STATUS FROM db1_new WHERE Engine <> 'InnoDB';"

步驟 4：導出純數據

mysqldump -t -u root -p --databases db1 > db1_data.sql
# -t / --no-create-info 僅導出 INSERT

步驟 5：導入數據到新庫

mysql -u root -p db1_new < db1_data.sql

步驟 6：驗證與切換

# 1. 表行數對比
mysql -u root -p -e "SELECT TABLE_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA IN ('db1','db1_new');"

# 2. 關鍵業務抽樣核對
# 3. 低峰期重命名切換（原子操作）
RENAME TABLE db1 TO db1_old, db1_new TO db1;
# 或應用層修改數據庫連接配置平滑切換

MyISAM vs InnoDB 核心差異對比

維度	MyISAM	InnoDB
事務支持	❌	✅ ACID
鎖粒度	表級鎖	行級鎖 (含間隙鎖、Next-Key Lock)
全文索引	✅ 原生	5.6+ 支持 (ngram/MeCab)
外鍵約束	❌	✅
崩潰恢復	❌ 需 REPAIR TABLE	✅ Redo/Undo Log 自動恢復
MVCC 並發	❌	✅ 讀寫不阻塞
主鍵要求	可無	強制隱式/顯式主鍵
存儲格式	.MYD/.MYI/.frm	.ibd (file-per-table) + ibdata1
Count(*) 性能	O(1) 元數據	O(N) 掃描 (可用觸發器維護計數表)

遷移風險與對策

風險	影響	對策
大表 ALTER 鎖表時間長	阻塞寫入數分鐘-數小時	用「導出結構+導入數據」避免長鎖；或 pt-online-schema-change/gh-ost 在線遷移
主鍵缺失導致隱式 6 字節 ROWID	二級索引體積增大、性能下降	遷移前為無主鍵表添加 id BIGINT AUTO_INCREMENT PRIMARY KEY
全文索引失效	MyISAM FULLTEXT 不兼容 InnoDB	重建為 FULLTEXT INDEX ... WITH PARSER ngram 或遷移至 ES
自增值重置/跳躍	業務依賴自增 ID 連續性	遷移後 ALTER TABLE t AUTO_INCREMENT = 次大值+1
主從同步中斷	Binlog 記錄 ALTER 導致從庫重做	主庫 SET sql_log_bin=0; 會話級關閉 Binlog 再 ALTER（僅限非 GTID）

在線遷移工具對比（大表零停機）

• pt-online-schema-change (Percona Toolkit)：觸發器同步增量、分塊複製、原子切換，成熟穩定。
• gh-ost (GitHub)：無觸發器、基於 Binlog 異步同步、對主庫零負載、支持暫停/限流，推薦極大表（>100G）。
• MySQL 8.0 Instant DDL：部分操作（添加列、刪除列、修改默認值）元數據級秒級完成，但 ENGINE 變更仍需重建表。

總結

MyISAM → InnoDB 遷移核心：**導出結構改引擎 → 新庫建表 → 導入數據 → 驗證切換**，避免大表 ALTER 長鎖。大表必上 **gh-ost/pt-osc** 實現真正在線遷移。遷移後務必補齊主鍵、重建全文索引、校驗自增值、驗證主從一致性。