前言
索引( 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= 1414 0 2456 )
1 0 TABLE ACCESS (FULL) OF 'MYTABLES' (Cost=3132 Card=318474 Byt es= 1414 0 2456 )
Statistics
----------------------------------------------------------
7 recursive calls
89 db block gets
4 1473 consistent gets
19828 physical reads
0 redo size
1314 89563 bytes sent via SQL*Net to client
1760245 bytes received via SQL*Net from client
2 1729 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
325917 rows processed
第二句：
Select * from mytables where myid>=400000;
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=663 Bytes=294372)
2 1 INDEX (RANGE SCAN) OF 'PK_MYTABLES' (UNIQUE) (Cost=5 Card=663)
Statistics
----------------------------------------------------------
1278 recursive calls
0 db block gets
6647 consistent gets
292 physical reads
0 redo size
3 5448 98 bytes sent via SQL*Net to client
42640 bytes received via SQL*Net from client
524 SQL*Net roundtrips to/from client
1 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)
----------
6427
Execution 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=11 Card=126)
Statistics
----------------------------------------------------------
172 recursive calls
1 db block gets
5054 consistent gets
2206 physical reads
0 redo size
293 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，它讀取了7000個資料塊來獲得所查詢的 6000多行。
現在，去掉這兩個單列索引，增加一個複合索引I_mytabs_test ( coid, issuedate), 重新執行，結果如下：
COUNT(1)
----------
6436
Execution 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 calls
5 db block gets
283 consistent gets
6 physical reads
0 redo size
293 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 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 f1<="f2">
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 拆開，有時候會極大地提高效率，因爲能獲得很好的優化。當然這已經是題外話了。
留言列表
資訊技術 (11)
