核心原理：三大物理層面的開銷消除
| 開銷來源 | 單行 INSERT | 批量載入 | 優化倍數 |
|---|
| 網絡往返 (RTT) | N 次 | 1 次 | ~N 倍 |
| SQL 解析/優化 | N 次 | 1 次 | ~N 倍 |
| 索引刷新/刷盤 | N 次 (每行) | 1 次 (批次) | ~N 倍 |
**結論**：成批載入通過**合併網絡包、合併解析、延遲索引維護**三位一體實現數量級提速。
工具選型決策樹
數據源格式？
├─ 文本文件 (CSV/TSV/固定寬度) → LOAD DATA INFILE (首選)
├─ 邏輯備份文件 (.sql) → mysql < backup.sql / 多行 INSERT
├─ 異構數據庫遷移 → mysqldump --tab + LOAD DATA / ETL 工具
└─ 應用程式實時寫入 → 批量 INSERT / PreparedStatement 批處理 / DELAYED INSERT
LOAD DATA INFILE 終極指南
基礎語法與關鍵參數
LOAD DATA [LOCAL] INFILE '/path/data.csv'
INTO TABLE target_table
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES -- 跳過標題行
(id, name, email, created_at)
SET created_at = IF(created_at='', NULL, STR_TO_DATE(created_at, '%Y-%m-%d %H:%i:%s'));
LOCAL vs 非 LOCAL 關鍵差異
| 維度 | LOAD DATA INFILE | LOAD DATA LOCAL INFILE |
|---|
| 文件位置 | 伺服器端文件系統 | 客戶端文件系統 |
| 權限需求 | FILE 權限 (高危) | 無特殊權限 |
| 網絡傳輸 | 無 (伺服器直讀) | 客戶端讀取 → 網絡傳輸 → 伺服器 |
| 速度 | ✅ 極快 (磁盤直讀) | 較慢 (受網絡帶寬限制) |
| 安全性 | 需檔案系統訪問權 | ✅ 更安全 (客戶端可控) |
生產建議：文件先傳至伺服器 (scp/rsync)，使用非 LOCAL 模式；或啟用 local_infile=ON 並限制客戶端 IP。
多行 INSERT 最佳實踐
單條語句包含盡可能多行
INSERT INTO logs (level, message, created_at) VALUES
('INFO', 'User login', '2024-01-01 10:00:00'),
('WARN', 'Disk 80%', '2024-01-01 10:01:00'),
('ERROR', 'Connection timeout', '2024-01-01 10:02:00'),
... -- 建議單條 1000-5000 行，單條 SQL < 16MB (max_allowed_packet)
('DEBUG', 'Cache hit', '2024-01-01 10:03:00');
mysqldump 導出優化參數
# 必加參數：生成多行 INSERT
mysqldump --extended-insert --quick --single-transaction \
--no-autocommit --net_buffer_length=1M \
-u root -p db_name > dump.sql
# 等價於 --opt (預設開啟 extended-insert, quick 等)
mysqldump --opt -u root -p db_name > dump.sql
# 禁用 --complete-insert (單行 INSERT、含欄位名、極慢)
# 禁用 --skip-extended-insert (強制單行)
索引策略：裝載前後的博弈
場景一：新建表大量導入 (最快路徑)
CREATE TABLE target (id INT PRIMARY KEY, data VARCHAR(100)) ENGINE=InnoDB;
-- 1. 先導入數據 (無索引開銷)
LOAD DATA INFILE 'data.csv' INTO TABLE target;
-- 2. 再建立索引 (一次性構建，掃描排序)
ALTER TABLE target ADD INDEX idx_data (data);
場景二：現有表增量導入 (權衡決策)
| 策略 | 適用條件 | 操作 | 風險 |
|---|
| 保留索引直接導入 | 增量 < 5% 表大小 | 直接 LOAD DATA / 批量 INSERT | 索引維護開銷線性增長 |
| 刪除索引 → 導入 → 重建 | 增量 > 10% 表大小 | DROP INDEX... → LOAD → CREATE INDEX... | 重建耗時長、鎖表久、需鎖協議 |
| 禁用索引 → 導入 → 啟用 (MyISAM) | MyISAM 表 | myisamchk --keys-off → LOAD → myisamchk --keys-on | 需停寫、崩潰風險 |
InnoDB 線上索引重建 (MySQL 5.6+)
-- DDL 線上執行，允許並發 DML (僅元數據鎖短暫阻塞)
ALTER TABLE target ADD INDEX idx_data (data), ALGORITHM=INPLACE, LOCK=NONE;
-- 或使用 pt-online-schema-change 零停機重建
網絡與協議層優化
- 壓縮協議：
mysql --compress -u root -p 或 JDBC useCompression=true。慢網絡 (跨機房/VPN) 效果顯著，局域網 CPU 開銷可能得不償失。
- 批量預處理：應用層使用
PreparedStatement.addBatch() + executeBatch()，配合 rewriteBatchedStatements=true (MySQL Connector/J) 自動重寫為多行 INSERT。
- 默認值推給伺服器：
INSERT INTO t (a) VALUES (1) 而非 INSERT INTO t (a,b) VALUES (1, DEFAULT)，減少網絡傳輸與解析。
進階技巧：DELAYED_KEY_WRITE 與臨時表緩衝
表級延遲鍵寫入 (MyISAM/InnoDB 不支援)
CREATE TABLE logs (...) ENGINE=MyISAM DELAY_KEY_WRITE=1;
-- 索引塊只在關表/FLUSH TABLES/塊滿時刷盤
-- 風險：崩潰丟失未刷盤索引修改，需定期 FLUSH TABLES
全域延遲鍵寫入
[mysqld]
delayed_key_write=ON # 所有 MyISAM 表生效
-- 或啟動參數 --delayed-key-write=ON
臨時表緩衝寫入模式 (高併發寫入 + 讀取衝突場景)
-- 1. 應用寫入臨時表 (無索引、Memory/InnoDB)
INSERT INTO tmp_logs (...) VALUES (...); -- 極快
-- 2. 定時任務批量合併入主表
INSERT INTO main_logs SELECT * FROM tmp_logs;
TRUNCATE TABLE tmp_logs;
-- 優勢：主表讀取無鎖爭用、索引維護批量化、寫入延遲可控 (秒/分級)
效能對比基準 (參考值：百萬行導入)
| 方法 | 耗時 | 相對提速 | 適用場景 |
|---|
| 單行 INSERT (自動提交) | ~45 分鐘 | 1x (基準) | 極小數據、測試 |
| 單行 INSERT (手動事務批次 1000) | ~8 分鐘 | 5.6x | 應用層無法改造 |
| 多行 INSERT (1000 行/條) | ~2 分鐘 | 22x | 邏輯備份導入 |
| LOAD DATA INFILE (非 LOCAL) | ~15 秒 | 180x | ✅ 文本文件導入首選 |
| LOAD DATA + 無索引 → 後建索引 | ~10 秒 | 270x | ✅ 新表大量導入極致 |
避坑指南
- max_allowed_packet：多行 INSERT/LOAD DATA 超過此限制會報錯，臨時調大
SET GLOBAL max_allowed_packet=1073741824; (1GB)。
- 字元集不匹配：文件編碼與
CHARACTER SET 子句不一致導致亂碼/截斷。
- 日期格式：使用
STR_TO_DATE 在 SET 子句中統一轉換，避免 0000-00-00。
- 主鍵衝突：
REPLACE / INSERT ... ON DUPLICATE KEY UPDATE / IGNORE 三選一，業務語義不同。
- 外鍵檢查：大量導入前
SET FOREIGN_KEY_CHECKS=0; 導入後 =1;，大幅減少檢查開銷。
數據裝載優化的本質是**將離散操作聚合為批量操作，將同步維護轉為異步批量維護**。掌握 LOAD DATA、多行 INSERT、索引時機選擇、網絡協議優化這四大槓桿，能將導入效能提升 2-3 個數量級，是數據倉庫建設、異構遷移、日誌歸檔等場景的核心競爭力。
