oracle组合索引选择率计算及如何选择组合索引的列顺序-凯发app官方网站

对应语句的索引访问条件是 id between 1000 and 40000，不包括"code"=159，所以选择率只是id条件的：         
ix_sel
=effective index selectivity
=sel(id between 1000 and 40000) 
=实际范围/总范围 1/num_distinct 1/num_distinct
=(40000-1000)/(100000-1) 1/100000 1/100000
= .3900239
=0.390024
sel(code=159)=1/63904=0.000015648


ix_sel_with_filter用于计算返回行数以及cost,所以要和将id和code条件选择率组合起来，因为是and条件，所以：
ix_sel_with_filter
= .3900239*0.000015648
=.0000061030939872
=.000006


对应总cost=
blevel
ceiling(leaf_blocks * effective index selectivity)
ceiling(clustering_factor * effective table selectivity)
=1
ceiling(290*0.390024)
ceiling(341*.000006)
=1 114 1
=116
注意这里的effective index selectivity按照索引访问条件只有id between 1000 and 40000，虽然执行计划里是：
access("id">=1000 and "code"=159 and "id"<=40000)，这是显示的问题，要看后面的filter("code"=159)，说明
"code"=159是index filtert条件,符合索引访问leftmost prefix规则，前导列code是非等值，后面的列id不能参与索引访问，
只能是index filter。
所以effective index selectivity按照id between 1000 and 40000计算，而不是前面的组合条件选择率，
所以索引访问cost=
blevel
ceiling(leaf_blocks * effective index selectivity)
=1
ceiling(290*0.390024)
=1 114
=115
这里的0.390024只是id选择率。
而执行计划里的rows则是对应条件组合的结果，包括了code过滤条件，所以返回行数=
rows=round(100000*.390125601*0.000015648)=1

对应10053：

注意cost和rows的计算方式，索引访问cost是按照索引访问条件计算的，不包括索引过滤条件，
而rows是按照整个条件计算的结果。

也就是说，执行计划里省略了真正扫描的行数(rows examined)，这里扫描的行数是按照 id between 1000 and 40000 查找的，
因为按照id between 1000 and 40000行数接近总行数40%，如下：


返回3.9w行，扫描过程中按照code =159过滤，所以cost比前面id等值的大很多，所以索引效率要看真正参与index access的索引效率，
等值且选择性好的放前面，这样减少真正access访问的行数（也即减少io），减少index filter数目提高效率。

可以再看个例子，上面的2个例子回表之后没有额外条件，下面这个例子加上ext用于回表后的过滤
（不是ix_sel_with_filters，而是回表后的condition filter selectivity)。

sql如下：

对应的10053如下：

执行计划如下：

强制索引：


从执行计划以及索引访问leftmost prefix规则可以看出，这里的索引访问选择率是："id">=1000 and "id"<=40000条件的，不包括code条件：
索引访问的cost还是按照实际index access的条件计算：
1 ceiling(290*0.390024)=115
回表的按照ix_sel_filters计算，貌似不包括回表的过滤条件"ext" like 'test5%'，
也就是回表cost=ceiling(clustering_factor * effective table selectivity)
=ceiling(341*0.389418 )
=133


id=2返回行是包括index filter条件的，因此返回的行数=
ix_sel_with_filter*num_rows
=round(0.389418*100000)
=38942


回表过滤条件，不是ix_sel_filter,是单纯的索引回表后非索引访问条件的过滤，貌似cost没有包含这个过滤条件，是按照ix_sel_filter计算的。
但是对于返回行数，要考虑回表过滤条件选择率，也即"ext" like 'test5%'的选择率sel_ext,sel_ext*index_rows=返回rows


可以看下ext列统计信息：


对于like后通配，实际上内部是将like转为>= and <来计算：
where "ext" like 'test5%'
按照下面条件计算：
"ext" >= 'test5' and "ext" < 'test6'
实际上不是转为test6,像mysql是转为>='test5\0\0\0\0\0\...'  and <=  'test5    ...'


然后选择率计算要将字符串转为数字，这里使用get_internal_value函数：


具体的字符串如何转为数字的：
1）右侧补0，保留到15个字节
2）转为16进制
3）round左起21位
如果不用get_internal_value，也可以按照上面三点自己编写语句：



对应的
sel=
（604364106164241-604364106163032）/(604364106168138-604364106158196) 1/100000
= 0.121615311
所以返回行数是：round(38942*0.121615)=4736。


注意：如果是like前通配则不一样，可以看到估算返回5000行，对应5%的选择率，这个是默认值，无法转为> and <。

总结：

综上所述，要想建的索引效率高，也就是要提高index access的访问效率，减少访问io,其次要减少index filter以及回表的io。具体来说，组合索引要考虑：

1）组合索引要将常用且等值访问的列作为前导列，如果均匀分布较好，如果选择性好，则更佳。
   因为等值条件后续列还可能参与索引访问（like前通配，<>不行，<,>可以），这样索引访问效率是多列组合结果


2）将选择性好的列放前面，这个指的是条件的选择率，如果列是均匀的且等值访问，可以直接利用列计算条件选择率，
   如果分布不均或非等值，则要考虑具体条件选择率，也即条件返回的行数占总行数比例。
  


3）范围查询一般放后面，比如>,<,between,like后通配,date/timestamp列放后面，因为date/timestamp列一般都是用于范围查询，而非等值查询。


4）创建的索引尽可能地能够消除排序。


5）组合索引的每个列前导列都要按照上面3点考虑，特别是1,2，然后要考虑leftmost prefix规则，让前面的列条件尽可能是等值的，
   才能让后续列也能参与索引访问。