您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 大量資料裝載效能優化：從 INSERT 到 LOAD DATA 的 10 倍提速指南

編輯：MySQL 資料庫相關知識

核心原理：為何批次勝過單行

索引維護成本：單行插入每次皆需 B+ 樹分裂、合併、寫入 Redo/Undo 日誌、刷新 Buffer Pool；批次僅在結束時一次性刷新
網路往返 (RTT)：1 萬筆單行 = 1 萬次 RTT；1 次批次 = 1 次 RTT
SQL 解析優化：1 萬條 INSERT = 1 萬次 Parse/Resolve/Optimize；1 條多值 INSERT = 1 次
事務提交：自動提交模式下單行 = 1 萬次 fsync；批次 = 1 次 (或配合 innodb_flush_log_at_trx_commit=2)

六大實戰策略對比

策略	適用場景	相對速度	關鍵參數/語法	注意事項
單行 INSERT (autocommit)	極少量、即時業務	1x (基準)	INSERT INTO t VALUES (...);	最慢，勿用於大量資料
多值 INSERT (批次 100-1000 行)	應用層組裝、中等量級	10-50x	INSERT INTO t VALUES (...),(...),(...);	單包 ≤ max_allowed_packet (預設 64M)
LOAD DATA INFILE (本地檔案)	海量導入 (GB/TB)、離線 ETL	100-500x	LOAD DATA INFILE '/path/file.csv' INTO TABLE t;	需 FILE 權限、檔案在 Server 端、安全風險
LOAD DATA LOCAL INFILE (客戶端檔案)	異機導入、無 Server 檔案權限	50-200x	LOAD DATA LOCAL INFILE 'local.csv' INTO TABLE t;	需客戶端/伺服端同時啟用 local_infile=1
mysqldump --tab + mysqlimport	跨實例遷移、分庫分表合併	200-400x	mysqldump --tab=/tmp/db --fields-terminated-by=',' db; mysqlimport --local db /tmp/db/*.txt	並行度可控、支援壓縮、斷點續傳
LOAD DATA ... REPLACE/IGNORE	增量同步、主鍵衝突處理	同 LOAD DATA	LOAD DATA ... REPLACE INTO TABLE t;	REPLACE 觸發 DELETE+INSERT，觸發器執行兩次

LOAD DATA 進階參數完全指南

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '"']
        [ESCAPED BY '\\']
    ]
    [LINES
        [STARTING BY '']
        [TERMINATED BY '\n']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var [, col_name_or_user_var] ...)]
    [SET col_name = expr [, col_name = expr] ...]

關鍵選項實戰用法

FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'：標準 CSV (RFC 4180)
LINES TERMINATED BY '\r\n'：Windows 檔案換行符
IGNORE 1 LINES：跳過標題列
(@var1, @var2, col3) SET col1=TRIM(@var1), col2=CAST(@var2 AS UNSIGNED)：使用者變數預處理，實現清洗、轉型、衍生欄位
PARTITION (p202401, p202402)：指定分區導入，避免全表鎖定
CONCURRENT：MyISAM 專用，允許並發插入 (InnoDB 忽略)

避開十大效能陷阱

未關閉索引/外鍵/唯一約束 → SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; 導入後重開
導入前未停用二進位日誌 → SET SESSION sql_log_bin=0; (需 SUPER 權限) 或 --skip-log-bin 重啟
字符集不匹配導致轉換開銷 → 檔案編碼 = 表字符集 = 連線字符集 = CHARACTER SET utf8mb4
單一巨大檔案導入 → 切分為 100-500MB 分片，並行導入不同分區/表
未使用 --extended-insert 導出 → mysqldump 預設單行 INSERT，必須加 --extended-insert 或 --opt
忽略 max_allowed_packet → 多值 INSERT 包體超過限制報錯，調大至 256M/1G
自增主鍵間隙鎖爭用 → 批次導入前 SET SESSION innodb_autoinc_lock_mode=2 (interleaved 模式，需 ROW 格式 binlog)
未調整 innodb_flush_log_at_trx_commit → 導入期間暫設 2 (每秒刷盤)，導入後恢復 1
未預熱 Buffer Pool → 導入後執行 ANALYZE TABLE、SELECT COUNT(*) FROM t FORCE INDEX (idx) 預熱熱點索引
未監控磁碟空間與 IOPS → 導入期間 iostat -xmt 1、df -h 實時觀察，預留 2 倍空間

實戰案例：1 億行 CSV 導入優化記錄

# 環境：MySQL 8.0, 32GB RAM, NVMe SSD, 16 vCPU
# 目標：1 億行、50GB CSV (gzip 後 8GB) 導入分區表 orders_2024

# 1. 切分檔案 (並行下載/解壓)
split -l 1000000 orders_2024.csv.gz orders_part_
# 得到 100 個 100 萬行分片

# 2. 建立分區表 (按月 RANGE 分區)
CREATE TABLE orders_2024 (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)*100 + MONTH(created_at)) (
  PARTITION p202401 VALUES LESS THAN (202402),
  PARTITION p202402 VALUES LESS THAN (202403),
  ...
  PARTITION p202412 VALUES LESS THAN (202501)
);

# 3. 會話級優化參數
SET SESSION sql_log_bin=0;
SET SESSION foreign_key_checks=0;
SET SESSION unique_checks=0;
SET SESSION innodb_autoinc_lock_mode=2;
SET SESSION innodb_flush_log_at_trx_commit=2;
SET SESSION sort_buffer_size=16M;
SET SESSION read_buffer_size=8M;

# 4. 並行導入 (GNU Parallel, 8 併發對應 8 分區)
ls orders_part_* | parallel -j 8 "
  mysql -uroot -p'***' --local-infile=1 -e \"
    SET SESSION sql_log_bin=0;
    LOAD DATA LOCAL INFILE '{}'
    INTO TABLE orders_2024
    FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\\'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (user_id, amount, @created)
    SET created_at = STR_TO_DATE(@created, '%Y-%m-%d %H:%i:%s');
  \"
"

# 5. 導入後收尾
SET SESSION foreign_key_checks=1;
SET SESSION unique_checks=1;
ANALYZE TABLE orders_2024;
# 總耗時：約 22 分鐘 (含解壓、網路傳輸)，峰值寫入 2.3 GB/s

總結：選型決策流程圖

資料量 < 10 萬行、頻繁小批次 → 應用層組裝多值 INSERT (批次 500-1000)
資料量 10 萬 - 1 億行、離線 ETL → LOAD DATA LOCAL INFILE + 會話優化參數
資料量 > 1 億行、跨實例遷移 → mysqldump --tab + mysqlimport 並行導入
需增量同步、主鍵衝突處理 → LOAD DATA ... REPLACE/IGNORE + 觸發器改寫
生產環境零停機導入 → pt-archiver / gh-ost / 新表雙寫 + 切換