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

MySQL INSERT 優化實戰：批次插入、延遲插入、LOAD DATA、緩衝區調優

編輯：MySQL 資料庫相關知識

優化策略對比表

策略	適用場景	效能提升	關鍵參數/語法
多值單語句	同一客戶端批次寫入	10~50x	`INSERT INTO t VALUES (1),(2),(3)...`
INSERT DELAYED	寫入緩衝、非關鍵日誌、MyISAM	高併發寫入不阻塞	`INSERT DELAYED INTO t ...` (8.0 移除)
LOAD DATA INFILE	大量文字檔匯入	20x 以上	`LOAD DATA INFILE 'file' INTO TABLE t`
bulk_insert_buffer_size	MyISAM 批次插入	減少索引刷新	`SET GLOBAL bulk_insert_buffer_size=64M`
分離索引/資料檔	多磁碟陣列	I/O 平行化	`DATA DIRECTORY`, `INDEX DIRECTORY`
REPLACE / IGNORE	去重寫入、冪等導入	避免唯一鍵衝突錯誤	`REPLACE INTO`, `INSERT IGNORE`

詳細實作指南

1. 多值 INSERT（最通用）

-- 單條：慢（每次解析、優化、鎖、提交）
INSERT INTO logs (msg) VALUES ('a');
INSERT INTO logs (msg) VALUES ('b');

-- 批次：快（單次解析、單次提交）
INSERT INTO logs (msg) VALUES ('a'), ('b'), ('c'), ...;

-- 建議批次大小：1000~5000 行 / 1~4 MB SQL 文本
-- 應用層累積陣列，達閾值或定時 flush

2. LOAD DATA INFILE（極速匯入）

LOAD DATA INFILE '/data/import/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, email, created_at);

需 FILE 權限、secure_file_priv 目錄限制。
配合 SET autocommit=0; ... COMMIT; 進一步加速。
InnoDB 關閉 unique_checks/foreign_key_checks 再開啟：

SET unique_checks=0; SET foreign_key_checks=0; SET autocommit=0;
LOAD DATA ...
SET unique_checks=1; SET foreign_key_checks=1; COMMIT;

3. 批次插入緩衝區（MyISAM 專用）

SET GLOBAL bulk_insert_buffer_size = 64M;  -- 預設 8M

僅對 INSERT ... VALUES (...),(...)、INSERT ... SELECT、LOAD DATA 生效。

4. 索引與資料分離（表空間）

CREATE TABLE t (
  id INT PRIMARY KEY,
  data BLOB
) DATA DIRECTORY = '/mnt/data1' INDEX DIRECTORY = '/mnt/data2';

需 innodb_file_per_table=ON，適用於 HDD 多盤陣列，SSD 單盤無顯著差異。

5. REPLACE vs INSERT IGNORE

語句	行為	適用
`REPLACE INTO t ...`	唯一鍵衝突 → 刪舊插新（觸發 DELETE + INSERT 觸發器）	需更新現有行
`INSERT IGNORE INTO t ...`	唯一鍵衝突 → 靜默忽略，不報錯	冪等導入、去重
`INSERT ... ON DUPLICATE KEY UPDATE col=VALUES(col)`	衝突 → 更新指定欄位	增量同步、計數器

InnoDB 專屬加速清單

innodb_flush_log_at_trx_commit = 2（匯入期間暫設，完成後改回 1）。
innodb_buffer_pool_size 設大（記憶體 70%）。
innodb_log_file_size 設大（1GB+），減少 checkpoint 刷盤。
innodb_autoinc_lock_mode = 2（interleaved，高併發自增鎖優化）。
關閉 unique_checks、foreign_key_checks、autocommit。

總結

INSERT 優化核心：減少語句解析/提交次數（批次）、減少索引維護開銷（緩衝區/延遲索引）、利用原生匯入工具。
選型口訣：
📦 文字檔大量匯入 → LOAD DATA + 關閉檢查
🔄 應用層批次寫入 → 多值 INSERT (1000~5000 行/批)
🔁 需去重/更新 → ON DUPLICATE KEY UPDATE
⚡ MyISAM 批次 → 調大 bulk_insert_buffer_size
永遠在測試環境用真實資料量驗證，再上生產。