? 索引( Index )是常見的數(shù)據(jù)庫對象拟蜻，它的設(shè)置好壞、使用是否得當枯饿，極大地影響數(shù)據(jù)庫應(yīng)用程序和Database 的性能酝锅。 雖然有許多資料講索引的用法， DBA 和 Developer 們也經(jīng)常與它打交道奢方，但筆者發(fā)現(xiàn)屈张，還是有不少的人對它存在誤解，因此針對使用中的常見問題袱巨，講三個問題阁谆。此文所有示例所用的數(shù)據(jù)庫是 Oracle 8.1.7 OPS on HP N series ,示例全部是真實數(shù)據(jù)，讀者不需要注重具體的數(shù)據(jù)大小愉老，而應(yīng)注重在使用不同的方法后场绿，數(shù)據(jù)的比較。本文所講基本都是陳詞濫調(diào)嫉入，但是筆者試圖通過實際的例子焰盗，來真正讓您明白事情的要害璧尸。?

? 第一講、索引并非總是最佳選擇 假如發(fā)現(xiàn)Oracle 在有索引的情況下熬拒，沒有使用索引爷光，這并不是Oracle 的優(yōu)化器出錯。在有些情況下澎粟，Oracle 確實會選擇全表掃描（Full Table Scan）,而非索引掃描（Index Scan）蛀序。這些情況通常有： 1. 表未做statistics, 或者 statistics 陳舊，導致 Oracle 判定失誤活烙。 2. 根據(jù)該表擁有的記錄數(shù)和數(shù)據(jù)塊數(shù)徐裸，實際上全表掃描要比索引掃描更快。 對第1種情況啸盏，最常見的例子重贺，是以下這句sql 語句： select count(*) from mytable; 在未作statistics 之前，它使用全表掃描回懦，需要讀取6000多個數(shù)據(jù)塊（一個數(shù)據(jù)塊是8k）, 做了statistics 之后气笙，使用的是 INDEX (FAST FULL SCAN) ，只需要讀取450個數(shù)據(jù)塊怯晕。但是潜圃，statistics 做得不好，也會導致Oracle 不使用索引贫贝。 第2種情況就要復雜得多。一般概念上都認為索引比表快蛉谜，比較難以理解什么情況下全表掃描要比索引掃描快稚晚。為了講清楚這個問題，這里先介紹一下Oracle 在評估使用索引的代價（cost）時兩個重要的數(shù)據(jù)：CF(Clustering factor) 和 FF(Filtering factor). CF: 所謂 CF, 通俗地講型诚，就是每讀入一個索引塊客燕，要對應(yīng)讀入多少個數(shù)據(jù)塊。 FF: 所謂 FF, 就是該sql 語句所選擇的結(jié)果集狰贯，占總的數(shù)據(jù)量的百分比也搓。 大約的計算公式是：FF * (CF + 索引塊個數(shù)) ，由此估計出涵紊，一個查詢傍妒， 假如使用某個索引，會需要讀入的數(shù)據(jù)塊塊數(shù)摸柄。需要讀入的數(shù)據(jù)塊越多颤练，則 cost 越大，Oracle 也就越可能不選擇使用 index. （全表掃描需要讀入的數(shù)據(jù)塊數(shù)等于該表的實際數(shù)據(jù)塊數(shù)） 其核心就是驱负， CF 可能會比實際的數(shù)據(jù)塊數(shù)量大嗦玖。CF 受到索引中數(shù)據(jù)的排列方式影響患雇，通常在索引剛建立時，索引中的記錄與表中的記錄有良好的對應(yīng)關(guān)系宇挫，CF 都很锌林ā；在表經(jīng)過大量的插入器瘪、修改后翠储，這種對應(yīng)關(guān)系越來越亂，CF 也越來越大娱局。此時需要 DBA 重新建立或者組織該索引彰亥。 假如某個sql 語句以前一直使用某索引，較長時間后不再使用衰齐，一種可能就是 CF 已經(jīng)變得太大任斋，需要重新整理該索引了。 FF 則是Oracle 根據(jù) 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 大于實際的數(shù)據(jù)塊數(shù)奏赘，則Oracle 可能會選擇完全不同的優(yōu)化方式寥闪。而實際上，在我們的數(shù)據(jù)庫上的測試驗證了我們的猜測. 以下是在HP 上執(zhí)行時它們的 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 calls 89 db block gets 41473 consistent gets 19828 physical reads 0 redo size 131489563 bytes sent via SQL*Net to client 1760245 bytes received via SQL*Net from client 21729 SQL*Net roundtrips to/from client 1 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=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 3544898 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 谐檀，要經(jīng)過同樣多的 level. 索引可以只有一個字段（Single column）, 也可以有多個字段（Composite）,最多32個字段，8I 還支持 Function-based index. 許多developer 都傾向于使用單列B 樹索引裁奇。 所謂索引的好壞是指： 1桐猬，索引不是越多越好。非凡是大量從來或者幾乎不用的索引刽肠，對系統(tǒng)只有損害溃肪。OLTP系統(tǒng)每表超過5個索引即會降低性能，而且在一個sql 中音五， Oracle 從不能使用超過 5個索引惫撰。 2，很多時候躺涝，單列索引不如復合索引有效率厨钻。 3，用于多表連結(jié)的字段坚嗜，加上索引會很有作用夯膀。 那么，在什么情況下單列索引不如復合索引有效率呢苍蔬？有一種情況是顯而易見的诱建，那就是，當sql 語句所查詢的列碟绑，全部都出現(xiàn)在復合索引中時俺猿，此時由于 Oracle 只需要查詢索引塊即可獲得所有數(shù)據(jù)，當然比使用多個單列索引要快得多格仲。（此時押袍，這種優(yōu)化方式被稱為 Index only access path） 除此之外呢？我們還是來看一個例子吧：?

在 HP（Oracle 8.1.7） 上執(zhí)行以下語句：?

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

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

?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個數(shù)據(jù)塊來獲得所查詢的 6000多行惭蟋。 現(xiàn)在，去掉這兩個單列索引告组，增加一個復合索引I_mytabs_test ( coid, issuedate), 重新執(zhí)行煤伟，結(jié)果如下：?

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 76 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個數(shù)據(jù)塊便锨。 7000塊對300塊围辙，這就是在這個例子中放案，單列索引與復合索引的代價之比姚建。這個例子提示我們， 在許多情況下吱殉，單列索引不如復合索引有效率掸冤。 可以說友雳，在索引的設(shè)置問題上，其實有許多工作可以做押赊。正確地設(shè)置索引，需要對應(yīng)用進行總體的分析考杉。? 第三講、索引再好咽袜，不用也是白搭 拋開前面所說的，假設(shè)你設(shè)置了一個非常好的索引询刹，任何傻瓜都知道應(yīng)該使用它萎坷，但是Oracle 卻偏偏不用，那么哆档，需要做的第一件事情，是審閱你的 sql 語句瓜浸。 Oracle 要使用一個索引，有一些最基本的條件：

1杠巡， where 子句中的這個字段雇寇，必須是復合索引的第一個字段蚌铜；

2嫩海， where 子句中的這個字段，不應(yīng)該參與任何形式的計算 具體來講叁怪，假設(shè)一個索引是按 f1, f2, f3的次序建立的造壮，現(xiàn)在有一個 sql 語句, where 子句是 f2 = : var2, 則因為 f2 不是索引的第1個字段骂束，無法使用該索引。 第2個問題展箱，則在我們之中非常嚴重。以下是從 實際系統(tǒng)上面抓到的幾個例子： Select jobid from mytabs where isReq='0' and to_date (updatedate) >= to_Date ( '2001-7-18', 'YYYY-MM-DD')攀隔； ……… 以上的例子能很輕易地進行改進栖榨。請注重這樣的語句天天都在我們的系統(tǒng)中運行，消耗我們有限的cpu 和 內(nèi)存資源婴栽。 除了1驻啤，2這兩個我們必須牢記于心的原則外斑响，還應(yīng)盡量熟悉各種操作符對 Oracle 是否使用索引的影響。這里我只講哪些操作或者操作符會顯式（explicitly）地阻止 Oracle 使用索引戒职。以下是一些基本規(guī)則： 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 也會不用索引 對于這些操作，別無辦法畅铭，只有盡量避免。比如硕噩，假如發(fā)現(xiàn)你的 sql 中的 in 操作沒有使用索引，也許可以將 in 操作改成 比較操作 + union all辉懒。筆者在實踐中發(fā)現(xiàn)很多時候這很有效谍失。 但是，Oracle 是否真正使用索引快鱼，使用索引是否真正有效，還是必須進行實地的測驗线罕。合理的做法是窃判，對所寫的復雜的 sql, 在將它寫入應(yīng)用程序之前钞楼，先在產(chǎn)品數(shù)據(jù)庫上做一次explain . explain 會獲得Oracle 對該 sql 的解析（plan）,可以明確地看到 Oracle 是如何優(yōu)化該 sql 的袄琳。 假如經(jīng)常做 explain, 就會發(fā)現(xiàn)，喜愛寫復雜的 sql 并不是個好習慣雳殊，因為過分復雜的sql 其解析計劃往往不盡如人意窗轩。事實上，將復雜的 sql 拆開痢艺，有時候會極大地提高效率，因為能獲得很好的優(yōu)化色建。當然這已經(jīng)是題外話了舌缤。