Oracle數據庫中數據訪問優化方法

李永亮

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

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

1 影響查詢優化器決策的因素

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

1）進行全表掃描需要讀取的數據塊數目。

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

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

4）內存緩存中的索引塊和數據塊數目的假設。

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

2 影響數據訪問的因素

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

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

3 全表掃描避免的優化

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

即使存在適當的索引或散列檢索路徑，由于SQL語句的寫法，優化器也可能無法利用這些訪問路徑，因此避免意外的表掃描主要有以下幾個原則及解決方法：

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

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

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

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

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

6）利用Oracle的統計信息或創建虛擬列等技術，幫助優化器做出精確的估算，從而更準確的選擇出數據的訪問方式。

4 全表掃描的優化

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

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

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

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

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

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

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

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

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

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

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

5 總結

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

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

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

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

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

［4］李展濤,曹英忠.基于Oracle數據庫的SQL語句優化[J].微型機與應用,2011,(21)：11.

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