ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 组合索引顺序选取一例

组合索引顺序选取一例

原创 Linux操作系统 作者:realkid4 时间:2011-07-07 21:20:46 0 删除 编辑

 

组合索引在应对特殊的多条件查询是,可以起到意想不到的效果。我们经常在网络上讨论组合索引顺序选取的原则。

 

江湖流传原则多种多样,有建议使用选择率大小判断的,有建议查看分布的,更有追求多需求共用的。这些方案都有一定的道理和支持成功案例。笔者以为,组合索引顺序问题,还要具体情况具体分析。最重要的因素,还是我们的SQL访问方式。

 

1、  问题提出

 

最近接触到一个报表需求,要求从文件加载日志中出具报表数据。条件特征是一个等于条件和一个日期范围条件。由于日志数据表是一个累积性的数据表,所以潜在数据量可能是很大的。下面是对实验环境的模拟。

 

 

SQL> create table t as select * from dba_objects;

Table created

 

 

SQL语句:select * from t where created between to_date('2009-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2009-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss') and wner='CTXSYS';

 

获取ownerctxsys的在20099月的全部数据。

 

2、  无索引情况

 

作为参照,我们首先查看一下无索引方案的时候,Oracle执行计划和相应的消耗。说明,在每次SQL语句前,为了从实验条件公平,都将shared_poolbuffer_Cache进行清空。

 

 

SQL> select * from t where created between to_date('2009-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2009-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss') and wner='CTXSYS';

 

已选择105行。

 

执行计划

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    99 |  9603 |   282   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T    |    99 |  9603 |   282   (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OWNER"='CTXSYS' AND "CREATED"<=TO_DATE(' 2009-09-30

              23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND "CREATED">=TO_DATE('

              2009-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

统计信息

----------------------------------------------------------

        577  recursive calls

          0  db block gets

       1156  consistent gets

       1039  physical reads

          0  redo size

       6190  bytes sent via SQL*Net to client

        442  bytes received via SQL*Net from client

          8  SQL*Net roundtrips to/from client

         17  sorts (memory)

          0  sorts (disk)

        105  rows processed

 

 

在没有任何优化方案的情况下,Oracle选择了全表扫描作为执行路径。

 

3、  组合索引方案——方案1

 

如果要对该数据SQL进行优化,注意我这里说的是如果。一个系统中,SQL形态需求多样,哪个进行优化,哪个放任不管都是有所选取的。并不是每个需求我们都要进行优化,优化是有代价和副产品的,我们要将有限的资源投入到关键用例需求的优化中。RUP中有句名言:关键用例决定架构,同样道理,关键用例决定优化方向

 

我们计划选择组合索引,索引列为ownercreated两个列。那么,就出现哪个作为前导列的问题。我们首先选择created作为前导列。

 

 

SQL> create index idx_t_comp1 on t(created,owner);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

SQL> select * from t where created between to_date('2009-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2009-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss') and wner='CTXSYS';

已选择105行。

执行计划

----------------------------------------------------------

Plan hash value: 3419152522

-------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |   160 | 15520 |   238   (0)| 00:00:03 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |   160 | 15520 |   238   (0)| 00:00:03 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_COMP1 |   160 |       |    78   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("CREATED">=TO_DATE(' 2009-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

              AND "OWNER"='CTXSYS' AND "CREATED"<=TO_DATE(' 2009-09-30 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

filter("OWNER"='CTXSYS')

统计信息

----------------------------------------------------------

        621  recursive calls

          0  db block gets

        302  consistent gets

         93  physical reads

          0  redo size

       8556  bytes sent via SQL*Net to client

        442  bytes received via SQL*Net from client

          8  SQL*Net roundtrips to/from client

         18  sorts (memory)

          0  sorts (disk)

        105  rows processed

 

 

CBO的作用下,Oracle选择了索引路径。相应的成本显而易见。下面我们看看替代方案。

 

4、  组合索引方案——方案2

 

这次我们选择ownercreated顺序建立组合索引。

 

 

SQL> create index idx_t_comp2 on t(owner,created);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

SQL>  select * from t where created between to_date('2009-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2009-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss') and wner='CTXSYS';

 

已选择105行。

执行计划

----------------------------------------------------------

Plan hash value: 2902496991

-------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |    89 |  8633 |    90   (0)| 00:00:02 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |    89 |  8633 |    90   (0)| 00:00:02 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_COMP2 |    89 |       |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OWNER"='CTXSYS' AND "CREATED">=TO_DATE(' 2009-09-01 00:00:00',

              'syyyy-mm-dd hh24:mi:ss') AND "CREATED"<=TO_DATE(' 2009-09-30 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

统计信息

----------------------------------------------------------

        621  recursive calls

          0  db block gets

        228  consistent gets

         19  physical reads

          0  redo size

       8556  bytes sent via SQL*Net to client

        442  bytes received via SQL*Net from client

          8  SQL*Net roundtrips to/from client

         18  sorts (memory)

          0  sorts (disk)

        105  rows processed

 

 

下面我们对比三种执行路径的成本情况。

 

 

无索引

组合索引1

Createdowner顺序

组合索引2

Ownercreated顺序

CPU Cost

282

238

90

Time

00:00:04

00:00:03

00:00:02

Consisent Get

1156

302

228

Physical Get

1039

93

19

Sortsmemory

17

18

18

 

 

 

 

 

优劣显而易见,组合顺序ownercreated具有绝对的优势地位。那么,为什么数据集合相同,SQL语句相同,而且都是选择组合索引,只是顺序的不同带来成本如此大的变化呢?

 

5、  原因分析

 

这个原因应该从组合索引结构和不同结构下SQL的搜索行为入手分析。

 

我们先来看第一个组合索引方案(created owner)。根据Oracle索引树的结构,索引列会全部出现在索引树的叶子节点上。不同的前导列,差距就是叶子节点上排序的顺序,会影响到Oracle检索的数据量。当我们选择created owner顺序时,Oracle叶子节点排序的顺序就是有限进行Created排序、之后进行owner排序,形成下面图的结构。

 

 

created列在前,owner列在后。在进行检索的时候,Oracle为了满足第一个时间范围条件,会将所有的阴影部分叶子节点都扫过一遍,将阴影索引块全部复制在buffer cache中。这时候,我们注意到其中有ownerctxsys的数据行,就会将这些记录再次进行剔除选择。

 

这个过程我们可以从方案一的执行计划中看出。

 

   2 - access("CREATED">=TO_DATE(' 2009-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

              AND "OWNER"='CTXSYS' AND "CREATED"<=TO_DATE(' 2009-09-30 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

filter("OWNER"='CTXSYS')

 

注意,此处Oracle是先按照时间范围进行access获取操作,之后再按照owner条件进行filter操作。这是两步操作。

 

再回头看我们的索引方案二。索引结构不同,引起的扫描方式也就是不同的。

 

首先,Oracle从分支branch节点定位到第一条ownerctxsys的叶子节点上。之后顺序进行scan,应用created日期范围条件。这样,只要通过scan操作就可以取到所有的结果集合,不需要向方案一一样还要进行删选选择。我们的猜想可以通过执行计划进行证实。

 

   2 - access("OWNER"='CTXSYS' AND "CREATED">=TO_DATE(' 2009-09-01 00:00:00',

              'syyyy-mm-dd hh24:mi:ss') AND "CREATED"<=TO_DATE(' 2009-09-30 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

 

可以看到,这个执行计划中,只有access操作,而且是充分应用上了全部条件。这样做,访问索引块的个数、进行CPU消耗的量就会小很多。从而,执行计划就更加优化。

 

6、结论

 

通过这个案例,笔者主要想说明几个问题:

ü        我们在确定确实需要进行组合索引优化方案的时候,要关注前导列的选取问题,绝对不要轻易相信各种“流言秘籍”,多问问为什么;

ü        不同的前导列选取方案,选择的时候要看清楚访问SQL的组合条件究竟是什么方式?范围条件和等于条件很大程度上会影响到我们的方案;

ü        最后确定的前导列方案,是一个综合全局的方案,还要考虑其他功能对该表的访问方式。本篇中我们没有涉及到对该表其他关键优化需求。一切优化方案所追求的就是一系列平衡,性能的平衡、用例功能间的平衡、各种成本的平衡乃至收益付出的平衡;

 

优化方案选取就是一个选取寻找平衡的过程。

 

indexcomposit1.jpg

indexcomposit2.jpg

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-701646/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7631609