在數據庫應用程序開發期間，開發人員傾向於在表上定義大量索引，以保證每個查詢能夠良好地執行。當應用程序開發完成並且數據庫投入到生產環境中之後，存在過多索引將導致數據庫性能下降。大量的索引意味著數據庫系統在執行 update、insert 和 delete (uid) 操作時要做更多的工作。另外，存在大量索引時，runstats 和 reorg 等常規維護活動的運行也顯著變慢。因此， 要實現最佳的數據庫性能，最關鍵的是確定哪些索引對查詢執行是必要的，哪些是可以安全刪除而且不影響查詢運行時的。本文解釋了幾種方法，db2® for linux®, unix®, and windows® (db2 luw) 數據庫管理員（dba）可以通過它們識別未使用和不常使用的索引。
db2 design advisor (db2advis)
db2 design advisor 是識別未使用索引的簡單方法。您可以使用 design advisor 分析一組 sql 語句及其執行頻率。分析完成之後，design advisor 返回以下信息：
額外的索引定義，它們通過減少查詢的運行時間改進查詢性能
db2 用於查詢執行的現有索引的名稱
db2 未用於查詢執行的現有索引的名稱
讓我們看看基於 db2 測試數據庫 sample 的 design advisor 使用場景。
首先，通過在命令行上執行 db2sampl 命令創建 sample 數據庫，如清單 1 所示。
清單 1. 創建 sample 數據庫
db2sampl
在調用 design advisor 之前，您必須運行 explain.ddl 腳本來創建它需要的 explain 表。您可以在 db2 安裝的 sqllib/misc 子目錄中找到該腳本。您僅需運行該腳本一次。清單 2 顯示了運行該腳本所需的命令。
清單 2. 創建 explain 表
db2 "connect to sample"
db2 -tf "c:\program files\ibm\sqllib\misc\explain.ddl"
當調用 design advisor 時，您必須定義需要對其進行分析的 sql 語句組。您可以通過幾種方法定義 sql 語句組。其中一種方法是提供一個包含需要分析的 sql 語句的文本文件。例如，清單 3 顯示了一個名為 queries.sql 樣例文件，它包含一組 sql 語句。
清單 3. db2 design advisor 輸入文件 queries.sql
-- employee queries
--#set frequency 123
select lastname from employee where empno = '000010';
--#set frequency 456
select lastname from employee where workdept = 'a00';
-- department queries
--#set frequency 245
select deptname from department where deptno = 'a00';
--#set frequency 678
select deptname from department where mgrno = '000010';
-- project queries
--#set frequency 345
select projname from project where projno = 'op1000';
在創建 design advisor 需要分析的 sql 語句文本文件時，您需要考慮以下事項：
注釋行以 -- 開始，design advisor 將忽略它們。
如果知道的話，您可以選擇指定每個查詢的大致執行頻率。這能夠幫助 design advisor 更好地估計創建新的索引能夠對數據庫性能有多大的改進。為此，在查詢定義之前添加另外一個以 --#set frequency 開始的行（盡管這些行以 -- 開始，但不被看作是注釋行）。
文本文件中的每個 sql 語句都必須以分號結束（;）。
sql 語句可能包含參數標記。可以存在參數標記，因為 design advisor 並沒有實際執行 sql 語句，它僅為它們計算其他可行的訪問計劃。
對於所有沒有完全限定的表和視圖（不帶有顯式模式），調用方的授權 id 被用作默認的模式。在調用 design advisor 時，您也可以選擇使用 -q 選項覆蓋默認模式。
清單 4 顯示了一個樣例命令，您可以使用它調用 design advisor 和將 queries.sql 指定為包含需要分析的 sql 語句的輸入文件。
清單 4. 使用輸入文件 queries.sql 調用 db2 data studio
db2advis -d sample -i queries.sql -m i -l -1 -t 0 -o db2advis_file.txt
以上命令的選項包含一些含義：
-d，數據庫名。
-i，包含需要分析的 sql 語句的輸入文件。
-m，除了相關的索引之外，design advisor 還可以推薦 materialized query tables (mqts)、multidimensional clustering (mdc) 表和對分區表進行重新分區。在這個示例場景中，-m 的值為 i，這表示僅建議使用相關的索引（同時也是默認值）。
-l，新的索引或 mqt 的定義的大小限制。值 -1 表示沒有大小限制。
-t，執行 design advisor 的運行時限制。值 0 表示沒有時間限制。
-o，輸出文件，用於寫 design advisor 的建議的位置。
design advisor 創建的輸出文件包含 3 個部分：建議的額外索引、建議的現有索引和未使用索引。對於這個場景，您主要對列出未使用索引的部分感興趣。清單 5 顯示了未使用索引部分的示例。
清單 5. db2 design advisor 輸出中的未使用索引部分
-- unused existing indexes
-- ============================
-- drop index "fechner "."xdept3";
-- drop index "fechner "."xproj2";
-- ===========================
對於這個場景，design advisor 表明索引 xdept3 和 xproj2 未被使用。不過要記住，該建議僅基於 design advisor 分析的一組 sql 語句（包含在輸入文件中的 sql 語句）。除了輸入文件中的 sql 語句之外，還可能存在其他依賴於識別到的索引改善執行時間的語句。因此，在刪除識別到的未使用索引之前，一定要小心檢查和認真考慮依賴它們的其他 sql 語句。
包含 sql 語句的輸入文件有一個代替辦法，即讓 design advisor 計算數據庫的 sql 語句緩存的內容。為此，您需要在 db2advis 上使用 -g 選項，如清單 6 所示。
清單 6. 調用 db2 design advisor 計算 sql 語句緩存內容
db2advis -d sample -g -m i -l -1 -t 0 -o db2advis_sql_cache.txt
以這種方式調用 design advisor 比手動地創建包含 sql 語句的輸入文件要省事得多。不過，您仍然要記住，分析得到的結果僅取決於 sql 語句緩存的當前內容。調用 data studio 時未駐留在緩存中的內容將未被分析。因此，當使用 sql 語句緩存作為輸入時，應該在當天的不同時間點運行 design advisor，以計算更大范圍的 sql 語句。
回頁首
db2pd 實用程序
db2pd 實用程序通常用於問題診斷和監控，但也可用於查詢關於數據庫活動的信息。使用 db2pd 能夠獲得的信息之一是：自從數據庫活動之後索引被訪問的次數。清單 7 顯示了如何使用 db2pd 實用程序為數據庫中的所有表上的索引獲取該信息。
清單 7. 使用 db2pd 實用程序查詢表和索引的度量指標
db2pd -db sample -tcbstats all -file db2pd_tab_all.txt
以上調用 db2pd 的選項包含以下含義：
-db 數據庫名
-tcbstats all 顯示所有表和索引度量指標
-file 輸出文件
如果您想要限制 db2pd 實用程序的輸出，讓它僅顯示某些表及其索引，那麼使用 -tcbstats 選項指定表空間 id 和表 id。為此，您首先需要在 syscat.tables 目錄視圖上執行 select 語句來確定表空間 id 和表 id，如清單 8 所示。
清單 8. 查詢數據庫目錄以確定表的表空間 id 和表 id
db2 "select tbspaceid, tableid
from syscat.tables
where tabschema = 'fechner' and tabname = 'department'"
清單 9 顯示了來自類似於以上的表空間 id 和表 id 查詢的樣例結果。
清單 9. 表空間 id 和表 id 查詢的結果集
tbspaceid tableid
--------- -------
2 5
1 record(s) selected.
在確定您需要查詢的表的表空間 id 和表 id 之後，您可以將 db2pd 輸出僅限制到該表。為此，您可以在 -tcbstats 選項之後包含 tbspaceid 和 tableid 子選項，如清單 10 所示。
清單 10. 將 db2pd -tcbstats 輸出限制到指定的表
db2pd -db sample -tcbstats all tbspaceid=2 tableid=5 -file db2pd_tab_dept.txt
清單 11 顯示了來自類似於以上的 db2pd 查詢的樣例結果集。
清單 11. db2pd 實用程序顯示的索引度量指標
database partition 0 -- database sample -- active -- up 0 days 00:20:34
tcb table information:
address tbspaceid tableid partid mastertbs mastertab tablename schemanm
objclass datasize lfsize lobsize xmlsize
0x797df2b8 2 5 n/a 2 5 department fechner
perm 1 0 0 0
tcb table stats:
address tablename schemanm scans udi rtsudi pgreorgs
nochgupdts reads fscrupdates inserts updates deletes ovflreads ovflcrtes
rowscomp rowsuncomp cclogreads storebytes bytessaved
0x797df2b8 department fechner 0 0 0 0
0 951 0 0 0 0 0 0
0 0 0 - -
tcb index information:
address inxtbspace objectid partid tbspaceid tableid mastertbs mastertab
tablename schemanm iid indexobjsize
0x797e0330 2 5 n/a 2 5 2 5
department fechner 3 8
0x797e0330 2 5 n/a 2 5 2 5
department fechner 2 8
0x797e0330 2 5 n/a 2 5 2 5
department fechner 1 8
tcb index stats:
address tablename iid partid emppgdel rootsplits bndrysplts pseuemptpg
empgmkdusd scans ixonlyscns keyupdates inclupdats nonbndspts pgallocs merges
pseudels delclean intnodspl
0x797e0330 department 3 n/a 0 0 0 0
0 0 0 0 0 0 1 0
0 0 0
0x797e0330 department 2 n/a 0 0 0 0
0 678 0 0 0 0 1 0
0 0 0
0x797e0330 department 1 n/a 0 0 0 0
0 245 0 0 0 0 1 0
0 0 0
與索引使用相關的結果集部分包含在 tcb index stats 部分中。以上的樣例輸出顯示了 department 表有 3 個索引。這些索引是通過索引 id 來表示的（iid 列），而不是索引名。scans 列顯示了自數據庫激活之後索引被訪問的次數：
id 1 索引被訪問了 245 次。
id 2 索引被訪問了 678 次。
id 3 索引從未被訪問過。
因為索引 id 3 從未被訪問過，所以可以推斷該索引為未使用或很少使用的索引。要獲得 id 3 索引的名稱，請使用 select 語句查詢 syscat.indexes 目錄視圖，如清單 12 所示。
清單 12. 根據索引 id (iid) 查詢數據庫目錄以確定索引名
db2 "select indschema, indname
from syscat.indexes
where tabschema = 'fechner' and tabname = 'department' and iid = 3"
清單 13 顯示了以上的 select 語句的輸出。
清單 13. 索引名查詢的結果集
indschema indname
------------------------------ ------------------------------
fechner xdept3
1 record(s) selected.
除了 scans 列之外，db2pd 實用程序的 tcb index stats 部分還有一個 ixonlyscns 列，它顯示僅掃描索引的數量。僅掃描索引是未包含表訪問的索引訪問，因為索引本身包含了所有被請求的數據。因此，如果 ixonlyscns 計數器不為 0，您就必須將 scans 列和 ixonlyscns 列的計數合並起來，以得到索引被訪問的總次數。
在討論 design advisor 時已經提到，在解釋索引使用信息時必須格外小心。使用 db2pd 方法時也要持有這種態度。某個索引可能要在特定時間點才被使用，但這不一定就意味著該索引永遠未被使用。因此，在決定是否刪除當前未使用的索引時，一定要仔細考慮。如果您決定刪除索引，那麼首先要保存它的 create index 語句，以便在需要時輕松地重新創建它。
回頁首
db2 workload manager (wlm)
db2 design advisor 和 db2pd 實用程序都是在 db2 version 8 中引入的，並且在 db2 version 9 中仍可使用。從 db2 v9.5 開始，您還可以使用 db2 workload manager (wlm) 來收集索引使用信息。一般而言，wlm 是一個收費的 db2 特性，必須單獨購買使用許可證。不過，作為 wlm 的一部分的擴展數據庫監控選項可以免費使用，不需要單獨的使用許可證。這個小節不介紹 db2 wlm，而是通過一個逐步的說明，教您使用特定於 wlm 的事件監控器和兩個 perl 腳本（db2 安裝的一部分）收集和計算索引使用信息。
像 design advisor 一樣，wlm 要求您在使用它之前創建 explain 表（參見 清單 2 了解關於運行 explain.dll 腳本創建 explain 表的細節）。同樣，您必須首先運行位於 sqllib/misc 子目錄中的 wlmevmon.ddl 腳本。該腳本創建並啟用特定於 wlm 的事件監控器：db2activities、db2statistics 和 db2thresholdviolations。這些事件監控器將收集到的數據寫到表中。默認情況下，這些表在表空間 userspace1 中創建。如果您想讓表位於另一個表空間中，可以在運行之前對腳本進行相應的修改。清單 14 顯示了運行 wlmevmon.ddl 腳本的命令。
清單 14. 創建特定於 wlm 的事件監控器
db2 -tf "c:\program files\ibm\sqllib\misc\wlmevmon.ddl"
默認情況下，事件監控器創建之後帶有 autostart 選項，這表示在數據庫下一次被激活時將自動運行事件監控器。在使用首次創建的事件監控器之前，您必須首先禁用數據庫然後再激活它，或手動運行事件監控器，如清單 15 所示。
清單 15. 啟動特定於 wlm 的事件監控器
db2 "set event monitor db2activities state 1"
db2 "set event monitor db2statistics state 1"
db2 "set event monitor db2thresholdviolations state 1"
要理解下一個命令，您需要基本了解服務類 的 wlm 概念。wlm 允許您將具有不同響應時間需求的工作負載分配給不同的服務類和服務子類。從 db2 version 9.5 開始，服務類的使用緊密地集成到 db2 引擎中。即使 wlm 沒有顯式地使用服務類（即數據庫管理員沒有定義任何服務類），所有用戶事務都是在預定義的服務類上下文中執行的。預定義的服務類為 sysdefaultuserclass，它的對應子類為 sysdefaultsubclass。特定於 wlm 的事件監控器通常針對特定的服務類或服務子類進行激活。這意味著它們僅為這些服務類或服務子類收集數據。
在這個樣例場景中，您將為服務子類 sysdefaultsubclass 激活 db2actvities 事件監控器。這樣，您將為所有用戶事務收集監控器信息，因為沒有定義特定於應用程序的服務類。由於不存在特定於應用程序的服務類，所有用戶事務都在默認服務類及其子類的上下文中執行。清單 16 顯示了為默認的服務類及其子類激活事件監控器的命令。
清單 16. 為默認的服務類及其子類激活事件監控器
db2 "alter service class sysdefaultsubclass under sysdefaultuserclass
collect activity data on all database partitions with details"
監控器數據收集應該包含一個時間框架，您期望在這段時間內發生普通數據庫活動（例如，每個工作日的 9 a.m. 到 5 p.m.）。在監控器數據收集完成之後，需要禁用事件監控器，如清單 17 所示。
清單 17. 禁用針對默認服務類及其子類的事件監控器
db2 "alter service class sysdefaultsubclass under sysdefaultuserclass
collect activity data none"
運行 perl 樣例程序
在運行 perl 樣例程序之前，必須為 perl dbi 安裝 perl 解釋器和 dbd::db2 驅動程序。關於如何獲得最新的驅動程序的信息，請查看 db2 perl database interface for luw。
為了根據收集到的 wlm 事件監控器數據計算索引的使用，db2 提供了兩個 perl 腳本，它們是 wlmhist.pl 和 wlmhistrep.pl。您可以在 db2 安裝目錄下的 samples\perl 子目錄中找到它們。這兩個腳本都要求使用數據庫名、數據庫用戶名和密碼作為輸入參數。
首先運行 wlmhist.pl 腳本。它將讀取從事件監控器表執行的 sql 語句，然後對每個語句運行 explain 實用程序以生成對應的訪問計劃。在讀取 sql 語句之後，腳本將從 explain 表提取訪問計劃信息並將其寫到名為 wlmhist 的表中（如果還沒有 wlmhist 表，就創建它）。
接下來，運行 wlmhistrep.pl 腳本以計算儲存在 wlmhist 表中的數據。該腳本創建一個包含關於表和索引使用的細節的報告。當運行 wlmhistrep.pl 時，除了數據庫名、數據庫用戶名和密碼之外，您還必須指定生成的報告文件的名稱。
清單 18 顯示了運行 perl 腳本的命令，該腳本幫助您根據 wlm 事件監控器數據計算索引使用。
清單 18. 執行 wlmhist.pl 和 wlmhistrep.pl perl 腳本
cd "c:\program files\ibm\sqllib\samples\perl"
perl wlmhist.pl sample userid password
perl wlmhistrep.pl sample userid password wlmhistrep.txt
wlmhistrep.pl 生成的報告包含 4 個部分：已使用（命中）表、未使用（未命中）表、已使用（命中）索引和未使用（未命中）索引。對於這個場景，您主要對未使用索引部分感興趣。清單 19 顯示了一個樣例報告。
清單 19. wlmhistrep.pl 生成的樣例報告
indexes hit report for database sample
_______________________________________________________
table name table schema object name object schema total hits
__________________ _______________ __________________ _______________ __________
...
department fechner pk_department fechner 245
department fechner xdept2 fechner 678
employee fechner pk_employee fechner 123
employee fechner xemp2 fechner 456
project fechner pk_project fechner 345
...
indexes not hit report for database sample
___________________________________________________________
table name table schema index name index schema index type
__________________ _______________ __________________ _______________ __________
...
department fechner xdept3 fechner reg
project fechner xproj2 fechner reg
...
indexes hit report 部分有一個 total hits 列，它表明索引被訪問的次數。從未被訪問過的索引顯示在 indexes not hit report 部分。
回頁首
mon_get_index 表函數
本文的 db2pd 實用程序 小節演示了如何使用 db2pd 實用程序獲取索引度量指標。不過，如果您使用的是 db2 version 9.7，可以通過查詢新的 mon_get_index 表函數獲取相同的信息。使用表函數取代 db2pd 實用程序簡化了結果計算，因為可以通過與 syscat.indexes 目錄視圖結合將索引 ids (iids) 轉換成索引名。清單 20 顯示了如何調用 mon_get_index 表函數的樣例。
清單 20. mon_get_index 表函數（文件名 mon_get_index.sql）的樣例調用
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,
syscat.indexes as si
where
mgi.tabschema = si.tabschema
and mgi.tabname = si.tabname
and mgi.iid = si.iid
order by
mgi.index_scans desc;
mon_get_index 期望接收以下參數：
表模式
表名
數據庫分區號
對於單分區數據庫，您可以對數據庫分區號僅指定 -1（當前分區）或 -2（所有分區），結果是一樣的。如果您省略了表名（即指定 null 或空字符串），那麼為指定的模式中的所有表返回信息。如果您省略了表名和表模式，那麼為數據庫中的所有表返回信息。
清單 21 顯示了來自類似於以上查詢的樣例結果。
清單 21. 來自 mon_get_index 表函數的結果
db2 -tf mon_get_index.sql
indschema indname index_scans index_only_scans
-------------------- -------------------- -------------------- --------------------
fechner xdept2 678 0
fechner pk_department 245 0
fechner xdept3 0 0
3 record(s) selected.
mon_get_index 表函數和 db2pd 實用程序提供相同的索引度量指標（掃描索引和僅掃描索引的數量）。不過，您可能發現，處理 mon_get_index 表函數返回的結果集比計算 db2pd 實用程序的輸出更加容易。
回頁首
結束語
未使用和很少使用的索引可能導致 uid 操作（update、insert 和 delete）和數據庫維護活動（比如 runstats 和 reorg）的運行時間更長。這些索引還占用表空間容器和備份映像中的存儲空間，您可以通過刪除這些索引來更好地利用它們所占用的空間。本文展示了幾種方法，用於識別在數據庫中定義的未使用和很少使用的索引。您可以根據通過這些方法獲得的信息刪除不需要的索引。刪除不必要的索引能夠顯著改善 db2 luw 數據庫的性能。
