MySQL 大數據高效加載實戰指南

核心原則：批量優於單條

大量數據導入時，批量操作可避免每條記錄都刷新索引、提交事務，大幅降低 CPU 與 I/O 開銷。

加速數據加載的 7 大技巧

1. 優先使用 LOAD DATA INFILE

比 INSERT 快 20 倍以上。服務器直接從本地磁碟讀取文件，省去網絡傳輸與解析開銷。

2. 避免 LOAD DATA LOCAL INFILE

LOCAL 關鍵字導致客戶端讀取文件並經網絡發送給服務器，效率顯著降低。

3. 多行 INSERT 合併

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

單條語句插入數百/數千行，減少客戶端往返與 SQL 解析。

4. 事務包裹多條 INSERT

START TRANSACTION;
INSERT INTO t VALUES (...);
INSERT INTO t VALUES (...);
...
COMMIT;

非事務表可用 LOCK TABLES t WRITE ... UNLOCK TABLES 達到類似效果。

5. 啟用壓縮協議

客戶端連接參數添加 --compress 或 MYSQL_CLIENT_COMPRESS，減少網絡傳輸量。注意：會增加 CPU 開銷，適用於帶寬受限、數據量大的場景。

6. 利用默認值減少傳輸

INSERT 語句中省略有默認值的列，減少網絡包體積與解析時間。

7. MyISAM/ISAM：先導數據後建索引

-- 方法一：ALTER TABLE
ALTER TABLE t DISABLE KEYS;
-- 大量導入數據
ALTER TABLE t ENABLE KEYS;

-- 方法二：myisamchk 工具
myisamchk --keys-used=0 -rq /path/to/t.MYI   # 禁用索引
# 導入數據
myisamchk --recover --quick /path/to/t.MYI   # 重建索引

實戰檢查清單

1. 目標表是否為空？空表導入可先禁用索引/外鍵檢查（SET FOREIGN_KEY_CHECKS=0）。
2. 數據文件編碼是否與表字符集一致？
3. 主鍵是否有序？InnoDB 按主鍵順序導入可減少頁分裂。
4. 批次大小控制：建議每批 1000~5000 行，避免事務過大導致回滾段膨脹。
5. 導入後執行 ANALYZE TABLE 更新統計信息。