第6章 影响索引设计过程的因素 练习
6.1 图6.5中的SELECT查询在当前索引条件下需要花费1分钟。请用两种方案设计可能的最佳索引:(1)不增加额外的第三个索引,(2)增加第三个索引。
分析: 如图6.5所示,TABLE上字段A有主键聚簇索引,字段C有非聚簇索引。查询谓词中有字段C的范围条件,并且结果集按C排序。因为谓词中只有字段C有索引,所以在不添加新索引的前提下,不可能创建三星理想索引(不满足第一颗星)。扫描现有的字段C上的索引,可以消除排序,所以C上的索引本身满足第二颗星。不增加额外的第三个索引,可以做的就是修改字段C上的索引,以C作为索引前缀,把查询中其他字段加入到现有的索引中,这样可以最大化索引过滤(B = :B AND D = 1),而且这是一个宽索引,满足第三颗星,只需访问索引,避免回表。有了上面的分析,第一小问的答案就是索引(C,B,D,E,F),C后面的DB字段顺序可换,EF字段顺序可换。该索引有1个匹配列C,两个过滤列B和D。扫描的是满足C > :C的索引片,用B = :B和D = 1作为过滤条件。如果可以增加第三个索引,可以把等值谓词作为索引前缀,而范围谓词C正好是唯一的排序字段,所以可以创建三星理想索引。第二小问的答案就是索引(B,D,C,E,F),其中BD的顺序可换,EF的顺序可换。这是一个三星索引,匹配列是BDC三列,扫描满足B = :B AND D = 1 AND C > :C的索引片,同时消除额外的排序。 6.2 假设1秒是我们可接受的执行时间,并且我们不愿增加一个新索引。对于上一题中的(1)方案,你需要了解哪些信息来预计它所能带来的性能提升? (1)方案扫描的是满足C > :C的索引片。根据第5章QUBE方法的假设,顺序扫描一行的时间为0.01ms,Fetch一行的时间是0.1ms。 那么根据1秒的要求可以得出下面的关系: 满足C > :C的最差输入的FF * 10000000 * 0.01ms + x * 0.1ms <= 1s 其中FF是C > :C的过滤因子,x是结果集返回的行数,这两个参数就是需要了解的信息。当FF=1%时,需要扫描100000行,所需时间是1秒。