同乐学堂第4章 为SELECT语句创建理想的索引
练习
4.1 为SQL4.5中的查询语句设计候选索引A和选索引B。
[sql]
-
-- 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行
微信扫一扫，打赏作者吧～
