ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引中字段顺序对访问性能影响(四)

索引中字段顺序对访问性能影响(四)

原创 Linux操作系统 作者:yangtingkun 时间:2010-11-25 22:21:50 0 删除 编辑

索引中字段的顺序确实会对访问性能有所影响,不过并不像很多人想象的那么简单。

这一篇讨论选项性低的列作为前缀索引时执行范围扫描的情况。

索引中字段顺序对访问性能影响(一):http://yangtingkun.itpub.net/post/468/509188

索引中字段顺序对访问性能影响(二):http://yangtingkun.itpub.net/post/468/509265

索引中字段顺序对访问性能影响(三):http://yangtingkun.itpub.net/post/468/509328

 

 

上一篇文章将选择性高的字段作为索引的前缀字段,这篇讨论选择性低的字段作为索引前缀的情况。

SQL> drop index ind_t_id_owner;

Index dropped.

SQL> create index ind_t_owner_id
  2  on t (owner, object_id);

Index created.

SQL> select object_name
  2  from t
  3  where object_id > 45000
  4  and wner = 'U1';

OBJECT_NAME
--------------------------------
T_BIG
S1
T


Execution Plan
----------------------------------------------------------
Plan hash value: 2014292028

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    35 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T              |     1 |    35 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_OWNER_ID |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='U1' AND "OBJECT_ID">45000 AND "OBJECT_ID" IS NOT NULL)


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

如果是后缀字段的范围扫描,那么上一篇文章介绍的情况没有什么区别。只不过对于选择性不高的索引而言,由于返回的结果相对较多,因此产生的逻辑读也会较大。

SQL> select object_name
  2  from t
  3  where owner > 'S'
  4  and object_id = 50000;

OBJECT_NAME
------------------------------------------
MGMT_CURRENT_METRIC_ERRORS


Execution Plan
----------------------------------------------------------
Plan hash value: 2801825686

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    35 |    25   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T              |     1 |    35 |    25   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IND_T_OWNER_ID |     1 |       |    24   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER">'S' AND "OBJECT_ID"=50000 AND "OWNER" IS NOT NULL)
       filter("OBJECT_ID"=50000)


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

当选择性差的索引作为前缀字段,且执行范围扫描,而后缀选择性好的字段进行等值扫描时,执行计划选择了索引跳扫。这就与上一篇文章描述的情况有所区别了。

当前缀字段的DISTINCT值不是很多的情况下,INDEX SKIP SCAN扫描成为可能,这种情况下,Oracle扫描前缀字段每种可能性对应的后缀字段的值。

观察Predicate Information可以发现,OWNER > ‘S’这个前缀条件也出现在ACCESS条件中。这说明什么问题呢,为了更好的说明这个问题,修改一下SQL语句:

SQL> select object_name
  2  from t
  3  where owner || '' > 'S'
  4  and object_id = 50000;

OBJECT_NAME
----------------------------
MGMT_CURRENT_METRIC_ERRORS


Execution Plan
----------------------------------------------------------
Plan hash value: 2801825686

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    35 |    60   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T              |     1 |    35 |    60   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IND_T_OWNER_ID |     1 |       |    59   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=50000)
       filter("OBJECT_ID"=50000 AND "OWNER"||''>'S')


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

执行计划没有改变,但是逻辑读却相差很多,原因仍然是ACCESSFILTER导致的。在前一个语句中,前缀字段的限制条件出现在ACCESS中,说明在执行索引跳扫的时候,会根据前缀索引的条件来确定扫描的范围,因此索引跳扫只扫描前缀字段大于’S’,且不为空的可能性上。而对于而一个SQL语句,前缀字段的查询条件出现在FILTER处,因此索引跳扫需要访问前缀字段的每一种的可能性,产生的逻辑读当然要大得多。

SQL> select object_name
  2  from t
  3  where owner > 'S'
  4  and object_id in (50000, 50001);

OBJECT_NAME
-----------------------------------------------------
MGMT_CURRENT_METRIC_ERRORS
MGMT_TARGET_ASSOC


Execution Plan
----------------------------------------------------------
Plan hash value: 2014292028

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    35 |    49   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T              |     1 |    35 |    49   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_OWNER_ID |     1 |       |    48   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER">'S' AND "OWNER" IS NOT NULL)
       filter("OBJECT_ID"=50000 OR "OBJECT_ID"=50001)


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

最后看一下前缀字段范围扫描,后缀字段不再是简单相等的情况。这时执行计划与上一篇文章中介绍的情况又恢复了一致。原因是无论后缀字段是IN语句还是范围查询,都使得索引跳扫执行计划不可能成立。显然Oracle不会生成INLIST INDEX SKIP SCAN这种复杂的执行计划。

这时只有使用选择性不高的前缀字段的范围扫描,因此产生的逻辑读显然高得多。

如果将语句修改一下,变成两个UNION ALL的子查询,则语句仍然可以利用索引跳扫:

SQL> select object_name
  2  from t
  3  where owner > 'S'
  4  and object_id = 50000
  5  union all
  6  select object_name
  7  from t
  8  where owner > 'S'
  9  and object_id = 50001;

OBJECT_NAME
------------------------------
MGMT_CURRENT_METRIC_ERRORS
MGMT_TARGET_ASSOC


Execution Plan
----------------------------------------------------------
Plan hash value: 2279076093

--------------------------------------------------------------------------------------------
| Id| Operation                    | Name           | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|  0| SELECT STATEMENT             |                |    2 |    70 |    51  (51)| 00:00:01 |
|  1|  UNION-ALL                   |                |      |       |            |          |
|  2|   TABLE ACCESS BY INDEX ROWID| T              |    1 |    35 |    25   (0)| 00:00:01 |
|* 3|    INDEX SKIP SCAN           | IND_T_OWNER_ID |    1 |       |    24   (0)| 00:00:01 |
|  4|   TABLE ACCESS BY INDEX ROWID| T              |    1 |    35 |    25   (0)| 00:00:01 |
|* 5|    INDEX SKIP SCAN           | IND_T_OWNER_ID |    1 |       |    24   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OWNER">'S' AND "OBJECT_ID"=50000 AND "OWNER" IS NOT NULL)
       filter("OBJECT_ID"=50000)
   5 - access("OWNER">'S' AND "OBJECT_ID"=50001 AND "OWNER" IS NOT NULL)
       filter("OBJECT_ID"=50001)


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

就像文章开头说的,索引字段顺序对于访问性能的影响并不像想象中的那么简单,事实上问题的复杂性也超出我的预期。本来打算通过两篇文章描述一下这个问题,后来发现两篇文章说不清楚,等到第三篇的时候发现,如果还仿照前两篇的架构来说明文章,恐怕很容易乱,于是就又分了两篇文章。

这篇文章并没有打算说明选择性高字段作为索引前缀好,还是选择性的字段作为索引前缀好。和很多特性一样,如何做选择要看在什么情况下使用。

 

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

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10454789