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