第5卷第2期浙江工貿(mào)職業(yè)技術(shù)學(xué)院學(xué)報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ǔ)上，重點介紹了幾種SQL查詢語句的優(yōu)化方法。關(guān)鍵詞:查詢優(yōu)化; SQ;關(guān)聯(lián);索引中圖分類號: F274文獻(xiàn)標(biāo)識碼: B文章編號: 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)化對數(shù)據(jù)庫的查詢操作在數(shù)據(jù)庫應(yīng)用系統(tǒng)中都占有很大的操作比例。所謂查詢優(yōu)化就是對于給定的查詢選擇代價最小的操作序列，使查詢過程既節(jié)省時間，又節(jié)省空間。關(guān)系數(shù)據(jù)庫系統(tǒng)，對于用戶給出的查詢語句，系統(tǒng)中的查詢處理程序能自動實現(xiàn)查詢優(yōu)化。但是，對于一個較復(fù)雜的查詢要求，通常都可以用幾種不同的查詢語句來表達(dá)，雖然它們的結(jié)果是相同的，但執(zhí)行過程可能有很大的差別。所以，作為數(shù)據(jù)庫應(yīng)用系統(tǒng)開發(fā)人員也同樣有必要進(jìn)行查詢優(yōu)化的探討，對特定的查詢操作寫出最優(yōu)的查詢語句，從而在系統(tǒng)開銷最小的情況下對查詢進(jìn)行盡可能的優(yōu)化。查詢優(yōu)化既有邏輯方法的優(yōu)化(如關(guān)系代數(shù)表達(dá)式的優(yōu)化)，也有物理方法的優(yōu)化(如存取路徑和存取方法的優(yōu)化)。由于磁盤讀寫比CPU的處理在速度上慢得多，因此查詢優(yōu)化的關(guān)鍵就是減少對磁盤的訪問。具體策略主要有: 1. -元選擇首先做(指針對一一個關(guān)系、只涉及-一個屬性的選擇); 2. 投影、選擇同時做; 3. 乘積、選擇合并做(把笛卡兒與隨后以選擇形式出現(xiàn)的連接條件-起做連接運(yùn)算); 4. 索引、排序預(yù)先做。具體的實現(xiàn)步驟是利用關(guān)系代數(shù)等價變換規(guī)則對查詢表達(dá)式進(jìn)行變換，最后得到一個優(yōu)化代價合理、查詢效率較高的查詢計劃。收稿日期: 2005-03-23中國煤化工作者簡介:程有娥(1963- )，女，副教授。MYHCNMHG5.浙江工貿(mào)職業(yè)技術(shù)學(xué)院學(xué)報二、基于SQL查詢語句的查詢優(yōu)化辦法在SQL中要提高查詢效率，除了提高網(wǎng)速、把數(shù)據(jù)、日志、索引放到不同的I/O設(shè)備上，以增加讀取速度等措施以外，對查詢語句進(jìn)行優(yōu)化也是常用的提高查詢效率的辦法。下面以學(xué)生檔案管理系統(tǒng)為例，介紹在SQ中查詢語句的優(yōu)化辦法。假如在一個學(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é)生成績表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)化器自動生成索引。3.不要對只有有限幾個值的字段建立單一索引。例如若對“性別”字段建立了索引不但不會提高查詢效率，反而會嚴(yán)重降低更新速度。4.如果待排序的列有多個，可在這些列上建立復(fù)合索引。.(二)合理使用查詢條件，充分發(fā)揮系統(tǒng)優(yōu)化器的作用，避免對數(shù)據(jù)表記錄的順序讀取表的順序讀取是導(dǎo)致系統(tǒng)性能直線下降的原因。在以下幾種情況下，從理論上講可以使用索引進(jìn)行優(yōu)化。但實際上會使優(yōu)化器忽略相應(yīng)的查詢語句或者無法進(jìn)行優(yōu)化處理，而導(dǎo)致對數(shù)據(jù)表的順序讀取。1.在表達(dá)式中使用了非法參數(shù)(SQ Server 2000 中，搜索參數(shù)的合法操作符包括“=，<，>，<=,>=”中的任何一個)例如: 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.對列進(jìn)行了算術(shù)、比較或函數(shù)等運(yùn)算如以下三個查詢都不能通過系統(tǒng)優(yōu)化器進(jìn)行優(yōu)化SLECT * FROMG WHERE Sorce*0. 9>80 ( 對列進(jìn)行了算術(shù)運(yùn)算)SLECT * FROMG WERE uppercase( xm)=’SMTH'(對列進(jìn)行了函數(shù)運(yùn)算)SLECT * FRCMGWERE Student. id=Ks_ id ( 列與列之間進(jìn)行了比較)3.使用了IN或者CR等時可能導(dǎo)致查詢沒有使用索引優(yōu)化，可使用顯式申明指定索引如查詢SELECT * FROMG WERE St udent_ i d=100 OR Sorce=60 在執(zhí)行時如果發(fā)現(xiàn)沒有使用索引優(yōu)化，可使用顯式申明指定索引。注意:在查詢語句中應(yīng)極力避免使用“<>” 或NOT操作符，因為，這些操作符都會導(dǎo)致對表的順序讀取。(三)使用多個條件對同一表進(jìn)行查詢時，F(xiàn)RQM子 句中將條件嚴(yán)格的表放在前面.中國煤化工例查找2004年9月5號入學(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°個記錄，其中Party記錄有10個，而表的所有記錄中2004年9月5號入學(xué)的記錄有10個，其中Party的有100個。在上述兩種查詢語句中有兩個選擇條件: Pol iti cal- Party=’Party’ 和InData= “5- SEP- 2004' ,在執(zhí)行完第-個選擇條件后，得到一個105行的臨時表格，第二個選擇條件是對這個10行的臨時表再進(jìn)行選擇運(yùn)算，從而得到所需結(jié)果。如果將選擇條件的次序?qū)Q，即將WERE語句改寫為WERE I ndat e=‘5- SEP- 2004’AND Political_ Party= ‘Party'， 則先得到10行的臨時表格，然后再從這個10行的臨時表中選擇出符合條件的記錄，可見，選擇條件的次序極大地影響查詢語句的計算量，前者臨時表格的行數(shù)是后者的100倍，所以要提高查詢響應(yīng)速度，可將較嚴(yán)格的選擇條件放在前面，較寬松的條件放在后面，這樣在執(zhí)行過程中可以將不滿足的行迅速刪除，從而是第二次選擇提高速度。(四)對多個表查詢時，將返回結(jié)果集記錄數(shù)較少的表放在后面對多個表格進(jìn)行查詢操作時，F(xiàn)RQM子句中表格的次序也影響查詢的響應(yīng)速度。設(shè)學(xué)生檔案管理系統(tǒng)中還有一個與st udents結(jié)構(gòu)類似的表st udents1，students1 中2004年9月5號入學(xué)的學(xué)生有200個，若要查詢st udents和st udents1兩個表中2004年9月5號入學(xué)的學(xué)生，則SQ語句將返回包含所有1200個記錄的表，相應(yīng)的SQ查詢語句如下:SELECT Student_ nane, Sex, Fi nely_ .pl aceFRQM St udent , St udent 1WHERE i ndat a=’5- SEP- 2004'在具體的執(zhí)行過程中，先對表格students進(jìn)行選擇運(yùn)算,得到1000個記錄的臨時表，再對st udents1進(jìn)行選擇運(yùn)算,然后將得到的符合條件的200個記錄插入到1000個記錄的臨時表中,需200次插入運(yùn)算。若將FROM語句后面的表次序?qū)Q，即為“FROM St udents1, St udent ds”,則先得到-一個200行的臨時表后，需要做1000次的插入運(yùn)算，其工作量遠(yuǎn)大于第-種情況，故可將返回較少記錄的表格排在后面，較多記錄的表格排在前面，以便減少插入運(yùn)算。(五)對多個表進(jìn)行多條件查詢時，使用相關(guān)子查詢聯(lián)結(jié)運(yùn)算是數(shù)據(jù)庫查詢中經(jīng)常使用的運(yùn)算之-，由于聯(lián)結(jié)運(yùn)算會產(chǎn)生-個很大的臨時表格，特別是多個表的聯(lián)結(jié)運(yùn)算，不優(yōu)化的SQ查詢語句會產(chǎn)生巨大的運(yùn)算量，且增加對內(nèi)存的需求，嚴(yán)重時可使軟件在執(zhí)行中出現(xiàn)異常。例如查詢2004年9月5號入學(xué)的學(xué)生成績,利用下面兩個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é)報兩個查詢語句返回同樣的數(shù)據(jù)結(jié)果，但查詢響應(yīng)時間不同，在1)中先進(jìn)行兩個表格的聯(lián)結(jié)運(yùn)算,得到一個10行的臨時表格，再進(jìn)行選擇運(yùn)算，得到所需的結(jié)果。而2)中先對表格St udents進(jìn)行選擇運(yùn)算，立刻刪除了大量多余數(shù)據(jù)，得到一個僅有1000行的臨時表(由前可知)，再對這個表與g進(jìn)行聯(lián)結(jié)運(yùn)算而得到最終結(jié)果?？梢?)中的計算量要比2)大得多，這是因為在1)中的聯(lián)結(jié)運(yùn)算的兩個表都是10°行，選擇運(yùn)算也是在巨大的表上進(jìn)行，而2)中的聯(lián)結(jié)運(yùn)算僅對一-個1000行的表進(jìn)行。可見在對多個表進(jìn)行多條件查詢時，使用相關(guān)子查詢可以使查詢優(yōu)化。(六)適當(dāng)建立關(guān)聯(lián)統(tǒng)計表或臨時表，減少對大型表的直接查詢把表的一個子集進(jìn)行排序并創(chuàng)建臨時表，避免了對數(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如果這個查詢要被執(zhí)行數(shù)次，可以把所有及格的學(xué)生找出來放在-個臨時表文件中，并按學(xué)生的名字進(jìn)行排序: .FORMSt udents, GWKERE St udents. St udent. i d=G. student. id AND G . score>60INTO TEMP Student. _wi th Score :然后按下面的方式在臨時表中查詢: :SELECT St udents. Student_ nane, g . score, St udents. Post codeFORMSt udent_ with_ ScoreWHERE Post_ Code>’ 5100'臨時表中的行比主表少，而且物理順序就是所要求的順序，減少了磁盤1/0所以查詢效率大大增加。注意:臨時表創(chuàng)建后不會反映主表的修改。三、結(jié)束語.查詢優(yōu)化是數(shù)據(jù)庫應(yīng)用程序設(shè)計中一個關(guān)鍵性的問題，本文是作者在多年的教學(xué)中總結(jié)出的一些方法，相信對相關(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.