索引中字段顺序对访问性能影响（一）

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

这一篇讨论查询条件为等值的情况。

 

 

很多人认为将选择性高的字段放在索引前缀字段可以提高查询性能，事实上对于等值查询而言，哪个字段在前的性能没有差别。

SQL> create table t as
  2  select * from dba_objects;

Table created.

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

Index created.

SQL> select count(distinct object_id), count(distinct owner), count(*)
  2  from t;

COUNT(DISTINCTOBJECT_ID) COUNT(DISTINCTOWNER)   COUNT(*)
------------------------ -------------------- ----------
                   76381                   62      76424

SQL> select object_id, owner
  2  from t
  3  where object_id = 1000;

 OBJECT_ID OWNER
---------- ------------------------------
      1000 SYS

SQL> set autot on
SQL> select object_name
  2  from t
  3  where wner = 'SYS'
  4  and object_id = 1000;

OBJECT_NAME
------------------------------
V_$BUFFER_POOL_STATISTICS

Execution Plan
----------------------------------------------------------
Plan hash value: 1951931306

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

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

   2 - access("OBJECT_ID"=1000 AND "OWNER"='SYS')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        541  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

显然object_id列的选择度很高，而owner列重复值就多得多了。通过前缀object_id列，查询语句只需要4个block就可以获得记录。

但是这并不意味着前缀选择性高的列的性能就一定最高：

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

Index created.

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

OBJECT_NAME
------------------------------
V_$BUFFER_POOL_STATISTICS

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

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

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

   2 - access("OWNER"='SYS' AND "OBJECT_ID"=1000)

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        541  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

对于同时包含索引所有列，且条件都是相等的查询，访问性能与前缀列的选择性没有关系。

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

转载于:http://blog.itpub.net/4227/viewspace-679182/