MySQL 插入效能優化實戰：MyISAM 與 InnoDB 專屬策略

通用優化原則

• **批次插入**：單條 INSERT 改為多值 INSERT INTO t VALUES (...), (...), (...)，減少網路往返與解析開銷
• **大量導入用 LOAD DATA INFILE**：比 INSERT 快 20 倍以上
• **硬體基礎**：NVMe SSD、足夠記憶體、獨立 Redo Log/Binlog 磁碟

MyISAM 專屬優化

並發插入配置

concurrent_insert=2  # 允許尾部並發插入，不阻塞讀取

鎖調度平衡

# 方案一：降低寫優先級
low_priority_updates=1

# 方案二：寫鎖計數閾值
max_write_lock_count=1

批次插入緩衝

bulk_insert_buffer_size=64M  # 預設 8MB，大量導入時調大

非空表大量導入技巧

ALTER TABLE t DISABLE KEYS;  -- 停用索引更新
LOAD DATA INFILE 'data.txt' INTO TABLE t;
ALTER TABLE t ENABLE KEYS;   -- 重建索引 (一次性排序建立，極快)

空表導入無需 DISABLE KEYS (MyISAM 空表導入會先寫資料再建索引)。

延遲插入

INSERT DELAYED INTO t VALUES (...);

資料入隊列立即返回，背景執行緒批次寫入。注意：無法獲取自增 ID、唯一鍵衝突延後發現、崩潰風險。

InnoDB 專屬優化

關閉檢查 (導入階段)

SET unique_checks=0;
SET foreign_key_checks=0;
SET autocommit=0;
-- 大量導入操作
SET autocommit=1;
SET foreign_key_checks=1;
SET unique_checks=1;

交易批次提交

每 1000-5000 行 COMMIT 一次，避免單一巨大交易導致 Undo Log 膨脹與鎖持有過久。

主鍵順序寫入

InnoDB 按主鍵聚簇索引儲存，**主鍵遞增插入**為順序寫入 (追加頁)，亂序主鍵導致頁分裂、碎片、隨機 I/O。建議使用 AUTO_INCREMENT 或有序 UUID (如 ULID/TSID)。

Redo Log 與 Doublewrite Buffer

• innodb_log_file_size 調大 (如 1GB) 減少 Checkpoint 壓力
• innodb_flush_log_at_trx_commit=2 導入階段可暫時放寬 (風險：崩潰丟失 1 秒資料)
• innodb_doublewrite=OFF 導入階段可關閉 (風險：頁損壞)

對比總結表

避坑指南

• 不要在生產高峰期大量導入，預留維護窗口
• 導入前備份，驗證資料完整性 (行數、校驗和、關鍵業務查詢)
• 大表導入考慮分區表、分批次、並行導入不同分區
• 監控 Innodb_row_lock_waits、Table_locks_waited 確認無異常阻塞