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也可以考虑