Access Path
1.并不是用index就一定快.有時(shí)候一個(gè)表只有很少的records,那么你直接scan這個(gè)表,比你先讀index,再訪問表要快.
2.不過大多數(shù)情況下還是用index快一些(要不然這個(gè)技術(shù)就沒有意義了).DB2中index的應(yīng)用需要兩個(gè)前提條件:
i.至少有一個(gè)predicate是indexable的.
ii.這些indexable的predicates中,至少有一個(gè)predicate的一個(gè)column,這個(gè)column也在index中.
3.index access在DB2中有幾種:
i.首先,最簡(jiǎn)單的一種就是
direct index lookup.它從index的root開始遍歷,直到找到符合要求的leaf page為止.然后再利用leaf page中的datapage pointer去訪問真正的data.這種方法要求index中的每個(gè)column都有明確的值.比如在下面這個(gè)表中,index建立在
DEPTNO, TYPE, 和EMPCODE三個(gè)column上.
SELECT FIRSTNAME, LASTNAME
FROM EMPLOYEE
WHERE DEPTNO = 5
AND TYPE = 'X'
AND EMPCODE = 10;
如果你只給了DEPTNO, TYPE的值,那么是沒有辦法direct index lookup的,因?yàn)闊o法和一個(gè)index key對(duì)應(yīng).對(duì)于這種情況,就要用到下面所說的index scan.
ii.index scan
先說下index中的column,還是上面那個(gè)例子,index建立在DEPTNO, TYPE,EMPCODE上,那么這三個(gè)column不是像電影的演員表一樣排名不分先后的,DEPTNO優(yōu)先級(jí)高(也可以稱之為high order column),TYPE次之,EMPCODE最后.index scan的時(shí)候,是先根據(jù)DEPTNO的值,確定一個(gè)范圍,然后再TYPE,最后EMPCODE.如果大家還不理解,我們可以想象這三個(gè)column構(gòu)成了一個(gè)表,DEPTNO是第一個(gè)column,TYPE是第二個(gè)column,EMPCODE是第三column.而且這個(gè)表里的數(shù)據(jù)都是排好序的.如果我指定了三個(gè)確定的值(5,'X',10),那么我肯定在這個(gè)表里可以找到唯一的一個(gè)row.但是如果我只有兩個(gè)值(5,'X'),那么我可以找到一個(gè)由一些row構(gòu)成的block,這塊數(shù)據(jù)里面的row都是符合條件的.如果我丟掉了highorder column,比如(,'X',10),那么你可以想象的到,你在這個(gè)表里可以找到很多符合條件的row,但是它們不是聚集在一起的.好,接下來說下index scan中的兩種 scan方法.
1.matching index scan
還是上面的例子,加入去掉EMPCODE = 10,那么matching index scan會(huì)從index的root開始找,直到找到第一個(gè)符合條件(DEPTNO = 5, TYPE = 'X' )的leaf page,但這時(shí)候它不能確定有幾個(gè)leaf page符合條件,由于index key不完整,有可能有很多歌leaf page都符合條件(EMPCODE1-10000的都是符合條件的),但是由于high order column已經(jīng)指定了,所以滿足條件的leafpage肯定是在一起的,所以DB2會(huì)從第一個(gè)開始,向右一直scan,直到把所有符合條件的leaf page都找到為止.
2.non-matching index scan
假如去掉DEPTNO=5,也就是去掉了high order column,那么根據(jù)我們上面的討論,返回的結(jié)果是很多l(xiāng)eaf page而且這些leaf page不一定是連續(xù)的在一起的.所以這時(shí)候,index tree的B+樹結(jié)構(gòu)就沒有意義了,DB2需要整個(gè)遍歷index tree的leaf page來找到所有符合條件的leaf page.(這時(shí)不會(huì)再訪問non-leaf page了)
iii.index only access
這種情況比較少見.它出現(xiàn)的條件是index的column已經(jīng)包含了你的query中的column.還是上面的例子,我們稍微改一下select語(yǔ)句:
SELECT DEPTNO, TYPE
FROM EMPLOYEE
WHERE EMPCODE = 10;
你會(huì)發(fā)現(xiàn)DEPTNO, TYPE都是index中的column,所以DB2根本不需要訪問data page,直接從index page中就可以拿數(shù)據(jù)了.
joinmethod
SQL語(yǔ)句不可能簡(jiǎn)單到永遠(yuǎn)只含有一個(gè)table,當(dāng)有多個(gè)表的時(shí)候,就要開始考慮join的問題.join的效率也是accesspath的重要指標(biāo).DB2怎么實(shí)現(xiàn)多個(gè)表的join呢?每個(gè)多表的query,DB2會(huì)把它分解為若干個(gè)獨(dú)立的accesspath.DB2的優(yōu)化器(optimizer)會(huì)從若干個(gè)表中,選出兩個(gè),為這兩個(gè)表的join找到一個(gè)比較優(yōu)的accesspath,然后再繼續(xù)優(yōu)化下一個(gè)join.表的選擇并不是隨機(jī)的,DB2有自己的機(jī)制來找出它認(rèn)為的最適合優(yōu)化的join.
在join的時(shí)候,join 算法的選擇也很重要,也稱之為join method.有三種類型的join method:Nested Loop Join(NLJ),Merge Scan Join 和hash join.每個(gè)join method操作數(shù)據(jù)的方法可能不一樣,但返回的結(jié)果肯定是一樣的.雖然join method有多種,但一些基本的steps 和concepts還是一樣的.一般來說,所有join method要做的第一件事就是先訪問哪個(gè)表,這個(gè)表被稱為外表(outer table).確定外表后,在join之前,會(huì)有一系列的針對(duì)外表的操作以期提高效率.另外一張表就稱為內(nèi)表(inner table),同樣,在join之前,DB2也會(huì)對(duì)內(nèi)表進(jìn)行一些處理.或者當(dāng)join 發(fā)生的時(shí)候,或者兩個(gè)phase都有.除了這些基本的東西,三種join method還是有很多不一樣的地方.DB2的優(yōu)化器知道這些method的優(yōu)缺點(diǎn)和在什么時(shí)候用何種method能提高performance.基于在system catalog里的statistics,優(yōu)化器知道該選哪個(gè)表做內(nèi)表,哪個(gè)做外表.下面是一些high-level的經(jīng)驗(yàn)總結(jié):
i.比較小的表可以被選作外表,這是因?yàn)檫@可以減少內(nèi)表的重復(fù)訪問次數(shù)
ii.如果一個(gè)表上有select predicate,那它可以被選作外表,通過select perdicate可以大大減少row的數(shù)目,從而減少內(nèi)表的訪問次數(shù),因?yàn)橥獗淼囊粋€(gè)row就要訪問一次內(nèi)表.
iii.如果一個(gè)表上有index,那它適合做內(nèi)表,反之則不然.因?yàn)闆]有index的表在訪問它的時(shí)候只能做整個(gè)表的scan操作.
iiii.重復(fù)record越少的table越適合做外表
當(dāng)然,這些規(guī)則并不是死的.所以在描述的時(shí)候我也盡量用"可以"這樣的詞.畢竟它只是一些經(jīng)驗(yàn)性的總結(jié),在不同的情況下,可能有些規(guī)則是不起作用的.優(yōu)化器會(huì)根據(jù)cost estimate模型來選擇outer table和inner table.下面我們來介紹下三種join method的特點(diǎn).
最常見的join method就是nested loop.從外表拿出一條row,然后scan inner table,尋找match的row.內(nèi)表有可能被scan很多次,所以用index來減少IO是很常用的優(yōu)化手段.
第二種是Merge Join.它在join之前,需要先對(duì)兩個(gè)表進(jìn)行排序.這樣在join的時(shí)候,我們就可以按順序讀兩個(gè)表,每個(gè)表都只需要掃描一遍就可以完成join.
第三種是Hash Join.把inner table讀入內(nèi)存中,并按照hash code對(duì)每個(gè)row排序,然后掃描外表,通過比較hash code來尋找符合條件的row.顯然,這種method很耗內(nèi)存.
當(dāng)表都不大的時(shí)候,NLJ就夠用了.當(dāng)row數(shù)量很多的時(shí)候,MJ是更好的選擇.最后,如果內(nèi)存很多的話,HJ也可以考慮