MySQL 大量資料高效載入指南：批次插入、索引策略、壓縮協定與延遲索引刷新

核心原則

1. 批次 > 單行：減少網路往返、索引刷新次數、SQL 解析開銷。
2. 無索引載入 > 有索引載入：索引維護是主要負擔，載入後再建立索引最快。
3. 短 SQL > 長 SQL：少傳輸位元組、少解析 Token。

工具與語法選擇

mysqldump 產生高效匯入檔

# 最佳組合：多行 INSERT + 快速導出
mysqldump --opt --extended-insert db_name > dump.sql

# 避免使用
mysqldump --complete-insert db_name  # 產生單行 INSERT，極慢

索引策略：載入前刪除/禁用，載入後重建

MyISAM 表

# 禁用索引 (需停止寫入或鎖表)
myisamchk --keys-used=0 -rq /path/to/table_name.MYI
# 或
ALTER TABLE tbl DISABLE KEYS;  # MySQL 4.0+

# 載入資料
LOAD DATA INFILE 'huge.txt' INTO TABLE tbl;

# 重建索引
myisamchk -rq /path/to/table_name.MYI
# 或
ALTER TABLE tbl ENABLE KEYS;

InnoDB 表

• InnoDB **不支援** DISABLE/ENABLE KEYS。
• 建議：先載入主鍵順序資料 (按 PK 排序)，減少頁分裂；或載入後 OPTIMIZE TABLE 重建 (會鎖表)。
• 大量載入可暫時關閉 innodb_flush_log_at_trx_commit=2、innodb_doublewrite=0 (風險自負)，載入完畢恢復並 FLUSH LOGS。

網路與協定優化

• --compress：Client/Server 協定壓縮，**僅適用慢速網路** (LAN 通常 CPU 成本大於頻寬收益)。
• 省略預設值欄位：INSERT INTO t(a,b) VALUES (1,2) 不寫有 DEFAULT 的欄位，縮短 SQL。

延遲索引刷新 (MyISAM 專用)

• 表級：CREATE TABLE t (...) DELAY_KEY_WRITE=1 → 索引緩衝區不立即刷盤，僅在關表/刷新時寫入。
• 全域：啟動 mysqld --delayed-key-write=ON (預設 ON)。
• 風險：崩潰時索引可能損壞，需 REPAIR TABLE 或 myisamchk 修復。關鍵業務表**勿啟用**。

臨時表緩衝寫入模式 (適用頻繁小批次寫入)

1. 寫入暫存表 (無索引、Memory/InnoDB)。
2. 定期 (如每分鐘) 批次 INSERT INTO main SELECT * FROM staging 或 LOAD DATA。
3. 優點：主表鎖定時間極短、索引刷新合併、讀取查詢不受寫入衝擊。
4. 缺點：資料延遲可見、需額外調度邏輯。

效能對比速查 (百萬筆測試參考)