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

MySQL INSERT 效能調優深度解析：從單行耗時分解到批次、延遲、鎖表、LOAD DATA 實戰技巧

編輯：MySQL 資料庫相關知識

單行 INSERT 耗時分解（相對單位）

階段	相對耗時	說明
連線建立	3	TCP 握手、認證、線程分配
發送查詢	2	網路傳輸、協議封裝
解析優化	2	Parser、Resolver、Optimizer
寫入記錄	1 × 行大小	資料複製、Undo Log、Redo Log
索引維護	1 × 索引數	B+ Tree 插入、分裂、合併
提交/關閉	1	Binlog 寫入、鎖釋放、連線清理

表大小以 N log N 影響索引插入速度（B+ Tree 特性）。

加速 INSERT 核心技巧

1. 多值單語句（批次插入）

INSERT INTO t (a,b) VALUES (1,2), (3,4), (5,6), ...;
-- 比循環執行單行 INSERT 快 5-10 倍（減少網路往返、解析優化、提交開銷）

2. INSERT DELAYED（異步寫入，MyISAM/MEMORY/ARCHIVE）

INSERT DELAYED INTO t (a,b) VALUES (1,2);
-- 客戶端立即返回，Server 排隊寫入，適合日誌、統計類非即時業務
-- InnoDB 不支援，5.6+ 棄用，建議改用應用層批次佇列

3. MyISAM 並發插入

若表無刪除產生的空洞，SELECT 與 INSERT 可並發（資料附加在檔案尾部）。

4. LOAD DATA INFILE（大量導入王者，快 20 倍）

LOAD DATA INFILE '/path/data.txt' INTO TABLE t
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

極致優化流程（百萬級以上）：

CREATE TABLE t (...) ENGINE=MyISAM;（或 InnoDB）
FLUSH TABLES;
myisamchk --keys-used=0 -rq /path/to/t —— **暫時關閉所有索引**
LOAD DATA INFILE ... —— 僅寫資料，無索引維護，**極快**
myisamchk -r -q /path/to/t —— **重建索引**（記憶體中建樹、一次寫盤、完美平衡）
FLUSH TABLES;

InnoDB 同理可用 ALTER TABLE t DISABLE KEYS / ENABLE KEYS（5.7+ 支援）。

5. 表鎖優化批次寫入

LOCK TABLES t WRITE;
INSERT INTO t VALUES ...;  -- 批次 1
INSERT INTO t VALUES ...;  -- 批次 2
UNLOCK TABLES;
-- 優勢：索引緩衝區僅在 UNLOCK 時刷盤一次，而非每條 INSERT 刷盤
-- 注意：會阻塞其他寫入/讀取，適合「大批次、低併發」維護窗口

效能對比參考（百萬行、InnoDB、SSD）

方式	耗時	適用場景
單行 INSERT 循環	~45 分鐘	極小量、極簡邏輯
多值批次 (1000 行/語句)	~3 分鐘	常規業務導入
LOAD DATA + 索引重建	~20 秒	大量初始化、ETL 全量同步

結語

INSERT 效能優化的核心公式：**減少網路往返（批次）、減少解析優化（預處理/多值）、減少索引維護開銷（延遲索引/LOAD DATA）、控制鎖粒度（鎖表/無空洞並發插入）**。根據資料量、業務即時性、併發需求，選擇「多值批次」或「LOAD DATA + 索引重建」兩大主流路徑，並配合 innodb_flush_log_at_trx_commit=2、innodb_buffer_pool_size 等參數調優，即可獲得數量級效能提升。