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