Oracle數(shù)據(jù)庫中數(shù)據(jù)訪問優(yōu)化方法

李永亮

（沈陽航空航天大學(xué)，遼寧 沈陽110136）

Oracle數(shù)據(jù)庫被越來越多的應(yīng)用在各個領(lǐng)域，信息系統(tǒng)的優(yōu)劣與數(shù)據(jù)庫系統(tǒng)的性能有著直接的關(guān)聯(lián)。隨著數(shù)據(jù)庫規(guī)模的不斷擴大，如何保持?jǐn)?shù)據(jù)庫應(yīng)用系統(tǒng)高效地運行，受到人們越來越多的關(guān)注。對于應(yīng)用系統(tǒng)來說，數(shù)據(jù)訪問操作在整個系統(tǒng)中占據(jù)著相當(dāng)大的比重，也就是說，數(shù)據(jù)訪問速度的快慢直接影響著系統(tǒng)的性能。

1 影響查詢優(yōu)化器決策的因素

Oracle數(shù)據(jù)庫引擎訪問數(shù)據(jù)基本上有兩種方式,一種是掃描整個表,一種是基于冗余訪問結(jié)構(gòu)(如索引)進(jìn)行查找或基于表本身的機構(gòu)(比如哈希聚簇)進(jìn)行查找。查詢優(yōu)化器對于訪問數(shù)據(jù)選用全表掃描還是索引掃描，基于對這兩種訪問方式相應(yīng)開銷的估算，影響優(yōu)化器決策的因素主要有以下幾個：

1）進(jìn)行全表掃描需要讀取的數(shù)據(jù)塊數(shù)目。

2）進(jìn)行索引查詢需要讀取的數(shù)據(jù)塊數(shù)目，這主要基于對WHERE子句謂詞返回的記錄數(shù)目的估計。

3）進(jìn)行全表掃描時多塊讀的相關(guān)開銷以及為滿足索引查詢進(jìn)行的單塊讀的開銷。

4）內(nèi)存緩存中的索引塊和數(shù)據(jù)塊數(shù)目的假設(shè)。

當(dāng)查詢優(yōu)化器正確的選擇執(zhí)行計劃后，并不意味著特定執(zhí)行計劃一定運行良好。所以有時可以通過調(diào)整SQL語句或訪問結(jié)構(gòu)，來達(dá)到優(yōu)化的目的。判斷數(shù)據(jù)訪問最有效的標(biāo)準(zhǔn)是處理數(shù)據(jù)時消耗的資源最少，當(dāng)然最有效不一定是最快。

2 影響數(shù)據(jù)訪問的因素

一般對全表掃描和索引掃描來說，在WHERE子句的條件選擇性不是很高時，全表掃描是最合適的檢索路徑，而在條件的選擇性很高時，索引或聚簇方法將更合適。

其中影響索引的因素主要有緩存區(qū)高速緩存區(qū)的命中率、記錄大小和數(shù)據(jù)的分布情況。索引檢索有助于獲得更高的高速緩存命中率；就IO而言，無論記錄多大，索引訪問的開銷幾乎是相同的，而記錄越大，全表掃描必須讀取的數(shù)據(jù)塊就越多，代價越高。對于數(shù)據(jù)的分布，如果表中記錄大體按照索引列的順序存儲，那么索引訪問需要更少的數(shù)據(jù)庫并大大減少邏輯IO的開銷。而對于全表掃描，如果需要訪問表的所有或大部分記錄的話，那么全表掃描是最合適的方法。

3 全表掃描避免的優(yōu)化

對于這兩種訪問方式的優(yōu)化就是對于該使用索引的，卻使用了全表掃描，而應(yīng)該使用全表掃描的卻使用了索引。因此對應(yīng)的優(yōu)化方法主要有兩類，一類是讓索引起到該起的作用，避免意外的表掃描，一類優(yōu)化必要的全表掃描。

即使存在適當(dāng)?shù)乃饕蛏⒘袡z索路徑，由于SQL語句的寫法，優(yōu)化器也可能無法利用這些訪問路徑，因此避免意外的表掃描主要有以下幾個原則及解決方法：

1）避免使用不等條件。如果使用不等條件，Oracle一般不會使用索引，因此如果認(rèn)為某個查詢使用索引更加高效，那么請使用IN、OR或>來重寫查詢。

2）空值查詢。當(dāng)索引中列的數(shù)據(jù)都為空值時，B*樹索引中的條目將不會創(chuàng)建，因此不能使用某列的B*樹索引查找空值，而應(yīng)該將列定義成非空并設(shè)置默認(rèn)值，而后用默認(rèn)值檢索。

3）在列上使用函數(shù)或表達(dá)式而無意中禁用了索引，因此應(yīng)避免在WHERE子句中的索引列應(yīng)用函數(shù)或操作，而是在與索引進(jìn)行比較的值上應(yīng)用函數(shù)或操作。

4）對于無法避免在索引列使用函數(shù)或表達(dá)式是，可以考慮使用基于相同函數(shù)或表達(dá)式的函數(shù)索引。

5）對于多列查詢可以對多列使用組合索引或使用多個索引合并。

6）利用Oracle的統(tǒng)計信息或創(chuàng)建虛擬列等技術(shù)，幫助優(yōu)化器做出精確的估算，從而更準(zhǔn)確的選擇出數(shù)據(jù)的訪問方式。

4 全表掃描的優(yōu)化

以上討論的避免全表掃描的方法并不總是能獲得最優(yōu)的訪問，尤其是訪問表的大部分?jǐn)?shù)據(jù)時，更是如此。有許多的全表掃描優(yōu)化技術(shù)，主要的幾個技術(shù)包括：

1）通過讓表變小來減少掃描所需要的邏輯讀取的數(shù)量。通過提高數(shù)據(jù)庫IO的效率降低掃描所需要的物理塊讀取的數(shù)量。

2）通過緩存或抽樣降低掃描的開銷。

3）通過使用并行查詢選項為掃描分配更多的資源。

4）執(zhí)行快速的全索引掃描來代替全表掃描。

5）對表進(jìn)行分區(qū)，只讀取所選擇的分區(qū)。

特別需要注意的是，完成全表掃描的工作數(shù)量主要取決于需要掃描的數(shù)據(jù)塊數(shù)量，因此也有很多有效的方法來減少掃描的數(shù)據(jù)塊數(shù)量，主要有以下四種方式：

1）通過重建表來降低高水位線。當(dāng)需要進(jìn)行全表掃描時，Oracle會去讀取從第一個被分配的塊到曾經(jīng)包含數(shù)據(jù)的最高那個塊之間的每一個塊。因此如果一張表上經(jīng)常發(fā)生大量的刪除操作，高水位線將比實際需要的位置更高，每塊里的記錄條數(shù)減少，檢索時IO的開銷就會增加。

2）通過修改PCTFREE和PCTUSED參數(shù)來向每個塊里放入更多的記錄。該方法由于自動段存儲管理的廣泛使用，而現(xiàn)在幾乎沒有意義，但是對使用早期的數(shù)據(jù)庫版本來說有一定的作用。

3）減少記錄的長度，可能是通過將大的且并不常訪問的列遷移到獨立的表中。如果表經(jīng)常進(jìn)行全表掃描而且包含大量的不常訪問的列，就可以將這些列遷移到其他的表中來減少需要掃描的塊，尤其是很少訪問的長列。

4）壓縮表中的數(shù)據(jù)，壓縮表也能生成更小的表，從而使掃描速度更快。

5 總結(jié)

大多數(shù)情況下，優(yōu)化器會自動選擇好數(shù)據(jù)的訪問方式，但是當(dāng)優(yōu)化器不能選擇最優(yōu)的訪問路徑時或需要一個不同的訪問路徑，需要系統(tǒng)管理維護(hù)人員能夠評估優(yōu)化器的選擇，并給出一個更優(yōu)的訪問路徑，這就需要利用以上的優(yōu)化方法如創(chuàng)建索引、聚簇或表分區(qū)等來影響優(yōu)化器做出更正確的決策。

同時在索引訪問和全表掃描之間沒有通用的一個平衡點，如果需要訪問表的所有或大部分記錄，那么全表掃描時最快的方式，如果從大表里檢索一條記錄，那么基于該列的索引將提供更有效的訪問路徑，在這兩個極端之間，要預(yù)見哪種方式更高效是很難的。

［1］Sam R.Alapati,Darl Kuhn,Bill Padfield.Oracle Database 11g性能優(yōu)化攻略[M].朱浩波,譯.北京：人民郵電出版社,2013：50.

［2］Christian Antognini.Oracle性能診斷藝術(shù)[M].童家旺,胡怡文,馮大輝,譯.北京：人民郵電出版社,2009：269-274.

［3］Guy Harrison.Oracle性能優(yōu)化求生指南[M].鄭勇斌,胡怡文,童家旺,譯.北京：人民郵電出版社,2012：197-209.

［4］李展?jié)?曹英忠.基于Oracle數(shù)據(jù)庫的SQL語句優(yōu)化[J].微型機與應(yīng)用,2011,(21)：11.

［5］趙新民,崔海.Oracle數(shù)據(jù)庫SQL語句優(yōu)化要點分析[J].信息安全與技術(shù),2014,(6)：65-66.