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

索引优化原则及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 

    在Y上面有一个索引,但是Y列是VARCHAR2的,那么Oracle会将上面的5进行一个隐式的转换成SELECT * FROM T WHERE TO_NUMBER(Y) = 5,这个时候也是有可能用不到索引的。 

5、如果表只有几个数据块大小,而且可以被Oracle一次性抓取,那么就没有使用索引的必要了,因为抓取索引还需要去根据rowid从数据块中获取相应的元素值,因此在表特别小的情况下,索引没有用到是情理当中的事情。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值