MySQL 存儲引擎變更指南：InnoDB 與 MyISAM 互轉完整操作與風險控制

前言

MySQL 支援多種存儲引擎，InnoDB（事務、行鎖、外鍵、崩潰恢復）與 MyISAM（表鎖、全文索引、壓縮表、讀多寫少）是最常見的兩種。業務演進中常需互轉，本文詳細講解變更語法、數據遷移原理、鎖影響、風險點與最佳實踐。

一、核心變更語法

1. ALTER TABLE ... ENGINE= (標準方式)

-- 單表轉換
ALTER TABLE `db_name`.`table_name` ENGINE = InnoDB;
ALTER TABLE `db_name`.`table_name` ENGINE = MyISAM;

-- 指定表空間 (InnoDB 獨立表空間)
ALTER TABLE `db_name`.`table_name` ENGINE = InnoDB, TABLESPACE = innodb_file_per_table;

• 操作類型：ALGORITHM=COPY（重建表，5.6 前唯一方式）或 ALGORITHM=INPLACE（原地重建，5.6+ InnoDB 支援）
• 鎖模式：LOCK=EXCLUSIVE（預設，全程排他鎖）或 LOCK=SHARED（允許讀，InnoDB 5.6+）
• 在線 DDL：5.6+ InnoDB 轉 InnoDB 支援 ALGORITHM=INPLACE, LOCK=NONE，但跨引擎轉換必須 COPY

2. CREATE TABLE ... SELECT (新建表遷移)

-- 創建新表結構指定引擎，導入數據
CREATE TABLE `new_table` ENGINE = InnoDB
SELECT * FROM `old_table`;

-- 切換
RENAME TABLE `old_table` TO `old_table_bak`, `new_table` TO `old_table`;

• 優點：原表零鎖定、可驗證新表、可並行導入
• 缺點：需雙倍磁盤空間、需手動處理索引/外鍵/觸發器/權限

3. 批量轉換腳本

#!/bin/bash
# convert_engine.sh db_name target_engine
DB=$1
TARGET_ENGINE=${2:-InnoDB}

mysql -N -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='$DB' AND ENGINE != '$TARGET_ENGINE';" | \
while read TABLE; do
  echo "Converting $DB.$TABLE to $TARGET_ENGINE..."
  mysql -e "ALTER TABLE \`$DB\`.\`$TABLE\` ENGINE = $TARGET_ENGINE;"
done

二、InnoDB ↔ MyISAM 關鍵差異對照

三、轉換風險與避坑指南

⚠️ 風險 1：數據類型不兼容

• MyISAM 支援 BLOB/TEXT 默認值，InnoDB 不支援（5.7+ 嚴格模式報錯）
• 轉換前檢查：SHOW CREATE TABLE 確認無 BLOB/TEXT DEFAULT

⚠️ 風險 2：外鍵約束丟失

-- 轉 MyISAM 前必須刪除外鍵
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` DROP FOREIGN KEY `', CONSTRAINT_NAME, '`;')
FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_SCHEMA = 'db_name';

⚠️ 風險 3：全文索引語法差異

• MyISAM：FULLTEXT INDEX idx (col) WITH PARSER ngram
• InnoDB：5.6+ 支援 ngram，但停用詞表、最小詞長參數不同

⚠️ 風險 4：自增值重置

• ALTER TABLE ... ENGINE=MyISAM 可能重置 AUTO_INCREMENT 為最大值+1
• 轉換後檢查：SHOW TABLE STATUS LIKE 'tbl'\G 確認 Auto_increment

⚠️ 風險 5：大表轉換鎖表時間不可控

• 百萬行以上必須使用 在線遷移工具：pt-online-schema-change、gh-ost
• 原理：建影子表 → 同步數據 → 交換表名，原表僅瞬間鎖定

四、在線遷移最佳實踐

方案 A：pt-online-schema-change (Percona Toolkit)

pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  D=db_name,t=table_name \
  --execute \
  --max-load "Threads_running=100" \
  --critical-load "Threads_running=200" \
  --chunk-time 0.5 \
  --chunk-size 1000 \
  --recursion-method=hosts \
  --progress time,30

方案 B：gh-ost (GitHub Online Schema Change)

gh-ost \
  --host=localhost --user=root --password=xxx \
  --database=db_name --table=table_name \
  --alter="ENGINE=InnoDB" \
  --max-load=Threads_running=100 \
  --critical-load=Threads_running=200 \
  --chunk-size=1000 \
  --execute

• 無觸發器、無外鍵依賴、基於 Binlog 同步、可暫停/續傳
• 推薦生產核心大表使用

五、轉換後驗證清單

1. 引擎確認：SHOW TABLE STATUS LIKE 'tbl'\G → Engine: InnoDB
2. 行數一致：SELECT COUNT(*) FROM tbl 對比轉換前
3. 索引完整：SHOW INDEX FROM tbl 對比 SHOW CREATE TABLE
4. 外鍵恢復：InnoDB 時補充 ALTER TABLE ... ADD CONSTRAINT ...
5. 應用驗證：讀寫測試、事務回滾測試、並發壓測
6. 監控指標：Innodb_row_lock_waits、Innodb_buffer_pool_reads、慢查詢變化

六、選型決策矩陣