建立组合索引的字段顺序优化

简介

组合索引我们经常用到,建立组合索引大家也都会,但是如何考虑建立组合索引的顺序是一个值得推敲的事情。

正文

1. 尽量把最常用的字段放在最前面

对于我们需要创建的组合索引,如果同时又经常单独使用其中某个字段作为查询条件,这样的字段是要求放在组合索引前面的。

因为这种场景下,能直接使用组合索引做范围扫描,否则,如果该字段放在后面,可能走索引跳跃扫描,全索引扫描,甚至全表扫描。

举例:
  1. 首先创建表

    create table t_userserviceinfo_test as select * from T_USERSERVICEINFO nologging;
    
  2. 创建索引,把常用字段 phonenumber 作为组合索引的前导列

    create index ix_userserviceinfo_test_1 on t_userserviceinfo_test(phonenumber,servstaus) tablespace ringidx;
    
  3. 按号码查询,查看执行计划,走了该索引的范围扫描,很快就查到了结果。

反例:
  1. 删除上面的索引

    drop index ix_userserviceinfo_test_1;
    
  2. 创建新的索引,把 phonenumber 不作为前导列

    create index ix_userserviceinfo_test_1 on t_userserviceinfo_test(servstaus,phonenumber) tablespace ringidx;
    
  3. 同样按号码查询,查看执行计划,走了该索引的跳跃扫描,效果不好。

当然 Oracle考虑执行COST,可能就不会走这个索引了,导致全表扫描。

2. 尽量把离散值较高的字段往前放

   	1. 条件中有单独使用这个字段,那么使用该索引有很好的效果
   	2. 放置误用索引,如果离骚之较少的字段放前面,同时条件中仅包含该字段,那么 Oracle 可能会选择该索引,但是其实选择该索引,选择率很低。

3. 查询时,有的列是非等值条件,有点是等值条件,则等值条件字段放在前面

   	1. 等值条件字段放在前面,在查找的时候,找到的索引块都是有效数据。
   2. 如果非等值字段放在前面,那么需要进行索引跳跃扫描,或者范围扫描,这是就扫描了很多无效的索引。
举例:
  1. 现需要根据状态和时间查找数据

     select * from t_userserviceinfo_test t where servstatus = 1 and t.upstatusstime > sysdate -100;
    
  2. 简历两个索引,分别把状态和时间字段顺序颠倒:

    create index ix_userserv_test_1 on t_userserviceinfo_test(servstaus,upstatustime);
    
    create index ix_userserv_test_2 on t_userserviceinfo_test(upstatustime,servstaus);
    
  3. 使用第一个索引查找

    select /* +index(ix_userserv_test_1) */  *
    from t_userserviceinfo_test t where t.servstaus = 1 and t.upstatustime > sysdate -100;
    

    得到执行分析如下,看到一致读数量为 334

    数据的查找过程是:首先从 servstaus = 1,upstatustime = sysdate -100 开始,找到第一条满足 servstaus = 1,upstatustime > sysdate -100 的数据,然后在索引树叶子节点顺序查找,直到找到第一条不满足条件的数据(servstaus = 2),退出查找,这个过程中查找到的索引都是有效索引。

    1. 使用第二个索引查找:
    select /* +index(ix_userserv_test_2) */  *
    from t_userserviceinfo_test t where t.servstaus = 1 and t.upstatustime > sysdate -100;
    

    看到一致读是前面的十倍,效果很不好。

    数据超找过程是:根据 upstatustime > sysdate -100 走的范围索引扫描,同时通过 servstaus = 1 过滤数据,存在大量的无用查找。

总结:

建立组合索引要考虑自身以及其他场景的使用情况,不要随意指定顺序。

02-11 07:56