Oracle主键与复合主键的性能分析

 

总结：

1、主键和复合主键，查询性能相同（索引高度相同，恰当的运用索引）。

2、主键和复合主键，(update,insert)性能不同(因为复合主键会用更多的块来创建索引，所以update,insert性能低)

 

 

实验思路：

1、 建立实验表，及主键，联合2个主键，联合3个主键

2、 查看索引的结构

3、查看条件相同的，执行计划（来确定主键和复合主键的效率）

 

 

一、             建立实验表；test1为单主键为1个column，test2为联合主键2个columns，test3为联合主键3个columns

SQL> create table test1(a number,b number,c number,primary key(a));

 

Table created.

 

SQL> create table test2(a number,b number,c number,primary key(a,b));

 

Table created.

 

SQL> create table test3(a number,b number,c number,primary key(a,b,c));

 

Table created.

 

二、             查看索引的结构

1、先查看一下建立的表对应的索引

SQL> select index_name,table_name from user_indexes;

 

INDEX_NAME                    TABLE_NAME

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

SYS_C005198                   TEST1

SYS_C005199                   TEST2

SYS_C005200                   TEST3

 

2、写个储存过程来给实验表插入数据

begin

for i in 1..10000 loop

insert into test1 values(i,i+1,i+2);

commit;

end loop;

end;

 

 

Test1

SQL>analyze index SYS_C005198 validate structure;

 

Index analyzed.

 

SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats ;

 

   HEIGHT    BLOCKS   BR_BLKS   LF_BLKS   LF_ROWS DEL_LF_ROWS

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

        2        24         1        18     10000          0

Test2

SQL> analyze index SYS_C005199 validate structure;     

 

Index analyzed.

 

SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats ;

 

 

   HEIGHT    BLOCKS   BR_BLKS   LF_BLKS   LF_ROWS DEL_LF_ROWS

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

        2        32         1        23     10000          0

Test3

SQL>analyze index SYS_C005200 validate structure;

 

Index analyzed.

 

SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats ;

 

   HEIGHT    BLOCKS   BR_BLKS   LF_BLKS   LF_ROWS DEL_LF_ROWS

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

        2        40         1        28     10000          0

 

总结：根据B-TREE索引的结构特点。说明主键和联合主键，同样的数据联合主键需要更多的资源来维护索引。（联合主键索引因为用了更多的块，所以update,insert会比主键索引慢一些。至于查询下面研究）

 

三、             查看相同情况下，主键的效率。

 

1、 语句都让其走INDEX UNIQUE SCAN，看看效率：

 

 

Test1

SQL> select a from test1 where a=5555;

 

        A

----------

     5555

 

 

Execution Plan

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

Plan hash value: 2716871853

 

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

-

 

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

|

 

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

-

 

|  0 | SELECT STATEMENT |            |    1 |   13 |    1  (0)| 00:00:01

|

 

|* 1 | INDEX UNIQUE SCAN| SYS_C005198 |    1 |   13 |    1  (0)| 00:00:01

|

 

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

-

 

 

Predicate Information (identified by operation id):

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

 

  1 - access("A"=5555)

 

 

Statistics

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

         1 recursive calls

         0 db block gets

         2 consistent gets

         0 physical reads

         0 redo size

       405 bytes sent via SQL*Net to client

       385 bytes received via SQL*Net from client

         2 SQL*Net roundtrips to/from client

         0 sorts (memory)

         0 sorts (disk)

1        rows processed

Test2

SQL> select a,b from test2 where a=5555 and b=5556;

 

        A         B

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

     5555      5556

 

 

Execution Plan

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

Plan hash value: 3210951477

 

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

-

 

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

|

 

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

-

 

|  0 | SELECT STATEMENT |            |    1 |   26 |    1  (0)| 00:00:01

|

 

|* 1 | INDEX UNIQUE SCAN| SYS_C005199 |    1 |   26 |    1  (0)| 00:00:01

|

 

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

-

 

 

Predicate Information (identified by operation id):

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

 

  1 - access("A"=5555 AND "B"=5556)

 

 

Statistics

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

         1 recursive calls

         0 db block gets

         2 consistent gets

         0 physical reads

         0 redo size

       460 bytes sent via SQL*Net to client

       385 bytes received via SQL*Net from client

         2 SQL*Net roundtrips to/from client

         0 sorts (memory)

         0 sorts (disk)

1        rows processed

Test3

SQL> select a,b,c from test3 where a=5555 and b=5556 and c=5557;

 

        A         B         C

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

     5555      5556      5557

 

 

Execution Plan

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

Plan hash value: 1852305570

 

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

-

 

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

|

 

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

-

 

|  0 | SELECT STATEMENT |            |    1 |   39 |    1  (0)| 00:00:01

|

 

|* 1 | INDEX UNIQUE SCAN| SYS_C005200 |    1 |   39 |    1  (0)| 00:00:01

|

 

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

-

 

 

Predicate Information (identified by operation id):

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

 

  1 - access("A"=5555 AND "B"=5556 AND "C"=5557)

 

 

Statistics

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

         1 recursive calls

         0 db block gets

         2 consistent gets

         0 physical reads

         0 redo size

       515 bytes sent via SQL*Net to client

       385 bytes received via SQL*Net from client

         2 SQL*Net roundtrips to/from client

         0 sorts (memory)

         0 sorts (disk)

         1 rows processed

 

分析：通过执行SQL走INDEX UNIQUE SCAN索引的情况，分析执行计划得到的结果是主键和联合主键性能相同：

 

（我们关注的：

          1 recursive calls

         0 db block gets

         2 consistent gets

         0 physical reads

         0 redo size

消耗一致和COST消耗一致。）

 

总结：主键和联合主键，应用B-tree索引的情况下，如果我们的索引高度相同,且正确的应用索引。这样的情况下我们查询性能是相同的。

 

 

欢迎大家给与纠正错误，共同提升！