13518219792
專業(yè)成都網(wǎng)站建設(shè)公司，做排名好的好網(wǎng)站，排在同行前面，為您帶來客戶和效益!成都創(chuàng)新互聯(lián)為您提供成都網(wǎng)站建設(shè)，五站合一網(wǎng)站設(shè)計(jì)制作，服務(wù)好的網(wǎng)站設(shè)計(jì)公司，成都網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì)負(fù)責(zé)任的成都網(wǎng)站制作公司!
索引是我們經(jīng)常使用的一種數(shù)據(jù)庫搜索優(yōu)化手段。適當(dāng)?shù)臉I(yè)務(wù)操作場景使用適當(dāng)?shù)乃饕桨缚梢燥@著的提升系統(tǒng)整體性能和用戶體驗(yàn)。在Oracle中，索引有包括很多類型。不同類型的索引適應(yīng)不同的系統(tǒng)環(huán)境和訪問場景。其中，唯一性索引Unique Index是我們經(jīng)常使用到的一種。
唯一性索引unique index和一般索引normal index最大的差異就是在索引列上增加了一層唯一約束。添加唯一性索引的數(shù)據(jù)列可以為空，但是只要存在數(shù)據(jù)值，就必須是唯一的。
那么，在使用唯一性索引時(shí)，同一般索引有什么差異呢？下面通過一系列的演示實(shí)驗(yàn)來說明。
1、實(shí)驗(yàn)環(huán)境準(zhǔn)備
為了體現(xiàn)出一致性和可能的差異性，選擇相同的數(shù)據(jù)值列加入不同類型的索引結(jié)構(gòu)。
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> create table t as select * from dba_objects;
Table created
//保證data_object_id和object_id取值相同；
SQL> update t set data_object_id=object_id;
72581 rows updated
SQL> commit;
Commit complete
//普通索引
SQL> create index idx_t_normalid on t(object_id);
Index created
//唯一性索引
SQL> create unique index idx_t_uniid on t(data_object_id);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
2、體積容量比較
在環(huán)境準(zhǔn)備中，我們將索引列取值設(shè)置為完全相同，盡量避免由于外在原因引起的差異。下面我們檢查數(shù)據(jù)字典中的容量比較信息。
首先是查看索引段index segment信息。
SQL> select segment_name, segment_type, bytes, blocks, extents from dba_segments where segment_name in ('IDX_T_NORMALID','IDX_T_UNIID');
SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS EXTENTS
-------------------- ------------------ ---------- ---------- ----------
IDX_T_NORMALID INDEX 983040 120 15
IDX_T_UNIID INDEX 917504 112 14
一般索引normal index較唯一性索引空間消耗略大。索引idx_t_normalid占據(jù)15個(gè)分區(qū)，120個(gè)block。略大于idx_t_uniid的14個(gè)分區(qū)塊。
這個(gè)地方需要注意一下，在數(shù)據(jù)字典中一個(gè)segment的分區(qū)占據(jù)，是Oracle系統(tǒng)分配給的空間，并不意味著全部使用殆盡。可能兩個(gè)索引結(jié)構(gòu)差異很小，但是額外多分配出一個(gè)extent。
索引葉子結(jié)構(gòu)上，檢查數(shù)據(jù)字典內(nèi)容。
SQL> select index_name, index_type, UNIQUENESS, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS from dba_indexes where index_name in ('IDX_T_NORMALID','IDX_T_UNIID');
INDEX_NAME INDEX_TYPE UNIQUENESS BLEVEL LEAF_BLOCKS DISTINCT_KEYS
-------------------- --------------- ---------- ---------- ----------- -------------
IDX_T_UNIID NORMAL UNIQUE 1 106 51330
IDX_T_NORMALID NORMAL NONUNIQUE 1 113 51330
兩者的差異不大，normal index空間消耗要略大于unique index。
結(jié)論：從數(shù)據(jù)字典反映出的情況可以知道，同一般索引相比，唯一性索引的空間消耗略小一些。由于我們采用的實(shí)驗(yàn)數(shù)據(jù)都是相同的，這一點(diǎn)點(diǎn)的差距可能就意味著兩種索引類型在存儲(chǔ)結(jié)構(gòu)上存在略微的差異。
3、違反約束實(shí)驗(yàn)
作為唯一性索引，在添加創(chuàng)建和進(jìn)行dml操作的時(shí)候，都會(huì)嚴(yán)格發(fā)揮出約束的作用。
SQL> insert into t select * from t where rownum<2;
insert into t select * from t where rownum<2
ORA-00001:違反唯一約束條件 (SYS.IDX_T_UNIID)
4、等號(hào)檢索實(shí)驗(yàn)
當(dāng)我們進(jìn)行等號(hào)查詢的時(shí)候，Oracle對(duì)兩種索引生成的執(zhí)行計(jì)劃有何種差異？注意：下面的select檢索之前，都使用flush語句對(duì)shared_pool和buffer_cache進(jìn)行清理。
--精確查詢
SQL> select * from t where object_id=1000;
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 776407697
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 101 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_NORMALID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
統(tǒng)計(jì)信息
----------------------------------------------------------
528 recursive calls
0 db block gets
87 consistent gets
11 physical reads
0 redo size
1200 bytes sent via SQL*Net to client
376 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 data_object_id=1000;
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 335537167
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 101 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_T_UNIID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DATA_OBJECT_ID"=1000)
統(tǒng)計(jì)信息
----------------------------------------------------------
528 recursive calls
0 db block gets
86 consistent gets
10 physical reads
0 redo size
1200 bytes sent via SQL*Net to client
376 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
這里，我們看到了Unique Index的一個(gè)特性，就是等號(hào)操作時(shí)執(zhí)行計(jì)劃的差異。對(duì)Unique Index而言，進(jìn)行相等查詢的結(jié)果只有一行值或者沒有，所以沒必要進(jìn)行傳統(tǒng)的Index Range Scan操作。此處，執(zhí)行計(jì)劃中使用的是Index Unique Scan操作，直接精確定位到指定的記錄項(xiàng)目，返回rowid記錄。
而一般索引在進(jìn)行等號(hào)檢索的時(shí)候，通常使用的就是Index Range Scan操作。沿著索引樹葉子節(jié)點(diǎn)進(jìn)行水平掃描操作，直到獲取索引符合條件索引列值的rowid列表。
從成本上看，兩者雖然執(zhí)行計(jì)劃操作方式有一定差別，但是成本實(shí)際差異不大。CPU成本和執(zhí)行時(shí)間上相同。各種塊讀操作（邏輯讀和物理讀）存在一些差異，筆者認(rèn)為源于兩個(gè)索引結(jié)構(gòu)的微量區(qū)別，這樣讀取的塊數(shù)一定有些差異。
5、范圍搜索實(shí)驗(yàn)
當(dāng)我們進(jìn)行索引列的范圍搜索時(shí)，執(zhí)行計(jì)劃和成本有何種差異呢？
--范圍匹配
SQL> select * from t where object_id>=1000 and object_id<=1500;
已選擇490行。
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 776407697
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 485 | 48985 | 14 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 485 | 48985 | 14 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_NORMALID| 485 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=1000 AND "OBJECT_ID"<=1500)
統(tǒng)計(jì)信息
----------------------------------------------------------
528 recursive calls
0 db block gets
158 consistent gets
17 physical reads
0 redo size
23775 bytes sent via SQL*Net to client
728 bytes received via SQL*Net from client
34 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
490 rows processed
SQL> select * from t where data_object_id>=1000 and data_object_id<=1500;
已選擇490行。
執(zhí)行計(jì)劃
----------------------------------------------------------
Plan hash value: 2700411221
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 485 | 48985 | 14 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 485 | 48985 | 14 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_UNIID | 485 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DATA_OBJECT_ID">=1000 AND "DATA_OBJECT_ID"<=1500)
統(tǒng)計(jì)信息
----------------------------------------------------------
528 recursive calls
0 db block gets
157 consistent gets
16 physical reads
0 redo size
23775 bytes sent via SQL*Net to client
728 bytes received via SQL*Net from client
34 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
490 rows processed
從實(shí)驗(yàn)結(jié)果看，兩者在進(jìn)行范圍搜索時(shí)，執(zhí)行計(jì)劃沒有差異。兩者都是進(jìn)行Index Range Scan操作。各類型成本基本相同。
6、結(jié)論
本篇主要從應(yīng)用角度，分析Unique Index與一般normal Index的差異。從結(jié)果看，Unique Index就是額外添加的唯一性約束。該約束嚴(yán)格的保證索引列的取值唯一性，這在一些數(shù)據(jù)列上的業(yè)務(wù)約束實(shí)現(xiàn)是很重要的功能。比如一個(gè)數(shù)據(jù)列，不能作為主鍵，而且允許為空，但是業(yè)務(wù)上要求唯一特性。這時(shí)候使用唯一性索引就是最好的選擇。
從執(zhí)行計(jì)劃where條件中的表現(xiàn)看，Unique Index和一般normal Index沒有顯著性的差異。
兩者數(shù)據(jù)基礎(chǔ)值一樣的情況下，生成索引的體積存在略微的差異，說明在存儲(chǔ)結(jié)構(gòu)上兩者可能有不同。下面我們來分析一下兩類型索引的結(jié)構(gòu)信息。
