再說Unique Index和Normal Index行為差異
在筆者早期的文章中,從結構視角討論過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
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
-------------------------------------------------------------------------------
| 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
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
-------------------------------------------------------------------------------
| 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是我們非常常見的最佳化策略,其內容也是複雜多變。唯一索引作為我們經常用到的索引型別,其特性值得我們好好研究。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30496894/viewspace-1825212/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Unique Index和Normal Index差異經典對比IndexORM
- 唯一性索引(Unique Index)與普通索引(Normal Index)差異(上)索引IndexORM
- 唯一性索引(Unique Index)與普通索引(Normal Index)差異(中)索引IndexORM
- 唯一性索引(Unique Index)與普通索引(Normal Index)差異(下)索引IndexORM
- Index Full Scan和Index Fast Full Scan行為差異分析(上)IndexAST
- Index Full Scan和Index Fast Full Scan行為差異分析(下)IndexAST
- pk 、unique index 和 index 區別Index
- INDEX UNIQUE SCAN,INDEX FULL SCAN和INDEX FAST FULL SCANIndexAST
- Index Unique Scan (213)Index
- Sparse Indexes vs unique indexIndex
- unique index與primary key的區別Index
- oracle index unique scan/index range scan和mysql range/const/ref/eq_ref的區別OracleIndexMySql
- [20171211]UNIQUE LOCAL(Partitioned)IndexIndex
- 高效的SQL(Index unique scan最優化)SQLIndex優化
- 索引唯一性掃描(INDEX UNIQUE SCAN)索引Index
- 在什麼情況下用index unique scansIndex
- rebuild index 和 recreate index (重新建立index) 的區別RebuildIndex
- Data Warehouse Guide閱讀筆記(六):unique constraint & unique indexGUIIDE筆記AIIndex
- Index Full Scans和Index Fast Full ScansIndexAST
- mysql中key 、primary key 、unique key 與index區別MySqlIndex
- Oracle alter index rebuild 說明OracleIndexRebuild
- optimizer_index_caching和optimizer_index_cost_adj兩個引數說明Index
- 建立檢視和淺析LOCAL INDEX和GLOBAL INDEXIndex
- ORA-02429: cannot drop index used for enforcement of unique/primary keyIndex
- MYSQL中的type:index 和 Extra:Using indexMySqlIndex
- INDEX FULL SCAN和INDEX FAST FULL SCAN區別IndexAST
- index full scan 和 index FAST full scan 區別IndexAST
- KEEP INDEX | DROP INDEXIndex
- min(), max()和indexIndex
- INDEX FULL SCAN和INDEX FAST FULL SCAN的區別IndexAST
- ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys foundIndex
- rowid,index,INDEX FULL SCAN,INDEX FAST FULL SCAN|IndexAST
- mysql索引型別:FULLTEXT、NORMAL、SPATIAL、UNIQUEMySql索引型別ORM
- 說說C# 8.0 新增功能Index和Range的^0是什麼?C#Index
- create index online 和create index 不同及注意點Index
- OPTIMIZER_INDEX_CACHING和OPTIMIZER_INDEX_COST_ADJIndex
- LOCAL INDEX和HINT的使用【DO BE USED LOCAL INDEX IN HINT】薦Index
- index full scan 和 index fast full scan (IFS,FFS)的不同IndexAST