MySQL 大量資料插入優化實戰：從批次 INSERT 到 LOAD DATA 的效能躍升

前言

大量資料匯入是常見運維場景。本文由淺入深介紹 5 種優化策略，實測效能可提升數十倍。

策略 1：多值 INSERT 批次寫入

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

• 單條 SQL 插入數百~數千行，減少網路往返與解析開銷。
• 調大 bulk_insert_buffer_size（MyISAM）或 innodb_log_buffer_size（InnoDB）加速緩衝刷盤。

策略 2：INSERT DELAYED（僅 MyISAM/MEMORY）

INSERT DELAYED INTO `t` VALUES ...;

• 客戶端立即返回，伺服器背景執行緒批次寫入。
• 適合日誌、統計等容許延遲的場景。

策略 3：MyISAM 並發插入

若表無刪除空洞，concurrent_insert=ALWAYS 允許 SELECT 與 INSERT 並行。

策略 4：LOAD DATA INFILE —— 效能王者

LOAD DATA INFILE '/path/data.csv'
INTO TABLE `t`
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

• 繞過 SQL 解析、索引即時維護，直接寫入資料頁。
• 官方測試比多條 INSERT 快 20 倍以上。
• 搭配 --local-infile 參數可從客戶端上傳。

策略 5：鎖表/事務批次提交

MyISAM（鎖表）

LOCK TABLES `t` WRITE;
INSERT INTO `t` VALUES ...; -- 多條
UNLOCK TABLES;

索引緩衝區僅在 UNLOCK 時刷盤一次。

InnoDB（事務）

START TRANSACTION;
INSERT INTO `t` VALUES ...; -- 多條
COMMIT;

• 減少 redo log 刷盤頻率。
• 大筆資料建議每 1,000~5,000 行提交一次，避免事務過大導致鎖等待或回滾段膨脹。

實務檢核表

1. 匯入前 DISABLE KEYS（MyISAM）或 SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0;（InnoDB）。
2. 使用 LOAD DATA 並預先排序主鍵順序（InnoDB 聚簇索順序寫入最快）。
3. 調大 innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_log_at_trx_commit=2（匯入期間）。
4. 匯入後 ANALYZE TABLE 更新統計資訊。