Oracle 索引的三個問題(轉)

Oracle 索引的三個問題(轉)[@more@]

　　索引( Index )是常見的資料庫物件，它的設定好壞、使用是否得當，極大地影響資料庫應用程式和Database 的效能。雖然有許多資料講索引的用法， DBA 和 Developer 們也經常與它打交道，但筆者發現，還是有不少的人對它存在誤解，因此針對使用中的常見問題，講三個問題。此文所有示例所用的資料庫是 Oracle 8.1.7 OPS on HP N series ,示例全部是真實資料，讀者不需要注意具體的資料大小，而應注意在使用不同的方法後，資料的比較。本文所講基本都是陳詞濫調，但是筆者試圖透過實際的例子，來真正讓您明白事情的關鍵。

　　第一講、索引並非總是最佳選擇

　　如果發現Oracle 在有索引的情況下，沒有使用索引，這並不是Oracle 的最佳化器出錯。在有些情況下，Oracle 確實會選擇全表掃描（Full Table Scan）,而非索引掃描（Index Scan）。這些情況通常有：

　　1. 表未做statistics, 或者 statistics 陳舊，導致 Oracle 判斷失誤。

　　2. 根據該表擁有的記錄數和資料塊數，實際上全表掃描要比索引掃描更快。

　　對第1種情況，最常見的例子，是以下這句sql 語句：

　　select count(*) from mytable;

　　在未作statistics 之前，它使用全表掃描，需要讀取6000多個資料塊（一個資料塊是8k）, 做了statistics 之後，使用的是 INDEX (FAST FULL SCAN) ，只需要讀取450個資料塊。但是，statistics 做得不好，也會導致Oracle 不使用索引。

　　第2種情況就要複雜得多。一般概念上都認為索引比錶快，比較難以理解什麼情況下全表掃描要比索引掃描快。為了講清楚這個問題，這裡先介紹一下Oracle 在評估使用索引的代價（cost）時兩個重要的資料：CF(Clustering factor) 和 FF(Filtering factor).

　　CF: 所謂 CF, 通俗地講，就是每讀入一個索引塊，要對應讀入多少個資料塊。

　　FF: 所謂 FF, 就是該sql 語句所選擇的結果集，佔總的資料量的百分比。

　　大約的計算公式是：FF * (CF + 索引塊個數) ，由此估計出，一個查詢， 如果使用某個索引，會需要讀入的資料塊塊數。需要讀入的資料塊越多，則 cost 越大，Oracle 也就越可能不選擇使用 index. （全表掃描需要讀入的資料塊數等於該表的實際資料塊數）

　　其核心就是， CF 可能會比實際的資料塊數量大。CF 受到索引中資料的排列方式影響，通常在索引剛建立時，索引中的記錄與表中的記錄有良好的對應關係，CF 都很小；在表經過大量的插入、修改後，這種對應關係越來越亂，CF 也越來越大。此時需要 DBA 重新建立或者組織該索引。

　　如果某個sql 語句以前一直使用某索引，較長時間後不再使用，一種可能就是 CF 已經變得太大，需要重新整理該索引了。

　　FF 則是Oracle 根據 statistics 所做的估計。比如, mytables 表有32萬行，其主鍵myid的最小值是1，最大值是409654，考慮以下sql 語句：

　　Select * from mytables where myid>=1; 和Select * from mytables where myid>=400000

　　這兩句看似差不多的 sql 語句，對Oracle 而言，卻有巨大的差別。因為前者的 FF 是100%， 而後者的 FF 可能只有 1%。如果它的CF 大於實際的資料塊數，則Oracle 可能會選擇完全不同的最佳化方式。而實際上，在我們的資料庫上的測試驗證了我們的預測. 以下是在HP 上執行時它們的 explain plan:

　　第一句：

　　SQL> select * from mytables where myid>=1;

　　已選擇325917行。

　　Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3132 Card=318474 Byt es=141402456)1 0 TABLE ACCESS (FULL) OF 'MYTABLES' (Cost=3132 Card=318474 Byt es=141402456)Statistics----------------------------------------------------------7 recursive calls89 db block gets41473 consistent gets19828 physical reads0 redo size131489563 bytes sent via SQL*Net to client1760245 bytes received via SQL*Net from client21729 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)325917 rows processed

　　第二句：

　　Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=346 Card=663 Bytes=2 94372)1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MYTABLES' (Cost=346 Card=663Bytes=294372)2 1 INDEX (RANGE SCAN) OF 'PK_MYTABLES' (UNIQUE) (Cost=5 Card=663)Statistics----------------------------------------------------------1278 recursive calls0 db block gets6647 consistent gets292 physical reads0 redo size3544898 bytes sent via SQL*Net to client42640 bytes received via SQL*Net from client524 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)7838 rows processed

　　顯而易見，第1句沒有使用索引，第2句使用了主鍵索引pk_mytables. FF的巨大影響由此可見一斑。由此想到，我們在寫sql 語句時，如果預先估計一下 FF, 你就幾乎可以預見到 Oracle 會否使用索引。

　　第二講、索引也有好壞

　　索引有 B tree 索引， Bitmap 索引， Reverse b tree 索引， 等。最常用的是 B tree 索引。 B 的全稱是Balanced , 其意義是，從 tree 的 root 到任何一個leaf ，要經過同樣多的 level. 索引可以只有一個欄位（Single column）, 也可以有多個欄位（Composite）,最多32個欄位，8I 還支援 Function-based index. 許多developer 都傾向於使用單列B 樹索引。

　　所謂索引的好壞是指：

　　1，索引不是越多越好。特別是大量從來或者幾乎不用的索引，對系統只有損害。OLTP系統每表超過5個索引即會降低效能，而且在一個sql 中， Oracle 從不能使用超過 5個索引。

　　2，很多時候，單列索引不如複合索引有效率。

　　3，用於多表連結的欄位，加上索引會很有作用。

　　那麼，在什麼情況下單列索引不如複合索引有效率呢？有一種情況是顯而易見的，那就是，當sql 語句所查詢的列，全部都出現在複合索引中時，此時由於 Oracle 只需要查詢索引塊即可獲得所有資料，當然比使用多個單列索引要快得多。（此時，這種最佳化方式被稱為 Index only access path）

　　除此之外呢？我們還是來看一個例子吧：

　　在 HP（Oracle 8.1.7） 上執行以下語句：

　　select count(1) from mytabs where coid>=130000 and issuedate >= to_date ('2001-07-20', 'yyyy-mm-dd')。

　　一開始，我們有兩個單列索引：I_mytabs1(coid), I_mytabs2(issuedate), 下面是執行情況：

　　COUNT(1)----------6427Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=384 Card=1 Bytes=11)1 0 SORT (AGGREGATE)2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_MYTABS' (Cost=384 Card=126 Bytes=1386)3 2 INDEX (RANGE SCAN) OF 'I_MYTABS2' (NON-UNIQUE) (Cost=11Card=126)Statistics----------------------------------------------------------172 recursive calls1 db block gets5054 consistent gets2206 physical reads0 redo size293 bytes sent via SQL*Net to client359 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client5 sorts (memory)0 sorts (disk)1 rows processed

　　可以看到，它讀取了7000個資料塊來獲得所查詢的 6000多行。

　　現在，去掉這兩個單列索引，增加一個複合索引I_mytabs_test ( coid, issuedate), 重新執行，結果如下：

　　COUNT(1)----------6436Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=11)1 0 SORT (AGGREGATE)2 1 INDEX (RANGE SCAN) OF 'I_MYTABS_TEST' (NON-UNIQUE) (Cost=3 Card=126 Bytes=1386)Statistics----------------------------------------------------------806 recursive calls5 db block gets283 consistent gets76 physical reads0 redo size293 bytes sent via SQL*Net to client359 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client3 sorts (memory)0 sorts (disk)1 rows processed

　　可以看到，這次只讀取了300個資料塊。

　　7000塊對300塊，這就是在這個例子中，單列索引與複合索引的代價之比。這個例子提示我們， 在許多情況下，單列索引不如複合索引有效率。

　　可以說，在索引的設定問題上，其實有許多工作可以做。正確地設定索引，需要對應用進行總體的分析。

　　第三講、索引再好，不用也是白搭

　　拋開前面所說的，假設你設定了一個非常好的索引，任何傻瓜都知道應該使用它，但是Oracle 卻偏偏不用，那麼，需要做的第一件事情，是審視你的 sql 語句。

　　Oracle 要使用一個索引，有一些最基本的條件：

　　1， where 子句中的這個欄位，必須是複合索引的第一個欄位；

　　2， where 子句中的這個欄位，不應該參與任何形式的計算

　　具體來講，假設一個索引是按 f1, f2, f3的次序建立的，現在有一個 sql 語句, where 子句是 f2 = : var2, 則因為 f2 不是索引的第1個欄位，無法使用該索引。

　　第2個問題，則在我們之中非常嚴重。以下是從 實際系統上面抓到的幾個例子：

　　Select jobid from mytabs where isReq='0' and to_date (updatedate) >= to_Date ( '2001-7-18', 'YYYY-MM-DD')；………

　　以上的例子能很容易地進行改進。請注意這樣的語句每天都在我們的系統中執行，消耗我們有限的cpu 和 記憶體資源。

　　除了1，2這兩個我們必須牢記於心的原則外，還應儘量熟悉各種運算子對 Oracle 是否使用索引的影響。這裡我只講哪些操作或者運算子會顯式（explicitly）地阻止 Oracle 使用索引。以下是一些基本規則：

　　1， 如果 f1 和 f2 是同一個表的兩個欄位，則 f1>f2, f1>=f2, f1

　　2， f1 is null, f1 is not null, f1 not in, f1 !=, f1 like ‘%pattern%’;

　　3， Not exist

　　4， 某些情況下，f1 in 也會不用索引；

　　對於這些操作，別無辦法，只有儘量避免。比如，如果發現你的 sql 中的 in 操作沒有使用索引，也許可以將 in 操作改成 比較操作 + union all。筆者在實踐中發現很多時候這很有效。

　　但是，Oracle 是否真正使用索引，使用索引是否真正有效，還是必須進行實地的測驗。合理的做法是，對所寫的複雜的 sql, 在將它寫入應用程式之前，先在產品資料庫上做一次explain . explain 會獲得Oracle 對該 sql 的解析（plan）,可以明確地看到 Oracle 是如何最佳化該 sql 的。

　　如果經常做 explain, 就會發現，喜愛寫複雜的 sql 並不是個好習慣，因為過分複雜的sql 其解析計劃往往不盡如人意。事實上，將複雜的 sql 拆開，有時候會極大地提高效率，因為能獲得很好的最佳化。當然這已經是題外話了。