在筆者早期的文章中，從結構視角討論過Unique Index和Normal Index的差異。Oracle的Unique Index是一種特殊的約束索引結構，通常而言，Unique Index可以有幾個方面的優勢：

首先是更加精簡的結構，同內容情況下，Unique Index在體積上略小於Normal Index。其次，Unique Index提供出額外的列取值約束保證。第三就是Oracle在Unique Index中，有一些獨特的SQL檢索行為。

如果表採用唯一索引，在SQL執行過程中，是有很多的效能優勢和好處的。本篇我們藉助常用的效能測量工具進行比較研究。

1、環境介紹

我們選擇Oracle 11gR2進行實驗，建立資料表T，對應的兩個資料列結構和內容完全相同。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> create table t as select object_id obj_id_1, object_id obj_id_2 from dba_objects;

Table created

SQL> select count(*) from t;

COUNT(*)

----------

75596

在完全相同的列obj_id_1和obj_id_2上建立普通和唯一索引。

SQL> create index idx_t_normal on t(obj_id_1);

Index created

SQL> create unique index idx_t_unique on t(obj_id_2);

Index created

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

PL/SQL procedure successfully completed

2、執行計劃分析

先從執行計劃層面看兩種型別索引的差異，選擇索引最高效的=SQL語句結構。

SQL> explain plan for select * from t where obj_id_1=1000;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 400739531

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

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

| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 2 (0)

|* 2 | INDEX RANGE SCAN | IDX_T_NORMAL | 1 | | 1 (0)

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

Predicate Information (identified by operation id):

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

2 - access("OBJ_ID_1"=1000)

14 rows selected

SQL> explain plan for select * from t where obj_id_2=1000;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 399591198

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

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

| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 2 (0)

|* 2 | INDEX UNIQUE SCAN | IDX_T_UNIQUE | 1 | | 1 (0)

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

Predicate Information (identified by operation id):

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

2 - access("OBJ_ID_2"=1000)

14 rows selected

從上面的執行計劃中，我們可以看到明確的差異。在普通索引idx_t_normal在等號條件下的時候，執行路徑操作位Index Range Scan。Range Scan的行為是從索引根節點開始，透過分支節點逐層比較定位，定位到第一個符合條件的葉子節點上。由於索引葉子節點都是有序排列，符合條件的其他值一定在第一個符合條件葉子節點的水平位置上進行Range Scan操作。

而唯一索引Unique Index在操作上使用的是不同的操作。唯一索引在葉子節點上有一個滿足條件約束，就是使用=號的時候，至多隻有一個符合條件的取值。Oracle只需要透過根節點導航定位到第一個條件葉子節點就可以了，不需要Range Scan動作。

由於我們使用的基礎資料完全相同，所以在成本計算值和行數上，兩個方案沒有任何差別。注意：執行計劃中反映的情況是透過統計量的計算值，真實情況如何呢？

3、SQL執行統計量分析

我們使用autotrace工具，進行SQL實際執行分析。

SQL> alter system flush shared_pool;

System altered

SQL> alter system flush buffer_cache;

System altered

SQL> select * from t where obj_id_1=1000;

統計資訊

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

31 recursive calls

0 db block gets

48 consistent gets

11 physical reads

0 redo size

419 bytes sent via SQL*Net to client

411 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select * from t where obj_id_2=1000;

統計資訊

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

19 recursive calls

0 db block gets

47 consistent gets

11 physical reads

0 redo size

353 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

1 rows processed

注意標紅的內容：我們透過一系列實驗，發現在進行定位操作的時候，普通索引Range Scan動作要比Unique Scan多進行一次邏輯讀。

那麼，我們怎麼理解這個問題，一種猜想是：對於Normal Index，每次進行的葉子節點檢索過程中，是一個“判斷”的過程。由於葉子節點是有序的，Oracle在讀到某一個節點時候，只有判斷下一個節點是否是不符合情況的記錄，才能決定終止。而Unique Index的結構造成，當進行等號條件檢索的時候，Oracle一次最多能找到一條符合條件的記錄。也就是說，如果當前葉子節點已經符合條件了，就不需要進行下一個節點的試探驗證動作了。就是這個試探動作，讓邏輯讀的數目差距1。

真實情況是如何呢？我們能找到的比較細節工具就是10046事件跟蹤。

4、10046事件跟蹤

我們分別使用兩個會話，進行10046跟蹤Oracle在兩個SQL中的行為。

SQL> select value from v$diag_info where name='Default Trace File';

VALUE

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

/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5821.trc

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> select * from t where obj_id_1=1000;

OBJ_ID_1 OBJ_ID_2

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

1000 1000

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> conn / as sysdba

Connected.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> select value from v$diag_info where name='Default Trace File';

VALUE

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

/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5839.trc

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> select * from t where obj_id_2=1000;

OBJ_ID_1 OBJ_ID_2

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

1000 1000

SQL> alter session set events '10046 trace name context off';

Session altered.

分析對obj_id_1條件，也就是普通索引的Raw Trace片段。

=====================

PARSING IN CURSOR #4831628 len=35 dep=0 uid=0 ct=3 lid=0 tim=1382679804979468 hv=1557130689 ad='2fe520b8' sqlid='16r9h71fczvf1'

select * from t where obj_id_1=1000

END OF STMT

PARSE #4831628:c=34995,e=95739,p=6,cr=45,cu=0,mis=1,r=0,dep=0,og=1,plh=400739531,tim=1382679804979464

EXEC #4831628:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=400739531,tim=1382679804979584

WAIT #4831628: nam='SQL*Net message to client' ela= 12 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1382679804979635

WAIT #4831628: nam='db file sequential read' ela= 71 file#=1 block#=96817 blocks=1 obj#=78115 tim=1382679804979791

WAIT #4831628: nam='db file sequential read' ela= 50 file#=1 block#=96820 blocks=1 obj#=78115 tim=1382679804979919

WAIT #4831628: nam='db file sequential read' ela= 59 file#=1 block#=90226 blocks=1 obj#=78114 tim=1382679804980048

FETCH #4831628:c=0,e=413,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=400739531,tim=1382679804980082

WAIT #4831628: nam='SQL*Net message from client' ela= 437 driver id=1650815232 #bytes=1 p3=0 obj#=78114 tim=1382679804980566

FETCH #4831628:c=0,e=36,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=400739531,tim=1382679804980633

STAT #4831628 id=1 cnt=1 pid=0 pos=1 bj=78114 p='TABLE ACCESS BY INDEX ROWID T (cr=4 pr=3 pw=0 time=406 us cost=2 size=10 card=1)'

STAT #4831628 id=2 cnt=1 pid=1 pos=1 bj=78115 p='INDEX RANGE SCAN IDX_T_NORMAL (cr=3 pr=2 pw=0 time=288 us cost=1 size=0 card=1)'

WAIT #4831628: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=78114 tim=1382679804980702

*** 2013-10-25 13:43:40.038

WAIT #4831628: nam='SQL*Net message from client' ela= 15057747 driver id=1650815232 #bytes=1 p3=0 obj#=78114 tim=1382679820038463

CLOSE #4831628:c=0,e=34,dep=0,type=0,tim=1382679820039051

=====================

在normal index動作中，Oracle進行了三次單塊讀動作（db file sequential read），讀取了索引和資料塊。分析步驟index range scan操作中，進行了三次一致讀（consistent read）動作cr=3。注意一點，這個3次讀是SQL語句本身的讀動作。我們在上一部分中看到了48是這個3次外加其他recursive SQL進行的讀次數。

那麼，唯一索引情況呢？

=====================

PARSING IN CURSOR #8985920 len=35 dep=0 uid=0 ct=3 lid=0 tim=1382679944147064 hv=2859221912 ad='3168c9f0' sqlid='3b0tusfp6shws'

select * from t where obj_id_2=1000

END OF STMT

PARSE #8985920:c=24997,e=39398,p=6,cr=45,cu=0,mis=1,r=0,dep=0,og=1,plh=399591198,tim=1382679944147058

EXEC #8985920:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=399591198,tim=1382679944147191

WAIT #8985920: nam='SQL*Net message to client' ela= 12 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1382679944147241

WAIT #8985920: nam='db file sequential read' ela= 101 file#=1 block#=91953 blocks=1 obj#=78116 tim=1382679944147422

WAIT #8985920: nam='db file sequential read' ela= 55 file#=1 block#=91955 blocks=1 obj#=78116 tim=1382679944147698

WAIT #8985920: nam='db file sequential read' ela= 78 file#=1 block#=90226 blocks=1 obj#=78114 tim=1382679944147830

FETCH #8985920:c=1000,e=605,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=399591198,tim=1382679944147881

STAT #8985920 id=1 cnt=1 pid=0 pos=1 bj=78114 p='TABLE ACCESS BY INDEX ROWID T (cr=3 pr=3 pw=0 time=598 us cost=2 size=10 card=1)'

STAT #8985920 id=2 cnt=1 pid=1 pos=1 bj=78116 p='INDEX UNIQUE SCAN IDX_T_UNIQUE (cr=2 pr=2 pw=0 time=440 us cost=1 size=0 card=1)'

WAIT #8985920: nam='SQL*Net message from client' ela= 793 driver id=1650815232 #bytes=1 p3=0 obj#=78114 tim=1382679944148753

FETCH #8985920:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=399591198,tim=1382679944148789

WAIT #8985920: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=78114 tim=1382679944148826

*** 2013-10-25 13:45:58.623

WAIT #8985920: nam='SQL*Net message from client' ela= 14474484 driver id=1650815232 #bytes=1 p3=0 obj#=78114 tim=1382679958623347

CLOSE #8985920:c=0,e=125,dep=0,type=0,tim=1382679958624126

=====================

Index Unique Scan同樣進行了三次的單塊讀動作，但是在Index Unique Scan操作中，進行一致讀cr的次數為2，比剛剛的normal index少一次。

相信這也就是我們看到統計量中一次邏輯的差異的根源。

5、結論

Oracle Index是我們非常常見的最佳化策略，其內容也是複雜多變。唯一索引作為我們經常用到的索引型別，其特性值得我們好好研究。

再說Unique Index和Normal Index行為差異

相關文章