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

Oracle主键与复合主键的性能分析，主键和复合主键，查询性能相同（索引高度相同，恰当的运用索引）。主键和复合主键，(update,in

总结：
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索引的情况下，如果我们的索引高度相同,且正确的应用索引。这样的情况下我们查询性能是相同的。
 
 
欢迎大家给与纠正错误，，共同提升！