MySQL 大量資料載入優化：批次操作、索引策略與協定壓縮

核心原則

1. 批次優於單筆：減少索引緩衝區刷新、網路往返、SQL 解析開銷。
2. 無索引載入優於有索引：避免每筆更新索引，載入後再建立索引。
3. 短 SQL 優於長 SQL：降低解析與網路傳輸成本。

具體優化技巧

1. 使用 LOAD DATA INFILE (最快)

• 伺服器端直接讀檔，無網路開銷。
• LOAD DATA INFILE > LOAD DATA LOCAL INFILE (客戶端讀檔再傳送)。
• 需 FILE 權限與檔案置於伺服器。

2. 多值 INSERT 批次寫入

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

• 單條 SQL 寫入數百/上千列，大幅減少解析與索引刷新次數。
• mysqldump --extended-insert (或 --opt) 自動產生此格式。
• 避免 --complete-insert (單列 INSERT)。

3. 壓縮客戶端/伺服器協定

mysql --compress -h host -u user -p
# 或程式碼中啟用 CLIENT_COMPRESS

• 適用於低速網路 (VPN、跨機房)。
• 高速區域網路壓縮 CPU 成本可能大於收益。

4. 利用預設值縮短 SQL

• 欄位有 DEFAULT 時，INSERT 不指定該欄位，由伺服器自動填入。
• 減少傳輸位元組與解析工作。

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

-- 方法一：DROP/CREATE INDEX
DROP INDEX idx_name ON table;
LOAD DATA ...
CREATE INDEX idx_name ON table (col);

-- 方法二：myisamchk 禁用/啟用 (需獨佔存取)
myisamchk --keys-used=0 -rq /path/table.MYI  # 禁用
LOAD DATA ...
myisamchk -rq /path/table.MYI                # 重建並啟用

• 適用於大量資料初始載入或重建大表。
• 若僅增量載入少量資料，重建索引成本可能超過逐筆維護成本，需實測比較。
• 操作期間需鎖定表防止併發修改（參考表修復鎖定協定）。

6. 延遲索引寫入 (MyISAM)

• 建表指定 DELAY_KEY_WRITE=1：索引緩衝區不即時刷新，僅在關閉表或 FLUSH TABLES 時寫入。
• 或啟動參數 --delayed-key-write 全域生效。
• 風險：異常崩潰可能導致索引損壞，需 CHECK TABLE 或 myisamchk -r 修復。

衍生應用：以暫存表緩衝高頻寫入

場景：Web 存取日誌高頻寫入，但查詢需求低。

1. 寫入暫存表 (MyISAM/MEMORY)，無索引或極簡索引。
2. 定期 (如每分鐘) 批次 INSERT INTO main_table SELECT * FROM staging_table 並清空暫存表。
3. 優點：主表索引維護集中化、減少鎖爭用、批次寫入效率高。
4. 缺點：資料延遲可見性 (秒~分鐘級)。