http://istl.inspur.com/showart.asp?id=27
1、準備實驗環境
我們創建了一個模擬tpch（數據庫工業標準測試）測試的數據庫，庫中一共有3張數據表，分別是：
part 產品部件表
supplier 供應商表
partsupp 產品供應商關聯表
其中part表中含有200000條數據，partsupp表中含有800000條數據，supplier表中含有10000條數據
1） 我們為如上的3張表分別建立如下的索引：
|
create index part_idx1 on tpcd.part(p_partkey,p_size);
create index partsupp_idx1 on tpcd.partsupp(ps_partkey, ps_supplycost, ps_suppkey);
create index supp_idx1 on tpcd.supplier(s_suppkey);
|
2） 建立索引后，我們收集一下相關的統計信息，在db2cmd中執行如下的命令：
|
runstats on table tpcd.part with distribution and detailed indexes all;
runstats on table tpcd.partsupp with distribution and detailed indexes all;
runstats on table tpcd.supplier with distribution and detailed indexes all;
|
分別對PART, PARTSUPP, SUPPLIER運行以下命令，確保runstats已經成功執行：
|
db2 “select card,npages,stats_time from syscat.tables where tabname=’PART’”
CARD NPAGES STATS_TIME
-------------------- -------------------- -----------------------------------------------------------
200000 7616 2008-08-21-17.20.22.828000
|
其中，CARD為該表的記錄數，NPAGES為該表所占有的存儲空間(頁數)，STATS_TIME為收集統計信息的時間。
2、發現問題
1） 我們有如下的一個SQL語句：
|
select
count(*)
from
tpcd.part,
tpcd.partsupp,
tpcd.supplier
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 30
and ps_suppkey = 9988@
|
目前，該SQL的運行速度不理想，我們希望通過調優提高這個SQL語句的執行效率。
首先，我們為了記錄這條查詢語句執行的時間，運行如下SQL文件，記錄一個時間：
文件名：lab.sql
|
values current timestamp@
select
count(*)
from
tpcd.part,
tpcd.partsupp,
tpcd.supplier
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 30
and ps_suppkey = 9988@
values current timestamp@
|
在db2cmd中運行：
得到結果如下：
|
1
--------------------------
2009-01-04-15.09.25.281000
1 條記錄已選擇。
select count(*) from tpcd.part, tpcd.partsupp, tpcd.supplier where p_partkey = ps_partkey and s_suppkey = ps_suppkey an p_size = 30 and ps_suppkey = 9988
1
-----------
1
1 條記錄已選擇。
values current timestamp
1
--------------------------
2009-01-04-15.09.33.359000
1 條記錄已選擇。
|
通過前后時間對比，我們發現這個SQL運行了大約6秒鐘(不同的機器性能可能有差異)。
3、分析問題
1） 為了了解這個SQL的執行過程，我們開始分析它的執行計劃，在db2cmd中運行：
|
db2expln -d tpcd -f lab.sql -t -z @ -g > lab-before.exp
|
可以用文本編輯器打開lab-before.exp，下面，我們詳細解讀其中的執行計劃：如圖1所示
分析：執行計劃是倒樹狀的結構，首先對part表、partsupp表和supplier表進行索引掃描，然后對part和partsupp表的索引掃描結果進行NLJOIN（嵌套循環連接），再將結果與supplier表的索引掃描結果進行HSJOIN（HASH連接），再進行排序，最后返回查詢結果。
其中黃色標記部分，我們發現執行part表的索引掃描花費較大（1261.42個單位），且掃描結果（3810行）與我們的最終期望結果（1）差距較大，執行NLJOIN的花費（7443.88—1261.42—15.1451=6167.31個單位），因此我們認為這里part和partsupp表建立的索引是影響查詢效率的因素。
4、解決問題
1） 在仔細分析的問題之后，我們嘗試來解決這個問題，我們規劃了一個新的索引方案，我們建立新的索引：
|
drop index part_idx1;
create index part_idx1 on tpcd.part(p_size,p_partkey);
drop index partsupp_idx1;
create index partsupp_idx1 on tpcd.partsupp(ps_suppkey,ps_partkey, ps_supplycost );
drop index supp_idx1;
create index supp_idx1 on tpcd.supplier(s_suppkey);
|
我們改變了part表和partsupp表的索引順序
2） 建立索引后，我們再收集一下相關的統計信息，在db2cmd中執行如下的命令：
|
runstats on table tpcd.part with distribution and detailed indexes all;
runstats on table tpcd.partsupp with distribution and detailed indexes all;
runstats on table tpcd.supplier with distribution and detailed indexes all;
|
3） 下面，我們再執行一下原來的SQL，在db2cmd中執行：
|
db2 connect to tpcd
db2 –td@ -vf lab.sql
1
--------------------------
2009-01-04-16.02.45.078000
1 條記錄已選擇。
select count(*) from tpcd.part, tpcd.partsupp, tpcd.supplier where p_partkey = ps_partkey and s_suppkey = ps_suppkey an p_size = 30 and ps_suppkey = 9988
1
-----------
1
1 條記錄已選擇。
values current timestamp
1
--------------------------
2009-01-04-16.02.45.218000
1 條記錄已選擇。
|
通過前后時間對比，我們發現這次，這個SQL運行時間在1秒之內 (不同的機器性能可能有差異)。
4） 為了進一步分析這個SQL的執行過程，我們再分析一下SQL的執行計劃：
在db2cmd中運行：
|
db2expln -d tpcd -f lab.sql -t -z @ -g > lab-after.exp
|
可以用文本編輯器打開lab-after.exp，下面，我們詳細解讀這個執行計劃，如圖2所示
從執行的總花費（84.817）上我們可以明顯的看到優化后的效果。
5、解決方案分析
我們來看實驗Sql語句的謂詞部分：
|
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 30
and ps_suppkey = 9988@
|
DB2的sql優化器在執行查詢sql語句，根據謂詞進行表連接查詢，并不依賴于where條件中謂詞的順序，而是根據所建索引來進行先后順序的連接。
我們再來看優化前的索引：
|
create index part_idx1 on tpcd.part(p_partkey,p_size);
create index partsupp_idx1 on tpcd.partsupp(ps_partkey, ps_supplycost, ps_suppkey);
create index supp_idx1 on tpcd.supplier(s_suppkey);
|
1）我們目標是盡量增大第一次或前幾次join的數據量縮小幅度，所以首先要進行小表的索引掃描和連接。而這里，從業務角度來說，把業務主鍵放到索引的第一個位置是有意義的，但是對于優化器來說，這毫無意義。優化器會根據索引優化器會首先選擇謂詞：p_partkey = ps_partkey 對part和partsupp進行NLJOIN，而這兩個表是數據量相對大的表。
2）NLJOIN中外表只掃描一次，內表掃描N次，所以內表要盡量的小一些。而這里的內表partsupp有800000條數據。
我們期望優化器做如下處理：
1）優化器首先根據謂詞p_size = 30和 ps_suppkey = 9988@進行索引掃描，縮小數據范圍。
2）優化器根據謂詞s_suppkey = ps_suppkey對supplier和partsupp進行表的NLJOIN。內表（partsupp）是數據量較小的一個表
所以，我們要將p_size和ps_suppkey的索引提前，建立如下索引
|
create index part_idx1 on tpcd.part(p_size,p_partkey);
create index partsupp_idx1 on tpcd.partsupp(ps_suppkey,ps_partkey, ps_supplycost );
create index supp_idx1 on tpcd.supplier(s_suppkey);
|
6、總結
使用db2expln解釋工具，能夠得到DB2 Sql優化器的詳細Sql執行計劃，通過其中的花費我們可以結合sql語句及表、索引、連接的結構進行分析，發現并定位問題，然后對sql進行改進，達到優化的目標。