MySQL大批量插入性能優化五大黃金法則

法則一：LOAD DATA INFILE 替代 INSERT —— 20倍性能提升

# 文本文件準備：字段Tab分隔，行換行符，NULL用\N
LOAD DATA LOCAL INFILE '/data/huge_import.txt' 
    INTO TABLE target_table 
    FIELDS TERMINATED BY '\t' 
    LINES TERMINATED BY '\n'
    (col1, col2, col3);

• 繞過SQL解析、優化器、Binlog格式化（ROW格式除外）
• 直接由存儲引擎批量寫入數據頁
• 配合 bulk_insert_buffer_size (默認8M) 調大至 256M-1G，MyISAM專用，InnoDB無效

法則二：多值 INSERT 批量提交 —— 5-10倍提升

-- 惡例：逐行提交，每次網絡往返+事務提交
INSERT INTO t VALUES (1); INSERT INTO t VALUES (2); ...

-- 優例：單語句千行批次
INSERT INTO t (col1, col2) VALUES 
    (1, 'a'), (2, 'b'), (3, 'c') ... (1000, 'x');

• 單次網絡往返、單次解析、單次事務提交
• 批次大小建議 500-5000 行，避免超過 max_allowed_packet
• 應用層構建批次邏輯：緩衝隊列達閾值或定時刷新

法則三：MyISAM 並行插入 —— 讀寫不阻塞

[mysqld]
concurrent_insert=2  # 1: 尾部無洞插入(默認)  2: 尾部允許有洞插入  0: 禁用

• MyISAM表級鎖特性：SELECT加共享鎖，INSERT加排他鎖，默認互斥
• concurrent_insert=2 允許在表尾部有刪除空洞時仍並行插入
• 前提：表無DELETE操作或僅尾部刪除，中間無碎片
• InnoDB 行級鎖天然支持並行插入，無需配置

法則四：INSERT DELAYED 異步寫入 —— 高併發寫入削峰

INSERT DELAYED INTO log_table (msg) VALUES ('event_data');

• 客戶端立即返回，Server將行放入隊列，單線程批量寫入
• 適用：日志、監控、統計等不需即時可見、允許丟失(崩潰時隊列數據丟失)的場景
• 多客戶端寫入自動合併為大批次，減少索引刷盤次數
• MySQL 5.6+ 棄用，8.0 移除，替代方案：應用層異步隊列(Kafka/RocketMQ) + 批量消費入庫

法則五：顯式鎖表/事務控制 —— 減少索引刷盤

非事務表

LOCK TABLES target_table WRITE;
INSERT INTO target_table ...;  -- 批量插入
INSERT INTO target_table ...;
UNLOCK TABLES;  -- 單次索引刷盤

• 鎖定期間禁止讀寫，僅適用維護窗口或離線導數據
• 單條INSERT ... VALUES (...),(...),... 已隱含單次提交，無需額外鎖表

事務表

START TRANSACTION;
INSERT INTO target_table ...;
INSERT INTO target_table ...;
COMMIT;  -- 單次Redo Log刷盤 (innodb_flush_log_at_trx_commit=1)

• 嚴禁用 LOCK TABLES 替代事務，InnoDB鎖機制不同，會導致死鎖或語義錯誤
• 大事務拆分：每 1000-5000 行提交一次，避免 Undo Log 膨脹、鎖持有過久

綜合優化清單

禁用索引/外鍵加速導入模板

SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;
ALTER TABLE huge_table DISABLE KEYS;  -- MyISAM專用

LOAD DATA INFILE ... INTO TABLE huge_table ...;

ALTER TABLE huge_table ENABLE KEYS;   -- MyISAM專用，重建索引
SET UNIQUE_CHECKS=1;
SET FOREIGN_KEY_CHECKS=1;

InnoDB無DISABLE KEYS，但可通過SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0;跳過約束檢查，導入後重新開啟觸發檢查。