DB2 系統效能優化全維度指南：設計、查詢、並發、架構四大支柱深度實踐

效能優化評價三維指標

• 吞吐量：單位時間處理交易/查詢數。
• 響應時間：單一請求從提交到返回的延遲。
• 並行能力：系統在資源爭用下維持 SLA 的最大併發數。

優化需從資料庫設計、查詢重寫、並發控制、客戶/伺服器架構四大支柱系統推進。

支柱一：資料庫設計 —— 奠定效能基因

1. 深度理解業務領域

不熟悉業務的設計師，縱使精通 DB2 內核，也無法產出最優實體模型。業務實體識別、流程邊界、數據生命週期、訪問熱點分佈 是設計輸入的核心元數據。

2. 規範化與反規範化的動態平衡

• 規範化（3NF/BCNF）：消除冗余、窄化行寬、單頁容納更多行 → 掃描加速、單表查詢極快。
• 代價：多表 JOIN 增加 CPU/I/O，百萬級訂單明細表若嚴格 3NF，單次查詢可能需連接 10+ 表、耗時數小時。
• 實戰策略：核心交易表保持 3NF 保證寫入一致性；查詢/報表高頻維度屬性（客戶名、產品類別、銷售區域）適度冗余至事實表，用空間換時間，JOIN 數從 10 降至 2-3，查詢從小時級降至秒級。

3. 數據分條帶化（水平/垂直分區）

4. 資料類型選擇原則

• 主鍵/外鍵優先整數（INT/BIGINT）：比較/連接/索引成本遠低於字串。
• 大文本用 BLOB/CLOB：指標存表內，實體可外置檔案系統，減緩緩衝池壓力。
• UDT（用戶自訂類型）：封裝業務語義（如 CREATE TYPE EMAIL AS VARCHAR(254) WITH WEAK TYPE RULES），提升模型自描述能力。

5. 索引設計黃金法則

• 頻繁 JOIN 但非 FK 的欄位 → 建索引。
• GROUP BY/ORDER BY 高頻欄位 → 建索引（避免排序溢寫磁碟）。
• 高基數（Distinct 值多）謂詞欄位 → 建索引；低基數（性別、狀態碼）不建，優化器傾向全表掃描。
• 多欄位排序/過濾 → 複合索引（等值欄位前、範圍欄位後）。
• 代價意識：每新增一索引，INSERT/UPDATE/DELETE 額外維護一 B+ 樹，權衡讀寫比決定保留與否。

支柱二：查詢優化 —— 從 SQL 文本到執行計畫的精準干預

1. 排序消除與簡化

利用索引順序天然滿足 ORDER BY/GROUP BY，避免顯式排序。排序不可避免時：縮小排序列寬度、限制排序行數（FETCH FIRST n ROWS ONLY）、增加 SORTHEAP/SHEAPTHRES 記憶體配額。

2. 主鍵整數化

字串主鍵比較開銷約為整數的 5-10 倍；資料量達千萬級時，改用 BIGINT 代理鍵可顯著降低連接/索引遍歷成本。

3. 子查詢改寫為 JOIN

反模式：WHERE col IN (SELECT ...) 導致外層每行觸發內層全表掃描（N×M 複雜度）。
正模式：INNER JOIN ... ON ... 使優化器能選擇 Hash Join / Merge Join，複雜度降為 O(N+M)。
原則：能 JOIN 絕不子查詢；子查詢不可避免時，內層盡早過濾（WHERE 下推）。

4. 通配符陷阱與改寫

LIKE 'M%' 可走索引範圍掃描；LIKE '%M%' 或 LIKE '_M_' 必觸發全表掃描。改寫為範圍條件：col >= 'M' AND col < 'N' 充分利用索引順序。

5. DISTINCT 濫用治理

DISTINCT 產生工作表 + 排序 + 去重，I/O 與 CPU 雙重打擊。替代方案：GROUP BY（可利用索引分組）、應用層去重、或資料模型層面保證唯一性（UK 約束）。

6. 負邏輯消除

!=、<>、NOT IN、NOT EXISTS 導致全表掃描。改寫為正向條件或 LEFT JOIN ... WHERE right.key IS NULL（反連接模式）。

7. 臨時表謹慎使用

磁碟物化中間結果，引入 I/O 延遲；分散式環境下還涉及進程同步。優先考慮：CTE（WITH 子句）、內聯視圖、物化查詢表（MQT）/ 物化視圖。

8. 預存程序：編譯一次、多次複用

複雜業務邏輯封裝為 Stored Procedure，首次執行生成最優計畫並缓存，後續調用跳過解析/優化/編譯階段。配合 DB2 Query Patroller 對比多計畫代價、Performance Monitor 監控 I/O/排序/同步讀寫指標，持續迭代優化。

支柱三：並發控制 —— 鎖粒度與隔離級別的藝術

• 鎖粒度階梯：表空間 → 表 → 頁 → 行。粒度越細，並發度越高，但鎖結構開銷越大。
• 避免表級鎖：長跑查詢加表鎖 = 全系統凍結。解法：視圖隔離（查詢打視圖，視圖定義含 WITH UR）、LOCKSIZE ROW 強制行鎖、LOCK TABLE ... IN SHARE MODE 短時共享鎖。
• 死鎖預防：統一存取順序（按主鍵遞增）、縮短事務邊界、避免用戶交互嵌入事務。

支柱四：客戶/伺服器架構優化

• 連線池：應用層複用物理連線，消除頻繁連接/斷開開銷。
• 預取/批次提取：FETCH FIRST n ROWS ONLY + OPTIMIZE FOR n ROWS 引導優化器選擇最適合的存取路徑。
• 非同步提交：高寫入場景啟用 DB2_ASYNC_COMMIT=ON（需業務容忍極小概率丟失）。
• 網路協定調優：TCPIP 緩衝區 TCP_SEND_BUF/TCP_RECV_BUF、關閉 Nagle 算法（TCP_NODELAY=ON）降低小包延遲。

持續優化閉環

1. 基線建立：收集典型負載下的吞吐/延遲/資源使用基線。
2. 瓶頸定位：db2top、db2pd -agents -locks -transactions、MONREPORT.DBSUMMARY 定位 Top SQL/等待事件。
3. 變更實施：單一變更（索引、參數、SQL 改寫）→ 回歸測試 → 生產灰度發布。
4. 效果驗證：對比基線，確認指標改善且無退化。
5. 知識沉澱：將優化案例轉化為團隊開發規範與審核清單。

將四大支柱融入 DB2 全生命週期管理，讓效能優化從「救火式被動響應」轉型為「架構級主動治理」，支撐業務持續高速增長。