INSERT 執行全生命週期拆解
一條 INSERT 從發送到持久化，經歷六大階段：語句準備 → 傳輸列值 → 尋找存放頁面 → 緩衝池/I/O/頁清除 → 鎖管理 → 日誌記錄 → 提交 → 索引維護。各階段皆有優化切入點。
階段一：語句準備策略
| 模式 | 適用場景 | 優化建議 |
|---|
| 動態（CLP/CLI SQLExecDirect） | 臨時腳本、低頻操作 | 無特別優化空間 |
| 顯式準備 | 高頻重複執行 | 使用 PREPARE / SQLPrepare / prepareStatement 重用執行計畫 |
| 靜態 SQL | 嵌入式 SQL、預編譯應用 | 編譯期綁定，執行期零解析開銷，效能最佳 |
階段二：批量傳輸列值——減少網路往返
- VALUES 多列語法：
INSERT INTO t VALUES (..), (..), (..)（支援多行單語句）。
- JDBC 批次處理：
addBatch() / executeBatch()。
- CLI 陣列輸入：參考
sqllib/samples/cli/tbload.c 範例。
- Compound SQL：將多條語句打包單次送達（需評估版本支援度）。
- 編碼頁一致：客戶端與資料庫使用相同代碼頁，避免隱式轉換開銷。
階段三：尋找存放頁面——三大演算法選擇
| 演算法 | 觸發條件 | 特性 | 優化建議 |
|---|
| FSCR 搜尋 | 預設模式 | 掃描自由空間控制記錄，預設搜尋 5 頁 | 設 DB2MAXFSCRSEARCH 降低搜尋頁數（I/O 受限環境效果明顯） |
| APPEND 模式 | ALTER TABLE ... APPEND ON | 直接寫入表尾，零搜尋開銷 | 批量載入最快，但不利後續叢集索引查詢 |
| 叢集索引導向 | 存在 Clustering Index | 嘗試鄰近鍵值頁面，失敗退回 FSCR | 預留 PCTFREE + REORG；或載入前 Drop 索引、載入後重建 |
階段四：緩衝池、I/O 與頁清除策略
- SMS 表空間：執行
db2empfa 啟用區段預分配，減少檔案系統擴展頻率。
- DMS 表空間：區段大小調整對 INSERT 影響有限。
- 積極非同步頁清除：確保緩衝池常有乾淨頁可用。
CHNGPGS_THRESH 從 60 降至 5~10，觸發更早清除。- 啟用
DB2_USE_ALTERNATE_PAGE_CLEANING=ON (V8 FP4+)。
- I/O 硬體層面：多容器跨磁碟、快速存儲、寫入快取、避免 RAID-5（除非高階存儲如 Shark）。
階段五：鎖開銷與並發控制
- 列鎖 CPU 開銷大：每列 X 鎖獲取/釋放成本可觀。
- 表鎖替代方案：
ALTER TABLE ... LOCKSIZE TABLE（持久生效，直到改回 ROW）。LOCK TABLE IN EXCLUSIVE MODE（立即生效，Commit 釋放，需在每次 Commit 後重發）。- 調低
LOCKLIST / MAXLOCKS 觸發自動升級。
- 上述方法測試顯示耗時減少約 3%。
- V8 FP4+：
DB2_EVALUNCOMMITTED=YES 可減少謂詞掃描時的鎖獲取。
- 頻繁 Commit 釋放鎖緩解衝突，但需平衡 Commit 開銷（見階段七）。
階段六：日誌記錄優化
- LOGBUFSZ：預設 32KB (8 頁)，批量載入建議調大（如 256KB~1MB）。
- NOT LOGGED INITIALLY (NLI)：
ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY
- 優點：工作單元內 INSERT 不記錄日誌，極大減少日誌 I/O。
- 缺點 1：語句失敗導致表標記不可存取，需 Drop 重建。
- 缺點 2：Commit 必須等待所有髒頁刷盤，若無積極頁清除反而更慢。
- 組合建議：NLI + 積極頁清除 = 顯著加速；單用 NLI 風險高。
- 日誌檔案放置：獨立磁碟、多磁碟條帶化、考慮 Raw Device、避免 RAID-5。
階段七：Commit 頻率黃金法則
- 自動 Commit 關閉（CLI/CLP 預設開啟，必須關閉）。
- 每 1000 列 Commit 一次：效能較逐列 Commit 提升 ~10 倍。
- 超過 1000 列效能邊際效益遞減，但失敗回滾成本上升。
MINCOMMIT > 1 可批次化日誌寫入，但批量載入場景通常不建議開啟，保持 MINCOMMIT=1。
階段八：索引維護——隱形效能殺手
- 每列 INSERT 需更新所有索引（含主鍵），成本 = 樹遍歷 CPU + 頁讀寫 I/O。
- 最差場景：隨機鍵值 + 索引頁超出緩衝池 → 大量隨機 I/O。
- 策略：
- 空表載入：先 INSERT 後 CREATE INDEX。
- 非空表大量載入：載入前 DROP INDEX，載入後重建（當插入量 > 表現有 10-20% 時最優）。
- 索引表空間配大緩衝池。
- 預排序輸入資料依索引鍵值，將隨機 I/O 轉為循序 I/O。
總結：批量載入優化清單
- 使用靜態 SQL 或顯式 PREPARE
- 批次傳輸（多列 VALUES / JDBC Batch / CLI Array）
- 啟用 APPEND 模式或預留 PCTFREE
- 調大 LOGBUFSZ、啟用替代頁清除、降低 CHNGPGS_THRESH
- 關閉自動 Commit，每 ~1000 列 Commit
- 大量載入時 Drop/重建索引
- 日誌與資料分離存放，優化存儲子系統
系統性優化而非單點調整，才能將 DB2 INSERT 吞吐量推向硬體極限。
