我们通常会去建一些联合索引来支持复杂的sql语句，以便查询更加有效，索引中包含多个字段，字段先后顺序的不同常常会导致巨大的性能差异。看下面的例子：
1.联合索引的定义

crm    idx_det_sta_deal      status        1 valid   tbs_crm_ind
crm    idx_det_sta_deal      deal_id       2 valid   tbs_crm_ind
crm    idx_det_sta_deal      gmt_create    3 valid   tbs_crm_ind

crm    ind_det_sta_gmtcr     status        1 valid   tbs_crm_ind
crm    ind_det_sta_gmtcr     gmt_create    2 valid   tbs_crm_ind
crm    ind_det_sta_gmtcr     deal_id       3 valid   tbs_crm_ind

2.sql分别使用两个索引的效率

SQL> set autot traceonly
SQL> select count(*)
  2    from crm_detail t
  3   where t.status = 1 and t.gmt_create > (sysdate - 5);
Execution Plan
----------------------------------------------------------
  SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=11)
0   SORT (AGGREGATE)
1     INDEX (RANGE SCAN) OF 'IND_DETAIL_STA_GMTCREATE' (NON-UNIQUE)
     (Cost=23 Card=20297 Bytes=223267)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         30  consistent gets
         17  physical reads
          0  redo size
        492  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--指定另一个索引
SQL> select/*+index(t IDX_DET_STA_DEAL)*/ count(*)
  2    from crm_detail t
  3   where t.status = 1 and t.gmt_create > (sysdate - 5);
Execution Plan
----------------------------------------------------------
   SELECT STATEMENT Optimizer=CHOOSE (Cost=1038 Card=1 Bytes=11  )
 0   SORT (AGGREGATE)
 1     INDEX (RANGE SCAN) OF 'IDX_DET_STA_DEAL' (NON-UNIQUE)
      (Cost=2595 Card=20297 Bytes=223267)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3650  consistent gets
          0  physical reads
          0  redo size
        492  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
1	rows processed

通过试验我们可以看出，索引字段位置稍一调整，逻辑读就相差了100倍左右。
这篇其实没有什么含义，只是提醒大家建联合索引时要细心一点，多去衡量sql查询条件（通常索引会支持很多个查询），衡量索引所包含字段的数据分布，衡量字段顺序不同带来的影响，以便建立出更有效的联合索引。

--EOF--

评论：

1.  1丁原

   例子举得不好。
   我应该通过status+gmt_create，gmt_create+status分别来建立索引，这样就好比对了，看起来也会清楚很多。

   我们经常会遇到一个索引包含多个字段，服务于多个查询sql，这时候就要评估一下关键字段的先后顺序。

   Comment on Sep 14th, 2008 at 9:14 am  
2.  2orphean

   where t.status = 1 and t.gmt_create > (sysdate - 5);
   针对这个查询条件，
   crm ind_det_sta_gmtcr status 1 valid tbs_crm_ind
   crm ind_det_sta_gmtcr gmt_create 2 valid tbs_crm_ind
   这个字段顺序的索引应该是最高效的。

   crm idx_det_sta_deal status 1 valid tbs_crm_ind
   crm idx_det_sta_deal deal_id 2 valid tbs_crm_ind
   crm idx_det_sta_deal gmt_create 3 valid tbs_crm_ind
   因为gmt_create属于第三等级的字段，而且查询中并没有deal_id，效率就不高了

   这些确实需要在平时开发过程中注意，建立最适合查询和最合理利用资源的索引 o(∩_∩)o..

本文来自：http://rdc.taobao.com/blog/dba/html/205_oracle_union_inde.html

分类： 数据库开发 数据仓库 Web技术