MySQL 大量資料快速插入優化：LOCK TABLES、DELAYED、LOAD DATA、批次提交實戰

核心原則

• 減少索引維護開銷：大量導入前 DROP INDEX，導入後 CREATE INDEX (MyISAM 極顯著，InnoDB 5.7+ 也有效)。
• 批次提交：每 1000~5000 行 COMMIT 一次，平衡 Redo Log 刷盤與鎖持有時間。
• 鎖表策略：LOCK TABLES tbl WRITE 可消除並發衝突，整體提速 ~40% (官方基準)。

INSERT 語法變體

-- 多值單語句 (最高效)
INSERT INTO tbl (a,b) VALUES (1,2),(3,4),(5,6)...;

-- SET 語法
INSERT INTO tbl SET a=1, b=2, c=3;

-- SELECT 導入
INSERT INTO tbl (a,b) SELECT a,b FROM src_tbl;

INSERT DELAYED 詳解 (MyISAM/MEMORY/ARCHIVE)

• 客戶端立即返回，資料進隊列，閒暇時寫入。
• 讀請求到來時隊列掛起，讀優先。
• 限制：
  ❌ 不支援 INSERT DELAYED ... SELECT
  ❌ 不支援 ON DUPLICATE KEY UPDATE
  ❌ 無法獲取 LAST_INSERT_ID()
  ❌ 從庫忽略 DELAYED
  ⚠️ 未寫入磁碟前崩潰會丟失資料

適用：日誌、統計、非關鍵資料，寫入峰值平滑。

LOAD DATA INFILE：極速導入王者

LOAD DATA INFILE '/path/file.txt'
INTO TABLE tbl
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n'
(a,b,c);

• 繞過 SQL 解析層，直接寫入儲存引擎。
• 配合 key_buffer_size (MyISAM) / innodb_buffer_pool_size (InnoDB) 調大，速度比 INSERT 快 **10-20 倍**。
• 需 FILE 權限，受 secure_file_priv 限制。

InnoDB 專屬優化

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
-- 大量 INSERT/LOAD DATA
SET foreign_key_checks=1;
SET unique_checks=1;
COMMIT;

• 關閉唯一/外鍵檢查，導入後重開並驗證。
• innodb_flush_log_at_trx_commit=2 臨時調整 (風險：崩潰丟 1 秒資料)。
• innodb_log_file_size 調大 (如 1GB) 減少 Checkpoint 壓力。

實戰核對清單

1. 目標表無觸發器 / 外鍵 / 複雜檢查約束。
2. 資料檔預處理：字符集一致、欄位順序對齊、轉義字元處理。
3. 大表分批次檔案，單檔 10-50 萬行，便於並行/重試。
4. 導入前 SET SESSION sql_log_bin=0 (非主庫) 避免 Binlog 膨脹。
5. 導入後 ANALYZE TABLE 更新統計資訊。

結語

大量插入優化核心三板斧：**LOAD DATA INFILE > 批次多值 INSERT > INSERT DELAYED**。InnoDB 場景配合關閉檢查、調大 Log Buffer、批次提交，可達到每秒百萬行寫入量級。切記：生產環境必須先在預發驗證資料完整性與回滾預案。