第5卷第2期浙江工貿(mào)職業(yè)技術(shù)學(xué)院學(xué)報(bào)Vol.5 No.22005年6月JOURNAL OF ZHEJIANG INDUSTRY&TRADE POLYTECHNICJun.2005查詢優(yōu)化及SQL查詢語句的優(yōu)化方法程有娥(浙江工貿(mào)職業(yè)技術(shù)學(xué)院，浙江溫州325003)摘要:查詢是數(shù)據(jù)庫應(yīng)用系統(tǒng)中使用最頻繁的操作，查詢語句的優(yōu)劣直接影響數(shù)據(jù)庫應(yīng)用系統(tǒng)的性能。本文在分析查詢優(yōu)化策略的基礎(chǔ)上，重點(diǎn)介紹了幾種SQL查詢語句的優(yōu)化方法。關(guān)鍵詞:查詢優(yōu)化; SQ;關(guān)聯(lián);索引中圖分類號(hào): F274文獻(xiàn)標(biāo)識(shí)碼: B文章編號(hào): 1672- 0105 (2005) - 02- 0053- 04Enquiry Optimizing and SQLCheng Youer( Zhejiang Industry and Trade PolytechnicWenzhou Zhejiang 325003)Abstract: Enquiry is frequently used in data base application system which depends on whether the enquiry statement issuperior or inferior. The author introduces some methods of SQL through analysis of enquiry optimizing strategy.Keywords: Enquiry optimizing; SQL; Iterrelation; Index一、關(guān)于查詢優(yōu)化對(duì)數(shù)據(jù)庫的查詢操作在數(shù)據(jù)庫應(yīng)用系統(tǒng)中都占有很大的操作比例。所謂查詢優(yōu)化就是對(duì)于給定的查詢選擇代價(jià)最小的操作序列，使查詢過程既節(jié)省時(shí)間，又節(jié)省空間。關(guān)系數(shù)據(jù)庫系統(tǒng)，對(duì)于用戶給出的查詢語句，系統(tǒng)中的查詢處理程序能自動(dòng)實(shí)現(xiàn)查詢優(yōu)化。但是，對(duì)于一個(gè)較復(fù)雜的查詢要求，通常都可以用幾種不同的查詢語句來表達(dá)，雖然它們的結(jié)果是相同的，但執(zhí)行過程可能有很大的差別。所以，作為數(shù)據(jù)庫應(yīng)用系統(tǒng)開發(fā)人員也同樣有必要進(jìn)行查詢優(yōu)化的探討，對(duì)特定的查詢操作寫出最優(yōu)的查詢語句，從而在系統(tǒng)開銷最小的情況下對(duì)查詢進(jìn)行盡可能的優(yōu)化。查詢優(yōu)化既有邏輯方法的優(yōu)化(如關(guān)系代數(shù)表達(dá)式的優(yōu)化)，也有物理方法的優(yōu)化(如存取路徑和存取方法的優(yōu)化)。由于磁盤讀寫比CPU的處理在速度上慢得多，因此查詢優(yōu)化的關(guān)鍵就是減少對(duì)磁盤的訪問。具體策略主要有: 1. -元選擇首先做(指針對(duì)一一個(gè)關(guān)系、只涉及-一個(gè)屬性的選擇); 2. 投影、選擇同時(shí)做; 3. 乘積、選擇合并做(把笛卡兒與隨后以選擇形式出現(xiàn)的連接條件-起做連接運(yùn)算); 4. 索引、排序預(yù)先做。具體的實(shí)現(xiàn)步驟是利用關(guān)系代數(shù)等價(jià)變換規(guī)則對(duì)查詢表達(dá)式進(jìn)行變換，最后得到一個(gè)優(yōu)化代價(jià)合理、查詢效率較高的查詢計(jì)劃。收稿日期: 2005-03-23中國煤化工作者簡(jiǎn)介:程有娥(1963- )，女，副教授。MYHCNMHG5.浙江工貿(mào)職業(yè)技術(shù)學(xué)院學(xué)報(bào)二、基于SQL查詢語句的查詢優(yōu)化辦法在SQL中要提高查詢效率，除了提高網(wǎng)速、把數(shù)據(jù)、日志、索引放到不同的I/O設(shè)備上，以增加讀取速度等措施以外，對(duì)查詢語句進(jìn)行優(yōu)化也是常用的提高查詢效率的辦法。下面以學(xué)生檔案管理系統(tǒng)為例，介紹在SQ中查詢語句的優(yōu)化辦法。假如在一個(gè)學(xué)生檔案管理系統(tǒng)中，有學(xué)生基本信息表Students(St udent_ Id, St udent_ nane, Photo, Sex,Nati onality, Brith, Pol iti cal _ party, Fanily_ pl ace, Id_ card, Hone_ .phone, Resi dence, Post_ code, Indate, a ass_ Id)和學(xué)生成績(jī)表G(Student_ Id, Ks_ Id, St udent_ nane, Score)。(-)合理建立和使用索引索引是數(shù)據(jù)庫中重要的數(shù)據(jù)結(jié)構(gòu)，也是查詢優(yōu)化的基礎(chǔ)，建立索引的根本目的是提高查詢效率。索引的使用要恰到好處，其使用原則如下:1.在頻繁進(jìn)行排序或分組( 即進(jìn)行g(shù)roup by或order by 操作)的列上建立索引。2.在經(jīng)常進(jìn)行連接的列上建立索引，而不經(jīng)連接的字段則由優(yōu)化器自動(dòng)生成索引。3.不要對(duì)只有有限幾個(gè)值的字段建立單一索引。例如若對(duì)“性別”字段建立了索引不但不會(huì)提高查詢效率，反而會(huì)嚴(yán)重降低更新速度。4.如果待排序的列有多個(gè)，可在這些列上建立復(fù)合索引。.(二)合理使用查詢條件，充分發(fā)揮系統(tǒng)優(yōu)化器的作用，避免對(duì)數(shù)據(jù)表記錄的順序讀取表的順序讀取是導(dǎo)致系統(tǒng)性能直線下降的原因。在以下幾種情況下，從理論上講可以使用索引進(jìn)行優(yōu)化。但實(shí)際上會(huì)使優(yōu)化器忽略相應(yīng)的查詢語句或者無法進(jìn)行優(yōu)化處理，而導(dǎo)致對(duì)數(shù)據(jù)表的順序讀取。1.在表達(dá)式中使用了非法參數(shù)(SQ Server 2000 中，搜索參數(shù)的合法操作符包括“=，<，>，<=,>=”中的任何一個(gè))例如: SELECT * FRCM St udents WERE St udent_ id like‘20039%6改進(jìn)后: SELECTFRCM St udentsWERE (Student_ id>' 2003’AND Student. i d<' 2004' )2.對(duì)列進(jìn)行了算術(shù)、比較或函數(shù)等運(yùn)算如以下三個(gè)查詢都不能通過系統(tǒng)優(yōu)化器進(jìn)行優(yōu)化SLECT * FROMG WHERE Sorce*0. 9>80 ( 對(duì)列進(jìn)行了算術(shù)運(yùn)算)SLECT * FROMG WERE uppercase( xm)=’SMTH'(對(duì)列進(jìn)行了函數(shù)運(yùn)算)SLECT * FRCMGWERE Student. id=Ks_ id ( 列與列之間進(jìn)行了比較)3.使用了IN或者CR等時(shí)可能導(dǎo)致查詢沒有使用索引優(yōu)化，可使用顯式申明指定索引如查詢SELECT * FROMG WERE St udent_ i d=100 OR Sorce=60 在執(zhí)行時(shí)如果發(fā)現(xiàn)沒有使用索引優(yōu)化，可使用顯式申明指定索引。注意:在查詢語句中應(yīng)極力避免使用“<>” 或NOT操作符，因?yàn)?，這些操作符都會(huì)導(dǎo)致對(duì)表的順序讀取。(三)使用多個(gè)條件對(duì)同一表進(jìn)行查詢時(shí)，F(xiàn)RQM子 句中將條件嚴(yán)格的表放在前面.中國煤化工例查找2004年9月5號(hào)入學(xué)的Pol itical - Party為PartyYHCNMHG.程有娥:查詢優(yōu)化及SQL查詢語句的優(yōu)化方法SELECT St udent_ _nane, Sex, Fi nely_ pl aceFRQM St udent sWHERE Pol itical_ party=' party’AND indata=' 5- SEP- 2004'如果St udents中有10°個(gè)記錄，其中Party記錄有10個(gè)，而表的所有記錄中2004年9月5號(hào)入學(xué)的記錄有10個(gè)，其中Party的有100個(gè)。在上述兩種查詢語句中有兩個(gè)選擇條件: Pol iti cal- Party=’Party’ 和InData= “5- SEP- 2004' ,在執(zhí)行完第-個(gè)選擇條件后，得到一個(gè)105行的臨時(shí)表格，第二個(gè)選擇條件是對(duì)這個(gè)10行的臨時(shí)表再進(jìn)行選擇運(yùn)算，從而得到所需結(jié)果。如果將選擇條件的次序?qū)Q，即將WERE語句改寫為WERE I ndat e=‘5- SEP- 2004’AND Political_ Party= ‘Party'， 則先得到10行的臨時(shí)表格，然后再從這個(gè)10行的臨時(shí)表中選擇出符合條件的記錄，可見，選擇條件的次序極大地影響查詢語句的計(jì)算量，前者臨時(shí)表格的行數(shù)是后者的100倍，所以要提高查詢響應(yīng)速度，可將較嚴(yán)格的選擇條件放在前面，較寬松的條件放在后面，這樣在執(zhí)行過程中可以將不滿足的行迅速刪除，從而是第二次選擇提高速度。(四)對(duì)多個(gè)表查詢時(shí)，將返回結(jié)果集記錄數(shù)較少的表放在后面對(duì)多個(gè)表格進(jìn)行查詢操作時(shí)，F(xiàn)RQM子句中表格的次序也影響查詢的響應(yīng)速度。設(shè)學(xué)生檔案管理系統(tǒng)中還有一個(gè)與st udents結(jié)構(gòu)類似的表st udents1，students1 中2004年9月5號(hào)入學(xué)的學(xué)生有200個(gè)，若要查詢st udents和st udents1兩個(gè)表中2004年9月5號(hào)入學(xué)的學(xué)生，則SQ語句將返回包含所有1200個(gè)記錄的表，相應(yīng)的SQ查詢語句如下:SELECT Student_ nane, Sex, Fi nely_ .pl aceFRQM St udent , St udent 1WHERE i ndat a=’5- SEP- 2004'在具體的執(zhí)行過程中，先對(duì)表格students進(jìn)行選擇運(yùn)算,得到1000個(gè)記錄的臨時(shí)表，再對(duì)st udents1進(jìn)行選擇運(yùn)算,然后將得到的符合條件的200個(gè)記錄插入到1000個(gè)記錄的臨時(shí)表中,需200次插入運(yùn)算。若將FROM語句后面的表次序?qū)Q，即為“FROM St udents1, St udent ds”,則先得到-一個(gè)200行的臨時(shí)表后，需要做1000次的插入運(yùn)算，其工作量遠(yuǎn)大于第-種情況，故可將返回較少記錄的表格排在后面，較多記錄的表格排在前面，以便減少插入運(yùn)算。(五)對(duì)多個(gè)表進(jìn)行多條件查詢時(shí)，使用相關(guān)子查詢聯(lián)結(jié)運(yùn)算是數(shù)據(jù)庫查詢中經(jīng)常使用的運(yùn)算之-，由于聯(lián)結(jié)運(yùn)算會(huì)產(chǎn)生-個(gè)很大的臨時(shí)表格，特別是多個(gè)表的聯(lián)結(jié)運(yùn)算，不優(yōu)化的SQ查詢語句會(huì)產(chǎn)生巨大的運(yùn)算量，且增加對(duì)內(nèi)存的需求，嚴(yán)重時(shí)可使軟件在執(zhí)行中出現(xiàn)異常。例如查詢2004年9月5號(hào)入學(xué)的學(xué)生成績(jī),利用下面兩個(gè)sQL語句都可以單獨(dú)完成:1) SELECT * FROMStudents, qWERE St udents. Student_ i d=G. Student_ i d AND Students. indata =’5- SEP- 2004'2) SELECT *FROM gWERE St udent_ i d=( SELECT St udent_ idFROMSt udents WERE i ndat a=’5- SEP- 2004' )中國煤化工MYHCNMHG5:.浙江工貿(mào)職業(yè)技術(shù)學(xué)院學(xué)報(bào)兩個(gè)查詢語句返回同樣的數(shù)據(jù)結(jié)果，但查詢響應(yīng)時(shí)間不同，在1)中先進(jìn)行兩個(gè)表格的聯(lián)結(jié)運(yùn)算,得到一個(gè)10行的臨時(shí)表格，再進(jìn)行選擇運(yùn)算，得到所需的結(jié)果。而2)中先對(duì)表格St udents進(jìn)行選擇運(yùn)算，立刻刪除了大量多余數(shù)據(jù)，得到一個(gè)僅有1000行的臨時(shí)表(由前可知)，再對(duì)這個(gè)表與g進(jìn)行聯(lián)結(jié)運(yùn)算而得到最終結(jié)果?？梢?)中的計(jì)算量要比2)大得多，這是因?yàn)樵?)中的聯(lián)結(jié)運(yùn)算的兩個(gè)表都是10°行，選擇運(yùn)算也是在巨大的表上進(jìn)行，而2)中的聯(lián)結(jié)運(yùn)算僅對(duì)一-個(gè)1000行的表進(jìn)行?？梢娫趯?duì)多個(gè)表進(jìn)行多條件查詢時(shí)，使用相關(guān)子查詢可以使查詢優(yōu)化。(六)適當(dāng)建立關(guān)聯(lián)統(tǒng)計(jì)表或臨時(shí)表，減少對(duì)大型表的直接查詢把表的一個(gè)子集進(jìn)行排序并創(chuàng)建臨時(shí)表，避免了對(duì)數(shù)據(jù)表的直接操作，從而提高了查詢效率。例如:SELECT St udents. St udent_ nane, g . score, St udents. Post_ codeFORMSt udents, gWVERE St udents. St udent_ i d=Gj . student. id ANDg. score>60 AND St udents. Post_ Code>' 5100'ORDER BY St udents. St udent_ nane如果這個(gè)查詢要被執(zhí)行數(shù)次，可以把所有及格的學(xué)生找出來放在-個(gè)臨時(shí)表文件中，并按學(xué)生的名字進(jìn)行排序: .FORMSt udents, GWKERE St udents. St udent. i d=G. student. id AND G . score>60INTO TEMP Student. _wi th Score :然后按下面的方式在臨時(shí)表中查詢: :SELECT St udents. Student_ nane, g . score, St udents. Post codeFORMSt udent_ with_ ScoreWHERE Post_ Code>’ 5100'臨時(shí)表中的行比主表少，而且物理順序就是所要求的順序，減少了磁盤1/0所以查詢效率大大增加。注意:臨時(shí)表創(chuàng)建后不會(huì)反映主表的修改。三、結(jié)束語.查詢優(yōu)化是數(shù)據(jù)庫應(yīng)用程序設(shè)計(jì)中一個(gè)關(guān)鍵性的問題，本文是作者在多年的教學(xué)中總結(jié)出的一些方法，相信對(duì)相關(guān)課程的教學(xué)和數(shù)據(jù)庫應(yīng)用系統(tǒng)的開發(fā)有-定的指導(dǎo)作用。參考文獻(xiàn):[1]史嘉權(quán).數(shù)據(jù)庫系統(tǒng)教程[M.清華大學(xué)出版社.[2]靳學(xué)輝、數(shù)據(jù)庫原理與應(yīng)用[M.電子工業(yè)出版社, 1997.[3] [美]微軟公司. Querying Mcrosoft SQ Server 2000 W thTr ans中國煤化工2001.YHCNMHG56.