索引建设原则总结

一、 数据库建立索引常用的规则如下：
1、表的主键、外键必须有索引；
2、数据量超过300的表应该考虑建索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
8、正确选择复合索引中的主列字段，一般是选择性较好的字段在前，按选择性降序排列；
9、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否
       极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
10、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
11、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
12、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
13、频繁进行数据操作的表，不要建立太多的索引；
14、删除无用的索引，避免对执行计划造成负面影响；

15、非空字段不要建立索引。

 

以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

二、复合索引建设原则与适用范围：

建设原则：

1.前缀性：只有当复合索引的第一个字段出现在sql语句的where条件中时，索引才会被用到。

       如复合索引为(A,B,C)，只要谓词条件中出现第一个字段A，就可以用复合索引，否则不会用。
唯一的例外是skip scan index（跳跃扫描索引），就是如果Oracle发现第一个字段的值很少，会自动拆分为两个复合索引。如复合索引(A,B,C)，因为第一个字段A只有两个值：男和女，因此Oracle会将这个索引拆分成('男',B,C)和('女',B,C)两个复合索引，这样即使A没有出现在谓词条件中，也可以用该复合索引。

INDEX SKIP SCAN是9i之后提供的新功能，实现了即使WHERE条件中不存在索引的前导列也可以使用索引。但是INDEX SKIP SCAN是需要下面几个条件的：

1，版本9i及以后

2，CBO（表和索引都经过分析）

3，索引的前导列重复值很少（选择性低）--这边其实已经与索引的第二个建设原则高选择性相违背了，所以若用到skip scan了，一般都是索引建的有问题。

4，WHERE条件中不存在索引前导列

 

2.高选择性：按照选择性（字段值的多少）的高低来排列索引字段的先后顺序。因为选择性越高，定位的记录就越少，查询效率就越高

 

什么情况下优化器会用到复合索引呢?

       (a) 当SQL语句的WHERE子句中有用到复合索引的领导字段时,ORACLE优化器会考虑用到复合索引来访问.

       (b) 当某几个字段在SQL语句的WHERE子句中经常通过AND操作符联合在一起使用作为过滤谓词,并且这几个字段合在一起时选择性比各自单个字段的选择性要更好时,可

       能考虑用这几个字段来建立复合索引.

       (c) 当有几个查询语句都是查询同样的几个字段值时,则可以考虑在这几个字段上建立复合索引.

 

三、不走索引的情况：

case1:建立组合索引，但查询谓词并未使用组合索引的第一列，此处有一个INDEX SKIP SCAN概念见书。
case2:在包含有null值的table列上建立索引，当时使用select count(*) from table时不会使用索引。
case3:在索引列上使用函数时不会使用索引，如果一定要使用索引只能建立函数索引。
case4:当被索引的列进行隐式的类型转换时不会使用索引。如:select * from t where indexed_column = 5，而indexed_column列建立索引但类型是字符型，这时Oracle会产生
隐式的类型转换，转换后的语句类似于select * from t where to_number(indexed_column) = 5，此时不走索引的情况类似于case3。日期转换也有类似问题，如:
select * from t where trunc(date_col) = trunc(sysdate)其中date_col为索引列，这样写不会走索引，可改写成select * from t where date_col >= trunc(sysdate)
and date_col < trunc(sysdate+1)，此查询会走索引。
case5:并不是所有情况使用索引都会加快查询速度，full scan table 有时会更快，尤其是当查询的数据量占整个表的比重较大时，因为full scan table采用的是多块读，
当Oracle优化器没有选择使用索引时不要立即强制使用，要充分证明使用索引确实查询更快时再使用强制索引。
case6:很久没有分析表了。如果没有正确的统计信息，CBO将无法做出正确的决定。

case7:<>

case8:like’%dd’百分号在前
case7:not in,not exist

  

附：位图索引与B-TREE索引的比较

• 位图索引更节省存储空间
• 位图索引比较适用于数据仓库环境,但不适于联机事务处理环境.在数据仓库环境,数据维护通常上通过批量INSERT和批量UPDATE来完成的,所以索引的维护被延迟直到DML操作结束.举例:当你批量插入1000行数据时,这些插入的行被放置到排序缓存中(SORT BUFFER),然后批处理更新这1000个索引条目,所以,每一个位图段在每一个DML操作中只需更新一次,即使在那个位图段里有多行被更新
• 一个键值的压缩位图是由一个或多个位图段所组成,每一个位图段大约相当于半个BLOCK SIZE那么大,锁的最小粒度是一个位图段,在联机事务处理环境,如果多个事务执行同时的更新(即并发的更新),使用位图索引就会影响UPDATE,INSERT,DELETE性能了
• 一个B-TREE索引的条目只包含一个ROWID,因此,当一个索引条目被锁定,即一行被锁定.但是对于位图索引, 一个索引条目潜在地有可能包含一段ROWID(即某一个范围内的ROWID,有多个ROWID),当一个位图索引条目被锁定时,则这个条目包含的那一段ROWID都被锁定,从而影响并发性.当一个位图段内的ROWID的数量越多时,并发性就越差.虽然如此,对于BULK INSERT,UPDATE和DELETE,位图索引的性能还是比B-TREE索引要好

什么情况下位图索引能够改善查询的性能呢?

• WHERE子句包含多个谓词于中低基数的字段 
•  单个的谓词在这些中低基数的字段上选取大量的行 
•  已经有位图索引创建于某些或全部的这些中低基数的字段上
• 被查询的表包含很多行
• 可以在单一个表上建立多个位图索引,因此,位图索引能够改善包含冗长WHERE子句的复杂查询的性能,在合计查询和星形模型的联接查询语句中,位图索引也可以提供比较优良的性能