SQL 性能調優核心原則與 DB2 實戰優化指南：從寫法到執行計劃的系統性提升

調優鐵律：測量 → 診斷 → 單一變更 → 驗證 → 固化

「過早優化是萬惡之源」 — 唐納德·克努斯。無基線不調優，無執行計劃不改寫，無統計信息不分析。

DB2 優化器架構與決策鏈

SQL 語句
    │
    ▼
解析器 (Parser) → 語義檢查器 (Semantic Checker) 
    │
    ▼
查詢重寫器 (Query Rewrite) 
    ├── 視圖合併、子查詢轉連接、常量折疊、謂詞下推
    ├── 物化視圖 (MQT) 匹配路由
    └── 星型連接轉換、分區剪枝
    │
    ▼
成本基礎優化器 (Cost-Based Optimizer - CBO)
    ├── 統計信息 (RUNSTATS) → 基數估算
    ├── 索引匹配 → 訪問路徑選擇 (IXSCAN/TBSCAN/IXAND)
    ├── 連接方法選擇 (NLJOIN/MSJOIN/HSJOIN)
    ├── 並行度決定 (INTRA_PARALLEL)
    └── 成本模型計算 (CPU/IO/COMM/MEMORY)
    │
    ▼
執行計劃生成 (Access Plan) → 計劃緩存 (Package Cache)
    │
    ▼
執行引擎 (Runtime Engine) → 結果集返回

寫法層面優化：讓優化器「看懂」你的意圖

1. 謂詞構造：索引友好 vs 索引殺手

2. 連接寫法優化

-- ❌ 隱式連接 (舊語法，難維護、易錯)
SELECT * FROM a, b WHERE a.id = b.id AND a.type = 'X';

-- ✅ 顯式 ANSI JOIN (清晰語義、優化器更友好)
SELECT * FROM a INNER JOIN b ON a.id = b.id WHERE a.type = 'X';

-- 連接順序提示 (DB2 不支持 HINT，但可通過優化器配置影響)
-- 1. 確保連接列有索引
-- 2. 統計信息準確 (RUNSTATS WITH DISTRIBUTION)
-- 3. 小表驅動大表: 將過濾性強的表放在 FROM 最前 (優化器通常會自動重排)

3. 子查詢重寫策略

4. 排序與分頁優化

-- ❌ 全表排序後取前 N (大表災難)
SELECT * FROM huge_table ORDER BY created_at DESC FETCH FIRST 10 ROWS ONLY;

-- ✅ 索引支持的 Top-N
-- 前提: 存在 INDEX ON huge_table(created_at DESC)
-- 優化器自動使用索引順序掃描 + 早期停止

-- 深分頁優化 (OFFSET 100000 LIMIT 10)
-- 方案 A: 游標分頁 (記住上一頁最大 ID)
SELECT * FROM huge_table WHERE id > :last_max_id ORDER BY id FETCH FIRST 10 ROWS ONLY;

-- 方案 B: 覆蓋索引 + 子查詢
SELECT * FROM huge_table 
WHERE id IN (SELECT id FROM huge_table ORDER BY created_at DESC OFFSET 100000 LIMIT 10)
-- 內層只掃描索引鍵，外層回表 10 行

索引設計黃金法則

1. 索引列順序：等值 → 範圍 → 排序 → 覆蓋

-- 查詢: WHERE a=1 AND b>2 AND c=3 ORDER BY d
-- 最佳索引: (a, c, b, d)  -- a/c 等值優先，b 範圍，d 排序
-- 覆蓋索引: (a, c, b, d) INCLUDE (e, f) -- 包含 SELECT 列，避免回表

2. 索引類型選擇

3. 索引維護策略

• RUNSTATS 節奏：OLTP 每日增量 (RUNSTATS ON TABLE ... WITH DISTRIBUTION ON KEY COLUMNS)，數倉每周全量
• REORG 觸發：REORGCHK 指標 F1/F2/F3 > 100 或 REORG_RECOMMENDATION = '*'
• 在線 REORG：REORG TABLE ... ALLOW WRITE ACCESS (V9.7+)，業務零停機
• 廢棄索引清理：db2pd -db x -indexes 找出 INDEXSCANS=0 且存在 > 30 天的索引

執行計劃深度解讀

獲取計劃三大法

-- 1. EXPLAIN 模式 (靜態/動態)
db2 "EXPLAIN PLAN FOR SELECT * FROM t WHERE id=1"
db2exfmt -d dbname -1 -o plan.txt -g TIC  # 格式化輸出

-- 2. db2pd 動態計劃緩存 (運行中 SQL)
db2pd -db dbname -dynamic -apinfo -file /tmp/dyn.sql

-- 3. 活動監控 (V9.7+)
CREATE EVENT MONITOR mon_act FOR ACTIVITIES WRITE TO TABLE;
SET EVENT MONITOR mon_act STATE 1;
-- 運行目標 SQL
SELECT * FROM ACTIVITYSTMT_MON_ACT WHERE STMT_TEXT LIKE '...';
-- 解析 XML 計劃

關鍵算子識別與優化對策

統計信息：優化器的「眼睛」

-- 基礎收集 (快速)
RUNSTATS ON TABLE schema.table WITH DISTRIBUTION ON KEY COLUMNS AND DETAILED INDEXES ALL;

-- 全量收集 (準確，耗時長)
RUNSTATS ON TABLE schema.table WITH DISTRIBUTION ON ALL COLUMNS AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;

-- 系統級自動收集 (STMM 配合)
UPDATE DB CFG USING AUTO_RUNSTATS ON AUTO_STMT_STATS ON AUTO_STATS_VIEWS ON;

-- 統計信息導出/導入 (跨環境復現)
CALL SYSPROC.EXPORT_STATS('DBNAME', '/tmp/stats', 'C');
CALL SYSPROC.IMPORT_STATS('DBNAME', '/tmp/stats', 'C');

配置參數對性能的杠桿作用

調優實戰檢查清單 (按 ROI 排序)

1. 統計信息：RUNSTATS 全量 + 分佈 — 成本最低、收益最高
2. 索引缺失：db2advis / EXPLAIN 找 TBSCAN/SORT → 創建索引
3. 謂詞重寫：消除函數列、前導通配符、隱式轉換 — 零成本、即時生效
4. 內存參數：SORTHEAP/LOCKLIST/PCKCACHESZ — 動態生效、立竿見影
5. 並行度：INTRA_PARALLEL + DFT_DEGREE=ANY — 大查詢加速明顯
6. 分區/MDC：大表範圍查詢 — 架構級優化、需規劃
7. MQT/物化視圖：重複聚合查詢 — 預計算換空間換時間
8. 應用邏輯：批量操作、減少往返、連接池 — 代碼層面、收益最大

總結

SQL 性能調優是 統計信息準確性、索引設計合理性、寫法 SARGable 化、配置參數適配性 四大支柱的系統工程。DB2 提供了完整的工具鏈 (RUNSTATS/db2advis/EXPLAIN/db2pd/db2top/活動監控) 支持科學調優。核心心法：

信數據、信計劃、信工具、信迭代。
每次只改一處，驗證效果，固化最佳實踐。
調優無終點，只有在當前業務負載下的「足夠好」。

1. 上一篇文章： DB2 跨版本遷移關鍵技巧：db2look 版本差異與 DDL 完整提取實戰
2. 下一篇文章： Visual C# 連接 DB2 實戰：OLE DB Provider 架構下的數據訪問完整指南

DB2 教學

企業雲端策略：在 Amazon EC2 公有雲部署 WebSphere sMash 與 DB2 Express-C

前言 在公有雲、私有雲與混合雲三大雲端模型中，公有雲擁有最成

DB2 卸載後資料庫恢復急救指南：目錄殘留與 catalog 指令重掛載

前言 誤執行 db2unins 卸載 DB2 實例後，資料庫

DB2 基礎：深入掌握 DB2 UDB 系統編目查詢技巧

前言 資料庫的所有核心資訊幾乎都儲存在系統編目這個元資料倉庫

Oracle 全角/半角數字互轉實戰：TO_SINGLE_BYTE 與 TO_MULTI_BYTE 詳解

Oracle 全角/半角數字互轉實戰：TO_SINGLE_B