快乐学习
前程无忧、中华英才非你莫属!

为SELECT语句创建理想的索引

第4章 为SELECT语句创建理想的索引

练习

4.1 为SQL4.5中的查询语句设计候选索引A和选索引B。

[sql] 

  1. — SQL 4.5  

  2. SELECT A, B, D, E  

  3.   FROM ORDERITEM  

  4.  WHERE B BETWEEN :B1 AND :B2   — (FF = 1…10%)  

  5.    AND C = 1                   — (FF = 2%)  

  6.    AND E > 0                   — (FF = 50%)  

  7.    AND F = :F                  — (FF = 0.1…1%)  

  8.  ORDER BY A, B, C, F  

  9. — WE WANT 20 ROWS PLEASE  

分析:

在SQL4.5中的查询语句的WHERE子句中有四个谓词条件,分别是,一个范围绑定变量谓词B BETWEEN :B1 AND :B2,一个等值常量谓词C = 1,一个范围常量谓词E > 0,一个等值绑定变量谓词F = :F。四个谓词条件的过滤因子分别是FF = 1…10%、FF = 2%、FF = 50%、FF = 0.1…1%。每次结果集有20条,按照A、B、C、F排序。


过滤因子(Filter Factor)描述了谓词的选择性,即表中满足谓词条件的记录行数所占全部行数的比例,它主要依赖于列值的分布情况。


范围谓词字段E没有出现在排序中,而排序中出现的字段A没有出现在WHERE子句的谓词中。根据三星索引理论,不可能为SQL 4.5创建理想的三星索引(第一颗星和第二颗星不可兼得),所以候选索引A为第一星+第三星的索引,候选索引B为第二星+第三星的索引。


创建候选A

1. 取出对于优化器来说不过分复杂的(BT谓词)等值谓词列,将这些列作为索引的前导列,为了使前导的索引片更小,按照将选择性最好的列顺序。最好的选择性是指对于最差的输入值有最低的过滤因子。SQL4.5中有两个等值谓词列C和F,过滤因子分别是2%和1%,所以候选索引为(F,C)。

2. 将选择性最好的范围谓词作为索引的下一个列,如果存在的话。只考虑对于优化器来说不过分复杂的(BT谓词)范围谓词即可。SQL4.5中有两个范围谓词列B和E,过滤因子分别是10%和50%,所以候选索引为(F,C,B,E)。

3. 以正确的顺序添加ORDER BY列(如果ORDER BY列有DESC的话,加上DESC)。忽略在第1步和第2步中已经添加的列。此时候选索引为(F,C,B,E,A)。

4. 以易变性从低到高的顺序将SELECT语句中其余的列添加至索引中。此时候选索引为(F,C,B,E,A,D),这也就是最终的候选索引A。


创建候选B

1. 取出对于优化器来说不过分复杂的(BT谓词)等值谓词列,将这些列作为索引的前导列,为了使前导的索引片更小,按照将选择性最好的列顺序。最好的选择性是指对于最差的输入值有最低的过滤因子。SQL4.5中有两个等值谓词列C和F,过滤因子分别是2%和1%,所以候选索引为(F,C)。

2. 以正确的顺序添加ORDER BY列(如果ORDER BY列有DESC的话,加上DESC)。忽略在第1步中已经添加的列。此时候选索引为(F,C,A,B)。

3. 以易变性从低到高的顺序将SELECT语句中其余的列添加至索引中。此时候选索引为(F,C,A,B,D,E),这也就是最终的候选索引A。


4.2 对每一个候选索引,计算在最差情况下一个事务必须访问的索引行数。ORDERITEM表有100000000行。

候选A

1%*2%*10%*50% * 100000000 = 1000行

候选B

1%*2% * 100000000 = 20000行

打赏

未经允许不得转载:同乐学堂 » 为SELECT语句创建理想的索引

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

特别的技术,给特别的你!

联系QQ:1071235258QQ群:226134712
error: Sorry,暂时内容不可复制!