查詢(xún)優(yōu)化及SQL查詢(xún)語(yǔ)句的優(yōu)化方法

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