Oracle Index 的三個(gè)問題

2020-07-26 14:39:03

字體：大中小

供稿：網(wǎng)友

正在看的ORACLE教程是:Oracle Index 的三個(gè)問題。

　索引( Index )是常見的數(shù)據(jù)庫(kù)對(duì)象，它的設(shè)置好壞、使用是否得當(dāng)，極大地影響數(shù)據(jù)庫(kù)應(yīng)用程序和Database 的性能。雖然有許多資料講索引的用法， DBA 和 Developer 們也經(jīng)常與它打交道，但筆者發(fā)現(xiàn)，還是有不少的人對(duì)它存在誤解，因此針對(duì)使用中的常見問題，講三個(gè)問題。此文所有示例所用的數(shù)據(jù)庫(kù)是 Oracle 8.1.7 OPS on HP N series ,示例全部是真實(shí)數(shù)據(jù)，讀者不需要注意具體的數(shù)據(jù)大小，而應(yīng)注意在使用不同的方法后，數(shù)據(jù)的比較。本文所講基本都是陳詞濫調(diào)，但是筆者試圖通過實(shí)際的例子，來真正讓您明白事情的關(guān)鍵。

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

　　如果發(fā)現(xiàn)Oracle 在有索引的情況下，沒有使用索引，這并不是Oracle 的優(yōu)化器出錯(cuò)。在有些情況下，Oracle 確實(shí)會(huì)選擇全表掃描（Full Table Scan）,而非索引掃描（Index Scan）。這些情況通常有：

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

　　2. 根據(jù)該表?yè)碛械挠涗洈?shù)和數(shù)據(jù)塊數(shù)，實(shí)際上全表掃描要比索引掃描更快。

　　對(duì)第1種情況，最常見的例子，是以下這句sql 語(yǔ)句：

　　在未作statistics 之前，它使用全表掃描，需要讀取6000多個(gè)數(shù)據(jù)塊（一個(gè)數(shù)據(jù)塊是8k）, 做了statistics 之后，使用的是 INDEX (FAST FULL SCAN) ，只需要讀取450個(gè)數(shù)據(jù)塊。但是，statistics 做得不好，也會(huì)導(dǎo)致Oracle 不使用索引。

　　第2種情況就要復(fù)雜得多。一般概念上都認(rèn)為索引比表快，比較難以理解什么情況下全表掃描要比索引掃描快。為了講清楚這個(gè)問題，這里先介紹一下Oracle 在評(píng)估使用索引的代價(jià)（cost）時(shí)兩個(gè)重要的數(shù)據(jù)：CF(Clustering factor) 和 FF(Filtering factor).

　　CF: 所謂 CF, 通俗地講，就是每讀入一個(gè)索引塊，要對(duì)應(yīng)讀入多少個(gè)數(shù)據(jù)塊。

　　FF: 所謂 FF, 就是該sql 語(yǔ)句所選擇的結(jié)果集，占總的數(shù)據(jù)量的百分比。

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

　　其核心就是， CF 可能會(huì)比實(shí)際的數(shù)據(jù)塊數(shù)量大。CF 受到索引中數(shù)據(jù)的排列方式影響，通常在索引剛建立時(shí)，索引中的記錄與表中的記錄有良好的對(duì)應(yīng)關(guān)系，CF 都很小；在表經(jīng)過大量的插入、修改后，這種對(duì)應(yīng)關(guān)系越來越亂，CF 也越來越大。此時(shí)需要 DBA 重新建立或者組織該索引。

　　如果某個(gè)sql 語(yǔ)句以前一直使用某索引，較長(zhǎng)時(shí)間后不再使用，一種可能就是 CF 已經(jīng)變得太大，需要重新整理該索引了。

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

　　這兩句看似差不多的 sql 語(yǔ)句，對(duì)Oracle 而言，卻有巨大的差別。因?yàn)榍罢叩?nbsp;FF 是100%，而后者的 FF 可能只有 1%。如果它的CF 大于實(shí)際的數(shù)據(jù)塊數(shù)，則Oracle 可能會(huì)選擇完全不同的優(yōu)化方式。而實(shí)際上，在我們的數(shù)據(jù)庫(kù)上的測(cè)試驗(yàn)證了我們的預(yù)測(cè). 以下是在HP 上執(zhí)行時(shí)它們的 explain plan:

　　第一句：

　　已選擇325917行。

　　第二句：

　　顯而易見，第1句沒有使用索引，第2句使用了主鍵索引pk_mytables. FF的巨大影響由此可見一斑。由此想到，我們?cè)趯憇ql 語(yǔ)句時(shí)，如果預(yù)先估計(jì)一下 FF, 你就幾乎可以預(yù)見到 Oracle 會(huì)否使用索引。

[NextPage]

第二講、索引也有好壞

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

　　所謂索引的好壞是指：

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

　　2，很多時(shí)候，單列索引不如復(fù)合索引有效率。

　　3，用于多表連結(jié)的字段，加上索引會(huì)很有作用。

　　那么，在什么情況下單列索引不如復(fù)合索引有效率呢？有一種情況是顯而易見的，那就是，當(dāng)sql 語(yǔ)句所查詢的列，全部都出現(xiàn)在復(fù)合索引中時(shí)，此時(shí)由于 Oracle 只需要查詢索引塊即可獲得所有數(shù)據(jù)，當(dāng)然比使用多個(gè)單列索引要快得多。（此時(shí)，這種優(yōu)化方式被稱為 Index only access path）

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

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

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

　　可以看到，它讀取了7000個(gè)數(shù)據(jù)塊來獲得所查詢的 6000多行。

　　現(xiàn)在，去掉這兩個(gè)單列索引，增加一個(gè)復(fù)合索引I_mytabs_test ( coid, issuedate), 重新執(zhí)行，結(jié)果如下：

　　可以看到，這次只讀取了300個(gè)數(shù)據(jù)塊。

　　7000塊對(duì)300塊，這就是在這個(gè)例子中，單列索引與復(fù)合索引的代價(jià)之比。這個(gè)例子提示我們，在許多情況下，單列索引不如復(fù)合索引有效率。

　　可以說，在索引的設(shè)置問題上，其實(shí)有許多工作可以做。正確地設(shè)置索引，需要對(duì)應(yīng)用進(jìn)行總體的分析。
1 3

[NextPage]

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

　　拋開前面所說的，假

[1] [2] 下一頁(yè)

正在看的ORACLE教程是:Oracle Index 的三個(gè)問題。設(shè)你設(shè)置了一個(gè)非常好的索引，任何傻瓜都知道應(yīng)該使用它，但是Oracle 卻偏偏不用，那么，需要做的第一件事情，是審視你的 sql 語(yǔ)句。

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

　　1， where 子句中的這個(gè)字段，必須是復(fù)合索引的第一個(gè)字段；

　　2， where 子句中的這個(gè)字段，不應(yīng)該參與任何形式的計(jì)算

　　具體來講，假設(shè)一個(gè)索引是按 f1, f2, f3的次序建立的，現(xiàn)在有一個(gè) sql 語(yǔ)句, where 子句是 f2 = : var2, 則因?yàn)?nbsp;f2 不是索引的第1個(gè)字段，無法使用該索引。

　　第2個(gè)問題，則在我們之中非常嚴(yán)重。以下是從實(shí)際系統(tǒng)上面抓到的幾個(gè)例子：

　　以上的例子能很容易地進(jìn)行改進(jìn)。請(qǐng)注意這樣的語(yǔ)句每天都在我們的系統(tǒng)中運(yùn)行，消耗我們有限的cpu 和內(nèi)存資源。

　　除了1，2這兩個(gè)我們必須牢記于心的原則外，還應(yīng)盡量熟悉各種操作符對(duì) Oracle 是否使用索引的影響。這里我只講哪些操作或者操作符會(huì)顯式（explicitly）地阻止 Oracle 使用索引。以下是一些基本規(guī)則：

　　1，如果 f1 和 f2 是同一個(gè)表的兩個(gè)字段，則 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 也會(huì)不用索引；

　　對(duì)于這些操作，別無辦法，只有盡量避免。比如，如果發(fā)現(xiàn)你的 sql 中的 in 操作沒有使用索引，也許可以將 in 操作改成比較操作 + union all。筆者在實(shí)踐中發(fā)現(xiàn)很多時(shí)候這很有效。

　　但是，Oracle 是否真正使用索引，使用索引是否真正有效，還是必須進(jìn)行實(shí)地的測(cè)驗(yàn)。合理的做法是，對(duì)所寫的復(fù)雜的 sql, 在將它寫入應(yīng)用程序之前，先在產(chǎn)品數(shù)據(jù)庫(kù)上做一次explain . explain 會(huì)獲得Oracle 對(duì)該 sql 的解析（plan）,可以明確地看到 Oracle 是如何優(yōu)化該 sql 的。

　　如果經(jīng)常做 explain, 就會(huì)發(fā)現(xiàn)，喜愛寫復(fù)雜的 sql 并不是個(gè)好習(xí)慣，因?yàn)檫^分復(fù)雜的sql 其解析計(jì)劃往往不盡如人意。事實(shí)上，將復(fù)雜的 sql 拆開，有時(shí)候會(huì)極大地提高效率，因?yàn)槟塬@得很好的優(yōu)化。當(dāng)然這已經(jīng)是題外話了。

上一頁(yè)

上一頁(yè) [1] [2]

上一篇：Oracle 數(shù)據(jù)庫(kù)操作技巧集

下一篇：自動(dòng)備份Oracle數(shù)據(jù)庫(kù)