隨著 MySQL 5.6+ 普及,InnoDB 已成為預設且唯一推薦的儲存引擎。越來越多業務將歷史 MyISAM 表遷移至 InnoDB,但兩者在儲存結構、鎖粒度、事務語義上存在本質差異,直接 ALTER TABLE ... ENGINE=InnoDB 往往踩坑。本文梳理 5 大遷移必看差異、4 大遷移核心收益,並給出實戰遷移策略。
| 差異點 | MyISAM | InnoDB | 遷移影響與對策 |
|---|---|---|---|
| 自增主鍵位置 | 允許非第一列(如 PRIMARY KEY (cat_id, id),id 可為 AUTO_INCREMENT) |
複合主鍵中自增列必須為第一列 | Discuz「搶樓」功能即依賴此特性。遷移前必須重設計主鍵或將計數器移至 Redis。 |
COUNT(*) 全表掃描 |
內建計數器,瞬間返回 | 需掃描聚簇索引,億級表極慢 | 帶 WHERE 條件走索引則差異微小。頻繁全表計數請維護獨立計數表或 Redis Counter。 |
| 全文索引 | 原生支援 | 5.6+ 才支援,中文分詞效果差 | 全文搜索請外掛 Elasticsearch/Sphinx/Lucene,勿在資料庫層強行解決。 |
| 大批量導入+後續加工 | 無事務、表鎖,載入極快 | 事務日誌、行鎖、雙寫緩衝,載入較慢 | 策略:先載入 MyISAM 臨時表 → 加工清洗 → INSERT INTO innodb_tbl SELECT ... 導入正式表。 |
LOAD TABLE FROM MASTER |
支援 | 不支援 | 極少用,忽略。 |
REPAIR TABLE 且耗時 4~5 倍。-- 1. 結構檢查 SELECT TABLE_NAME, COLUMN_NAME, COLUMN_KEY, EXTRA FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='your_db' AND EXTRA='auto_increment' AND COLUMN_KEY='PRI' AND ORDINAL_POSITION > 1; -- 若有結果,需先調整主鍵結構 -- 2. 低峰期線上遷移(利用 pt-online-schema-change 或 gh-ost) pt-online-schema-change --alter "ENGINE=InnoDB" D=db,t=tbl --execute -- 3. 遷移後驗證 ANALYZE TABLE tbl; -- 更新統計資訊 CHECK TABLE tbl; -- 檢查完整性 SHOW TABLE STATUS LIKE 'tbl'\G -- 確認 Engine=InnoDB、Row_format=Dynamic/Compressed -- 4. 觀察關鍵指標(持續 1 週) - Innodb_row_lock_waits / Innodb_row_lock_time_avg - Handler_read_rnd_next (全表掃描) - Slow query log 中該表查詢變化
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 可節省 40~60% 空間,CPU 開銷可接受。MyISAM 到 InnoDB 的遷移,本質是從「無鎖無事務的堆組織」向「MVCC+行鎖的索引組織表」邁進。只要妥善處理自增主鍵、計數器、全文搜索三大硬傷,並配合線上結構變更工具,遷移風險可控,收益顯著。別讓歷史包袱拖慢系統演進腳步。