MySQL 數據裝載效率優化策略深度分析 (上)

MySQL 大數據導入性能優化核心原則與實戰策略

雖然 SELECT 查詢優化最受關注，但數據裝載（導入）效率同樣關鍵，尤其在數據倉庫構建、日誌分析、批量遷移等場景。本文基於 MySQL 官方文檔核心原理，系統梳理數據裝載優化的底層邏輯與最佳實踐。

核心優化原理

1. 批量優於單行： 成批裝載避免每行後刷新索引緩存，僅在批次結束時刷新一次。
2. 無索引優於有索引： 索引會導致每行插入時同步更新所有索引結構，成本極高。
3. 短 SQL 優於長 SQL： 解析開銷小、網絡傳輸快，大量數據時微小差異被放大。

實戰優化策略

1. 偏好 LOAD DATA 而非 INSERT

LOAD DATA INFILE 批量讀取文件，索引刷新最少，服務器僅解析一條語句。所有變體（LOAD DATA LOCAL 等）均優於逐行 INSERT。

2. LOAD DATA 優於 LOAD DATA LOCAL

LOAD DATA 文件需在服務器端且需 FILE 權限，但服務器直接磁盤讀取；LOAD DATA LOCAL 客戶端讀文件再經網絡發送，顯著慢於前者。

3. 必須用 INSERT 時，使用多行語法

INSERT INTO table (col1, col2) VALUES
(1, 'a'), (2, 'b'), (3, 'c'), ...;

單條語句插入越多行越好，減少語句解析、網絡往返、索引刷新次數。使用 mysqldump 導出時添加 --extended-insert 或 --opt（默認含 extended-insert）。避免 --complete-insert，它生成單行 INSERT，解析慢、語句長。

4. 啟用壓縮協議減少網絡流量

客戶端添加 --compress 參數啟用壓縮協議。適用於慢速網絡，CPU 開銷換取帶寬，高速網絡下得不償失。

5. 利用默認值縮短 INSERT 語句

對將賦默認值的列（如自增 ID、DEFAULT 列），勿在 INSERT 中顯式指定。語句更短，網絡傳輸少，服務器解析轉換少。

6. 批量插入最小化索引開銷

無論 LOAD DATA 還是多行 INSERT，索引僅在批次結束時刷新一次，而非每行刷新，大幅降低索引維護成本。

7. 新表先導數據後建索引

大量數據裝入新表時：創建表 → 不建索引 → 裝載數據 → 創建索引。一次構建索引遠快於逐行維護。

8. 既有表可暫時刪除/禁用索引

大量導入前 DROP INDEX 或用 myisamchk/isamchk 禁用索引，導入後重建/啟用。但需實測：若導入量小於現有數據量，重建索引耗時可能超過導入收益。

索引禁用/啟用操作示例 (MyISAM)

# 進入數據庫目錄
myisamchk --keys-used=0 -rq 表名  # 禁用索引
# ... 載入數據 ...
myisamchk -rq 表名               # 重建並啟用索引

需服務器主機賬戶及表文件寫權限。操作期間必須鎖表防止併發修改（參考表修復鎖定協議）。

總結

數據裝載優化核心是「減少解析、減少網絡、減少索引刷新」。LOAD DATA 是首選，配合 --opt 導出、壓縮協議、默認值利用、索引延遲構建，可實現數量級性能提升。實際項目中務必結合數據量、表結構、硬件環境進行基準測試。

本文整理自 MySQL 官方文檔性能優化章節。