本文只講最最平常最最簡單的索引就是以create index ix on tx(abc);形式創建的索引而不講位圖索引反向鍵索引倒序索引基於函數的索引等等其實呢只要是基於B樹的索引不管是在Oracle Mysql還是其它數據庫中原理應當都是一樣的
索引最重要的一個性質應該就是有序索引中的每一項是從左到右從小到大以嚴格的順序排列好的
下面的討論都以上面的索引ix(abc)為例
把這棵索引的葉子節點畫到紙上大概是這樣的
a a a an
b b b bn
c c c cn
上面這個×n的矩陣每一列代表了一條記錄同時這一列記錄也對應了表裡的唯一一條記錄當然在Oracle裡對於nonunique索引需要補上rowid才是真正唯一的上面的索引相當於create unique index ix on tx(abcrowid); 我們把這個細節忽略掉
把每一列看作一個向量vi = (ai bi ci)
有序的含義就是
vi < vj iff i < j;
vi < vj這麼定義
(ai < aj) or (ai = aj and bi < bj) or (ai = aj and bi = bj and ci < cj)
從這個基本性質我們可以得到一些其它性質(為了打字方便ai+k表示a(i+k)而不是a(i)+k)
) 如果ai ai+ …… ai+k 都是相等的那麼
bi <= bi+ <= …… <= bi+k
) 如果ai ai+ …… ai+k是相等的而且bibi+ …… bi+k也是相等的那麼
ci <= ci+ <= …… < ci+k
但是從 ai ai+ …… ai+k相等我們得不到
ci <= ci+ <= …… <= ci+k這個結論
索引相關的很多問題都和上面提到的這幾個性質有關系
下面來看幾個常見的查詢:
q) select * from tx where a = :va and b = :vb;
q) select * from tx where b = :vb and c = :vc;
q) select * from tx where a = :va and c = :vc;
q) select * from tx where a = :va order by b;
q) select * from tx where a = :va order by b c;
q) select * from tx where a = :va order by c;
q) select * from tx where a = :va order by b c desc;
q) select * from tx where a = :va order by b desc c desc;
q) select * from tx where a = :va and b <= :vb
qa) select * from tx where a = :va and b >= :vb
qb) select * from tx where a = :va and c >= :vc
qc) select * from tx where a = :va and b >= :vb order by c
大家可以考慮一下這些查詢各自會以怎樣的方式執行不同查詢之間有什麼區別?
同樣為什麼在索引字段上作了函數運算之後索引不可用?
考慮下面這個語句:
select * from tx where f(a) = :vfa;
首先在字段 a上作了函數運算之後排序的規則是否仍舊一樣? a < b 與 f(a) < f(b)是否等價?
其次就算f(a)和a的排序規則一樣但是索引塊中存的a 但是你傳給它的是經過了函數運算的值:vfa 只有oracle知道函數f的反函數inv_f並在vfa上做inv_f(:vfa)計算之後才能通過索引的B樹結果進行查找
當然現實中f可能不是顯示的而是隱式的如傳入參數和字段類型不匹配的情況下Oracle可能在字段上作函數運算從語句上可能看不出索引字段上被做了函數運算但Oracle內部已經在字段上運用了函數這樣也會導致索引不可用這種情況下用hint強制使用索引也是沒用的
通過dbms_xplandisplay_cursor可以或許可以查看到這種隱式類型轉換
通過v$sql_bind_metadata應當可以查看到每個綁定變量的類型
通過v$sql_bind_capture這個視圖甚至可以看到每個綁定變量具體的值不要把bind_capture和bind peek搞混哦而且這裡bind_cature也不會每綁定一次變量就capture一次不然對執行量非常高綁定頻繁的語句capture以同樣頻率進行的話開銷可能還是有點大的
上面講到了索引的有序性下面來講講索引另外一個有趣的性質其實我們完全可以把索引看作一張表這張表包含和主表一樣多的記錄(如果不考慮null)只不過每條記錄只有主表的部分字段開個玩笑我們是不是可以把索引叫做有序視圖呢?或者精確一點有序物化視圖:)
那麼我在執行一些查詢的時候如果所有字段都包含在索引中是不是只要訪問索引就可以了呢?
這些字段可以出現在select列表中where條件中order by字段中也可以出現在兩個表連接時的連接條件中
那麼根據業務的需求我們是不是可以設計或調整索引以減少對主表的訪問呢?或者是不是可以適當的調整應用的設計或實現來滿足索引呢?
同時考慮到索引的有序性是不是可以利用索引來避免排序呢?
當然我們不能忽略null的存在如果一條記錄在索引中的所有字段上都是null的那麼oracle是不會索引這條記錄的比如如果記錄ri的ai bi ci字段都是null的索引中是找不到這條記錄的這會有什麼問題呢?首先表中的記錄和索引中的記錄從數量上來說就不一樣了
考慮一下Oracle會怎樣執行下面這個查詢:
select count(*) from tx;
這個呢hint起作用了嗎?
select /*+ parallel(tx ) */ count(*) from tx;
大家可以測試一下怎樣把count(*)這個操作並行化從這裡或許可以得到一些Oracle怎麼處理hint的提示
最後講一下Oracle CBO計算索引訪問成本的公式
cost =
blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)
這個公式相信很多地方可以找到(我是從cost base oracle fundamentals這本書裡copy出來的)簡單說一下我自己對這個公式的理解
blevel是索引樹的高度
leaf_blocks是索引的頁子節點的個數
effective index selectivity (eis)怎麼算呢?
還是舉幾個例子
where a = :va and b = :vb c = :vc
這裡eis是 (selectivity a) * (selectivity b) * (selectivity c)
where a = :va and c = :vc
這裡eis是 selectivity a
where b = :vb and c = :vc
這裡eis是
where a = :va and b >= :vb and c = :vc
這裡eis是 (selectivity a) * (selectivity range b)
就是說按索引字段的順序第一個不在where條件中出現的字段或者第一個做了范圍運算的字段之後出現的字段的selectivity是不能乘到effective index selectivity裡去的
簡單的說ceiling(leaf_blocks * effective index selectivity)表示的是Oracle需要訪問的索引葉子節點的個數
clustering_factor表示的是按索引的順序從頭走到尾需要訪問多少次數據塊這裡需要考慮到Oracle的一個優化如果連續n條記錄在同一個表塊中那麼oracle認為只需要訪問一次數據塊
那麼clustering_factor的值的范圍就很容易確定了cf >= table blocks and cf <= rows in index
effective table selectivity這個計算就容易了把索引中所有字段的selectivity乘起來就可以了
如果查詢中還有其它條件 比如 d = :vd and e = :ve 但是de這些字段又不在索引中那麼在這些列上的過濾條件需要回表後把這些值取出來才能判斷所以de這些列的selectivity是不能乘到effective table selectivity裡去的
ceiling(clustering_factor * effective table selectivity)表示需要回表的次數
所以上面索引訪問的cost就是走某個索引需要訪問的數據塊的個數
當然前面的討論忽略了index skip scan這種情況因為本人對index skip scan也不是很明白
什麼情況下會走skip scan?
select * from tx where a = :va and c = :vc 是不是會在c這個字段上也作一個skip scan呢?
同時也沒有考慮in list iterate這些情況需要進一步研究
From:http://tw.wingwit.com/Article/program/Oracle/201311/16730.html
