索引优化原则及Oracle中索引总结

Oracle索引建立原则

确定针对该表的操作是大量的查询操作还是大量的增删改操作。

尝试建立索引来帮助特定的查询。检查自己的sql语句,为那些频繁在where子句中出现的字段建立索引。

where语句中不得不对查询列采用函数查询,如upper函数,最好建立相应函数索引;

在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引

用于联接的列(主健/外健)上建立索引;

在经常存取的多个列上建立复合索引,但要注意复合索引的建立顺序要按照使用的频度来确定;

尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间,同时,复合索引也占磁盘空间。

对于小型的表,建立索引可能会影响性能

在不同值较少的字段上不必要建立索引,如性别字段;

应该避免对具有较少值的字段进行索引。???

避免选择大型数据类型的列作为索引。

缺省情况下建立的是非簇集索引,但在以下情况下最好考虑簇集索引,如:含有有限数目(不是很少)唯一的列;进行大范围的查询;

充分的利用索引可以减少表扫描I/0的次数,有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中

避免在有大量并发DML运算的表中使用Bitmap索引;

经常被更新,或者一个表虽然很大,但是如果多数查询返回结果都超过表中总行数的4%,那么一般认为也是不宜建立索引的。

经常查询的记录数目少于表中所有记录总数的5%时就应当创建索引

存储索引的表空间最好单独设定

随着数据的变化,索引的效率会下降,因此应定期重建索引

Oracle位图索引  B-树索引

B-树索引在Oracle中是一个通用的索引,在创建索引时它就是默认的索引类型。最多可以包括32列。

创建语句:create index indexName on tableName(columnName);

特点: 

1.索引不存储null值。更准确的说,单列索引不存储null值,复合索引不存储全为Null的值。索引不能存储Null,所以对这列采用is null条件时,因为索引上根本没Null值,不能利用到索引,只能全表扫描。

2.不适合键值较少的列。与数据块有关。

3.前导模糊查询不能利用索引(like '%XX'或者like '%XX%')。这样会导致全表扫描。

位图索引Oracle为每个唯一键创建一个位图,然后把与键值所关联的ROWID保存为位图。最多可以包括30列。

创建语句:create bitmap index indexName on tableName(columnName);

特点:

1.相对于B*Tree索引,占用的空间非常小,创建和使用非常快。位图索引由于只存储键值的起止Rowid和位图,占用的空间非常少。

2.不适合键值较多的列。

3.不适合update、insert、delete频繁的列。

4.可以存储null值。B*Tree索引由于不记录空值,当基于is null的查询时,会使用全表扫描,而对位图索引列进行is null查询时,则可以使用索引。

5.当select count(XX) 时,可以直接访问索引中一个位图就快速得出统计数据。

6.当根据键值做and,or或 in(x,y,..)查询时,直接用索引的位图进行或运算,快速得出结果行数据。

位图索引有很多限制:

基于规则的优化器不会考虑位图索引

当执行ATLER TABLE语句,并修改包含有位图索引的列时,会使位图索引失效

位图索引在索引块中储存了索引键的值;然而,他们并不能用户任何类型的完整性检查

位图索引不能被申明为唯一索引

索引不会被命中的情况

1、查询谓词没有使用索引的主要边界,可能会导致不走索引。

查询:SELECT * FROM T WHERE Y=XXX;

假如T表上有一个包含Y值的组合索引,但是优化器会认为需要一行行的扫描会更有效,这个时候,优化器可能会选择TABLE ACCESS FULL,

查询换成:SELECT Y FROM T WHERE Y = XXX,优化器会直接去索引中找到Y的值,因为从B树中就可以找相应的值。 

2、如果在B树索引中有一个空值的时候,优化器可能不会走索引。

查询:那么查询诸如SELECT COUNT(*) FROM T 

有两种方式可以让索引有效,一种是SELECT COUNT(*) FROM T WHERE XXX IS NOT NULL或者是不能为空。 

3、在索引字段上使用函数,导致不会走索引。

查询:SELECT * FROM T WHERE FUN(Y) = XXX。

如果在T表上有一个索引Y时,索引也不会被用到,因为你要查询的列中所有的行都需要被计算一遍,

可以在这个表上建立一个基于函数的索引,比如CREATE INDEX IDXFUNT ON T(FUN(Y));这种方式,等于Oracle会建立一个存储所有函数计算结果的值,再进行查询的时候就不需要进行计算了。 

4、索引不适用于隐式转换的情况。

查询:SELECT * FROM T WHERE Y = 5 

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/45b36e1cee5cf4c0ad28d65e7721f5be.html