MySQL 效能調優進階：INSERT 查詢加速策略與 LOAD DATA INFILE 最佳實踐

MySQL 插入效能深度優化指南

INSERT 耗時分解

• 連接：3
• 發送查詢：2
• 分析查詢：2
• 插入記錄：1 × 記錄大小
• 插入索引：1 × 索引數
• 關閉：1

表大小以 N log N (B-Tree) 速度影響索引插入。初始開表開銷每併發查詢一次。

加速插入策略

1. 批次多值 INSERT (同一客戶端)

INSERT INTO t VALUES (1,2),(3,4),(5,6); -- 比多條單值 INSERT 快數倍

2. INSERT DELAYED (不同客戶端高併發)

資料入佇列即返回，服務器空閒時批量寫入，降低鎖衝突。

3. MyISAM 並發插入

表無刪除產生的「空洞」時，SELECT 與末尾 INSERT 可並發。

4. LOAD DATA INFILE 大量載入 (最快，比 INSERT 快 20 倍)

優化流程：

1. CREATE TABLE 建表 (可用 mysql 或 Perl-DBI)。
2. FLUSH TABLES 或 mysqladmin flush-tables。
3. myisamchk --keys-used=0 -rq /path/to/db/tbl — 禁用所有索引。
4. LOAD DATA INFILE 載入資料 (不更新索引，極快)。
5. 若需壓縮：myisampack。
6. myisamchk -r -q /path/to/db/tbl — 重建索引 (記憶體中建樹，避免磁碟搜尋，索引完美平衡)。
7. FLUSH TABLES。

未來版本將此流程整合進 LOAD DATA INFILE 內部。

5. 表鎖定加速批次插入

LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
UNLOCK TABLES;

索引緩衝區僅在所有 INSERT 完成後刷盤一次。單條語句插入所有行則無需鎖定。鎖定會降低整體時間 ~40%，但增加部分執行緒等待時間。建議每 1000 行做一次 UNLOCK/LOCK 釋放併發。

總結

1. 小批量：多值 INSERT。
2. 高併發寫入：INSERT DELAYED。
3. 大量初始載入：LOAD DATA INFILE + 禁用/重建索引流程 (最快)。
4. 批次事務性插入：LOCK TABLES + 定期解鎖。