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

李永亮

（沈陽航空航天大學，遼寧 沈陽110136）

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5 總結

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

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

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

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

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

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

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