基于Oracle數(shù)據(jù)庫(kù)大數(shù)據(jù)的檢索優(yōu)化分析與設(shè)計(jì)

楊瑩 遼寧錦州渤海大學(xué)信息科學(xué)與技術(shù)學(xué)院

基于Oracle數(shù)據(jù)庫(kù)大數(shù)據(jù)的檢索優(yōu)化分析與設(shè)計(jì)

楊瑩 遼寧錦州渤海大學(xué)信息科學(xué)與技術(shù)學(xué)院

文中基于Oracle數(shù)據(jù)庫(kù)采用各種檢索優(yōu)化方法來(lái)提高數(shù)據(jù)庫(kù)的響應(yīng)速度：合理設(shè)計(jì)表字段存儲(chǔ)結(jié)構(gòu)、合理建立索引技術(shù)、表分區(qū)技術(shù)、優(yōu)化SQL檢索語(yǔ)句等技術(shù)手段實(shí)現(xiàn)數(shù)據(jù)庫(kù)的檢索優(yōu)化。本文研究關(guān)于如何優(yōu)化數(shù)據(jù)庫(kù)檢索性能優(yōu)化方案，通過(guò)分析影響檢索優(yōu)化因素提升系統(tǒng)性能，確定本文檢索優(yōu)化方案的目標(biāo)，期望通過(guò)本文檢索優(yōu)化方案達(dá)到最優(yōu)化，另外，基于分頁(yè)顯示檢索到的全部查詢(xún)結(jié)果在某種程度上是為了提高檢索速度。

Oracle數(shù)據(jù)庫(kù) 大數(shù)據(jù) 檢索優(yōu)化 表分區(qū)技術(shù) SQL優(yōu)化 索引技術(shù)

1 引言

目前，隨著Oracle數(shù)據(jù)庫(kù)不斷應(yīng)用深入大型應(yīng)用系統(tǒng)的基礎(chǔ)架構(gòu)中，作為軟件系統(tǒng)的運(yùn)行的骨架，用戶(hù)的訪問(wèn)量指數(shù)上升，數(shù)據(jù)庫(kù)需要同時(shí)高并發(fā)處理用戶(hù)請(qǐng)求瞬間達(dá)到極限，數(shù)據(jù)庫(kù)數(shù)據(jù)處理性能迅速下降，在大型數(shù)據(jù)庫(kù)中，用戶(hù)請(qǐng)求檢索的等待時(shí)間過(guò)長(zhǎng)、占用系統(tǒng)CPU資源過(guò)多是當(dāng)前系統(tǒng)面臨最大的問(wèn)題問(wèn)題。因此，對(duì)大型數(shù)據(jù)查詢(xún)需求有必要進(jìn)行檢索優(yōu)化操作。根據(jù)本文研究需要優(yōu)化系統(tǒng)的檢索性能，在滿(mǎn)足用戶(hù)使用的前提下，對(duì)其檢索性能的優(yōu)化操作，使數(shù)據(jù)庫(kù)的運(yùn)行處于最優(yōu)狀態(tài)。

對(duì)硬件的改進(jìn)也是提高檢索速度的有效方法，例如：更換或升級(jí)性能更優(yōu)秀的服務(wù)器，但是本文中僅對(duì)軟件編程方面實(shí)現(xiàn)的檢索優(yōu)化研究。以O(shè)racle研究對(duì)象，主要采取使用建立索引、表分區(qū)、優(yōu)化SQL語(yǔ)句等技術(shù)手段以系統(tǒng)的提高檢索速度，從而達(dá)到實(shí)現(xiàn)優(yōu)化數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)。

2 表建立索引技術(shù)

合理建立索引是為了達(dá)到快速檢索數(shù)據(jù)，索引建立在數(shù)據(jù)表的非空列上，當(dāng)某列建立索引之后，每次對(duì)該索引列發(fā)生修改操作之后需要根據(jù)索引值進(jìn)行重新排序，目的是為了以后方便對(duì)該列進(jìn)行檢索數(shù)據(jù)操作。因此，在建立索引結(jié)構(gòu)時(shí)需要把某個(gè)索引列設(shè)置在經(jīng)常進(jìn)行查詢(xún)操作的列，并不是經(jīng)常實(shí)現(xiàn)動(dòng)態(tài)修改列之上。關(guān)于大型數(shù)據(jù)表建立索引結(jié)構(gòu)需要注意:

(1)建立索引目的方便數(shù)據(jù)檢索，但是要時(shí)刻維護(hù)索引列的代價(jià)；

(2)為了檢索數(shù)據(jù)表中唯一的一行數(shù)據(jù)，不可避免的掃描整個(gè)數(shù)據(jù)表；

(3)由于多個(gè)表中有可能存在錯(cuò)誤的索引列，最終導(dǎo)致多表聯(lián)查檢索發(fā)生等待時(shí)間過(guò)長(zhǎng)的現(xiàn)象。

3 表的分區(qū)技術(shù)

Oracle數(shù)據(jù)庫(kù)中為了達(dá)到高效的檢索速度，建立表的分區(qū)技術(shù)是針對(duì)具有大數(shù)據(jù)的應(yīng)用系統(tǒng)而言，大型數(shù)據(jù)表進(jìn)行檢索優(yōu)化是一種多種優(yōu)化方案中選擇最佳的行為，理論上是將一個(gè)完整大型數(shù)據(jù)表實(shí)現(xiàn)物理上分區(qū)，但是，從邏輯結(jié)構(gòu)上依然是完整的大型數(shù)據(jù)表，系統(tǒng)管理員或者用戶(hù)看不出其中有什么變化，數(shù)據(jù)庫(kù)系統(tǒng)的設(shè)計(jì)人員需要知道數(shù)據(jù)存儲(chǔ)位置即可，只要程序設(shè)計(jì)人員從邏輯上將其當(dāng)成完整的大型數(shù)據(jù)表即可。為數(shù)據(jù)庫(kù)系統(tǒng)增加了可用性與靈活性。

3.1 實(shí)現(xiàn)表的分區(qū)方法

Oracle數(shù)據(jù)庫(kù)實(shí)現(xiàn)數(shù)據(jù)表的分區(qū)方法主要有多種:一是按照表存儲(chǔ)字段值范圍實(shí)現(xiàn)表分區(qū)方法；二是按照表存儲(chǔ)字段的HASH函數(shù)值實(shí)現(xiàn)表的HASH分區(qū)方法；三是按照字段的數(shù)據(jù)范圍進(jìn)行表分區(qū)劃分，必要說(shuō)明的是按照數(shù)據(jù)范圍實(shí)現(xiàn)的分區(qū)劃分需要有限的值范圍，再根據(jù)HASH函數(shù)值實(shí)現(xiàn)二次分區(qū)方法；四是在Oracle數(shù)據(jù)庫(kù)中增加了按找表存儲(chǔ)字段列值實(shí)現(xiàn)分區(qū)方法。

在本文的數(shù)據(jù)庫(kù)表分區(qū)實(shí)驗(yàn)中，針對(duì)馬路上車(chē)輛行走記錄表中的過(guò)車(chē)時(shí)間存儲(chǔ)字段實(shí)現(xiàn)表的分區(qū)方法，每月的行車(chē)記錄數(shù)據(jù)實(shí)現(xiàn)一個(gè)分區(qū)表，如果按照某個(gè)行車(chē)時(shí)間進(jìn)行檢索數(shù)據(jù)時(shí)，會(huì)大大增加數(shù)據(jù)庫(kù)系統(tǒng)的檢索速度。本文的數(shù)據(jù)庫(kù)表分區(qū)實(shí)驗(yàn)中，行車(chē)記錄分區(qū)表的SQL語(yǔ)句如下:

3.2 表實(shí)現(xiàn)分區(qū)的優(yōu)點(diǎn)

通過(guò)對(duì)大型數(shù)據(jù)表實(shí)行分區(qū)之后，可以獲得下面的幾點(diǎn)有點(diǎn)如下:

(1)表的操作性增強(qiáng):如果大型數(shù)據(jù)表的某個(gè)分區(qū)出現(xiàn)問(wèn)題，數(shù)據(jù)表的其他分區(qū)依然可用。

(2)維護(hù)表的分區(qū)方便:如果數(shù)據(jù)表的某個(gè)分區(qū)出現(xiàn)問(wèn)題，需要維護(hù)表的分區(qū)數(shù)據(jù)，只修改出現(xiàn)問(wèn)題的分區(qū)即可。

(3)均衡數(shù)據(jù)庫(kù)系統(tǒng)的I/O操作:可以根據(jù)表的不同分區(qū)映射到低速磁盤(pán)上以平衡數(shù)據(jù)庫(kù)I/O操作，以改善整個(gè)數(shù)據(jù)庫(kù)系統(tǒng)的檢索性能。

(4)改善檢索性能:對(duì)表的分區(qū)對(duì)象的檢索行為可以實(shí)現(xiàn)僅對(duì)檢索本身屬于該表分區(qū)的一些數(shù)據(jù)，在部分?jǐn)?shù)據(jù)中實(shí)現(xiàn)檢索操作，不但提高檢索速度，而且檢索數(shù)據(jù)精準(zhǔn)。

當(dāng)表分區(qū)所執(zhí)行的事務(wù)操作時(shí)，系統(tǒng)會(huì)指定了大型數(shù)據(jù)庫(kù)的某個(gè)分區(qū)，大型數(shù)據(jù)庫(kù)系統(tǒng)在執(zhí)行特定事務(wù)時(shí)則只檢索該分區(qū)的全部數(shù)據(jù)，大大地提高了數(shù)據(jù)檢索的速度。不需要指定數(shù)據(jù)表的某個(gè)分區(qū)檢索操作也可實(shí)現(xiàn)。

4 優(yōu)化SQL語(yǔ)句技術(shù)

4.1 優(yōu)化應(yīng)用系統(tǒng)的SQL語(yǔ)句技術(shù)

在數(shù)據(jù)庫(kù)系統(tǒng)中實(shí)現(xiàn)數(shù)據(jù)檢索行為，程序員可以編寫(xiě)多種可執(zhí)行的SQL語(yǔ)句實(shí)現(xiàn)對(duì)需求數(shù)據(jù)進(jìn)行檢索，因此，優(yōu)化SQL執(zhí)行語(yǔ)句就是為了提高數(shù)據(jù)的檢索效率，把低效的SQL語(yǔ)句替換為可以實(shí)現(xiàn)高效執(zhí)行、檢索的SQL語(yǔ)句。優(yōu)化SQL語(yǔ)句的目的主要是進(jìn)行數(shù)據(jù)檢索等價(jià)替換操作，主要是從數(shù)據(jù)庫(kù)數(shù)據(jù)檢索語(yǔ)句將用戶(hù)提交所執(zhí)行的SQL檢索語(yǔ)句替換為高效的方式。優(yōu)化SQL語(yǔ)句的一些方法:

（1）盡量為表數(shù)據(jù)結(jié)構(gòu)建立索引；

（2）選擇在多種數(shù)據(jù)表聯(lián)合檢索數(shù)據(jù)的次序；

（3）數(shù)據(jù)檢索過(guò)程中，要盡量子查詢(xún)時(shí)慎重使用IN或者NOT IN關(guān)鍵字，當(dāng)大數(shù)據(jù)檢索過(guò)程中，當(dāng)使用where NOT exists檢索效果要比其他語(yǔ)句好；

（4）非特別的情況下禁止使用視圖的實(shí)現(xiàn)關(guān)聯(lián)檢索數(shù)據(jù)，尤其非常復(fù)雜的視圖關(guān)系的管理檢索；

（5）盡量避免在SQL執(zhí)行語(yǔ)句中使用PL/SQL函數(shù)調(diào)用；

（6）避免對(duì)大數(shù)據(jù)庫(kù)實(shí)現(xiàn)遠(yuǎn)程數(shù)據(jù)表的訪問(wèn)；

（7）在where子句中包含索引列時(shí)，盡量使用UNION代替OR關(guān)鍵字；

（8）優(yōu)化GROUP BY關(guān)鍵字，將不需要排序的列在GROUP BY之前去除；

優(yōu)化Sql語(yǔ)句的方法有多種，在這里只是重點(diǎn)強(qiáng)調(diào)優(yōu)化Sql語(yǔ)句的使用方法，對(duì)于具體的檢索優(yōu)化方法還需要根據(jù)實(shí)際情況而定，在此不再具體贅述。

4.2 優(yōu)化數(shù)據(jù)庫(kù)的SQL語(yǔ)句

在數(shù)據(jù)庫(kù)系統(tǒng)中實(shí)現(xiàn)數(shù)據(jù)檢索功能時(shí)，往往通過(guò)本文檢索優(yōu)化方法達(dá)到一定的效果，但是，在大數(shù)據(jù)的檢索過(guò)程中，程序員往往使用分頁(yè)檢索技術(shù)來(lái)提高系統(tǒng)處理的檢索速度。分頁(yè)檢索的技術(shù)原理就是通過(guò)先執(zhí)行SQL語(yǔ)句后過(guò)濾掉大部分檢索數(shù)據(jù)，只將滿(mǎn)足需求的部分?jǐn)?shù)據(jù)檢索出n(一般情況下n值不會(huì)超出20條)條數(shù)據(jù)，如果用戶(hù)對(duì)已經(jīng)檢索的部分?jǐn)?shù)據(jù)不夠滿(mǎn)意，則需要檢索其他的后n條數(shù)據(jù)達(dá)到檢索數(shù)據(jù)的目的，通常在應(yīng)用系統(tǒng)中有“首頁(yè)”、“上一頁(yè)”、“下一頁(yè)”、“尾頁(yè)”等按鈕，可點(diǎn)擊“下一頁(yè)”按鈕方可檢索后n條數(shù)據(jù)而不必將全部的數(shù)據(jù)調(diào)入到內(nèi)存中。

5 本文結(jié)論與未來(lái)研究方向

針對(duì)本文所研究的數(shù)據(jù)檢索優(yōu)化內(nèi)容，下面專(zhuān)門(mén)做了一個(gè)關(guān)于數(shù)據(jù)檢索與優(yōu)化的測(cè)試，根據(jù)測(cè)試優(yōu)化結(jié)果進(jìn)行分析，本文的測(cè)試數(shù)據(jù)接近上百萬(wàn)條數(shù)據(jù)量。當(dāng)應(yīng)用系統(tǒng)的檢索功能未實(shí)現(xiàn)優(yōu)化之前，在同樣的數(shù)據(jù)庫(kù)表的數(shù)據(jù)內(nèi)容，檢索時(shí)間大致為6到8秒，通過(guò)本文研究的數(shù)據(jù)檢索與優(yōu)化方法實(shí)現(xiàn)的應(yīng)用系統(tǒng)優(yōu)化之后，檢索速度比之前縮減到幾十毫秒即可實(shí)現(xiàn)相同的檢索內(nèi)容，數(shù)據(jù)庫(kù)系統(tǒng)的檢索速度瞬間提高了數(shù)十倍的效果。表1是實(shí)現(xiàn)對(duì)數(shù)據(jù)表建立索引測(cè)試分析情況，表1數(shù)據(jù)表中數(shù)十萬(wàn)數(shù)據(jù)量的有無(wú)索引測(cè)試分析表。

表1 數(shù)據(jù)表中數(shù)十萬(wàn)數(shù)據(jù)量的有無(wú)索引測(cè)試分析表

當(dāng)將表1的數(shù)據(jù)檢索過(guò)程使用分頁(yè)技術(shù)處理時(shí)，會(huì)在表中建立索引的情形下的檢索時(shí)間都減少1毫秒左右，這樣可以大大的提高數(shù)據(jù)庫(kù)系統(tǒng)的檢索速度，增加用戶(hù)體驗(yàn)。

本文中主要是從數(shù)據(jù)庫(kù)與應(yīng)用系統(tǒng)對(duì)檢索優(yōu)化技術(shù)的重點(diǎn)論述，例如：如何建立表的索引技術(shù)、實(shí)現(xiàn)表的分區(qū)技術(shù)、優(yōu)化SQL執(zhí)行語(yǔ)句技術(shù)以及在大型數(shù)據(jù)表中的數(shù)據(jù)檢索分頁(yè)技術(shù)等等。另外，當(dāng)應(yīng)用系統(tǒng)的軟件部分全部實(shí)現(xiàn)檢索優(yōu)化操作之后，并沒(méi)有其他的優(yōu)化之處，還可以通升級(jí)硬件配置來(lái)提高數(shù)據(jù)庫(kù)系統(tǒng)的檢索速度。

[1]周彥,陳梅,王翰虎,等．基于層次位圖連接索引的數(shù)據(jù)倉(cāng)庫(kù)查詢(xún)優(yōu)化［J］．計(jì)算機(jī)技術(shù)與發(fā)展,2011,21(3):41－43

[2]王君,祝永志,魏榕暉,等．基于Oracle分布式數(shù)據(jù)庫(kù)的查詢(xún)優(yōu)化［J］．計(jì)算機(jī)技術(shù)與發(fā)展,2008,18(1):157－160

[3]楊小艷,尹明,戴學(xué)豐．Oracle數(shù)據(jù)庫(kù)查詢(xún)優(yōu)化方法研究［J］．計(jì)算機(jī)與現(xiàn)代化,2008(4):4－7