Clustering Factor——索引的成本指標
使用索引是我們面對海量資料搜尋是一種常用的手段。透過有效的索引訪問,可以使我們更快的訪問到需要的資料,減少物理、邏輯IO,從而提高系統效能。在CBO時代,Oracle對於提交SQL的執行路徑是有所選擇的。一個select是走Index還是走Full Table Scan,或者別的路徑,要根據Oracle對錶列的統計資訊收集結果加以計算出的執行計劃成本而確定。在計算索引成本的公式中,索引的clustering factor是一個重要參考資訊。
簡單的說,clustering factor就是反映資料錶行儲存有序程度與索引有序程度對照的指標。如果這個值越大,說明在進行索引搜尋是,需要獲取的資料塊數量越多,從而進行邏輯物理讀的次數也就越多,相應消耗的成本Cost越高。反之,對應的成本也就越低。
原理上,可以這麼理解。當我們使用索引進行查詢的時候,獲取到葉節點最後是符合條件的一系列ROWID,代表對應資料結果所在的實體地址。在根據ROWID所對應的位置,讀取對應的資料塊。如果指定的資料塊(Date Block)已經存在於SGA中的Buffer Cache,就直接讀取(進行邏輯讀)。如果不存在於Buffer Cache,就需要先從物理儲存上把資料塊讀取到記憶體SGA裡,之後再進行讀取。(物理讀+邏輯讀)。在這個過程中,我們通常傾向於減少物理讀和邏輯讀的次數。如果我們要獲取的資料都是在同樣塊或者儘可能少的資料塊裡,那麼我們索引的執行效率較高。如果有一個查詢,雖然執行路徑中包括了索引,但是將全表所有的資料塊都讀取在SGA裡,其成本也是值得商榷的。
Clustering Factor就是衡量索引執行效率成本的一個重要指標。我們通常會希望資料表中排列的順序與索引列排序的順序一致。但是,在使用一些儲存結構(隨機儲存結構),索引和資料分開儲存,資料行一般為隨機儲存。這樣,Clustering Factor是一個不斷退化的過程。下面,我們透過實驗,觀察clustering factor對於資料索引的影響,以及最佳化的方法。
1、 構建實驗環境
我們選擇Oracle版本為10gR2。
//獲取當前版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
Executed in 0.04 seconds
dba_object構建一張百萬級資料表,構造指令碼如下:
//插入指令碼
declare
i number;
begin
for i in 1..10 loop
insert /*+ append */ into t
select * from dba_objects order by i;
commit;
end loop;
end;
插入大約一百萬條資料。
SQL> select count(*) from t;
COUNT(*)
----------
1008000
Executed in 4.987 seconds
2、加入資料索引,收集統計資訊
我們構建的資料表基本是無序的,資料字典分析情況如下:
SQL> select owner, segment_name, blocks, extents, bytes from dba_segments where wner='SYS' and segment_name='T';
OWNER SEGMENT_NA BLOCKS EXTENTS BYTES
------- ---------- ---------- ---------- ----------
SYS T 14208 85 116391936
Executed in 0.221 seconds
構建t上的object_id索引列。
SQL> create index ind_t_id on t(object_id);
Index created
Executed in 17.515 seconds
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
Executed in 21.29 seconds
其索引的字典資訊。
SQL> select owner, segment_name, segment_type,blocks, extents, bytes from dba_segments where wner='SYS' and segment_name=upper('ind_t_id');
OWNER SEGMENT_NA SEGMENT_TYPE BLOCKS EXTENTS BYTES
------- ---------- ------------------ ---------- ---------- ----------
SYS IND_T_ID INDEX 2304 33 18874368
Executed in 0.17 seconds
發現,資料表T本身佔據空間超過116兆,對應的索引為18.9兆左右。
3、未進行資料表重構前,執行效率分析
在未進行重構之前,資料表T對應索引ind_t_id的clustering_factor資訊如下:
SQL> select owner, index_name, clustering_factor, num_rows from dba_indexes where wner='SYS' and index_name='IND_T_ID';
OWNER INDEX_NAME CLUSTERING_FACTOR NUM_ROWS
------- --------------- ----------------- --------------------
SYS IND_T_ID 1008000 1008000
Executed in 0.14 seconds
對索引列查詢,分析精確查詢和範圍查詢兩種分析。
精確查詢:
SQL> select * from t where object_id=1500;
已選擇20行。
已用時間: 00: 00: 00.04
執行計劃
----------------------------------------------------------
Plan hash value: 4182247035
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1860 | 23 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 20 | 1860 | 23 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_ID | 20 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1500)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
3126 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
範圍查詢:
當選取範圍略大的時候,Oracle執行最佳化器,會主動拒絕執行索引。
SQL> select * from t where object_id>1000 and object_id<2000
已選擇19980行。
已用時間: 00: 00: 04.85
執行計劃
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18947 | 1720K| 3092 (3)| 00:00:38 |
|* 1 | TABLE ACCESS FULL| T | 18947 | 1720K| 3092 (3)| 00:00:38 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<2000 AND "OBJECT_ID">1000)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
15179 consistent gets
13567 physical reads
1484 redo size
993274 bytes sent via SQL*Net to client
15026 bytes received via SQL*Net from client
1333 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19980 rows processed
雖然在資料列object_id上加了索引,而且查詢返回的資料量不到2萬行,遠遠少於資料表資料總量(100萬)。Oracle最佳化器在選取路徑的時候,放棄了按照索引進行搜尋的方案。
同樣的條件,使用Hint提示方法,強制走索引也可以檢視效果。
SQL> select /*+ index(t ind_t_id) */ * from t where object_id>1000 and object_id<2000;
已選擇19980行。
已用時間: 00: 00: 01.65
執行計劃
----------------------------------------------------------
Plan hash value: 4182247035
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18947 | 1720K| 19089 (1)| 00:03:50 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 18947 | 1720K| 19089 (1)| 00:03:50 |
|* 2 | INDEX RANGE SCAN | IND_T_ID | 19020 | | 45 (3)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">1000 AND "OBJECT_ID"<2000)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
21586 consistent gets
244 physical reads
16892 redo size
1938406 bytes sent via SQL*Net to client
15026 bytes received via SQL*Net from client
1333 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19980 rows processed
對比兩個結果,可以看出在這個返回結果比較小(約2%)的語句中,採用索引與全表掃描差別很大。
對比專案 |
全表掃描 |
強制索引 |
cpu cost |
3092 |
19083 |
time |
00:00:38 |
00:03:50 |
consistent get |
15179 |
21586 |
physical reads |
13567 |
244 |
redo size |
1484 |
16892 |
結論:我們在object_id上加入的索引ind_t_id,其clustering factor取值為100萬,與資料行相同。索引健康程度比較差。在唯一查詢object_id的時候,走索引。但是進行範圍查詢的時候,即使結果資料量僅為2%,oracle還是放棄了索引的執行計劃,選擇了full table scan,說明索引健康程度有時是比加索引的技巧左右大。
4、重構資料表,最佳化索引質量
從索引、Clustering Factor的原理看,解決clustering Factor的核心在於資料行重排。讓資料行按照索引列排序的順序儲存,效果比較好。
//重構表
SQL> create table tt as select * from t where 1=0;
Table created
Executed in 0.14 seconds
SQL> insert /*+ append */ into tt select * from t order by object_id;
1008000 rows inserted
Executed in 80.225 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
SQL> truncate table t;
Table truncated
Executed in 0.421 seconds
SQL> insert /*+ append */ into t select * from tt;
1008000 rows inserted
Executed in 29.182 seconds
SQL> commit;
Commit complete
Executed in 0.03 seconds
SQL> alter index ind_t_id rebuild;
Index altered
Executed in 10.665 seconds
獲取統計資料,並且檢視索引的健康程度。
//統計資訊
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
Executed in 19.157 seconds
SQL> select owner, index_name, clustering_factor, num_rows from dba_indexes where wner='SYS' and index_name='IND_T_ID';
OWNER INDEX_NAME CLUSTERING_FACTOR NUM_ROWS
------- --------------- ----------------- --------------------
SYS IND_T_ID 13831 1008000
Executed in 0.09 seconds
可以看到,我們按照object_id進行表重構之後,索引的clustering factor縮小為13831,只有num rows的約2%。健康程度最佳化。
5、重新執行搜尋,檢視執行計劃
進行精確查詢:
SQL> select * from t where object_id=1500;
已選擇20行。
已用時間: 00: 00: 00.08
執行計劃
----------------------------------------------------------
Plan hash value: 4182247035
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1860 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 20 | 1860 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_ID | 20 | | 3 (0)| 00:00:01 |
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1500)
統計資訊
----------------------------------------------------------
381 recursive calls
0 db block gets
62 consistent gets
1 physical reads
116 redo size
3126 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
20 rows processed
對比重構前後情況,發現CPU成本上有很明顯的變化,其中Index Range Scan的成本沒有發生變化,一直為3。而Table Access By Index ROWID有較大的變化,從原有的23下降到4。這一現象說明搜尋索引的成本沒有變化,但是根據索引返回ROWID,查詢資料表塊的成本有所小,和預計情況相同。
範圍查詢:
SQL> select * from t where object_id>1000 and object_id<2000;
已選擇19980行。
已用時間: 00: 00: 01.22
執行計劃
----------------------------------------------------------
Plan hash value: 4182247035
------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18980 | 1723K| 308 (1)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 18980 | 1723K| 308 (1)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | IND_T_ID | 19031 | | 45 (3)| 00:00:01 |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">1000 AND "OBJECT_ID"<2000)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
2931 consistent gets
249 physical reads
0 redo size
1938406 bytes sent via SQL*Net to client
15026 bytes received via SQL*Net from client
1333 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19980 rows processed
從返回的結果看,進行資料表重構後,我們的查詢效率大大提升,原有Oracle選擇全表掃描的情況已經不再出現,直接選擇了Index搜尋。而Index搜尋的效果也有相當提升,CPU成本只有308,消耗時間00:00:04。資料邏輯讀塊只有2931,物理讀只有249。兩次試驗的詳細對比如下:
對比專案 |
未重構表 |
重構後 | |||
全表掃描 (範圍) |
強制索引 (範圍) |
準確查詢 |
索引搜尋 (範圍) |
準確查詢 | |
cpu cost |
3092 |
19083 |
23 |
308 |
4 |
time |
00:00:38 |
00:03:50 |
00:00:01 |
00:00:04 |
00:00:01 |
consistent get |
15179 |
21586 |
25 |
2931 |
62 |
physical reads |
13567 |
244 |
0 |
249 |
1 |
redo size |
1484 |
16892 |
0 |
0 |
116 |
6、結論
透過上面的實驗,我們可以看到:clustering factor是索引健康程度的一個重要指標。我們有的時候,雖然在資料表的指定列中加入了索引,但是因為索引的健康程度不高,可能效率很差。我們經常可以聽說,如果查詢比例在15%或者XXX%以下的時候,我們要加索引,但是在我們的例子中,資料只有2%,但是還是成本高於FTS(Full Table Scan),不會選擇索引路徑。所以,經常性的維護我們的索引是很重要的。
但是,本文書寫的目的絕不是讓將重構表成為一個常態任務,重構表只是一個手段。在實際中,是有一些問題的。
首先,重構表的行順序是需要選擇的。一個需要我們規劃的表,其上常常不止一個索引,一個索引的clustering factor高效,可能就意味著另一個索引的惡化。所以一定要慎用。
其次,在一些物理效能不是很好的環境下做,重構資料表可能是一個比較繁重的工作。
最後,clustering factor總的趨勢一般都是不斷惡化(選擇隨機存取),合理的規劃,使用其他如分割槽等技術也是解決我們最終問題的方案。這部分可能在海量資料庫中應用比較多。
所以,在關注有沒有索引的同時,也要關注我們索引的健康程度。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28389881/viewspace-775741/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- DBA_INDEXES.CLUSTERING_FACTOR 索引的群集因子初探Index索引
- DBA_INDEXES.CLUSTERING_FACTOR 索引的群集因子初探(原)Index索引
- [Oracle] Clustering FactorOracle
- oracle的clustering_factorOracle
- 淺談索引系列之聚簇因子(clustering_factor)索引
- clustering factor索引聚簇因子和執行計劃索引
- 高效的SQL( clustering factor減少COST)SQL
- 關於索引聚簇因子(CLUSTERING_FACTOR)引起的sql優化過程索引SQL優化
- CLUSTERING_FACTOR、回表、資料分佈傾斜走全表還是索引索引
- CLUSTERING_FACTOR影響執行計劃
- 11g 是否有解決clustering_factor 高的問題
- 索引掃描可能不如全表掃描的場景的理解__純粹資料量而言,不涉及CLUSTERING_FACTOR索引
- 通達信成本無敵主圖指標公式原始碼指標公式原始碼
- NULL 指標、零指標、野指標Null指標
- 野指標 空指標指標
- 指標問題的一點體會(區別 [指向指標的指標] 與 [指標的指標] .) (轉)指標
- 指標常量和常量指標指標
- Go 陣列指標(指向陣列的指標)Go陣列指標
- 指向常量資料的指標和常量指標指標
- C語言指標(三):陣列指標和字串指標C語言指標陣列字串
- 陣列指標,指標陣列陣列指標
- ARC中強指標與弱指標指標
- 控制指標與統計指標指標
- 陣列指標 指標陣列陣列指標
- 關於指標傳遞和指標的指標指標
- 指向const物件的指標 和 const指標物件指標
- 指標指標
- 指向指標的指標指標
- 比如,一級指標、二級指標等,通過不同層級的指標資料指標
- 指標陣列與陣列指標指標陣列
- 詳解 常量指標和指標常量指標
- 陣列指標和指標陣列陣列指標
- 指標函式 和 函式指標指標函式
- 利用函式索引,最佳化因cluster factor過高導致不走索引一例函式索引
- 索引成本計算的基礎知識索引
- 【原創】淺談指標(十三)指向陣列的指標指標陣列
- 陣列指標:a pointer to an array,即指向陣列的指標陣列指標
- OC的isa指標指標