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