【IT168 技術(shù)文檔】本文先對(duì) DB2 提供的幾種用于提高查詢(xún)性能的相關(guān)工具和命令進(jìn)行介紹，然后根據(jù)筆者的工作經(jīng)驗(yàn)介紹一些常用的技巧和方法來(lái)提高查詢(xún)性能。主要集中于如何創(chuàng)建和維護(hù)索引、改寫(xiě)查詢(xún)以及改變查詢(xún)的實(shí)現(xiàn)方式，相關(guān)內(nèi)容都將通過(guò)實(shí)例加以說(shuō)明。
簡(jiǎn)介
隨著 DB2 應(yīng)用的逐漸增多，越來(lái)越多的數(shù)據(jù)庫(kù)開(kāi)發(fā)人員在項(xiàng)目開(kāi)發(fā)過(guò)程中都會(huì)遇到查詢(xún)過(guò)于復(fù)雜，導(dǎo)致性能難以接受的問(wèn)題。本文將主要從一個(gè)數(shù)據(jù)庫(kù)開(kāi)發(fā)者的角度介紹幾種常用的方法來(lái)提高 DB2 查詢(xún)的性能，而并不討論如何通過(guò)配置 DB2 的各項(xiàng)參數(shù)以及調(diào)整服務(wù)器環(huán)境等方式來(lái)提高整個(gè)數(shù)據(jù)庫(kù)性能的方法。系統(tǒng)配置等工作屬于 DBA 的工作范疇，在一般的項(xiàng)目開(kāi)發(fā)中，這對(duì)于開(kāi)發(fā)人員都是透明的。本文先對(duì) DB2 提供的幾種用于提高查詢(xún)性能的相關(guān)工具和命令進(jìn)行介紹，然后根據(jù)筆者的工作經(jīng)驗(yàn)介紹一些常用的技巧和方法來(lái)提高查詢(xún)性能。主要集中于如何創(chuàng)建和維護(hù)索引、改寫(xiě)查詢(xún)以及改變查詢(xún)的實(shí)現(xiàn)方式，相關(guān)內(nèi)容都將通過(guò)實(shí)例加以說(shuō)明。
DB2 提供的幾種相關(guān)工具和命令
我們將著重介紹如何使用 Visual Explain 和 db2expln 查看動(dòng)態(tài)查詢(xún)的存取計(jì)劃。讀者可以查閱 DB2 Info Center獲得有關(guān)查看靜態(tài)查詢(xún)存取計(jì)劃的內(nèi)容。
DB2 Visual Explain
DB2 提供了非常直觀有效的方法來(lái)查看查詢(xún)的存取計(jì)劃。DB2 Visual Explain 能夠獲得可視化的查詢(xún)計(jì)劃，而 db2expln 命令則可以獲得文本形式的查詢(xún)計(jì)劃。有了查詢(xún)計(jì)劃，我們就可以有針對(duì)的對(duì)查詢(xún)進(jìn)行優(yōu)化。根據(jù)查詢(xún)計(jì)劃找出代價(jià)最高的掃描 ( 表掃描，索引掃描等 ) 和操作 (Join，F(xiàn)ilter，F(xiàn)etch 等 )，繼而通過(guò)改寫(xiě)查詢(xún)或者創(chuàng)建索引消除代價(jià)較高的掃描或操作來(lái)優(yōu)化查詢(xún)。
DB2 提供了多種方法來(lái)得到可視化查詢(xún)計(jì)劃。
- 通過(guò) DB2 Control Center 獲得可視化查詢(xún)計(jì)劃。如圖 1：
圖 1. 可視化查詢(xún)計(jì)劃
點(diǎn)擊”Explain SQL”后輸入要進(jìn)行分析的查詢(xún)語(yǔ)句以及查詢(xún)標(biāo)號(hào)和標(biāo)簽，點(diǎn)擊 Ok 按鈕便可得到可視化的查詢(xún)計(jì)劃。此時(shí)，查詢(xún)計(jì)劃會(huì)被存儲(chǔ)在系統(tǒng)的 Explain 表中。用戶(hù)可以通過(guò)圖 1 中的”Show Explained Statements History”命令獲得存儲(chǔ)在 Explain 表中的所有查詢(xún)計(jì)劃。
- 通過(guò) Command Editor( 在 DB2 8.2 版本之前叫做 Command Center) 獲得可視化的查詢(xún)計(jì)劃。如圖 2：
圖 2. 獲得可視化的查詢(xún)計(jì)劃
在主窗口輸入查詢(xún)并連接數(shù)據(jù)庫(kù)后，點(diǎn)擊圖中所示的按鈕即可得到可視化的查詢(xún)計(jì)劃，如圖 3：
圖 3. 查詢(xún)計(jì)劃結(jié)果
在圖 3 所示的查詢(xún)計(jì)劃中，還可以點(diǎn)擊圖示中的每個(gè)節(jié)點(diǎn)來(lái)察看詳細(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)信息更為直觀的顯示，使用起來(lái)更加方便。命令如清單 1 所示：
清單 1. db2exfmt 命令
db2exfmt -d <db_name> -e <schema> -g T -o <output> -u <user> <password> -w <timestamp>
Example: db2exfmt -d test_db -e user -g T -o D:\temp\sql_1_result_db2exfmt.txt
-u user password -w l
Query:
sql_1.txt（附件中）
Results:
sql_1_result_db2exfmt.txt（附件中）
|
db2expln
db2expln 是命令行下的解釋工具，和前面介紹的 Visual Explain 功能相似。通過(guò)該命令可以獲得文本形式的查詢(xún)計(jì)劃。命令如清單 2 所示 :
清單 2. db2expln 命令
db2expln -d <db_name> -user <user> <password> -stmtfile <sql.file>
-z @ -output <output> -g
Example: db2expln -d test_db -user user password -stmtfile D:\temp\sql_1.txt
-z @ -output D:\temp\sql_1_result_db2expln.txt –g
Query:
sql_1.txt（附件中）
Results:
sql_1_result_db2expln.txt（附件中）
|
db2expln 將存取計(jì)劃以文本形式輸出，它只提供存取計(jì)劃中主要的信息，并不包含每一個(gè)操作占用多少 CPU、I/O、占用 Buffer 的大小以及使用的數(shù)據(jù)庫(kù)對(duì)象等信息，方便閱讀。但是 db2expln 也會(huì)將各項(xiàng)有關(guān)存取計(jì)劃的信息存入 Explain 表中，用戶(hù)可以使用 db2exfmt 察看詳細(xì)的格式化文本信息。
db2advis
db2advis 是 DB2 提供的另外一種非常有用的命令。通過(guò)該命令 DB2 可以根據(jù)優(yōu)化器的配置以及機(jī)器性能給出提高查詢(xún)性能的建議。這種建議主要集中于如何創(chuàng)建索引，這些索引可以降低多少查詢(xún)代價(jià)，需要?jiǎng)?chuàng)建哪些表或者 Materialized Query Table(MQT) 等。命令如清單 3 所示：
清單 3. db2advis 命令
db2advis -d <db_name> -a <user>/<password> -i <sql.file> -o <output>
Example: db2advis -d test_db -a user/password
-i D:\temp\sql_2.txt > D:\temp\sql_2_result_db2advis.txt
Query:
sql_2.txt（附件中）
Results:
sql_2_result_db2advis.txt（附件中）
|
通過(guò) -i 指定的 SQL 文件可以包含多個(gè)查詢(xún)，但是查詢(xún)必須以分號(hào)分隔。這與 db2expln 命令不同，db2expln 可以通過(guò) -z 參數(shù)指定多個(gè)查詢(xún)之間的分隔符。用戶(hù)可以把某一個(gè) workload 中所使用的所有查詢(xún)寫(xiě)入 SQL 文件中，并在每個(gè)查詢(xún)之前使用”--#SET FREQUENCY <num>”為其指定在這個(gè) workload 中的執(zhí)行頻率。db2advis 會(huì)根據(jù)每個(gè)查詢(xún)?cè)谶@個(gè) workload 的頻率指數(shù)進(jìn)行權(quán)衡來(lái)給出索引的創(chuàng)建建議，從而達(dá)到整個(gè) workload 的性能最優(yōu)。
db2batch
前面介紹的工具和命令只提供了查詢(xún)的估算代價(jià)，但有些時(shí)候估算代價(jià)和實(shí)際的執(zhí)行時(shí)間并不是完全呈線形關(guān)系，有必要實(shí)際執(zhí)行這些查詢(xún)。db2batch 就是這樣一個(gè) Benchmark 工具，它能夠提供從準(zhǔn)備到查詢(xú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>
Example: db2batch -d test_db -a user/password
-i complete -f D:\temp\sql_3.txt -r d:\temp\sql_3_result_db2batch.txt
Query:
sql_3.txt（附件中）
Results:
sql_3_result_db2batch.txt（附件中）
|
對(duì)于執(zhí)行 db2batch 時(shí)一些詳細(xì)的設(shè)置可以通過(guò) -o 參數(shù)指定，也可以在 SQL 文件中指定，譬如本例中在 SQL 文件中使用了下面的配置參數(shù) :
--#SET ROWS_FETCH -1 ROWS_OUT 5 PERF_DETAIL 1 DELIMITER @ TIMESTAMP
其中 ROWS_FETCH 和 ROWS_OUT 定義了從查詢(xún)的結(jié)果集中讀取記錄數(shù)和打印到輸出文件中的記錄數(shù)，PERF_DETAIL 設(shè)置了收集性能信息的級(jí)別，DELIMITER 則指定了多個(gè)查詢(xún)間的間隔符。
下面我們將從三個(gè)方面介紹一些提高查詢(xún)性能的方法。
創(chuàng)建索引
根據(jù)查詢(xún)所使用的列建立多列索引
建立索引是用來(lái)提高查詢(xún)性能最常用的方法。對(duì)于一個(gè)特定的查詢(xún)，可以為某一個(gè)表所有出現(xiàn)在查詢(xún)中的列建立一個(gè)聯(lián)合索引，包括出現(xiàn)在 select 子句和條件語(yǔ)句中的列。但簡(jiǎn)單的建立一個(gè)覆蓋所有列的索引并不一定能有效提高查詢(xún)，因?yàn)樵诙嗔兴饕辛械捻樞蚴欠浅Ｖ匾摹＿@個(gè)特性是由于索引的 B+ 樹(shù)結(jié)構(gòu)決定的。一般情況下，要根據(jù)謂詞的選擇度來(lái)排列索引中各列的位置，選擇度大的謂詞所使用的列放在索引的前面，把那些只存在與 select 子句中的列放在索引的最后。譬如清單 5 中的查詢(xún)：
清單 5. 索引中的謂詞位置
select add_date
from temp.customer
where city = 'WASHINGTON'
and cntry_code = 'USA';
|
對(duì)于這樣的查詢(xún)可以在 temp.customer 上建立 (city，cntry_code，add_date) 索引。由于該索引包含了 temp.customer 所有用到的列，此查詢(xún)將不會(huì)訪問(wèn) temp.customer 的數(shù)據(jù)頁(yè)面，而直接使用了索引頁(yè)面。對(duì)于包含多列的聯(lián)合索引，索引樹(shù)中的根節(jié)點(diǎn)和中間節(jié)點(diǎn)存儲(chǔ)了多列的值的聯(lián)合。這就決定了存在兩種索引掃描。回到清單 5 中的查詢(xún)，由于此查詢(xún)?cè)谛陆ㄋ饕牡谝涣猩洗嬖谥^詞條件，DB2 能夠根據(jù)這個(gè)謂詞條件從索引樹(shù)的根節(jié)點(diǎn)開(kāi)始遍歷，經(jīng)過(guò)中間節(jié)點(diǎn)最后定位到某一個(gè)葉子節(jié)點(diǎn)，然后從此葉子節(jié)點(diǎn)開(kāi)始往后進(jìn)行在葉子節(jié)點(diǎn)上的索引掃描，直到找到所有滿足條件的記錄。這種索引掃描稱(chēng)之為 Matching Index Scan。但是如果將 add_date 放在索引的第一個(gè)位置，而查詢(xún)并不存在 add_date 上的謂詞條件，那么這個(gè)索引掃描將會(huì)從第一個(gè)索引葉子節(jié)點(diǎn)開(kāi)始，它無(wú)法從根節(jié)點(diǎn)開(kāi)始并經(jīng)過(guò)中間節(jié)點(diǎn)直接定位到某一個(gè)葉子節(jié)點(diǎn)，這種掃描的范圍擴(kuò)大到了整個(gè)索引，我們稱(chēng)之為 Non-matching Index Scan。圖 5 顯示了 DB2 根據(jù)不同索引生成的存取計(jì)劃。
圖 5. 根據(jù)不同索引生成的存取計(jì)劃
根據(jù)條件語(yǔ)句中的謂詞的選擇度創(chuàng)建索引
因?yàn)榻⑺饕枰加脭?shù)據(jù)庫(kù)的存儲(chǔ)空間，所以需要在空間和時(shí)間性能之間進(jìn)行權(quán)衡。很多時(shí)候，只考慮那些在條件子句中有條件判斷的列上建立索引會(huì)也會(huì)同樣有效，同時(shí)節(jié)約了空間。譬如清單 5 中的查詢(xún)，可以只建立 (city，cntry_code) 索引。我們還可以進(jìn)一步地檢查條件語(yǔ)句中的這兩個(gè)謂詞的選擇度，執(zhí)行清單 6 中的語(yǔ)句檢查謂詞選擇度：
清單 6. 檢查謂詞選擇度
Queries:
1. select count(*) from temp.customer
where city = 'WASHINGTON'
and cntry_code = 'USA';
2. select count(*) from temp.customer
where city = 'WASHINGTON';
3. select count(*) from temp.customer
where cntry_code = 'USA';
Results:
1. 1404
2. 1407
3. 128700
|
選擇度越大，過(guò)濾掉的記錄越多，返回的結(jié)果集也就越小。從清單 6 的結(jié)果可以看到，第二個(gè)查詢(xún)的選擇度幾乎有和整個(gè)條件語(yǔ)句相同。因此可以直接建立單列索引 (city)，其性能與索引 (city，cntry_code，add_date) 具有相差不多的性能。表 1 中對(duì)兩個(gè)索引的性能和大小進(jìn)行了對(duì)比。
表 1. 兩個(gè)索引的性能和大小對(duì)比
| 索引 |
查詢(xún)計(jì)劃總代價(jià) |
索引大小 |
| cust_i1(city，cntry_code，add_date) |
28.94 timerons |
19.52M |
| cust_i3(city) |
63.29 timerons |
5.48M |
從表 1 中可以看到單列索引 (city) 具有更加有效的性能空間比，也就是說(shuō)占有盡可能小的空間得到盡可能高的查詢(xún)速度。
避免在建有索引的列上使用函數(shù)
這是一個(gè)很簡(jiǎn)單的原則，如果在建有索引的列上使用函數(shù)，由于函數(shù)的單調(diào)性不確定，函數(shù)的返回值和輸入值可能不會(huì)一一對(duì)應(yīng)，就可能存在索引中位置差異很大的多個(gè)列值可以滿足帶有函數(shù)的謂詞條件，因此 DB2 優(yōu)化器將無(wú)法進(jìn)行 Matching Index Scan，更壞的情況下可能會(huì)導(dǎo)致直接進(jìn)行表掃描。圖 6 中對(duì)比了使用 function 前后的存取計(jì)劃的變化。
圖 6. 使用 function 前后的存取計(jì)劃的變化
在那些需要被排序的列上創(chuàng)建索引
這里的排序不僅僅指 order by 子句，還包括 distinct 和 group by 子句，他們都會(huì)產(chǎn)生排序的操作。由于索引本身是有序的，在其創(chuàng)建過(guò)程中已經(jīng)進(jìn)行了排序處理，因此在應(yīng)用這些語(yǔ)句的列上創(chuàng)建索引會(huì)降低排序操作的代價(jià)。這種情況一般針對(duì)于沒(méi)有條件語(yǔ)句的查詢(xún)。如果存在條件語(yǔ)句，DB2 優(yōu)化器會(huì)首先選擇出滿足條件的紀(jì)錄，然后才對(duì)中間結(jié)果集進(jìn)行排序。對(duì)于沒(méi)有條件語(yǔ)句的查詢(xún)，排序操作在總的查詢(xún)代價(jià)中會(huì)占有較大比重，因此能夠較大限度的利用索引的排序結(jié)構(gòu)進(jìn)行查詢(xún)優(yōu)化。此時(shí)可以創(chuàng)建單列索引，如果需要?jiǎng)?chuàng)建聯(lián)合索引則需要把被排序的列放在聯(lián)合索引的第一列。圖 7 對(duì)比了清單 7 中的查詢(xún)?cè)趧?chuàng)建索引前后的存取計(jì)劃。
清單 7. 查詢(xún)?cè)趧?chuàng)建索引前后的存取計(jì)劃
select distinct add_date from temp.customer;
|
圖 7. 在創(chuàng)建索引前后的存取計(jì)劃
從圖 7 中我們可以看到在沒(méi)有索引的情況下 SORT 操作是 24751.69 timerons，但是有索引的情況下，不再需要對(duì)結(jié)果集進(jìn)行排序，可以直接進(jìn)行 UNIQUE 操作，表中顯示了這一操作只花費(fèi)了 2499.98 timerons.
圖 8 對(duì)比了清單 8 中的查詢(xún)?cè)趧?chuàng)建聯(lián)合索引前后的存取計(jì)劃，從中可以更好的理解索引對(duì)排序操作的優(yōu)化。
清單 8. 查詢(xún)示例
select cust_name from temp.customer order by add_date;
|
圖 8. 創(chuàng)建聯(lián)合索引前后的存取計(jì)劃
索引的 B+ 樹(shù)結(jié)構(gòu)決定了索引 temp.cust_i5 的所有葉子節(jié)點(diǎn)本身就是按照 add_date 排序的，所以對(duì)于清單 8 中的查詢(xún)，只需要順序掃描索引 temp.cust_i5 的所有葉子節(jié)點(diǎn)。但是對(duì)于 temp.cust_i6 索引，其所有葉子節(jié)點(diǎn)是按照 cust_name 排序，因此在經(jīng)過(guò)對(duì)索引的葉子節(jié)點(diǎn)掃描獲得所有數(shù)據(jù)之后，還需要對(duì) add_date 進(jìn)行排序操作。
合理使用 include 關(guān)鍵詞創(chuàng)建索引
對(duì)于類(lèi)似下面的查詢(xún) :
清單 9. 查詢(xún)示例
select cust_name from temp.customer
where cust_num between '0007000000' and '0007200000'
|
在第一點(diǎn)中我們提到可以在 cust_num 和 cust_name 上建立聯(lián)合索引來(lái)提高查詢(xún)性能。但是由于 cust_num 是主鍵，可以使用 include 關(guān)鍵字創(chuàng)建唯一性索引：
create unique index temp.cust_i7 on temp.customer(cust_num) include (cust_name)
使用 include 后，cust_name 列的數(shù)據(jù)將只存在于索引樹(shù)的葉子節(jié)點(diǎn)，并不存在于索引的關(guān)鍵字中。這種情況下，使用帶有 include 列的唯一索引會(huì)帶來(lái)優(yōu)于聯(lián)合索引的性能，因?yàn)槲ㄒ凰饕軌虮苊庖恍┎槐匾牟僮鳎缗判颉?duì)于清單 9 中的查詢(xún)創(chuàng)建索引 temp.cust_i7 后存取計(jì)劃的代價(jià)為 12338.7 timerons，創(chuàng)建聯(lián)合索引 temp.cust_i8(cust_num，cust_name) 后的代價(jià)為 12363.17 timerons。一般情況下，當(dāng)查詢(xún)的 where 子句中存在主鍵的謂詞我們就可以創(chuàng)建帶有 include 列的唯一索引，形成純索引訪問(wèn)來(lái)提高查詢(xún)性能。注意 include 只能用在創(chuàng)建唯一性索引中。
指定索引的排序?qū)傩?/strong>
對(duì)于下面用來(lái)顯示最近一個(gè)員工入職的時(shí)間的查詢(xún)：
select max(add_date) from temp.employee
很顯然這個(gè)查詢(xún)會(huì)進(jìn)行全表掃描。查詢(xún)計(jì)劃如圖 9.a:
圖 9. 查詢(xún)計(jì)劃
顯然我們可以在 add_date 上創(chuàng)建索引。根據(jù)下面的命令創(chuàng)建索引后的查詢(xún)計(jì)劃如圖 9.b。
create index temp.employee_i1 on temp.employee(add_date)
這里存在一個(gè)誤區(qū)，大家可能認(rèn)為既然查詢(xún)里要取得的是 add_date 的最大值，而我們又在 add_date 上建立了一個(gè)索引，優(yōu)化器應(yīng)該知道從索引樹(shù)中直接去尋找最大值。但是實(shí)際情況并非如此，因?yàn)閯?chuàng)建索引的時(shí)候并沒(méi)有指定排序?qū)傩裕J(rèn)為 ASC 升序排列，DB2 將會(huì)掃描整個(gè)索引樹(shù)的葉子節(jié)點(diǎn)取得所有值后，然后取其最大。我們可以通過(guò)設(shè)置索引的排序?qū)傩詠?lái)提高查詢(xún)性能，根據(jù)下面的命令創(chuàng)建索引后的查詢(xún)計(jì)劃如圖 9.c。
create index temp.employee_i1 on temp.employee(add_date desc)
對(duì)于降序排列的索引，DB2 不需要掃描整個(gè)索引數(shù)的葉子節(jié)點(diǎn)，因?yàn)榈谝粋€(gè)節(jié)點(diǎn)便是最大的。我們同樣可以使用 ALLOW REVERSE SCANS 來(lái)指定索引為雙向掃描，具有和 DESC 近似的查詢(xún)性能。ALLOW REVERSE SCANS 可以被認(rèn)為是 ASC 和 DESC 的組合，只是在以后數(shù)據(jù)更新的時(shí)候維護(hù)成本會(huì)相對(duì)高一些。
如果無(wú)法改變索引的排序?qū)傩裕俏覀兙哂蓄~外的信息，該公司每個(gè)月都會(huì)有新員工入職，那么這個(gè)查詢(xún)就可以改寫(xiě)成：
select max(add_date) from temp.employee where add_date > current timestamp - 1 month
這樣通過(guò)限定一個(gè)查詢(xún)范圍也會(huì)有效地提高查詢(xún)性能。
重新組織索引
隨著數(shù)據(jù)的不斷刪除，插入和更新，索引頁(yè)會(huì)變得越來(lái)越零散，索引頁(yè)的物理存儲(chǔ)順序不再匹配其邏輯順序，索引結(jié)構(gòu)的層次會(huì)變得過(guò)大，這些都會(huì)導(dǎo)致索引頁(yè)的預(yù)讀取變得效率低下。因此，根據(jù)數(shù)據(jù)更新的頻繁程度需要適當(dāng)?shù)闹匦陆M織索引。可以使用 REORG INDEXES 命令來(lái)重新組織索引結(jié)構(gòu)，也可以刪除并重新創(chuàng)建索引達(dá)到相同的目的。同樣的，對(duì)表進(jìn)行重新組織也會(huì)帶來(lái)性能的改善。
重新組織某一個(gè)表的所有索引的命令如下：REORG INDEXES ALL FOR TABLE table_name。
重新組織一個(gè)表的數(shù)據(jù)的命令如下，在下面的命令還可以為其指定一個(gè)特定的索引，REORG 命令將會(huì)根據(jù)這個(gè)索引的排序方式重新組織該表的數(shù)據(jù)。
REORG TABLE table_name INDEX index_name。
重新收集表和索引的統(tǒng)計(jì)信息
和在 2.1 中提到的原因類(lèi)似，當(dāng)一個(gè)表經(jīng)過(guò)大量的索引修改、數(shù)據(jù)量變化或者重新組織后，可能需要重新收集表以及相關(guān)索引的統(tǒng)計(jì)信息。這些統(tǒng)計(jì)信息主要是關(guān)于表和索引存儲(chǔ)的物理特性，包括記錄數(shù)目，數(shù)據(jù)頁(yè)的數(shù)目以及記錄的平均長(zhǎng)度等。優(yōu)化器將根據(jù)這些信息決定使用什么樣的存取計(jì)劃來(lái)訪問(wèn)數(shù)據(jù)。因此，不能真實(shí)反映實(shí)際情況的統(tǒng)計(jì)信息可能會(huì)導(dǎo)致優(yōu)化器選擇錯(cuò)誤的存取計(jì)劃。收集表及其所有索引的統(tǒng)計(jì)信息的命令如下：RUNSTATS ON TABLE table_name FOR INDEXES ALL。
上述兩個(gè)命令具有復(fù)雜的參數(shù)選擇，用戶(hù)可以參閱 DB2 Info Center 來(lái)根據(jù)實(shí)際情況使用這兩個(gè)命令。
修改查詢(xún)
合理使用 NOT IN 和 NOT EXISTS
一般情況下 NOT EXISTS 具有快于 NOT IN 的性能，但是這并不絕對(duì)。根據(jù)具體的數(shù)據(jù)情況、存在的索引以及查詢(xún)的結(jié)構(gòu)等因素，兩者會(huì)有較大的性能差異，開(kāi)發(fā)人員需要根據(jù)實(shí)際情況選擇適當(dāng)?shù)姆绞健?/p>
譬如下面的查詢(xún)：
清單 10. 查詢(xún)示例
表結(jié)構(gòu)：temp.customer(cust_num) 主鍵：cust_num
表結(jié)構(gòu)：temp.contact(cnt_id，cust_num) 主鍵：cnt_id
表結(jié)構(gòu)：temp.contact_detail(cnt_id，address，phone) 主鍵：cnt_id
查詢(xún) :
select cust_num
from temp.customer cust
where not exists (select 1 from temp.contact cont
here cust.cust_num = cont.cust_num)
|
此查詢(xún)用來(lái)列出所有不存在聯(lián)系人的客戶(hù)。對(duì)于這樣的需求，開(kāi)發(fā)人員會(huì)最自然的寫(xiě)出清單 10 中的查詢(xún)，的確，對(duì)于大部分情況它具有最優(yōu)的性能。該查詢(xún)的查詢(xún)代價(jià)為 178,430 timerons。讓我們?cè)賮?lái)看看使用 NOT IN 后查詢(xún)的總代價(jià)，請(qǐng)看清單 11。
清單 11. 查詢(xún)示例
查詢(xún)：
select cust_num
from temp.customer cust
where cust.cust_num not in (select cont.cust_num from temp.contact cont)
代價(jià)：12,648,897,536 timerons
|
可以看到 NOT EXISTS 的性能要比 NOT IN 高出許多。NOT IN 是自?xún)?nèi)向外的操作，即先得到子查詢(xún)的結(jié)果，然后執(zhí)行最外層的查詢(xún)，而 NOT EXISTS 恰好相反，是自外向內(nèi)的操作。在上述例子中，temp.contact 表中有 65 萬(wàn)條記錄，使得 10.2 查詢(xún)中的 NOT IN 列表非常大，導(dǎo)致了使用 NOT IN 的查詢(xún)具有非常高的查詢(xún)代價(jià)。下面我們對(duì) 10.1 和 10.2 的查詢(xún)進(jìn)行修改，將 temp.contact 表中的記錄限制到 100 條，請(qǐng)看下面的查詢(xún)：
清單 12. 查詢(xún)示例
查詢(xún)：
select cust_num
from temp.customer cust
where not exists (select 1 from temp.contact cont
here cust.cust_num = cont.cust_num
and cont.cnt_id < 100)
代價(jià)：42,015 timerons
|
清單 13. 查詢(xún)示例
查詢(xún)：
select cust_num
from temp.customer cust
where cust.cust_num not in (select cont.cust_num from temp.contact cont
where cont.cnt_id < 100)
代價(jià)：917,804 timerons
|
從 12 和 13 中可以看出 NOT EXISTS 的查詢(xún)代價(jià)隨子查詢(xún)返回的結(jié)果集的變化沒(méi)有大幅度的下降，隨著子查詢(xún)的結(jié)果集從 65 萬(wàn)下降到 100 條，NOT EXISTS 的查詢(xún)代價(jià)從 178,430 下降到 42,015，只下降 4 倍。但是 NOT IN 的查詢(xún)代價(jià)卻有著極大的變化，其查詢(xún)代價(jià)從 12,648,897,536 下降到 917,804，下降了 13782 倍。可見(jiàn)子查詢(xún)的結(jié)果集對(duì) NOT IN 的性能影響很大，但是這個(gè)簡(jiǎn)單的查詢(xún)不能說(shuō)明 NOT EXISTS 永遠(yuǎn)好于 NOT IN，因?yàn)橥瑯哟嬖谝恍┮蛩貙?duì) NOT EXISTS 的性能有很大的影響。我們?cè)倏聪旅娴睦樱?/p>
清單 14. 查詢(xún)示例
查詢(xún)：
select cust_num
from temp.customer cust
where not exists (select 1 from temp.contact cont
where cust.cust_num = cont.cust_num
and cont.cnt_id in (select cnt_id from temp.contact_detail
where cnt_id<100))
代價(jià)：5,263,096 timerons
|
清單 15. 查詢(xún)示例
查詢(xún)：
select cust_num
from temp.customer cust
where cust_num not in (select cust_num from temp.contact cont
where cont.cnt_id in (select cnt_id from temp.contact_detail
where cnt_id<100))
代價(jià)：4,289,095 timerons
|
在上面的例子中，我們只是對(duì)查詢(xún)?cè)黾恿艘粋€(gè)小改動(dòng)，使用一個(gè)嵌套查詢(xún)限制了在 temp.contact 中掃描的范圍。但是在這兩個(gè)新的查詢(xún)中，NOT IN 的性能卻又好于 NOT EXISTS。NOT EXISTS 的代價(jià)增加了 125 倍，而 NOT IN 的代價(jià)卻只增加了 4 倍。這是由于 NOT EXISTS 是自外向內(nèi)，嵌套查詢(xún)的復(fù)雜度對(duì)其存在較大的影響。因此在實(shí)際應(yīng)用中，要考慮子查詢(xún)的結(jié)果集以及子查詢(xún)的復(fù)雜度來(lái)決定使用 NOT EXISTS 或者 NOT IN。對(duì)于 IN，EXISTS 和 JOIN 等操作，大多數(shù)情況下 DB2 優(yōu)化器都能形成比較一致的最終查詢(xún)計(jì)劃。
合理使用子查詢(xún)減少數(shù)據(jù)掃描和利用索引
某些情況下可以將查詢(xún)中的某一部分邏輯提取出來(lái)作為子查詢(xún)出現(xiàn)，能夠減少掃描的數(shù)據(jù)量，以及利用索引進(jìn)行數(shù)據(jù)檢索。請(qǐng)看清單 16 中的查詢(xún)：
清單 16.
索引：temp.cust_i1 on temp.customer(add_date)
temp.order_i1 on temp.order(sold_to_cust_num)
temp.order_i2 on temp.order(add_date)
查詢(xún)：
select cust.cust_num
from temp.customer cust
left join temp.order ord
on cust.cust_num = ord.sold_to_cust_num
where cust.add_date > current timestamp - 2 months
or ord.add_date > current timestamp - 2 months
|
上面的查詢(xún)用來(lái)選擇所有兩個(gè)月內(nèi)新增加的用戶(hù)以及在兩個(gè)月內(nèi)定購(gòu)了產(chǎn)品的用戶(hù)。從圖 10.a 的查詢(xún)計(jì)劃中可看出沒(méi)有任何索引被使用。
圖 10. 查詢(xún)計(jì)劃
使用子查詢(xún)對(duì)該查詢(xún)重新改寫(xiě)后，請(qǐng)看清單 17:
清單 17.
查詢(xún)：
with tmp as(
select sold_to_cust_num from temp.order
where add_date > current timestamp - 2 months)
select cust.cust_num from temp.customer cust
where cust.add_date > current timestamp - 2 months
or cust.cust_num in (select sold_to_cust_num from tmp )
|
在清單 17 的查詢(xún)中，我們使用子查詢(xún)預(yù)先限定了要掃描 temp.order 表中的記錄數(shù)目，而不是像清單 16 中的查詢(xún)那樣對(duì) temp.order 表進(jìn)行全表掃描。同時(shí)，在預(yù)先限定數(shù)據(jù)范圍的時(shí)候，能夠利用 temp.order_i2 索引。請(qǐng)看其查詢(xún)計(jì)劃，如圖 10.b。可以看到查詢(xún)代價(jià)有大幅度下降。其實(shí)，即使沒(méi)有 temp.order_i2 索引，修改后的查詢(xún)也仍然由于前者，因?yàn)樗A(yù)先限定了數(shù)據(jù)的掃描范圍，也減少了后續(xù)連接處理的數(shù)據(jù)量，請(qǐng)看圖 10.c。
重新排列各個(gè)表的連接順序，盡量減小中間結(jié)果集的數(shù)據(jù)量
一般情況下，DB2 會(huì)根據(jù)各表的 JOIN 順序自頂向下順序處理，因此合理排列各表的連接順序會(huì)提高查詢(xún)性能。譬如清單 18 中的查詢(xún)：
清單 18.
查詢(xún)：
select cust.cust_name, ord.order_num, cnt.cnt_first_name
from temp.customer cust
left join temp.order ord
on cust.cust_num = ord.sold_to_cust_num
join temp.contact cnt
on cust.cust_num = cnt.cust_num
where cnt.mod_date > current timestamp - 1 months
|
清單 18 中的查詢(xún)用來(lái)選擇出所有最近一個(gè)月內(nèi)修改過(guò)聯(lián)系人信息的客戶(hù)的訂單信息。此查詢(xún)會(huì)按照鏈接的順序先將 temp.customer 表和 temp.order 表進(jìn)行 LEFT JOIN，然后使用結(jié)果集去 JOIN temp.contact 表。由于該查詢(xún)使用了 LEFT JOIN，因此在生成中間結(jié)果集的時(shí)候不會(huì)有任何記錄會(huì)被過(guò)濾掉，中間結(jié)果集的記錄數(shù)目大于等于 temp.customer 表。了解到了 DB2 是如何解釋和執(zhí)行這樣的查詢(xún)后，很自然的我們就會(huì)想到將 JOIN 提前。請(qǐng)看清單 19。
清單 19.
查詢(xún)：
select cust.cust_name, ord.order_num, cnt.cnt_first_name
from temp.customer cust
join temp.contact cnt
on cust.cust_num = cnt.cust_num
left join temp.order ord
on cust.cust_num = ord.sold_to_cust_num
where cnt.mod_date > current timestamp - 1 months
|
圖 11.a 和圖 11.b 分別為清單 18 和 19 的查詢(xún)的存取計(jì)劃。在 19 的查詢(xún)中，在形成中間結(jié)果集的時(shí)候也應(yīng)用到了 WHERE 語(yǔ)句中的條件，而不是在所有 JOIN 都結(jié)束以后才被應(yīng)用去除記錄的。
圖 11. 查詢(xún)計(jì)劃
另外，在修改查詢(xún)盡量減少中間結(jié)果集的記錄條數(shù)的時(shí)候還要考慮中間結(jié)果集的數(shù)據(jù)總量，譬如中間結(jié)果集需要保存的每條記錄的長(zhǎng)度。如果我們把 JOIN temp.contact 提前以后，由于中間結(jié)果集需要保存過(guò)多的 contact 表的列反而使得結(jié)果集的數(shù)據(jù)總量變大，可能不會(huì)帶來(lái)性能上的改善。
使用 UDF 代替查詢(xún)中復(fù)雜的部分
由于 UDF 是預(yù)先編譯的，性能普遍優(yōu)于一般的查詢(xún)，UDF 使用的存取計(jì)劃一經(jīng)編譯就會(huì)相對(duì)穩(wěn)定。筆者在工作中曾多次發(fā)現(xiàn)，使用 UDF 代替查詢(xún)或者視圖中的復(fù)雜部分會(huì)提高幾倍甚至幾十倍的性能，主要原因是迫使 DB2 使用指定的存取計(jì)劃來(lái)充分利用 index 或者調(diào)整其訪問(wèn)過(guò)程（如 Join 順序， Filter 位置等）。使用 UDF 進(jìn)行優(yōu)化的基本思路是，將復(fù)雜查詢(xún)分解為多個(gè)部分執(zhí)行，針對(duì)每個(gè)部分優(yōu)化處理，將各部分組合時(shí)能夠避免存取計(jì)劃的一些不必要變化，優(yōu)化整體性能。譬如清單 20 中的查詢(xún)：
清單 20.
查詢(xún)：select * from temp.customer where cust_num in (
select distinct sold_to_cust_num from temp.order
where add_date > current timestamp - 2 months
union
select distinct cust_num from temp.contact
where add_date > current timestamp - 2 months
)
|
這個(gè)查詢(xún)會(huì)導(dǎo)致優(yōu)化器生成比較復(fù)雜的查詢(xún)計(jì)劃，尤其是 temp.customer 是一個(gè)比較復(fù)雜的視圖的時(shí)候。這種情況下我們可以通過(guò)創(chuàng)建 UDF，將其分步執(zhí)行：先執(zhí)行子查詢(xún)獲得 cust_num 值的列表，然后執(zhí)行最外層的查詢(xún)。下面的例子是通過(guò) UDF 對(duì)清單 20 的查詢(xún)的改寫(xiě)：
清單 21.
CREATE FUNCTION temp.getCustNum(p_date timestamp)
RETURNS
TABLE (cust_num CHARACTER(10))
RETURN
select distinct sold_to_cust_num from temp.order
where add_date > p_date
union
select distinct cust_num from temp.contact
where add_date > p_date;
select * from customer where cust_num in (
select cust_num from table(temp.getCustNum(current timestamp - 2 months)) tbl
)
|
改寫(xiě)前后的查詢(xún)代價(jià)分別是 445,159.31 和 254,436.98。當(dāng)面對(duì)比較復(fù)雜的查詢(xún)時(shí)考慮使用 UDF 將其拆分為多步執(zhí)行常常會(huì)帶來(lái)意想不到的效果。在實(shí)際的項(xiàng)目中，如果數(shù)據(jù)處理和查詢(xún)調(diào)用是包含在其他應(yīng)用程序中如 Unix 腳本，Java 程序等，同樣可以考慮采用分步數(shù)據(jù)處理的方式來(lái)調(diào)用數(shù)據(jù)庫(kù)，以?xún)?yōu)化應(yīng)用性能。
總結(jié)
本文主要介紹了如何使用 DB2 提供的各種查看存取計(jì)劃的工具，并根據(jù)作者在 DB2 方面的開(kāi)發(fā)經(jīng)驗(yàn)總結(jié)了一些提高查詢(xún)性能的方法和技巧。如果能夠有效地利用 DB2 提供的各種工具，理解 DB2 中索引的結(jié)構(gòu)，以及查詢(xún)將如何被解釋?zhuān)瑪?shù)據(jù)庫(kù)開(kāi)發(fā)人員可以更好的提高查詢(xún)性能來(lái)滿足需求。