萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 百萬級大量資料快速插入方案總結

背景與挑戰

單純使用 INSERT INTO ... VALUES (...) 逐筆寫入百萬級資料,耗時極長且易受索引、鎖競爭影響。本文整理三大類加速方案:存儲過程批次提交、LOAD DATA INFILE、引擎層面優化,並附實測對比數據供參考。

方案一:存儲過程 + 批次提交(InnoDB / MyISAM)

核心技巧

  • 使用 WHILE 迴圈在單一交易中批次插入,減少網路往返與交易提交開銷。
  • MyISAM 可搭配 ALTER TABLE ... DISABLE KEYS 暫停非唯一索引維護,載入完成後 ENABLE KEYS 重建,大幅提速。
  • InnoDB 建議:
    1. 按主鍵順序寫入;
    2. SET UNIQUE_CHECKS=0、SET AUTOCOMMIT=0;
    3. 載入後恢復設定。

實測對比(200 萬筆)

引擎策略耗時
InnoDB存儲過程逐筆 INSERT3h 37min
MyISAM存儲過程 + DISABLE/ENABLE KEYS8min 50sec

結論:MyISAM 配合索引暫停機制,批量載入效能遠超 InnoDB 預設行為。若業務需 InnoDB 交易特性,可先載入 MyISAM 再 ALTER TABLE ... ENGINE=InnoDB 轉換。

方案二:LOAD DATA INFILE(最推薦)

優勢

  • 單條 SQL 完成批量載入,Server 僅解析一次。
  • 索引維護延遲至所有資料載入完畢,I/O 極小。
  • 實測同樣 200 萬筆,僅需十多分鐘。

基本語法

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:處理唯一鍵衝突策略。

方案三:多值 INSERT 語句

若無法使用 LOAD DATA,可合併多行為單一 INSERT:

INSERT INTO t (a,b,c) VALUES
(1,2,3), (4,5,6), (7,8,9) ... ;
  • 每批 500~1000 行,平衡 SQL 長度與網路封包大小(受 max_allowed_packet 限制)。
  • 配合 SET AUTOCOMMIT=0 手動提交,進一步提升吞吐。

進階優化清單

  1. 排序資料:InnoDB 按主鍵順序寫入可減少頁分裂。
  2. 關閉唯一檢查:SET UNIQUE_CHECKS=0(需確保資料不重複)。
  3. 關閉自動提交:SET AUTOCOMMIT=0,批次提交。
  4. 調大緩衝:bulk_insert_buffer_size(MyISAM)、innodb_log_file_size、innodb_buffer_pool_size。
  5. 分區表:按範圍分區可平行載入不同分區,降低鎖競爭。

總結建議

  1. 首選 LOAD DATA INFILE,效能最佳、語法最簡潔。
  2. 次選 存儲過程 + 批次 INSERT,適合需邏輯處理或無檔案系統存取權限的場景。
  3. MyISAM 載入後可 ALTER TABLE ... ENGINE=InnoDB 轉引擎,兼顧速度與交易支援。
  4. 務必在測試環境先跑通全流程,驗證資料完整性與還原時間。
copyright © 萬盛學電腦網 all rights reserved