Oracle开发建议系列第三期 ——索引的使用建议

作者：周鹏

索引的使用原则

使用索引可以迅速定位数据查询的目标记录。在表上建立合适的索引可极大提升应用程序的数据查询效率。

开发人员应对表上各种查询需求进行综合考虑，统一规划，建立恰当的索引来满足业务需求。并发量较高的查询，均应使用索引以避免随着表中数据量增大而导致查询性能严重下降。

索引使用的建议项

根据实际案例和运维经验，梳理以下建议项与大家分享：

（1）联机交易表上的索引数量不宜过多
建立索引会增加索引维护的成本，降低增删改的性能，在有大并发增删改需求的联机交易表上创建索引建议不超过5个。

（2）复合索引包含字段不宜过多
索引中包含字段过多会导致索引臃肿庞大，甚至超过表的大小，维护开销极大，因此要求单个索引包含的字段不超过5个。

应该选择最能过滤得到所需记录的字段放入索引，而不是把查询中用到的所有字段都放入索引。比如字段column_A和column_B都在where过滤条件中出现，符合column_A过滤条件的记录占表中总记录的10%，符合column_B过滤条件的记录占表中总记录的50%，则column_A应优先放入索引。

（3）复合索引字段组合唯一性越高越好
复合索引字段组合的唯一性一般越高越好，即：Select count(*) from (select distinct 所有索引字段 from 表)返回结果和表的总记录数越接近越好。如果索引字段组合的唯一性少，就意味着在相同过滤条件下通过该索引返回的记录数可能会很多，不利于提升查询效率。

注意：索引中所有字段都应当为查询条件中使用到的字段，不得为了提升索引组合唯一值而加入业务SQL中没有用到的字段。

（4）复合索引字段顺序要合理安排

复合索引中字段顺序非常重要，应优先将选择性最高的字段放在第一位。复合索引中其他字段的顺序也应该按选择性由高到低排列。选择性是指该字段中唯一值的数量，该数量和表中总记录数越接近，说明选择性越高。

选择性接近情况下，一般在Where过滤条件中为等值查询的字段应该放在范围查询字段（大于、小于、between and）的前面。

注意：首字段选择性不高时，执行计划中容易出现“INDEX SKIP SCAN”，此类执行计划常导致性能问题。单表中同一个字段同时作为多个索引的首字段时，容易导致Oracle无法选择出最适合的索引，造成执行计划异常。因此，建议选择同一个字段作为首字段的索引不超过2个。当选择性最高的字段已经在该表上其他两个索引上做为首字段时，新建索引时首字段应该选择其他高选择性字段。

（5）不得存在冗余索引

当一张表上一条索引的所有字段为另外一条索引的前导字段，且字段顺序也完全相同，则该索引是冗余索引。表上不得存在冗余索引，因为即使一条查询仅使用某复合索引前几个字段，该复合索引同样可以起到加速作用。

（6）异常状态类字段索引根据业务特性单独处理
STATE、TYPE、STATUS类型字段往往唯一值很少，绝大多数记录的值都一样，少数记录有不同的特殊值，而业务上通常需要找到这些特殊值进行处理，此时建立以此类字段为起始字段的索引有助于加速应用查询。

注意：为了避免统计信息不准确造成数据库无法应用此类索引，使用该索引的查询语句应在SQL文本中增加提示（hint）明确指定使用该索引。

（7）繁忙表上索引应HASH分区
联机交易中繁忙表索引应进行HASH分区，分区目的在于避免热点块的出现，让同时并发的操作分散在更多的分区上，这对于提升性能至关重要。出现热点块时，往往也伴随着索引分裂的大量出现，严重影响业务性能。

注意：应合理选择分区字段进行HASH分区，一般为索引首字段或其他选择性高的字段。不宜使用交易时间字段作为分区键，因为并发是在同一时间发生的，不能避免热块出现。索引HASH分区的数量应选择为2的幂数。繁忙表上除了主键，尽量不建其他唯一索引（UNIQUE index），以减少索引热块争用。

（8）主键选择有业务意义的字段
不得利用无业务意义的单调递增序列值作为主键。单调递增序列值作为主键容易产生索引热块，出现索引分裂等待事件：enq:TX-index contention，产生性能瓶颈。

（9）外键字段必须创建索引
若子表的外键上没有索引，当父表删除记录或更新主键字段时会锁定子表所有记录，不利于系统并发业务处理。因此外键字段上必须创建索引。