Oracle效能分析6：資料訪問方式之索引掃描

標籤：database   oracle   效能   資料庫   

這節將介紹各種索引掃描方式，在瞭解了各種索引掃描方式的特點後，你就可以判斷你的執行計畫中使用的掃描方式是否正確，並可以針對擷取的資訊作出改進。

索引唯一掃描

在下面的情境中使用相等條件時，資料庫使用索引唯一掃描。
 1）查詢條件中包含唯一索引中的所有列時；
 2）查詢條件使用主鍵約束列時。
下面是一個實際的例子，在表historyalarm中建立如下唯一索引：

create unique index idx_historyalarm$queryid on historyalarm(queryid) tablespace uep4x_fm_index

然後在表上執行查詢：

select * from historyalarm where queryid = 3

該查詢符合上面的第一種情況，會使用索引唯一掃描，該查詢的執行計畫如下：

DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS TABLE ACCESS BY GLOBAL INDEX ROWIDHISTORYALARM  INDEX UNIQUE SCANIDX_HISTORYALARM$QUERYID

這裡Oracle首先通過唯一索引掃描找到索引節點，然後使用索引節點中包含的rowid來訪問表中的資料。

索引範圍掃描

當查詢條件可能會返回一定範圍的資料時就會選用索引範圍掃描，索引可以是唯一索引或者不唯一索引，但如果查詢條件包含的資料範圍太大，也有可能導致全表掃描。查詢條件中使用<、>、LIKE、BETWEEN、=等都可能使用索引範圍掃描，需要注意單個=條件在唯一索引或者主鍵上將導致索引唯一掃描。
下面是一個索引範圍掃描的例子，在上面的histroyalarm中執行一個範圍查詢：

select * from caffm4x.historyalarm where queryid < 10

這裡查詢的資料是一個範圍，且使用了queryid列，在queryid列上有唯一索引，但任然會導致索引範圍掃描：

DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS TABLE ACCESS BY GLOBAL INDEX ROWIDHISTORYALARM  INDEX RANGE SCANIDX_HISTORYALARM$QUERYID

索引範圍掃描從索引的根節點出發，找到第一個匹配的條目所在的葉子資料區塊開始遍曆索引結構，首先從索引條目中取出rowid然後取出對應的表資料區塊（通過rowid訪問資料表），接下來葉子索引塊會被再次訪問並讀取下一個索引條目並擷取rowid，這樣反覆直到整個葉子索引塊被的索引條目全部被讀出。因此排除索引根節點和中間節點，每行資料讀取需要讀取兩個資料區塊，我們可以通過blevel來得到索引高度，通過索引高度和擷取的資料行數就能得到需要讀取的資料區塊數，例如：如果blevel為3，讀取5行資料，則總的需要訪問的資料區塊次數將是（5*2) + 3 = 13（注意只有根節點時blevel為0）。
如果在讀取了整個葉子索引塊之後，還需要訪問下一個葉子索引塊，在當前的葉子索引塊中有指向下一個葉子索引塊的指標（也含有指向上一個葉子索引塊的指標）。
使用索引範圍掃描的另一個優勢就在於排序，由於索引的節點是有序的，因此如果查詢的結果需要按照索引列排序（升序或者降序），那麼使用索引範圍掃描則可以很好的避免排序操作，例如：

select * from historyalarm where queryid > 10

由於queryid大於10的資料量佔總資料量的99%，因此Oracle的最佳化器選擇了全表掃描：

DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS PARTITION RANGE ALL  TABLE ACCESS FULLHISTORYALARM

如果我們在查詢時對資料指定排序，如下：

select * from historyalarm where queryid > 10 order by queryid

執行計畫如下：

DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS TABLE ACCESS BY GLOBAL INDEX ROWIDHISTORYALARM  INDEX RANGE SCANIDX_HISTORYALARM$QUERYID

最佳化器改為使用了索引範圍掃描。由於當資料量很大時，排序的代價是很大的（可能導致物理排序），這時使用索引範圍掃描將是一個很好的選擇，特別是當你排序後選擇部分資料的情況下（rownum < n）。

索引全掃描

索引全掃描會讀取索引上的所有條目，下面幾種情況可能導致索引全掃描：

1）沒有條件但是所需擷取列的列表可以通過其中一列的索引來獲得；

select id from t3DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS INDEX FULL SCANIDX_T3_ID

由於id列帶有索引，因此這裡最佳化器選擇了索引全掃描。

2）查詢條件中包含排序操作

select * from historyalarm order by queryidDESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS INDEX FULL SCANIDX_HISTORYALARM$QUERYID

索引全掃描讀取單個資料區塊，讀取每個條目的rowid，再通過rowid取出資料行，由於索引已經排序，所以不必執行排序操作。如果查詢只請求了索引列，資料庫將跳過表訪問，只通過訪問索引得到資料。
索引全掃描的另一個優勢在計算最大、最小值時：

select min(queryid) from historyalarm DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS SORT AGGREGATE  INDEX FULL SCAN (MIN/MAX)IDX_HISTORYALARM$QUERYID

由於索引本身已經排序，因此在計算最大最小值時只需要很小的代價。

索引跳躍掃描

當查詢條件中帶有符合索引中的列，但是不包含前置列時，就可能導致索引跳躍掃描。資料庫將一個複合索引拆分為多個邏輯子索引，符合索引前置列的不同值決定邏輯子索引的數量，即前置列的不同值越少，索引跳躍式掃描的效能就越好。

select value from t3 where value = 'test'DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS INDEX SKIP SCANIDX_T3_COMBINE

索引快速全掃描

當索引本身包含查詢中指定的所有列時，Oracle執行索引快速全掃描。索引快速全掃描和索引全掃描的區別在於：索引全掃描使用單塊讀操作，而索引快速全掃描使用多塊讀。這種掃描不能用於避免排序，因為資料區塊是通過無序的多塊讀取來讀取的。

select queryid from historyalarmDESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS INDEX FAST FULL SCANIDX_HISTORYALARM$QUERYID

 

 

Oracle效能分析6：資料訪問方式之索引掃描