岑巍

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

關鍵詞： Oracle； 數(shù)據庫優(yōu)化； CPU的優(yōu)化； SQL語句優(yōu)化

中圖分類號：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 引言

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

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

1 數(shù)據庫物理結構優(yōu)化

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

2 數(shù)據庫邏輯結構優(yōu)化

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

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

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

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

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

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

3 數(shù)據庫參數(shù)優(yōu)化

Oracle最關鍵的參數(shù)有四個：SGA_MAX_SIZE，PGA_

AGGREGATE_TARGET，DB_CASHE_SIZE，SHARED_POOL_

SIZE。這四個參數(shù)決定著數(shù)據庫的系統(tǒng)性能。

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

4 環(huán)境優(yōu)化

4.1 優(yōu)化內存

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

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

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

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

⑴ 避免不必要的系統(tǒng)進程或者應用程序進程；

⑵ 將一些工作移到另一個操作系統(tǒng)中；

⑶ 配置操作系統(tǒng)核心使用更少的內存；

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

⑸ 監(jiān)控分頁操作。

4.2 優(yōu)化數(shù)據庫磁盤輸入、輸出

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

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

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

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

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

4.3 優(yōu)化CPU的使用

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

4.4 優(yōu)化網絡輸入/輸出

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

5 SQL語句優(yōu)化

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

現(xiàn)使用基于成本的ORACLE優(yōu)化器和第三方工具對查詢過程中需要運行的SQL語句進行優(yōu)化。以常用的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是需要查詢的內容，不定長的字符串，需要冠以不同的字頭，字頭的數(shù)量相對固定，亦為不定長的字符串。C0N1，C0N2，……，C0N9是表的列名，C0N1，C0N2已索引。

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

改寫后的執(zhí)行計劃如下：

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 結束語

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

參考文獻：

[1] 黃河.Oracle9i數(shù)據庫系統(tǒng)培訓教程[M].清華大學出版社，2002.

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

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

清華大學出版社，2006.

[4] 張韜.淺析Oracle數(shù)據庫的性能優(yōu)化[J].中國科技信息，2005.5.

[5] SQL Server 數(shù)據庫性能的優(yōu)化 http：//publish.it168.com/2005/

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