Prayer

http://istl.inspur.com/showart.asp?id=27

1、準(zhǔn)備實(shí)驗(yàn)環(huán)境

我們創(chuàng)建了一個(gè)模擬tpch（數(shù)據(jù)庫工業(yè)標(biāo)準(zhǔn)測試）測試的數(shù)據(jù)庫，庫中一共有3張數(shù)據(jù)表，分別是：

part      產(chǎn)品部件表

supplier 供應(yīng)商表

partsupp 產(chǎn)品供應(yīng)商關(guān)聯(lián)表

其中part表中含有200000條數(shù)據(jù)，partsupp表中含有800000條數(shù)據(jù)，supplier表中含有10000條數(shù)據(jù)

1） 我們?yōu)槿缟系?/span>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） 建立索引后，我們收集一下相關(guān)的統(tǒng)計(jì)信息，在db2cmd中執(zhí)行如下的命令：

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;

 

分別對(duì)PART, PARTSUPP, SUPPLIER運(yùn)行以下命令，確保runstats已經(jīng)成功執(zhí)行：

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為該表的記錄數(shù)，NPAGES為該表所占有的存儲(chǔ)空間(頁數(shù))，STATS_TIME為收集統(tǒng)計(jì)信息的時(shí)間。

2、發(fā)現(xiàn)問題

1） 我們有如下的一個(gè)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的運(yùn)行速度不理想，我們希望通過調(diào)優(yōu)提高這個(gè)SQL語句的執(zhí)行效率。

首先，我們?yōu)榱擞涗涍@條查詢語句執(zhí)行的時(shí)間，運(yùn)行如下SQL文件，記錄一個(gè)時(shí)間：

文件名：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中運(yùn)行：

db2 -td@ -vf lab.sql

 

得到結(jié)果如下：

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 條記錄已選擇。

 

通過前后時(shí)間對(duì)比，我們發(fā)現(xiàn)這個(gè)SQL運(yùn)行了大約6秒鐘(不同的機(jī)器性能可能有差異)。

3、分析問題

1） 為了了解這個(gè)SQL的執(zhí)行過程，我們開始分析它的執(zhí)行計(jì)劃，在db2cmd中運(yùn)行：

db2expln -d tpcd -f lab.sql -t -z @ -g > lab-before.exp

可以用文本編輯器打開lab-before.exp，下面，我們?cè)敿?xì)解讀其中的執(zhí)行計(jì)劃：如圖1所示

 

 

分析：執(zhí)行計(jì)劃是倒樹狀的結(jié)構(gòu)，首先對(duì)part表、partsupp表和supplier表進(jìn)行索引掃描，然后對(duì)part和partsupp表的索引掃描結(jié)果進(jìn)行NLJOIN（嵌套循環(huán)連接），再將結(jié)果與supplier表的索引掃描結(jié)果進(jìn)行HSJOIN（HASH連接），再進(jìn)行排序，最后返回查詢結(jié)果。

其中黃色標(biāo)記部分，我們發(fā)現(xiàn)執(zhí)行part表的索引掃描花費(fèi)較大（1261.42個(gè)單位），且掃描結(jié)果（3810行）與我們的最終期望結(jié)果（1）差距較大，執(zhí)行NLJOIN的花費(fèi)（7443.88—1261.42—15.1451=6167.31個(gè)單位），因此我們認(rèn)為這里part和partsupp表建立的索引是影響查詢效率的因素。

4、解決問題

1） 在仔細(xì)分析的問題之后，我們嘗試來解決這個(gè)問題，我們規(guī)劃了一個(gè)新的索引方案，我們建立新的索引：

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） 建立索引后，我們?cè)偈占幌孪嚓P(guān)的統(tǒng)計(jì)信息，在db2cmd中執(zhí)行如下的命令：

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） 下面，我們?cè)賵?zhí)行一下原來的SQL，在db2cmd中執(zhí)行：

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 條記錄已選擇。

 

 

通過前后時(shí)間對(duì)比，我們發(fā)現(xiàn)這次，這個(gè)SQL運(yùn)行時(shí)間在1秒之內(nèi) (不同的機(jī)器性能可能有差異)。

4） 為了進(jìn)一步分析這個(gè)SQL的執(zhí)行過程，我們?cè)俜治鲆幌?/span>SQL的執(zhí)行計(jì)劃：

在db2cmd中運(yùn)行：

db2expln -d tpcd -f lab.sql -t -z @ -g > lab-after.exp

 

 

可以用文本編輯器打開lab-after.exp，下面，我們?cè)敿?xì)解讀這個(gè)執(zhí)行計(jì)劃，如圖2所示

 

從執(zhí)行的總花費(fèi)（84.817）上我們可以明顯的看到優(yōu)化后的效果。

5、解決方案分析

我們來看實(shí)驗(yàn)Sql語句的謂詞部分：

p_partkey = ps_partkey    and s_suppkey = ps_suppkey      and p_size = 30          and ps_suppkey = 9988@

 

DB2的sql優(yōu)化器在執(zhí)行查詢sql語句，根據(jù)謂詞進(jìn)行表連接查詢，并不依賴于where條件中謂詞的順序，而是根據(jù)所建索引來進(jìn)行先后順序的連接。

我們?cè)賮砜磧?yōu)化前的索引：

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）我們目標(biāo)是盡量增大第一次或前幾次join的數(shù)據(jù)量縮小幅度，所以首先要進(jìn)行小表的索引掃描和連接。而這里，從業(yè)務(wù)角度來說，把業(yè)務(wù)主鍵放到索引的第一個(gè)位置是有意義的，但是對(duì)于優(yōu)化器來說，這毫無意義。優(yōu)化器會(huì)根據(jù)索引優(yōu)化器會(huì)首先選擇謂詞：p_partkey = ps_partkey 對(duì)part和partsupp進(jìn)行NLJOIN，而這兩個(gè)表是數(shù)據(jù)量相對(duì)大的表。

2）NLJOIN中外表只掃描一次，內(nèi)表掃描N次，所以內(nèi)表要盡量的小一些。而這里的內(nèi)表partsupp有800000條數(shù)據(jù)。

我們期望優(yōu)化器做如下處理：

1）優(yōu)化器首先根據(jù)謂詞p_size = 30和 ps_suppkey = 9988@進(jìn)行索引掃描，縮小數(shù)據(jù)范圍。

2）優(yōu)化器根據(jù)謂詞s_suppkey = ps_suppkey對(duì)supplier和partsupp進(jìn)行表的NLJOIN。內(nèi)表（partsupp）是數(shù)據(jù)量較小的一個(gè)表

所以，我們要將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、總結(jié)

使用db2expln解釋工具，能夠得到DB2 Sql優(yōu)化器的詳細(xì)Sql執(zhí)行計(jì)劃，通過其中的花費(fèi)我們可以結(jié)合sql語句及表、索引、連接的結(jié)構(gòu)進(jìn)行分析，發(fā)現(xiàn)并定位問題，然后對(duì)sql進(jìn)行改進(jìn)，達(dá)到優(yōu)化的目標(biāo)。