MySQL 大數據量快速插入與語句性能優化實戰指南

事務與鎖策略

對於事務表，使用 BEGIN / COMMIT 替代 LOCK TABLES 可顯著提升插入速度。雖然鎖定會增加單查詢最大等待時間，但整體批量插入耗時可降低約 40%。

實戰案例

Connection 1: 1000 inserts
Connections 2,3,4: 各 1 insert
Connection 5: 1000 inserts

無鎖時，連接 2/3/4 會先完成；加鎖後雖順序改變，但總耗時縮短 40% 左右。

批量提交與鎖釋放節奏

連續插入/更新超過 5 次時，加鎖可獲得更好整體性能。大量插入時，每 1000 行執行一次 UNLOCK TABLES 釋放鎖，讓其他線程訪問，兼顧性能與並發。

INSERT vs LOAD DATA INFILE

LOAD DATA INFILE 速度遠快於 INSERT，即使使用上述優化策略。對 MyISAM 表，增加 key_buffer_size 可擴大鍵緩衝區，加速兩者。

INSERT 語法完整形式

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ON DUPLICATE KEY UPDATE col_name=expr, ...]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ON DUPLICATE KEY UPDATE col_name=expr, ...]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)]
SELECT ... [ON DUPLICATE KEY UPDATE col_name=expr, ...]

DELAYED 延遲插入深度解析

工作原理

• 客戶端發送 INSERT DELAYED 後，服務端將行放入隊列，立即返回狀態，客戶端可繼續工作
• 有讀請求時，插入隊列掛起，優先響應讀操作
• 無讀請求時，批量寫入隊列數據，直到隊列為空

關鍵限制

• 僅支持 VALUES 列表形式，SELECT 和 ON DUPLICATE KEY UPDATE 會被忽略
• 無法用 LAST_INSERT_ID() 獲取自增 ID
• 從庫複製中被忽略，避免主從數據不一致
• 風險：隊列僅駐留內存，kill -9 或崩潰會導致未刷盤數據丟失

優化建議總結

1. 大批量導入優先用 LOAD DATA INFILE
2. 事務表用 BEGIN/COMMIT 替代表鎖
3. 適度使用 DELAYED 異步寫入（接受數據丟失風險）
4. 調大 key_buffer_size 服務 MyISAM 索引緩衝
5. 每 1000 行釋放一次鎖，平衡吞吐與並發