MySQL 數據裝載提速指南：從原理到實戰的 10 大優化策略

核心原理

1. 批量 > 單行： 減少索引刷新、網絡交互、SQL 解析開銷。
2. 無索引裝載 > 有索引裝載： 避免每行維護 B+ 樹。
3. 短 SQL > 長 SQL： 降低解析、傳輸成本。

10 大實戰優化招式

1. 用 LOAD DATA INFILE 替代 INSERT

服務器端直接讀文件，批量寫入，單條語句解析，索引僅刷新一次。LOAD DATA 比 LOAD DATA LOCAL 快（後者經客戶端轉發）。

2. INSERT 必須用多值語法

INSERT INTO t (a,b) VALUES (1,2),(3,4),(5,6)...;

單條語句插入數百/上千行。mysqldump 備份請加 --extended-insert（--opt 默認含之），禁用 --complete-insert（生成單行 INSERT）。

3. 啟用壓縮協議

客戶端加 --compress，減少網絡流量。適合低帶寬環境，CPU 換帶寬。

4. 利用默認值，縮減 INSERT 列清單

不顯式寫入將使用默認值的列，SQL 更短，解析更快。

5. 批量插入自動延遲索引維護

LOAD DATA 或多值 INSERT 會在所有行寫入後統一刷新索引，而非逐行刷新。

6. 新表先裝數據後建索引

CREATE TABLE -> LOAD DATA -> CREATE INDEX。一次建索引遠快於逐行維護。

7. 大表裝載前刪除/禁用索引，事後重建

DROP INDEX idx ON t;  -- 或
myisamchk --keys-used=0 -rq /path/tbl.MYI  -- 禁用 MyISAM 索引
# 裝載數據
myisamchk -rq /path/tbl.MYI  -- 重建

權衡： 若裝載量小於現有數據 1%，重建索引成本可能超過裝載收益，需實測。

8. 臨時表緩衝高頻寫入

高併發寫入場景：寫入臨時表（無索引/少索引），定期批量 INSERT ... SELECT 合併到主表。減少主表鎖爭用，批量合併享受索引延遲刷新紅利。

9. MyISAM DELAYED_KEY_WRITE

表級：CREATE TABLE t (...) DELAYED_KEY_WRITE=1。
實例級：啟動 mysqld --delayed-key-write=ON。
索引塊不即時刷盤，僅在必要時（換出、FLUSH TABLES、關表）寫入。風險： 崩潰可能導致索引損壞，需 REPAIR TABLE。

10. 監控與調優

關注 Handler_write、Innodb_buffer_pool_pages_dirty、Key_blocks_not_flushed 等狀態變量，觀察刷盤頻率與臟頁比例。