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

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

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

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

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

1 引言

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

表1 數(shù)據(jù)表中數(shù)十萬數(shù)據(jù)量的有無索引測試分析表

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

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

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

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

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