單純使用 INSERT INTO ... VALUES (...) 逐筆寫入百萬級資料,耗時極長且易受索引、鎖競爭影響。本文整理三大類加速方案:存儲過程批次提交、LOAD DATA INFILE、引擎層面優化,並附實測對比數據供參考。
WHILE 迴圈在單一交易中批次插入,減少網路往返與交易提交開銷。ALTER TABLE ... DISABLE KEYS 暫停非唯一索引維護,載入完成後 ENABLE KEYS 重建,大幅提速。SET UNIQUE_CHECKS=0、SET AUTOCOMMIT=0;| 引擎 | 策略 | 耗時 |
|---|---|---|
| InnoDB | 存儲過程逐筆 INSERT | 3h 37min |
| MyISAM | 存儲過程 + DISABLE/ENABLE KEYS | 8min 50sec |
結論:MyISAM 配合索引暫停機制,批量載入效能遠超 InnoDB 預設行為。若業務需 InnoDB 交易特性,可先載入 MyISAM 再 ALTER TABLE ... ENGINE=InnoDB 轉換。
LOAD DATA INFILE '/path/to/data.txt'
INTO TABLE target_table
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(col1, col2, col3...);
FIELDS TERMINATED BY:欄位分隔符(預設 Tab)。ENCLOSED BY:欄位包裹字元(如 CSV 的雙引號)。LINES TERMINATED BY:換行符(Windows \r\n、Linux \n)。IGNORE n LINES:跳過標題列。REPLACE / IGNORE:處理唯一鍵衝突策略。若無法使用 LOAD DATA,可合併多行為單一 INSERT:
INSERT INTO t (a,b,c) VALUES
(1,2,3), (4,5,6), (7,8,9) ... ;
max_allowed_packet 限制)。SET AUTOCOMMIT=0 手動提交,進一步提升吞吐。SET UNIQUE_CHECKS=0(需確保資料不重複)。SET AUTOCOMMIT=0,批次提交。bulk_insert_buffer_size(MyISAM)、innodb_log_file_size、innodb_buffer_pool_size。ALTER TABLE ... ENGINE=InnoDB 轉引擎,兼顧速度與交易支援。