大數據量導出導入效能對比：mysqldump vs SELECT INTO OUTFILE vs LOAD DATA 實測數據

測試環境

• 硬體：Intel Xeon 5130 @ 2.00GHz × 2、4GB RAM、564GB SAS
• OS：RHEL 4 (2.6.9)、MySQL 5.0.27
• 資料：1,016,126 筆、平均行長 46,822 位元組 (~45GB)

導出效能對比

結論：mysqldump 導出速度更快，且輸出可直接執行還原。

導入效能對比

結論：LOAD DATA 導入最快，mysqldump + mysql 組合次之。

關鍵優化策略：索引建立時機

MyISAM 表專用技巧

-- 導入前
ALTER TABLE yejr DISABLE KEYS;
-- 導入資料
LOAD DATA INFILE 'yejr.txt' INTO TABLE yejr;
-- 導入後
ALTER TABLE yejr ENABLE KEYS;

• DISABLE KEYS 停止非唯一索引更新，僅寫資料頁。
• ENABLE KEYS 以排序方式批次重建索引，比逐筆插入 B-Tree 快數倍。
• 唯一索引/主鍵不受影響，仍即時維護（需確保資料無重複）。

InnoDB 表策略

• InnoDB 無 DISABLE KEYS，但可按主鍵順序排序資料檔案導入，減少頁分裂。
• 調大 innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_log_at_trx_commit=2 (導入期間)。
• 使用 LOAD DATA 而非 INSERT，減少語句解析與事務開銷。

完整最佳實踐流程

1. 目標庫建表結構 (含所有索引、外鍵)。
2. MyISAM：ALTER TABLE ... DISABLE KEYS。
3. 使用 mysqldump -t --no-create-info 導出純資料，或 SELECT INTO OUTFILE 導出文字。
4. 使用 LOAD DATA (本地) 或 mysqlimport 導入。
5. MyISAM：ALTER TABLE ... ENABLE KEYS；InnoDB：無需額外步驟。
6. 執行 ANALYZE TABLE 更新統計資訊。