原创

《数据库索引设计优化》读书笔记(二)

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://wxy0327.blog.csdn.net/article/details/50618710
第4章 为SELECT语句创建理想的索引
练习
4.1 为SQL4.5中的查询语句设计候选索引A和选索引B。
-- SQL 4.5
SELECT A, B, D, E
  FROM ORDERITEM
 WHERE B BETWEEN :B1 AND :B2   -- (FF = 1...10%)
   AND C = 1                   -- (FF = 2%)
   AND E > 0                   -- (FF = 50%)
   AND F = :F                  -- (FF = 0.1...1%)
 ORDER BY A, B, C, F
-- 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行
文章最后发布于: 2016-02-01 15:09:23
展开阅读全文
0 个人打赏
私信求帮助

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览