DB2 INSERT 效能極致優化：從單行插入到 LOAD 替代方案的完整指南

INSERT 處理全鏈路拆解

1. 客戶端準備語句（動態 SQL 需編譯，靜態 SQL 預編譯）
2. 組裝列值發送至服務器
3. 服務器定位目標頁（FSCR 搜尋 / APPEND 模式 / 叢集索引）
4. 緩衝池預留位置（舊頁讀盤、新頁分配）
5. 頁內格式化、加 X 行鎖
6. 寫入日誌緩衝區
7. 提交：強制日誌刷盤、釋放鎖
8. 額外開銷：索引維護、觸發器、約束檢查

替代方案：LOAD 与 IMPORT

實測：LOAD 比優化過的陣列 INSERT 快 2 倍，比未優化快 10 倍；FROM CURSOR 比 INSERT...SELECT 快 ~20%。

INSERT 優化七大維度

1. 語句準備：參數標記複用

-- 錯誤：每行獨立編譯
INSERT INTO t VALUES (1, 'a');
INSERT INTO t VALUES (2, 'b');

-- 正確：預處理一次，重複執行
INSERT INTO t VALUES (?, ?);

靜態 SQL 用主機變數同理。可提升 數倍速度。

2. 減少網路往返：批量傳輸

• VALUES 多行：INSERT INTO t VALUES (1,'a'),(2,'b'),(3,'c')
• CLI 陣列插入：綁定陣列，SQL_ATTR_PARAMSET_SIZE=100，SQLExecute 一次插 100 行 → 耗時縮減 2.5 倍
• JDBC 批次：PreparedStatement.addBatch() + executeBatch()
• LOAD 到暫存表 → INSERT...SELECT：服務端搬運，零網路開銷
• Compound SQL：BEGIN ATOMIC ... END 或 CLI SQL_ATTR_CHAINING

3. 目標頁定位策略

4. 緩衝池、I/O 與頁清除

• SMS 表空間：執行 db2empfa 啟用區段預分配（建議 32 頁/區段）
• DMS 表空間：預分配空間，效能優於 SMS ~20%
• 積極非同步頁清除：CHNGPGS_THRESH=5（預設 60），啟用 DB2_USE_ALTERNATE_PAGE_CLEANING=ON (V8 FP4+)，NUM_IOCLEANERS >= 物理設備數
• 大緩衝池僅延遲 I/O，最終仍須落盤；多容器分佈不同磁碟、避免 RAID5、用寫緩存

5. 鎖開銷與並發

• 預設行級 X 鎖，CPU 開銷可觀
• 表鎖替代：ALTER TABLE LOCKSIZE TABLE、LOCK TABLE IN EXCLUSIVE MODE、調小 LOCKLIST/MAXLOCKS 觸發升級 → 耗時減 3%
• 缺點：獨佔表，僅 UR 隔離級可讀
• 減輕衝突：增大 LOCKLIST/MAXLOCKS、應用端用 UR、DB2_EVALUNCOMMITTED=YES (FP4+)、高頻提交

6. 日誌寫入優化

• 日誌與資料表分離不同磁碟
• OS 層將日誌分條帶多磁碟
• 考慮原始裝置，避免 RAID5
• NOT LOGGED INITIALLY 表、BLOB/CLOB 不寫日誌（但備份庫仍分配空間）