程序员必须知道的Oracle索引知识

 

simon 发表于  2012 -11 - 7   标签   oracle , 数据库优化 , 程序员 , 索引  阅读 878 次       

0 评论

        关系数据库的精髓就是通过关系表进行规范化的数据存储，通过各种表连接技术和各种类型的索引技术来进行信息的检索和处理。合理的索引是保证各种操作快速、高效的关键。据统计，与数据库有关的性能问题，70%是因为程序员对索引和表连接的不了解，导致SQL语句共享性不高、全表扫描、索引被抑制而造成的。常用的索引类型有 B-树索引、位图索引、HASH索引、索引编排表、反转键索引、基于函数的索引、分区索引、本地和全局索引。

一、索引设计基本原则

     1) 针对B*树单字段索引的设计原则         1、分析SQL语句中的约束条件字段         2、如果约束条件字段不固定，建议创建针对单字段的普通 B*树索引         3、选择可选性最高的字段建立索引         4、如果是多表链接SQL语句，注意被驱动表的连接字段是否需要创建索引         5、通过分析工具，分析执行计划查看设设计是否合理      2) 针对复合索引          使用复合索引必须明白复合索引的前缀性(在Oracle9i之前，只有在使用到索引的前导索引时才可以使用组合索引！)和可选性（使用USER_INDEXES视图，该视图中显示了一个distinct_keys列。比较一下唯一键的数量和表中的行数，就可以判断索引的选择性。选择性越高，索引返回的数据就越少）         1、分析SQL语句中的约束条件字段         2、如果约束字段比较固定，则优先考虑创建针对多字段的普通B*树复合索引         3、如果单个字段是主键或唯一字段，或可选择性非常高的字段，尽管约束条件比较固定，也不一定要建成复合索引，可建成单字段索引，降低索引开销         4、优先考虑复合索引的前缀性，再考虑可选性。按可选性由高到底复合。         5、如果条件涉及的字段不固定，组合比较灵活，则分别建立多个单字段上的索引         6、如果是多表连接SQL语句，看是否可以在被驱动表的连接字段与该表的其他约束条件字段上建立复合索引         7、通过分析工具，分析执行计划查看设设计是否合理

二、如何避免索引被抑制

    1、不要对字段加函数，包括自定义函数，加函数了索引会被抑制，可以用函数索引，但是应尽量避免使用，函数索引维护成本比普通索引大。     2、不要将字段嵌入表达式中例如 where a.s + 7 < 24,这样即表a的s字段有索引也不会被使用     3、连接字段类型要一致，连接字段如果一个为字符一个为long，索引也不会被使用     4、使用不等于操作符（<>、!=）、使用IS NULL 或 IS NOT NULL等也会造成索引被抑制

三、索引监控分析与优化

    1、如何查看索引的详细信息

1	select * from all_ind_columns where index_name = 'indexname';

           2、查询等待事件，如果在主要等待事件中，db file sequential read 比较高，说明在整个数据库系统中，索引的读写操作比较多       ，已经成为主要瓶颈。     3、(9i后）在某个业务开始之前，执行如下命令

1	alter index <索引名> monitoring usage;

     在业务结束之后执行如下命令，结束监控

1	alter index <索引名> nomonitoring usage;

     然后查询如下视图，就能知道索引到底有没有被利用

1	select * from v$object_usage;

          4、频繁的对索引字段进行UPDATE , DELETE操作，会对索引造成大量碎片，影响索引效率，并造成索引I/O的增加         a、执行如下语句可检测索引碎片的情况

1	select * from user_objects where object_type='INDEX';

1	analyze index <索引名> validate structure online;

1	select name,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100 from index_stats;

      表中：索引碎片率(%)=(del_lf_rows_len/lf_rows_len)*100 如果索引碎片率超过20%，则Oracle认为索引碎片已经非常严重，建议DBA编写一个检测所有索引碎片率的脚本，定期运行，保持对索引碎片率的监测,建议超过15%就进行索引碎片的整理。     b、索引碎片整理       重建(锁表)：alter index <索引名> rebuild;          压缩索引(不锁表)：alter index <索引名> coalesce;          建议定期对那些删除操作频繁的表重建索引。

四、其他相关操作建议

     1、当批量导入大量数据时，索引会影响导入速度，可以先Drop索引，导入后再重建索引。      2、不要乱建索引索引的目的是为了提高查询速度，但它会加重DML的负担。索引和表应该放在不同的表空间，如果把索引和表放在同一个空间，会引起竞争，因为在读取一个表时，记录和索引是同时读取，修改也同步进行的。      3、索引空间是extent是大小应该是5 blocks的倍数，因为oracle是一次读出5个blocks，如果你的extends是6，就会造成2次I/O操作。      4、在对大数据量的表进行创建索引时可以关闭索引对应的redo日志，提高速度，因为索引和数据不同，如果索引创建时出意外，数据还在，就再创建一次好了。     5、INITRANS 参数比对应的表的值大些，因为索引也是已表记录的方式保存的，但索引大大小于表的记录，所以一个block中存储的索引记录就大大多于表在一个block中的记录，加大INITRANS可以增加在一个block中的事务的并发数，就提高了效率。

说明:文中很多参考《品悟性能优化–罗敏》，在此也推荐大家去阅读，内容不错。