【IT168 技術文檔】本文先對 DB2 提供的幾種用于提高查詢性能的相關工具和命令進行介紹，然后根據筆者的工作經驗介紹一些常用的技巧和方法來提高查詢性能。主要集中于如何創建和維護索引、改寫查詢以及改變查詢的實現方式，相關內容都將通過實例加以說明。
隨著 DB2 應用的逐漸增多，越來越多的數據庫開發人員在項目開發過程中都會遇到查詢過于復雜，導致性能難以接受的問題。本文將主要從一個數據庫開發者的角度介紹幾種常用的方法來提高 DB2 查詢的性能，而并不討論如何通過配置 DB2 的各項參數以及調整服務器環境等方式來提高整個數據庫性能的方法。系統配置等工作屬于 DBA 的工作范疇，在一般的項目開發中，這對于開發人員都是透明的。本文先對 DB2 提供的幾種用于提高查詢性能的相關工具和命令進行介紹，然后根據筆者的工作經驗介紹一些常用的技巧和方法來提高查詢性能。主要集中于如何創建和維護索引、改寫查詢以及改變查詢的實現方式，相關內容都將通過實例加以說明。
我們將著重介紹如何使用 Visual Explain 和 db2expln 查看動態查詢的存取計劃。讀者可以查閱 DB2 Info Center獲得有關查看靜態查詢存取計劃的內容。
DB2 Visual Explain
DB2 提供了非常直觀有效的方法來查看查詢的存取計劃。DB2 Visual Explain 能夠獲得可視化的查詢計劃，而 db2expln 命令則可以獲得文本形式的查詢計劃。有了查詢計劃，我們就可以有針對的對查詢進行優化。根據查詢計劃找出代價最高的掃描 ( 表掃描，索引掃描等 ) 和操作 (Join，Filter，Fetch 等 )，繼而通過改寫查詢或者創建索引消除代價較高的掃描或操作來優化查詢。
DB2 提供了多種方法來得到可視化查詢計劃。
- 通過 DB2 Control Center 獲得可視化查詢計劃。如圖 1：
圖 1. 可視化查詢計劃
點擊”Explain SQL”后輸入要進行分析的查詢語句以及查詢標號和標簽，點擊 Ok 按鈕便可得到可視化的查詢計劃。此時，查詢計劃會被存儲在系統的 Explain 表中。用戶可以通過圖 1 中的”Show Explained Statements History”命令獲得存儲在 Explain 表中的所有查詢計劃。
- 通過 Command Editor( 在 DB2 8.2 版本之前叫做 Command Center) 獲得可視化的查詢計劃。如圖 2：
圖 2. 獲得可視化的查詢計劃
在主窗口輸入查詢并連接數據庫后，點擊圖中所示的按鈕即可得到可視化的查詢計劃，如圖 3：
圖 3. 查詢計劃結果
在圖 3 所示的查詢計劃中，還可以點擊圖示中的每個節點來察看詳細的統計信息。譬如雙擊節點”FETCH(13) 21,959.75” 后將會彈出如圖 4 所示的對話框：
圖 4. 詳細的統計信息
圖 4 中的統計信息主要包括此 FETCH 操作的總代價，CPU，I/O 以及獲得結果集中的第一行的代價。在這里，timerons 是結合了 CPU 和 I/O 代價的成本單位。此外，圖 4 中還收集了其他相關信息。譬如此操作讀取了哪個表的哪些列，每個謂詞的選擇度 (selectivity)，使用了多少 buffer 等等。
db2exfmt
db2exfmt 命令能夠將 Explain 表中存儲的存取計劃信息以文本的形式進行格式化輸出。db2exfmt 命令將各項信息更為直觀的顯示，使用起來更加方便。命令如清單 1 所示：
清單 1. db2exfmt 命令
| db2exfmt -d <db_name> -e <schema> -g T -o <output> -u <user> <password> -w <timestamp>
|
db2expln
db2expln 是命令行下的解釋工具，和前面介紹的 Visual Explain 功能相似。通過該命令可以獲得文本形式的查詢計劃。命令如清單 2 所示 :
清單 2. db2expln 命令
| db2expln -d <db_name> -user <user> <password> -stmtfile <sql.file> -z @ -output <output> -g
|
db2expln 將存取計劃以文本形式輸出，它只提供存取計劃中主要的信息，并不包含每一個操作占用多少 CPU、I/O、占用 Buffer 的大小以及使用的數據庫對象等信息，方便閱讀。但是 db2expln 也會將各項有關存取計劃的信息存入 Explain 表中，用戶可以使用 db2exfmt 察看詳細的格式化文本信息。
db2advis
db2advis 是 DB2 提供的另外一種非常有用的命令。通過該命令 DB2 可以根據優化器的配置以及機器性能給出提高查詢性能的建議。這種建議主要集中于如何創建索引，這些索引可以降低多少查詢代價，需要創建哪些表或者 Materialized Query Table(MQT) 等。命令如清單 3 所示：
清單 3. db2advis 命令
| db2advis -d <db_name> -a <user>/<password> -i <sql.file> -o <output>
|
通過 -i 指定的 SQL 文件可以包含多個查詢，但是查詢必須以分號分隔。這與 db2expln 命令不同，db2expln 可以通過 -z 參數指定多個查詢之間的分隔符。用戶可以把某一個 workload 中所使用的所有查詢寫入 SQL 文件中，并在每個查詢之前使用”--#SET FREQUENCY <num>”為其指定在這個 workload 中的執行頻率。db2advis 會根據每個查詢在這個 workload 的頻率指數進行權衡來給出索引的創建建議，從而達到整個 workload 的性能最優。
db2batch
前面介紹的工具和命令只提供了查詢的估算代價，但有些時候估算代價和實際的執行時間并不是完全呈線形關系，有必要實際執行這些查詢。db2batch 就是這樣一個 Benchmark 工具，它能夠提供從準備到查詢完成中各個階段所花費地具體時間，CPU 時間，以及返回的記錄。命令如清單 4 所示：
清單 4. db2batch 命令
| db2batch -d <db_name> -a <user>/<password> -i <time_condition> -f <sql.file> -r <output>
|
對于執行 db2batch 時一些詳細的設置可以通過 -o 參數指定，也可以在 SQL 文件中指定，譬如本例中在 SQL 文件中使用了下面的配置參數 :
--#SET ROWS_FETCH -1 ROWS_OUT 5 PERF_DETAIL 1 DELIMITER @ TIMESTAMP
其中 ROWS_FETCH 和 ROWS_OUT 定義了從查詢的結果集中讀取記錄數和打印到輸出文件中的記錄數，PERF_DETAIL 設置了收集性能信息的級別，DELIMITER 則指定了多個查詢間的間隔符。