Oracle-创建索引的字段选择原则

本文中所讨论的索引是B-Tree索引

1. 在WHERE子句中最频繁使用的字段
2. 联接语句中的联接字段
3. 选择高选择性的字段(如果很少的字段拥有相同值,即有很多独特值,则选择性很好)

 

另在创建索引时, 还可以充分考虑下面一些情况:

 

1. ORACLE在UNIQUE和主键字段上自动建立索引, 所以无需对主键再创建索引
2. 在选择性很差的字段上建索引只有在这个字段的值分布非常倾斜的情况下下才有益(在这种情况下,某一,两个字段值比其它字值少出现很多)
3. 不要在很少独特值的字段上建B-TREE索引,在这种情况下,你可以考虑在这些字段上建位图索引.在联机事务处理环境下,所由并发性非常高,索引经常被修改,所以不应该建位图索引
4. 不要在经常被修改的字段上建索引.当有UPDATE,DELETE,INSETT操作时,ORACLE除了要更新表的数据外,同时也要更新索引,而且就象更新数据一样,或产生还原和重做条目
5. 不要在有用到函数的字段上建索引,ORACLE在这种情况,优化器不会用到索引,除非你建立函数索引
6. 可以考虑在外键字段上建索引,这些索引允许当在主表上UPDATE,DELETE操作时,不需要共享子表的锁,这非常适用于在父表和子表上有很多并发的INSERT,UPDATE和DELETE操作的情况
7. 当建立索引后,请比较一下索引后所获得的查询性能的提高和UPDATE,DELETE,INSERT操作性能上的损失,比较得失后,再最后决定是否需建立这个索引

理解索引的选择性和密度  ?

选择性

选择性可以通过公式度量:(从查询中返回的行/表中所有的行). 表中仅有一行可以满足查询的独特索引具有100%的选择性,并且这种索引总会被使用. 如果返回行的百分比较抵,这个索引就有高度的选择性,它容易被查询优化器选择. 如果百分比高在表中许多行都会被获取,则选择性抵,索引的用处不大. 正如以上提到的,索引选择性至少需要90%. 如果表需要返回超过10%的行,则索引的选择性就太低了,这个索引就会被忽略. 经常的情况是,如果查询要获取的行超过5%,查询优化器就会选择不同的索引或者执行全表扫描. 事实上,在查询优化器选择一个索引之前,它希望可以90%-95%的行.一个比较好的消息就是你可以很容易地度量选择性.如果选择性超过95%,则这个索引就很有可能被选择以满足查询.

密度

密度定义了行中重复值的百分比.密度的范围从0-1.低密度值更受欢迎,高选择性的索引有低的密度值,通常范围从0.05-0.10.如果换算成选择性95%-90%.如果索引上的密度值超过了0.10,它就可能不被使用.当使用非群集索引的时候,数据怎样访问? 数据页会按照索引的次序获取,除了在页需要被再次访问的很少情况下,请求不会是顺序的..

在建立有用的索引的时候,列的唯一性是一个重要的变量.唯一性高对于非群集索引来讲是一个很好的选择.与此相对的,唯一性低对于群集索引来讲是一个很好的备选,唯一性非常小(小于3)的列根本不能被索引.

     选择性可以通过分析索引的分布统计得到.通过使用DBCC SHOWCONTIC 或者DBCC SHOW_STATISTICS 可以检查索引列上的数据分布.它们可以为索引的选择性和密度提供一个很好的度量.DBCC SHOWCONTIC 对于理解一个表或者索引的碎片程度有用.DBCC SHOW_STATISTICS 可以显示索引中包含有多少个重复值.重复值的数量越高,选择性就越低,要记住,在索引选择期间,查询优化器更偏好高选择性.DBCC SHOW_STATISTICS 在度量一个索引效果的时候非常有用.