谓词
WHERE 字句由一个或多个“谓词”组成。
WHERE SEX=’M’ AND (WEIGHT>90 OR HEIGHT >190) |
谓词表达式是索引设计的主要入手点。
优化器及访问路径
- 优化器决定访问路径。
- 在查询时,索引的一个窄的片段被顺序扫描,相应的表行从表中读取。所以,访问路径的成本很大程度上取决于索引片的厚度,即谓词表达式确定的值域范围。
索引片越厚,则需要扫描的索引页越多,需要处理的索引记录也越多,而最大开销还是来自于增加的对表的同步操作,每次表页读取需要10ms。
- 另一种较为广泛的描述索引片的方法是定义索引匹配列的数量。如果WHERE中有多个列,而且这个列也在索引上,从而能使得多个列能够一同定义一个更窄的索引片段。可有效减小索引的处理量,并减少对表的同步读的次数。
- 索引过滤及过滤列:并不是所有的索引列都能定义索引片的大小。不过这些列仍然能够减小回表次数,还是有作用的,这些列称为过滤列。下面是个例子:
如:索引列,A、B、C、D
查询谓词:WHERE A=:A AND B>:B AND C=:C
接下来分析:
谓词A:索引A也是第一个,等值匹配,A是匹配列,可以用来定义索引片
谓词B:B是一个范围谓词,它可以是匹配列,但它之后的,就不能是匹配列了。
谓词C:基于上面一句,C不是匹配列。它不能参与定义索引片大小,但能帮助避免不必要的回表访问。作为过滤列。
规律:匹配列由范围谓词中断,做不了匹配列,还可以做过滤列。如果没有B谓词,那只有列A是一个匹配列,但C仍然可以用来做过滤。如果B的是等值表达式,则三个都可以作为匹配列。如果没有A谓词,则B、C无法匹配,索引片是整个索引,但B、C还是可以用来做过滤。
- EXPLAIN:执行计划。当发现一个慢SQL时,首先怀疑的应该是优化器,需要查看执行计划看访问方式是否正确。
- 统计信息:收集统计信息,才能帮助优化器做更好的决策。收集信息可包括基础信息(表记录数、表页数、叶子页数、每个索引的聚簇绿、某些列或者列组的不同值得个数(基数),以及某些列的最大、最小值等);还有一些可选统计信息(如:列和列组值得分布情况)
- 路径选择资源消耗不可忽略:每次SQL语句执行都进行一次访问路径选择要比仅做一次消耗更多资源。但并非绑定变量就一定好。比如WHERE SALARY>1000比WHERE SALARY >:SALARY更明确。
- 过滤因子:即表中满足谓词条件的记录行数占的比例,它主要依赖于列值的分布情况。
性别:过滤因子很大
CITY=:CITY使用平均过滤因子(1/不同CITY的个数)
评估一个索引是否合适时,最差情况下的过滤因子比平均过滤因子更重要,因为最差情况与最差输入相关,此时查询将消耗最长时间。
组合谓词的过滤因子:如果组合谓词之间没有相关性,则过滤因子就是谓词过滤因子的乘积。有相关性时,过滤因子会更低。
优化器在评估可选的访问路径的成本时,必须先评估过滤因子。索引片大小就是匹配组合谓词的过滤因子*总行数。
一些教科书建议索引列顺序按照基数的降序来排序。不完全对,就不用遵循了。
物理化结果
- 物理化结果:执行必要的数据库访问来构建结果集。最好情况,只需要简单地从数据库缓冲池向应用程序返回一条记录、最差情况,需要发起大量的磁盘读取。
- 当SELECT语句只查询一条记录时,优化器必须在SELECT请求被执行时就物理化记录结果。
- 当结果集可能有多条记录而需要使用游标时,有两种选择:
第一:DBMS在OPEN CURSOR时物化整个结果集(或者至少在第一次FETCH时)
第二:每次FETCH物化一条记录。
- 一次FETCH按照DCLARE CURSOR语句定义向应用程序返回结果集的一条记录。如果游标声明包含了绑定变量,则会在OPEN CURSOR调用之前,就把变量值传递进去。如果应用程序期望使用一个游标获取多个结果集,那么首先需要执行一次CLOSE CURSOR调用,然后向绑定变量传入新值,在执行OPEN CURSOR打开游标。
- 一般说法:DELARE CURSOR所定义的SQL语句总是在OPEN CURSOR调用的时刻传值并组织结果集。但并非如此。方法1:DBMS尽可能晚地进行结果集物化。方法2:但如果先物化,那么FETCH调用时,可从临时表中检索记录,而在更新时,并不会更新临时表中的记过。
方法1:一次FETCH调用物化一条记录,条件
没有排序需求
或者虽然有排序,但是:存在一个索引满足结果集ORDER BY 排序需求、且优化器决定以传统的方法使用这个索引:访问第一个索引并读取相应的表,然后访问第二个。以此类推。
放法2:提前物化:主要就是有排序需求。对结果集排序,意味着即时只提取第一条记录,也必须物化整个结果集。