數(shù)據(jù)庫(kù)優(yōu)化在海量數(shù)據(jù)下的研究與應(yīng)用

岑巍

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

關(guān)鍵詞： Oracle； 數(shù)據(jù)庫(kù)優(yōu)化； CPU的優(yōu)化； SQL語(yǔ)句優(yōu)化

中圖分類號(hào)：TP311.5 文獻(xiàn)標(biāo)志碼：A 文章編號(hào)：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 引言

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

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

1 數(shù)據(jù)庫(kù)物理結(jié)構(gòu)優(yōu)化

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

2 數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)優(yōu)化

Oracle數(shù)據(jù)庫(kù)是由許多的數(shù)據(jù)庫(kù)對(duì)象組成的，其邏輯結(jié)構(gòu)的優(yōu)化是指通過(guò)增加、減少或者調(diào)整數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)來(lái)提高應(yīng)用業(yè)務(wù)的效率，下面通過(guò)對(duì)索引的討論來(lái)分析Oracle邏輯結(jié)構(gòu)的優(yōu)化。

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

⑴ 為查詢語(yǔ)句只選擇少量的字段進(jìn)行查詢的表建立索引；

⑵ 在頻繁進(jìn)行排序或分組的列上建立索引；

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

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

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

Oracle最關(guān)鍵的參數(shù)有四個(gè)：SGA_MAX_SIZE，PGA_

AGGREGATE_TARGET，DB_CASHE_SIZE，SHARED_POOL_

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

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

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

4.1 優(yōu)化內(nèi)存

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

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

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

可以通過(guò)以下措施合理配置內(nèi)存：

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

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

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

⑷ 保持SGA在單個(gè)共享內(nèi)存段中；

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

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

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

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

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

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

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

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

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

4.4 優(yōu)化網(wǎng)絡(luò)輸入/輸出

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

5 SQL語(yǔ)句優(yōu)化

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

現(xiàn)使用基于成本的ORACLE優(yōu)化器和第三方工具對(duì)查詢過(guò)程中需要運(yùn)行的SQL語(yǔ)句進(jìn)行優(yōu)化。以常用的SQL語(yǔ)句為例說(shuō)明這一過(guò)程。

SQL語(yǔ)句：

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是需要查詢的內(nèi)容，不定長(zhǎng)的字符串，需要冠以不同的字頭，字頭的數(shù)量相對(duì)固定，亦為不定長(zhǎng)的字符串。C0N1，C0N2，……，C0N9是表的列名，C0N1，C0N2已索引。

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

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

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 結(jié)束語(yǔ)

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

參考文獻(xiàn)：

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

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

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

清華大學(xué)出版社，2006.

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

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

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