關(guān)系數(shù)據(jù)庫查詢優(yōu)化技術(shù)的研究

【摘 要】當(dāng)今社會(huì)是信息技術(shù)高度交互的時(shí)代，各行業(yè)之間業(yè)務(wù)的處理和數(shù)據(jù)存儲(chǔ)都有相關(guān)的管理系統(tǒng)作為平臺(tái)環(huán)境，并且各行業(yè)之間的數(shù)據(jù)交互也日益頻繁。從傳統(tǒng)的C/S桌面應(yīng)用到主流的Web應(yīng)用，直至當(dāng)今的物聯(lián)網(wǎng)和移動(dòng)開發(fā)，都離不開數(shù)據(jù)的存儲(chǔ)和交互，所以，數(shù)據(jù)庫作為存儲(chǔ)和管理應(yīng)用的數(shù)據(jù)平臺(tái)，已經(jīng)成為信息管理和挖掘的基本工具。信息數(shù)據(jù)庫除了提供準(zhǔn)確的查詢結(jié)果，針對(duì)數(shù)據(jù)庫的查詢效率問題也越來越成為開發(fā)者和用戶關(guān)心的首要問題。如何構(gòu)建一個(gè)快速平穩(wěn)的數(shù)據(jù)傳輸流程是整個(gè)應(yīng)用系統(tǒng)運(yùn)行周期中最為關(guān)鍵的一個(gè)環(huán)節(jié)。

【關(guān)鍵字】關(guān)系數(shù)據(jù)庫、查詢處理、查詢優(yōu)化

一、引言

基于數(shù)據(jù)庫的信息系統(tǒng)在與數(shù)據(jù)庫進(jìn)行交互時(shí)，對(duì)數(shù)據(jù)庫的訪問查詢效率的影響因素會(huì)遇到軟硬件等多方面的瓶頸，在硬件方面包括：CPU的處理速度、I/O速度、存儲(chǔ)器容量及服務(wù)器磁盤鏡像配置等。在軟件方面包括如：服務(wù)器操作系統(tǒng)、所使用DBMS和前端開發(fā)工具以及是否創(chuàng)建索引等。數(shù)據(jù)的查詢操作是各種數(shù)據(jù)庫數(shù)據(jù)操作中最為頻繁的操作，所以查詢的效率是數(shù)據(jù)庫系統(tǒng)的重要性能指標(biāo)，查詢優(yōu)化亦是數(shù)據(jù)庫性能優(yōu)化的主要方法之一。通常情況下，在請(qǐng)求訪問數(shù)據(jù)庫服務(wù)器查詢數(shù)據(jù)時(shí)，編寫的SQL查詢語句一般會(huì)成為影響查詢效率的基本因素。本文從關(guān)系型數(shù)據(jù)庫查詢處理的基本過程出發(fā)，通過研究總結(jié)關(guān)系數(shù)據(jù)庫查詢優(yōu)化的一般方法，并依據(jù)其主要設(shè)計(jì)原則。對(duì)關(guān)系型數(shù)據(jù)庫進(jìn)行查詢優(yōu)化若干方法進(jìn)行總結(jié)。

二、關(guān)系型數(shù)據(jù)庫查詢處理

常見的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)中，使用數(shù)據(jù)操作語言發(fā)出查詢請(qǐng)求，生成并提交查詢計(jì)劃，即SQL語句集合，各種不同的DBMS會(huì)對(duì)原始查詢計(jì)劃進(jìn)行不同的處理步驟進(jìn)行變換優(yōu)化處理，最終查詢計(jì)劃是在優(yōu)化處理之后產(chǎn)生的，其一般過程如圖1所示：

（一）對(duì)基本SQL語句進(jìn)行解析，并針對(duì)詞法和語法分析檢查。

查詢處理生成查詢計(jì)劃之前，需要將SQL語句進(jìn)行語法分析并基于關(guān)系代數(shù)對(duì)查詢語句進(jìn)行翻譯，主要是檢查SQL查詢語句的語法，把查詢語句分析成關(guān)系數(shù)據(jù)庫能夠理解的模塊，并且構(gòu)造該查詢的語法分析樹，將其翻譯成關(guān)系代數(shù)表達(dá)式[1]。

（二）將查詢語句提交給DBMS的查詢優(yōu)化器進(jìn)行代數(shù)優(yōu)化和存取路徑的優(yōu)化。

查詢優(yōu)化的目的是為執(zhí)行查詢選擇高效、快速的查詢執(zhí)行計(jì)劃。其主要目的是設(shè)計(jì)生成最優(yōu)效能的等價(jià)關(guān)系代數(shù)表達(dá)式，或者選擇查詢語句處理的其他優(yōu)化策略。因?yàn)樵趫?zhí)行確定的查詢計(jì)劃之前，查詢計(jì)劃的代價(jià)通常是無法準(zhǔn)確計(jì)算的。所以優(yōu)化器只能估算執(zhí)行代價(jià)，然后在后期對(duì)這些查詢計(jì)劃估算值進(jìn)行合理篩選[1]。

為了獲取最佳的估算結(jié)果，優(yōu)化器會(huì)收集利用一些相關(guān)數(shù)據(jù)的統(tǒng)計(jì)信息進(jìn)行估算。當(dāng)然，現(xiàn)在主流的關(guān)系型數(shù)據(jù)庫系統(tǒng)本身都集成了一些優(yōu)化技術(shù)，這些優(yōu)化技術(shù)通常獲取的不一定都是最優(yōu)的，往往只是最有效的執(zhí)行計(jì)劃。常見的優(yōu)化技術(shù)諸如：語法語義優(yōu)化、啟發(fā)式優(yōu)化和基于成本優(yōu)化等。

（三）由DBMS預(yù)編譯模塊對(duì)查詢語句進(jìn)行處理并生成查詢計(jì)劃。提交執(zhí)行，返回結(jié)果。

將使用優(yōu)化器選擇的執(zhí)行計(jì)劃提取出來執(zhí)行。執(zhí)行引擎從數(shù)據(jù)庫文件中查詢數(shù)據(jù)結(jié)果集并輸出。

三、關(guān)系數(shù)據(jù)庫查詢優(yōu)化的一般準(zhǔn)則

（一）規(guī)則優(yōu)化

（1）選擇運(yùn)算應(yīng)該盡可能先做，用以減少中間關(guān)系。

（2）在執(zhí)行連接操作前，適當(dāng)?shù)膶?duì)關(guān)系進(jìn)行諸如索引和排序合并連接方法的預(yù)處理。索引連接方法即在連接屬性上建立索引。排序合并連接方法是按連接屬性排序。

（3）投影運(yùn)算和選擇運(yùn)算同時(shí)做，用以避免關(guān)系的重復(fù)掃描。

（4）將投影運(yùn)算與其前面或后面的雙目運(yùn)算結(jié)合，用以避免關(guān)系的重復(fù)掃描。

（5）把執(zhí)行的笛卡爾積和之后的選擇運(yùn)算進(jìn)行連接運(yùn)算，即合并操作。以此減小對(duì)時(shí)間和空間復(fù)雜度的開銷。

（6）減少中間結(jié)果操作：刪除與查詢無關(guān)的屬性和按小關(guān)系運(yùn)算優(yōu)化處理。

（7）提取公共子表達(dá)式，提前計(jì)算，并保存結(jié)果，以此避免重復(fù)計(jì)算浪費(fèi)時(shí)間。

（二）物理優(yōu)化

在查詢優(yōu)化與數(shù)據(jù)的物理組織結(jié)構(gòu)相關(guān)，并且依賴于復(fù)雜網(wǎng)絡(luò)訪問路徑情況下，需要思考如何選擇實(shí)現(xiàn)策略。例如在已經(jīng)組織了基于查詢的專門索引或者排序文件時(shí)的情況下需要思考針對(duì)物理優(yōu)化的策略選擇。

（三）代價(jià)估算優(yōu)化

指對(duì)于多個(gè)候選策略逐個(gè)進(jìn)行執(zhí)行代價(jià)估算，從中選擇代價(jià)最小的做為執(zhí)行策略。

四、關(guān)系數(shù)據(jù)庫查詢優(yōu)化方法

關(guān)系數(shù)據(jù)庫查詢優(yōu)化在遵循以上準(zhǔn)則的前提下，總結(jié)分析相關(guān)方法如下：

（一）合理使用索引

為數(shù)據(jù)庫創(chuàng)建索引是提高數(shù)據(jù)管理效率的重要方法。在數(shù)據(jù)庫中，索引就是為大量數(shù)據(jù)創(chuàng)建“目錄”，同時(shí)，索引的使用要做到恰到好處。其使用原則如下：

（1）在經(jīng)常做為搜索條件且不同值較多的列上建立檢索。比如學(xué)生信息表的性別列上只有“Male”與“Female”兩個(gè)不同值，就無需建立索引，在這樣的列字段建立索引會(huì)嚴(yán)重降低更新速度。

（2）在經(jīng)常用于連接的如外鍵字段的列上建立索引，而不經(jīng)常連接的字段則由優(yōu)化器自動(dòng)生成索引。

（3）在常用于排序或分組（如group by或order by操作）的字段建立索引。

（4）如果排序列有多個(gè)，可以在這些經(jīng)常存取的多個(gè)列字段上，按照各列的使用頻率建立復(fù)合索引。

在基于索引創(chuàng)建的原則下并非要盲目的創(chuàng)建索引，創(chuàng)建索引時(shí)注意事項(xiàng)如下：

創(chuàng)建索引要適量，并非越多越好

盡管創(chuàng)建索引可以為數(shù)據(jù)管理提供很大方便，但同時(shí)也會(huì)增加數(shù)據(jù)維護(hù)負(fù)擔(dān)，在數(shù)據(jù)庫中增刪數(shù)據(jù)都需要系統(tǒng)對(duì)索引進(jìn)行動(dòng)態(tài)維護(hù)，這樣就可以降低索引的維護(hù)速度，因此適量創(chuàng)建索引即可，一般一個(gè)表不超過6個(gè)。

索引可以是組合索引，但索引字段越少越好

聚集索引和非聚集索引都可以使用多個(gè)字段組合，即為組合索引，組合索引中的任何一個(gè)索引字段被修改，這個(gè)索引都要重新生成，這樣就加大了索引維護(hù)的消耗。另外，組合索引與單字段索引相比占用空間也較多，每個(gè)索引文件上只能存放較少的記錄，在索引中查找數(shù)據(jù)時(shí)，組合索引的效率就相對(duì)較低了。

索引一定要建在經(jīng)常使用字段上

創(chuàng)建索引并非為了滿足所有查詢需要，只是為了滿足絕大部分的查詢需要，且索引數(shù)量不宜太多，所以索引一定要?jiǎng)?chuàng)建在經(jīng)常使用的字段上，尤其是聚集索引，每個(gè)表只能有一個(gè)，所以就會(huì)特別珍貴，一定要建在最頻繁使用的字段上。

（二）將大型表進(jìn)行水平分表操作

對(duì)于數(shù)據(jù)量增長(zhǎng)很快的大型表，每次對(duì)表的操作都需要進(jìn)行大量的數(shù)據(jù)處理，盡管有時(shí)候創(chuàng)建了索引，但是隨著數(shù)據(jù)的繼續(xù)增長(zhǎng)，索引文件也在變大，數(shù)據(jù)表的管理效率就可能會(huì)逐漸下降，索引維護(hù)、數(shù)據(jù)查找等操作需要的時(shí)間代價(jià)也會(huì)越來越大?？梢詫⒋笮蛿?shù)據(jù)表進(jìn)行分解，分解為多個(gè)子表。將大型表從數(shù)據(jù)行上進(jìn)行分解，稱為“水平分表”。水平分表之后，用戶在進(jìn)行查詢時(shí)需要對(duì)各個(gè)子表進(jìn)行分別查詢，然后將結(jié)果進(jìn)行組合。分表后可以實(shí)現(xiàn)在數(shù)據(jù)庫服務(wù)器磁盤設(shè)備上對(duì)子表分別存放，從而實(shí)現(xiàn)子表的并行查詢，提高查詢效率。

水平分表效率分析：假設(shè)存在一個(gè)大表，記錄數(shù)為recs，單個(gè)記錄長(zhǎng)度為len，將此大表進(jìn)行水平分表成n個(gè)記錄相等的子表即：n*recsi=recs。將子表利用raid技術(shù)分別存儲(chǔ)在不同物理磁盤上以保證查詢時(shí)間最短，索引表的檢索和其引起的臨時(shí)空間暫時(shí)忽略情況下，該大表水平分表得到的子表數(shù)n取值越大時(shí)，查詢效率就越高[2]。

對(duì)子表查詢效率分析：

à采用無分組統(tǒng)計(jì)查詢，查詢時(shí)間和表記錄數(shù)成正比。

分表前查詢時(shí)間T=F（recs*len）

分表后查詢時(shí)間T'=max[F（recsi*len）]=1/n*[F（recs*len）]=1/n*T

由此可知，分表后的查詢時(shí)間為分表前的1/n。

à分組統(tǒng)計(jì)查詢，查詢時(shí)間和表記錄數(shù)的平方成正比。

分表前查詢時(shí)間T=F（recs*len）2

分表后查詢時(shí)間T'=max[F（recsi*len）]=1/n2*[F（recs*len）]=1/n2*T

由此可知，分表后的查詢時(shí)間為分表前的1/n2

（三）書寫高效的SQL語句

現(xiàn)在一些大型的DBMS在針對(duì)用戶的輸入的SQL語句之后，都會(huì)進(jìn)行一些優(yōu)化操作。但是在許多應(yīng)用系統(tǒng)開發(fā)過程中，SQL語句作為基本字符串內(nèi)嵌在代碼當(dāng)中直接和數(shù)據(jù)庫管理系統(tǒng)進(jìn)行交互，另外數(shù)據(jù)庫管理員在進(jìn)行針對(duì)需求的開發(fā)時(shí)也需要編寫SQL語句。所以，也要保證編寫的SQL語句有較好的性能。

導(dǎo)致SQL查詢語句效率地下的原因簡(jiǎn)單理解為：

à沒有索引或者沒用到索引，亦或者是SQL查詢讓索引失效。

à查詢語句使用了一些效率低的方法查詢數(shù)據(jù)。

基于以上原因，針對(duì)SQL語句優(yōu)化方法分析如下：

避免對(duì)索引字段進(jìn)行數(shù)值操作。

當(dāng)查詢記錄數(shù)固定時(shí)，使用top限制返回的數(shù)據(jù)集。

select字段需要多少提取多少，在使用SQL查詢時(shí)，不要出現(xiàn)select *的情況，即使要讀取全部字段，也要使用select column格式。因?yàn)閟elect *導(dǎo)致數(shù)據(jù)查詢量會(huì)很大，從而產(chǎn)生頻繁的物理I/O。

為order by排序應(yīng)盡量在索引上進(jìn)行以提高效率，減少I/O活動(dòng)量和減小執(zhí)行時(shí)間。

盡可能減少負(fù)邏輯操作符和函數(shù)的使用，因?yàn)樗鼈儠?huì)導(dǎo)致全表掃描引起出錯(cuò)?？梢园押蠳OT、<> 、！= 等負(fù)邏輯的條件表達(dá)式轉(zhuǎn)化為意思相當(dāng)?shù)恼壿嫛?/p>

（6） 盡量避免使用LIKE、EXISTS、IN、OR等標(biāo)準(zhǔn)表達(dá)式，這些會(huì)引起全表掃描，使索引失效。另外也要減少表的連接操作，即使存在連接操作也要適當(dāng)增加一些冗余條件，使參與連接的字段集盡可能少。

（7） 盡量不使用耗費(fèi)資源的操作，如含有distinct、union、order by、group by、操作的SQL查詢語句會(huì)執(zhí)行排序操作，此將損耗一定的性能。

（8） 減少嵌套查詢的層數(shù)，盡量避免使用相關(guān)的嵌套查詢

（四）使用存儲(chǔ)過程

創(chuàng)建存儲(chǔ)過程之后，會(huì)編譯成可執(zhí)行代碼。通?？梢詫⒁恍┡c應(yīng)用業(yè)務(wù)邏輯相關(guān)的查詢功能封裝在存儲(chǔ)過程當(dāng)中，那么該存儲(chǔ)過程會(huì)作為一個(gè)數(shù)據(jù)庫對(duì)象存放在數(shù)據(jù)庫服務(wù)器中，這樣在執(zhí)行過程中就避免了將復(fù)雜應(yīng)用程序業(yè)務(wù)邏輯代碼向服務(wù)器端傳送的網(wǎng)路代價(jià)，從而減輕了網(wǎng)絡(luò)負(fù)載，同時(shí)也節(jié)省了處理時(shí)間[3]。另外，因?yàn)榇鎯?chǔ)過程已經(jīng)編譯成為可執(zhí)行代碼，就不在需要在每次執(zhí)行時(shí)重新進(jìn)行分析和優(yōu)化，減少了編譯所花費(fèi)的時(shí)間，提高了執(zhí)行效率。

（五）應(yīng)用視圖

視圖可以從若干個(gè)基本表或視圖導(dǎo)出。本身也是一個(gè)表，是一個(gè)虛表。視圖數(shù)據(jù)存放在基本表中，所以數(shù)據(jù)庫視圖定義的基本對(duì)象。當(dāng)基本表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也會(huì)改變。

利用視圖可以設(shè)置用戶權(quán)限，提高數(shù)據(jù)的保密性。在主流的數(shù)據(jù)庫管理系統(tǒng)中，不同的數(shù)據(jù)庫角色在數(shù)據(jù)的處理的要求上也有差異，可以對(duì)不同類型的用戶角色創(chuàng)建對(duì)應(yīng)的視圖，能夠在特定條件下增加數(shù)據(jù)有效性，降低查詢響應(yīng)時(shí)間。

五、小結(jié)

在實(shí)際運(yùn)用中，隨著數(shù)據(jù)量的不斷增大，對(duì)數(shù)據(jù)查詢效率的要求也會(huì)越來越高，實(shí)踐中還需要結(jié)合具體數(shù)據(jù)庫的組織結(jié)構(gòu)和數(shù)據(jù)庫服務(wù)器的運(yùn)行環(huán)境，針對(duì)性的對(duì)關(guān)系數(shù)據(jù)庫進(jìn)行查詢優(yōu)化，合理配置。除了基本的優(yōu)化方法。也可以結(jié)合一些優(yōu)化算法，通過改進(jìn)實(shí)施來最大可能發(fā)揮查詢的最大效率。

參考文獻(xiàn)：

[1]王能斌，數(shù)據(jù)庫系統(tǒng)原理 [M] 電子工業(yè)出版社，2005

[2]周立柱 馮建華等，SQL SEVER 數(shù)據(jù)庫原理—設(shè)計(jì)與實(shí)現(xiàn)[M] 清華大學(xué)出版社 ，2004

[3]李動(dòng)周，大型關(guān)系型數(shù)據(jù)庫優(yōu)化探討[J]，辦公自動(dòng)化，2007