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

第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行