數據庫優化在海量數據下的研究與應用

岑巍

摘 要： 基于云計算技術和海量數據的支撐，對數據庫進行多方面的調整和優化能夠最大程度地提高數據庫系統的運行效率，降低系統運行的資源消耗，使得海量數據的響應效率大幅提高。以Oracle數據庫為例，分析了影響數據庫性能的各方面因素和數據庫優化應遵循的方法，就數據庫優化技術方法進行了探討。通過數據庫實施該優化方案前后的性能對比，得到數據庫在運用此方法進行優化后性能明顯提升的結果，這表明該方法有一定的使用價值。

關鍵詞： Oracle； 數據庫優化； CPU的優化； SQL語句優化

中圖分類號：TP311.5 文獻標志碼：A 文章編號：1006-8228（2015）02-33-03

Research and application of database optimization in the massive data

Cen Wei

（Shanghai Pudong Development Bank， Shanghai 200042， China）

Abstract： Based on cloud computing and mass data， the database is adjusted and optimized. The system operation efficiency has been improved to the maximal extent. The consumption of resource is lowered to increase the response efficiency of large amount data. Taking the Oracle database as an example， various factors which affect the database performance and methods which should be observed for optimization of the database are analyzed. The database optimization methods are discussed. Comparing performances before and after the optimization schemes， it is concluded that the performance is improved significantly， showing that the method has certain use value.

Key words： Oracle； database optimization； CPU optimization； SQL optimization method

0 引言

隨著云計算不斷廣泛和深入的應用，海量數據的處理和快速響應顯得特別重要，其中數據庫的調整和優化占據著核心的地位。通過對數據庫系統性能瓶頸問題的挖掘，依靠數據庫性能調整和優化方法，最大程度的提高數據庫系統的運行效率，降低系統運行的資源消耗，使得海量數據的響應效率大幅提高。數據庫的調整和優化包括多個層次，它包含數據庫運行環境的優化、數據庫參數優化和應用優化三個層面。Oracle數據庫作為一種高可靠性、高兼容性和安全性的大型關系型數據庫，能夠很好地承載海量數據，有效管理和利用不斷產生并急劇膨脹的數據，從而更好的為業務應用作支撐。

本文以Oracle數據庫為例，從CPU的優化、磁盤I/O的優化、內存的優化、數據庫邏輯結構和物理結構優化、網絡環境優化、SQL語句優化的方法與技巧，索引創建的相關規則等，逐一對數據庫的調整優化技術進行探討。

1 數據庫物理結構優化

從操作系統級別看，Oracle數據庫文件是存儲在磁盤上并由文件組成的，因此，其物理結構的優化決定著數據庫的效率。在非動態變化周期內，應用系統的數據庫不斷的增大/縮小，對其影響很小或者說效率合理，但是這樣的情況在動態變化的周期內性能會急劇下降，這是由于Oracle動態擴展造成的。在動態擴展過程中，Oracle在創建行、行變化獲取缺省值的時候，必須根據存儲的要求擴展和分配新的存儲空間，而且表格的擴展會進一步導致數據文件、表空間的增長，這些擴展會使得系統反應變慢。為了避免這種情況的發生，采用優化的方法，就是在建立的時候預先分配好空間來滿足足夠的增長幅度，在一個對象建立的時候要根據應用實際充分計算它們的大小，使得數據庫在物理存儲上和動態增長次數上達到一個比較好的平衡點，達到這些對象既不經常發生增長，也不過多占用數據庫資源的目的。

2 數據庫邏輯結構優化

Oracle數據庫是由許多的數據庫對象組成的，其邏輯結構的優化是指通過增加、減少或者調整數據庫的邏輯結構來提高應用業務的效率，下面通過對索引的討論來分析Oracle邏輯結構的優化。

索引是建立在表列上的數據庫對象，它可以用于提高數據查詢效率，但是其物理結構與邏輯結構都不依賴于表。在一個表上創建的索引數量和類型雖然不會影響表的使用方式，但會直接影響表中數據的查詢效率。對表創建了索引，就不用對表進行全表掃描了，而是先對索引進行掃描，利用索引表可以迅速查詢到符合條件的數據。索引的使用通常能提高SELETE、UPDATE及DELETE語句的性能，但并非多多益善，應遵循以下原則：

⑴ 為查詢語句只選擇少量的字段進行查詢的表建立索引；

⑵ 在頻繁進行排序或分組的列上建立索引；

⑶ 在不同值較多的列上建立索引；

⑷ 在多個帶排序的列上建立符合索引。

3 數據庫參數優化

Oracle最關鍵的參數有四個：SGA_MAX_SIZE，PGA_

AGGREGATE_TARGET，DB_CASHE_SIZE，SHARED_POOL_

SIZE。這四個參數決定著數據庫的系統性能。

SGA_MAX_SIZE的大小決定著其他參數的粒度大小。通常在開始把它設為物理內存的25%，如果系統的其他性能受限，系統會強制將它設為15%-20%。PGA_AGGREGATE_TARGET是Oracle在所有的會話中分配的PGA的內存總量。使用它可以設定用戶會話的總內存使用量來減少分頁。DB_CASHE_SIZE是Oracle最關鍵的參數，它設定了用來存儲和處理內存中數據的SGA區域的大小，還可用來調整數據庫緩存命中率。SHARED_POOL_SIZE類似于DB_CASHE_SIZE，能夠為那些數據字典緩存和共享SQL語句制定在SGA里所分配的內存。

4 環境優化

4.1 優化內存

Oracle數據管理系統具有內存敏感性的特點[1]，它的整體性能效率依賴實例的內存結構是否獲得了足夠的物理內存[2]。

系統內存的配置應盡可能實現如下目標：減少分頁、減少內存交換、盡可能讓系統全局區（SGA）駐留內存。當系統執行分頁操作時，會將當前沒有使用的信息從內存移到硬盤上。從而為當前需要內存的程序分配空間。如果頻繁的發生分頁，系統的性能就會嚴重降低，程序的執行時間隨之變長。當系統執行內存交換的時候，會將活動進程臨時從內存轉移到硬盤上，使得另一個活動進程進駐內存。內存交換基于系統循環時間，如果交換過于頻繁，就會產生大量的輸入/輸出，導致應用程序性能大打折扣。

SGA區是Oracle分配的共享內存結構，它和Oracle進程結構組成一個Oracle數據庫實例，包含數據庫實例共享的數據和控制信息。負責管理數據庫數據、應答用戶請求，為用戶提供服務。SGA對數據庫性能有著很大的影響，它決定數據庫高速緩存的命中率，減少重做日志緩沖區的申請失敗率和等待時間，減少共享存儲區中SQL語法的分析調用，保證共享池有足夠的空間尤其重要，如果它被頻繁的分頁和交換，系統的性能將嚴重惡化。

可以通過以下措施合理配置內存：

⑴ 避免不必要的系統進程或者應用程序進程；

⑵ 將一些工作移到另一個操作系統中；

⑶ 配置操作系統核心使用更少的內存；

⑷ 保持SGA在單個共享內存段中；

⑸ 監控分頁操作。

4.2 優化數據庫磁盤輸入、輸出

磁盤的輸入/輸出速度對整個系統性能有重要影響，要解決好磁盤的輸入/輸出問題，主要解決磁盤競爭、輸入/輸出次數過多和數據塊空間的分配管理。

⑴ 文件獨立原則。在物理設計時，為了避免數據庫文件之間的競爭，文件應該被放到不同的輸入/輸出通道，跨越驅動器的文件分離，避免磁盤爭用成為瓶頸。把握以下幾點技巧：把關鍵的數據文件分布在各個可用的磁盤上，這些文件包括SYSTEM表空間、TEMPORARY表空間、回滾段或者UNDO段、聯機重做日志文件、操作系統文件、Oracle-Home下的關鍵Oracle文件、經常訪問的表數據文件以及經常訪問的索引數據文件；把數據和索引文件分開放置；對于經常鏈接的表，把它們的數據和索引表空間分開，這樣每個表上的信息就不會放在相同的磁盤上；把控制文件的多個備份存儲到不同的磁盤和控制器上。

⑵ 使用磁盤陣列[3]。隨著硬件和文件系統的改進，磁盤陣價格的降低，RAID在提升Oracle數據庫的輸入/輸出操作方面變得很重要。在RAID環境中，數據存放在多個磁盤上，采用奇偶拆分、奇偶校驗實驗數據的冗余、錯誤檢查、數據分配和數據緩存，以減少數據庫的輸入輸出負荷。

⑶ 使用分區。分區可能是提高與大型表有關的性能的最佳方法，通過訪問一個表或者索引的較小片段，而不是訪問整個表或者索引。數據在物理上是分開的，但在邏輯上是在一起的。使用分區可以很好的提高維護操作、備份、恢復、事務處理和查詢的性能。

⑷ 應用裸設備。裸設備就是未經過格式化的磁盤分區，Oracle可以對其進行讀和寫操作，而沒有輸入/輸出的緩沖開銷。裸設備可以用于寫操作頻繁、順序訪問的數據和重做日志文件，但不能用在備份程序中。

4.3 優化CPU的使用

CPU是服務器的一項重要資源，服務器的良好工作狀態是在工作高峰時使用率在90%以上，如果空閑時間就在90%以上，說明服務器缺乏CPU資源，應該通過增加CPU的數量或者主頻，減少服務器上的負荷和提高數據庫進程運行的優先級等方法來優化數據庫服務器性能。

4.4 優化網絡輸入/輸出

網絡帶寬會影響系統性能、減少網絡負載，可以改善系統的性能[4]。減少網絡輸入/輸出的原則是：將應用邏輯集中在數據庫服務器中，可以使用Oracle數據庫的完整約束性、數據觸發器、存儲過程、存儲函數和包等，使在網絡上傳輸的只是調用過程的名字和輸出結果，大大減少網絡輸入/輸出的負荷。

5 SQL語句優化

SQL是一種數據子語言，它將數據集合處理為組，而且可以定位在不同表中存儲的數據。由于物理空間和索引時間等因素的限制，只能對表中查詢頻率最高的兩個字段分別建立索引，而其余字段沒有索引[5]。在實際應用當中，如果對其余沒有索引的字段進行查詢，就不可避免地需要對要查詢的表進行全表掃描。對使用索引的SQL語句進行優化，其優化效果不甚明顯。因此SQL語句優化的關鍵部分在于，對沒有索引的字段進行查詢時的SQL語句優化，如盡可能地減少表掃描的次數、使用應用端而不是服務端進行刪除副本和數據排序操作、盡量使用存儲過程進行查詢等，以減少查詢語句的響應時間。

現使用基于成本的ORACLE優化器和第三方工具對查詢過程中需要運行的SQL語句進行優化。以常用的SQL語句為例說明這一過程。

SQL語句：

SELECT * FROM TABLE1 WHERE CON1 IN

（SEARCHNUMBER，‘86||SEARCHNUMBER，……，0019||SEARCHNUMBER）OR CON2 IN （SEARCHNUMBER，'86||SEARCHNUMBER，……，0019||SEARCHNUMBER）

UNION SELECT * FROM TABLE2 WHERE CON1 IN

（SEARCHNUMBER，‘86||SEARCHNUMBER，……，0019||SEARCHNUMBER）OR CON2 IN （SEARCHNUMBER，'86||SEARCHNUMBER，……，0019||SEARCHNUMBER）

……

UNION SELECT * FROM TABLEN WHERE CON1 IN

（SEARCHNUMBER，‘86||SEARCHNUMBER，……，0019||SEARCHNUMBER）OR CON2 IN （SEARCHNUMBER，'86||SEARCHNUMBER，……，0019||SEARCHNUMBER）

TABLE1，……，TABLEN是需要查詢的表名。SEARCHNUMBER是需要查詢的內容，不定長的字符串，需要冠以不同的字頭，字頭的數量相對固定，亦為不定長的字符串。C0N1，C0N2，……，C0N9是表的列名，C0N1，C0N2已索引。

SQL語句的優化：在對其優化之前，應首先查看其基于成本的執行計劃，找出可優化的執行步驟，以減少SQL查詢語句的執行成本。根據SQL語句編寫查詢30個表的SQL查詢語句，由執行計劃可知，使用索引查詢一個表，執行成本為12，30個表共360，而總執行成本為167623，其成本遠大于查詢成本。為了盡量減少查詢響應時間，增加數據庫的吞吐量，減輕數據庫的負擔，用戶決定不在數據庫執行重復行分揀操作，是否分揀將由客戶端決定。優化的方案是將SQL語句中的UNION改寫為UNION ALL。UNION操作符返回查詢檢索出的所有非重復行。UNION ALL操作符返回查詢所檢索出的所有行。

改寫后的執行計劃如下：

SELECT STATEMENT ALL_ROWS Cost： 360 Bytes： 656，

368，436 Cardinality： 9，652，477

211 UNION-ALL

7 CONCATENATION

3 INLIST ITERATOR

2 TABLE ACCESS BY INDEX ROWID DB_20061201

Cost： 6 Bytes： 68 Cardinality： 1

1 INDEX RANGE SCAN NON-UNIQUE DB_INDEXB_

20061201 Cost： 2 Cardinality： I

6 INLIST ITERATOR

5 TABLE ACCESS BY INDEX ROWID DB_20061201

Cost： 6 Bytes： 68 Cardinality： 1

4 INDEX RANGE SCAN NON-UNIQUE DB_INDEX_

20061201 Cost： 2 Cardinality： 1

……

210 CONCATENATION

206 INLIST ITERATOR

205 TABLE ACCESS BY INDEX ROWID DB_20061230

Cost： 6 Bytes： 68 Cardinality： 1

204 INDEX RANGE SCAN NON-UNIQUE DB_INDEXB_

20061230 Cost： 2 Cardinality： I

209 INLIST ITERATOR

208 TABLE ACCESS BY INDEX ROWID DB_20061230

Cost： 6 Bytes： 68 Cardinality： 1

207 INDEX RANGE SCAN NON-UNIQUE DB_INDEX_

20061230 Cost： 2 Cardinality： 1

6 結束語

數據庫性能調整問題是貫穿數據庫系統和應用系統整個生命周期的活動，優化數據庫對提高系統效率，降低資源消耗，大幅提高海量數據的響應速度有重要意義。本文從CPU的優化、磁盤I/O的優化、內存的優化、數據庫邏輯結構和物理結構的優化、網絡環境的優化、SQL語句優化的方法，索引創建的相關規則等逐一對數據庫的調整優化技術進行探討。通過對比實施該優化方案前后的性能可知，數據庫性能明顯提升，這表明該方法有利于數據庫的穩定性和可靠性的優化，保證了系統的穩定運行，從而解決系統運作過程中的各種問題，更有利于提升海量數據的響應速度，保證其對于現實工作的良好適應。

參考文獻：

[1] 黃河.Oracle9i數據庫系統培訓教程[M].清華大學出版社，2002.

[2] 馮鳳娟.Oracle數據庫體系結構和管理[M].清華大學出版社，2003.

[3] Richard J.Niemiec.Oracle 9i performance tuning tips&techniques[M].

清華大學出版社，2006.

[4] 張韜.淺析Oracle數據庫的性能優化[J].中國科技信息，2005.5.

[5] SQL Server 數據庫性能的優化 http：//publish.it168.com/2005/

1019/20051019039201_hezuo.shtml？cChanNel=11&cpositioncode=296&hezuo=3