DB2 系統效能調優全方位指南：設計、查詢、並發與監控

資料庫設計階段優化策略

1. 深度理解業務系統

不熟悉業務的設計人員，縱使資料庫技術深厚，也難設計出高效能系統。業務知識是效能調優的基石。

2. 正規化與反正規化平衡

• 正規化優勢：減少冗餘、縮小資料列、單頁容納更多列、加速表掃描、提升單表查詢效能
• 正規化劣勢：多表查詢需大量 JOIN，增加 CPU/I/O 開銷
• 實務策略：在邏輯有效性前提下，適度冗餘關鍵欄位（如訂單表直接保留客戶/業務員資訊），以空間換時間，大幅降低百萬級資料查詢從數小時到秒級

3. 資料分區策略

• 水平分區：依日期/地區/部門切分，分離熱/冷資料
• 垂直分區：將寬表拆分為常用/不常用欄位群組，加速掃描

4. 資料類型選擇原則

• 大型文字欄位優先使用 BLOB 而非 CHAR(1024)，BLOB 僅存指標，實體內容可外置，減少資料庫體積提升速度
• 善用 UDT (User Defined Datatypes) 定義業務語義類型

5. 索引設計黃金法則

代價：索引降低 INSERT/UPDATE/DELETE 效能，佔用額外磁碟空間與維護成本。

查詢優化實戰技巧

1. 排序最佳化

• 利用索引順序產出結果可省略排序
• 不可省略情況：索引缺欄位、GROUP BY/ORDER BY 順序與索引不符、跨表排序
• 對策：正確建索引、合併資料表（適度反正規化）、縮小排序範圍

2. 主鍵型別選擇

整數型主鍵效能遠優於字串型。字串比較開銷大幅高於整數，資料量大時微小差異放大為顯著效能落差。

3. 子查詢改寫為 JOIN

-- 子查詢：效能差，每個外層值掃描一次內層表
SELECT CreateUser FROM Order WHERE OrderNo IN (SELECT OrderNo FROM OrderDetail WHERE Price=0.5)

-- JOIN 改寫：掃描比例優化
SELECT CreateUser FROM Order, OrderDetail WHERE Order.OrderNo=OrderDetail.OrderNo AND Price=0.5

原則：盡量避免子查詢，不可避免時在子查詢中過濾更多資料列。

4. 通配符查詢優化

-- 低效：LIKE 'M___' 即使有索引仍全表掃描
-- 高效：利用索引範圍查詢
SELECT * FROM Order WHERE CreateUser > 'M' AND CreateUser < 'N'

5. 避免 DISTINCT、負邏輯、臨時表

• DISTINCT 產生工作表並排序，大幅增加 I/O
• !=、<>、NOT IN 導致全表掃描
• 臨時表涉及磁碟 I/O 且分散式環境有同步問題

6. 儲存程序效能優勢

• 首次執行建立查詢計劃並快取，後續直接從高速緩存執行
• 省去優化編譯階段，提升效率與系統利用率
• 善用 Query Patroller 比較不同計劃代價，Performance Monitor 監控整體效能指標

並發控制與鎖管理

• 鎖粒度：表空間 > 表 > 列 > 索引。粒度大→開銷小、並發低；粒度小→開銷大、並發高
• 複雜查詢避免表級鎖，善用視圖隔離直接操作
• 長時間查詢鎖定熱門表將阻塞整個系統