過多索引是生產環境性能隱形殺手：每次 UPDATE/INSERT/DELETE 需同步維護所有索引，RUNSTATS/REORG 耗時線性增長，備份體積膨脹。核心課題：精準識別「真未用」與「極少用」索引，並在業務風險可控前提下清理。本文系統介紹四大識別方法、工具實操與治理策略。
# 1. 建立樣本庫與 Explain 表 (一次性)
db2sampl
db2 -tf $HOME/sqllib/misc/EXPLAIN.DDL
# 2. 準備 SQL 負載檔 (queries.sql)
--#SET FREQUENCY 123
SELECT LASTNAME FROM EMPLOYEE WHERE EMPNO = '000010';
--#SET FREQUENCY 456
SELECT LASTNAME FROM EMPLOYEE WHERE WORKDEPT = 'A00';
...
# 3. 執行分析
db2advis -d sample -i queries.sql -m I -l -1 -t 0 -o db2advis_out.txt
# 參數：-m I (僅索引) -l -1 (無大小限制) -t 0 (無時間限制)
-- UNUSED EXISTING INDEXES
-- ============================
-- DROP INDEX "FECHNER"."XDEPT3";
-- DROP INDEX "FECHNER"."XPROJ2";
-- ============================
⚠️ 關鍵警示：建議僅基於輸入負載集。生產環境可能存在未納入負載的關鍵查詢 (月結報表、年審腳本、應急手工查詢)。刪除前必須全量 SQL 審計。
db2advis -d sample -g -m I -l -1 -t 0 -o db2advis_cache.txt
# -g 從套件緩存抓取 SQL
需在不同時段多次執行覆蓋完整業務週期。
Scans：索引被掃描次數 (啟動後累計)IxOnlyScans：純索引掃描次數 (無需回表，含蓋索引)
db2pd -db sample -tcbstats all -file db2pd_all.txt
# 1. 獲取 TBSPACEID, TABLEID
db2 "SELECT TBSPACEID, TABLEID FROM SYSCAT.TABLES WHERE TABSCHEMA='FECHNER' AND TABNAME='DEPARTMENT'"
# 結果: 2, 5
# 2. 針對性收集
db2pd -db sample -tcbstats all tbspaceid=2 tableid=5 -file db2pd_dept.txt
TCB Index Stats:
IID=1 Scans=245 IxOnlyScans=0 → 總計 245
IID=2 Scans=678 IxOnlyScans=0 → 總計 678
IID=3 Scans=0 IxOnlyScans=0 → 總計 0 ← 疑似未用
db2 "SELECT INDSCHEMA, INDNAME FROM SYSCAT.INDEXES WHERE TABSCHEMA='FECHNER' AND TABNAME='DEPARTMENT' AND IID=3"
# 結果: FECHNER XDEPT3
授權提示：WLM 核心功能需付費，但擴展資料庫監控選項免費，含本文使用的事件監視器。
# 1. 建立 Explain 表 (同方法一)
# 2. 建立並啟用 WLM 事件監視器
db2 -tf $HOME/sqllib/misc/wlmevmon.ddl
# 建立 DB2ACTIVITIES, DB2STATISTICS, DB2THRESHOLDVIOLATIONS 表 (預設 USERSPACE1)
# 3. 手動啟動 (或待下次激活自動啟動)
db2 "SET EVENT MONITOR DB2ACTIVITIES STATE 1"
db2 "SET EVENT MONITOR DB2STATISTICS STATE 1"
db2 "SET EVENT MONITOR DB2THRESHOLDVIOLATIONS STATE 1"
# 4. 針對預設服務子類啟用活動收集
db2 "ALTER SERVICE CLASS SYSDEFAULTSUBCLASS UNDER SYSDEFAULTUSERCLASS COLLECT ACTIVITY DATA ON ALL DATABASE PARTITIONS WITH DETAILS"
# 5. 觀測窗口 (如工作日 9:00-17:00) 後停用
db2 "ALTER SERVICE CLASS SYSDEFAULTSUBCLASS UNDER SYSDEFAULTUSERCLASS COLLECT ACTIVITY DATA NONE"
cd $HOME/sqllib/samples/perl
perl wlmhist.pl sample userid password
perl wlmhistrep.pl sample userid password wlmhistrep.txt
INDEXES HIT REPORT:
TABLE SCHEMA INDEX TOTAL_HITS
DEPARTMENT FECHNER PK_DEPARTMENT 245
DEPARTMENT FECHNER XDEPT2 678
EMPLOYEE FECHNER PK_EMPLOYEE 123
...
INDEXES NOT HIT REPORT:
TABLE SCHEMA INDEX TYPE
DEPARTMENT FECHNER XDEPT3 REG
PROJECT FECHNER XPROJ2 REG
...
優勢：基於實際執行計劃 (EXPLAIN)，捕獲真實負載全貌，含頻率、成本上下文。
SELECT
SUBSTR(SI.INDSCHEMA,1,30) AS INDSCHEMA,
SUBSTR(SI.INDNAME,1,30) AS INDNAME,
MGI.INDEX_SCANS,
MGI.INDEX_ONLY_SCANS
FROM TABLE(MON_GET_INDEX('FECHNER','DEPARTMENT',-2)) AS MGI
JOIN SYSCAT.INDEXES SI
ON MGI.TABSCHEMA=SI.TABSCHEMA
AND MGI.TABNAME=SI.TABNAME
AND MGI.IID=SI.IID
ORDER BY MGI.INDEX_SCANS DESC;
參數：Schema, Table, Partition (-1 當前, -2 所有)。省略表名 = 全模式；全省略 = 全庫。
INDSCHEMA INDNAME INDEX_SCANS INDEX_ONLY_SCANS
FECHNER XDEPT2 678 0
FECHNER PK_DEPARTMENT 245 0
FECHNER XDEPT3 0 0 ← 未用
優勢：純 SQL 介面，易整合監控儀表板、自動化報表、無需外部工具/腳本。
| 維度 | db2advis | db2pd | WLM 事件監視器 | MON_GET_INDEX |
|---|---|---|---|---|
| 適用版本 | V8+ | V8+ | V9.5+ (免費監控組件) | V9.7+ |
| 數據來源 | 負載檔/緩存 SQL | 運行時計數器 | 實際執行活動 (EXPLAIN) | 運行時計數器 (表函數) |
| 負載覆蓋 | 輸入負載決定 | 啟動至今 | 觀測窗口內全負載 | 啟動至今/重置後 |
| 輸出豐富度 | 含新增建議、成本估算 | 基礎計數 | 含頻率、成本、計劃上下文 | 基礎計數 + 易查詢 |
| 部署複雜度 | 低 | 極低 | 中 (需監視器、Perl、Explain) | 極低 (單一 SQL) |
| 推薦場景 | 專項調優專案、遷移評估 | 快速巡檢、單表診斷 | 生產環境全負載審計、合規 | 日常監控儀表板、自動化 |
db2look -d <db> -t <tab> -i <user> -w -o idx_rollback.sql 導出索引 DDL建立 「負載畫像 → 定期審計 → 風險評估 → 受控清理 → 效果驗證」 的索引生命週期管理機制。將索引數量納入資料庫健康度 KPI (如 Index/Table Ratio、Unused Index %), 讓精簡成為常態，而非救火動作。
