山西科技SHANXI SCIENCE AND TECHNOLOCY2008年第5期9月 20日出版(信息技術SQLServer的查詢優(yōu)化張興”李海芳2)(太原理工大學計算機與軟件學院)摘要:文章首先介紹了數(shù)據(jù)庫系統(tǒng)的性能問題,查詢處理和優(yōu)化,并在此基礎上對SQL Server200的查詢優(yōu)化實現(xiàn)進行了分析,比較了不同查詢語句的執(zhí)行速度和執(zhí)行計劃的差異。關鍵詞:系統(tǒng)性能;查詢處理及優(yōu)化;SQL Server 2000中圖分類號:TP309文獻標識碼:A文章編號:1004- 6429(2008)05 - 0053-03在關系數(shù)據(jù)庫管理系統(tǒng)中,性能是一個關鍵性的問題,待查詢優(yōu)化是為關系代數(shù)表達式的計算選擇最有效的查詢別是怎樣通過優(yōu)化使性能達到最佳狀態(tài)。關系數(shù)據(jù)庫系統(tǒng)對計劃的過程。一般步驟為:①把查詢轉(zhuǎn)化成某種內(nèi)部表示。②用戶的查詢要求隱藏查詢路徑和存取方法,用戶只需指出需要代數(shù)優(yōu)化。③物理優(yōu)化。④生成查詢計劃,選擇代價最小者。查詢的內(nèi)容,而由DBMS負責選擇具體的查詢路徑和存取方1)代數(shù)優(yōu)化。代數(shù)優(yōu)化是對查詢表達式進行等效變換,以法。為了提高和達到用戶接受的查詢性能,關系系統(tǒng)中查詢優(yōu)減少執(zhí)行開銷。等效變換的主要依據(jù)是一組等價變換規(guī)則。化就顯得十分必要。其一般準則有:1性能評估及 監(jiān)枧選擇運算盡可能先做,目的是為了減小中間關系;通過有效的網(wǎng)絡流量,磁盤IV0和CPU時間,可獲得最短在執(zhí)行連接操作前對文件適當進行預處理,如排序或在連的響應時間和最大的吞吐率,從而獲得最佳性能。其中響應時接屬性上建立索引;間是處理查詢并將結(jié)果集中的首行返回給用戶所需的時間長投影運算和選擇運算同時做，目的是避免重復掃描關系;度,而吞吐率是服務器在給定時間內(nèi)處理的查詢總數(shù)的度量將投影運算與其前面或后面的雙目運算結(jié)合起來,以堿少值。因此,響應時間的長短和吞吐率的大小直接影響到系統(tǒng)性掃描關系的次數(shù);能的高低。影響SQL Sever系統(tǒng)性能的主要因素有;①系統(tǒng)硬合并乘積和其后的選擇為連接運算;件資源:主要有CPU大小,硬件容量,內(nèi)存大小等。②操作系去掉多余的屬性?？赡軙r對每個葉結(jié)點或中間結(jié)點加上統(tǒng)環(huán)境:是Windows NT還是Windows 200③數(shù)據(jù)庫應用程必要的投影操作,以除去對后續(xù)操作沒有影響的屬性,臧少中序:數(shù)據(jù)庫邏輯設計與物理設計的質(zhì)量。④客戶端應用程序: .間結(jié)果;程序中事務控制、鎖機制的應用和游標的應用等。⑤網(wǎng)絡環(huán)找出公共子表達式。境:網(wǎng)絡帶寬網(wǎng)絡連接及數(shù)據(jù)傳輸速度等。2)物理優(yōu)化。物理優(yōu)化是查詢語句處理的詳細策略的選若要對上述幾個方面進行監(jiān)督,就必須了解正常工作條件擇,例如選擇執(zhí)行運算所采用的具體算法,選擇將使用的特定下的合理性能級別?？梢酝ㄟ^監(jiān)視下列3個方面來解決問題:索引等。在關系代數(shù)表達式的基礎上,執(zhí)行計劃進-步說明每sQL Sever存儲過程或用戶應用程序提交的批處理SQL語句，個運算的實現(xiàn)算法,各運算的執(zhí)行順序及是否采用流水線技術。用戶活動和硬件活動?；具\算有選擇運算、連接運算、排序運算分組運算集函數(shù)運2查詢處理及優(yōu)化算,其中連接運算的執(zhí)行效率是影響查詢效率的重要部分。2.1查詢處理連接運算的實現(xiàn)方法有:嵌套循環(huán)連接、塊嵌套循環(huán)連接、數(shù)據(jù)庫的主要功能是存儲管理好資料供用戶查詢使用。索引嵌套循環(huán)連接排序-歸并連接及散列連接。其中散列連查詢是數(shù)據(jù)庫中最基本最常用的操作。關系數(shù)據(jù)庫的查詢操接只適用于等值連接;當要進行連接的兩表都較大且都已按照作一般用非過程性語言描述,如SQL語言。查詢語句本身主要連接屬性排了序,則選擇排序-歸并算法將很有效;當要進行注重表達查詢條件和結(jié)果關系的組成,而具體查詢的實施過程連接的兩表,一個較大另一個較小,則可以選擇嵌套循環(huán)連接，和查找策略的選擇由DBMS負責完成。DBMS 在接到查詢后，并選擇較小的表作為外表。首先進行詞法分析和語法分析,產(chǎn)生查詢的內(nèi)部表示(- -般為2.3查詢優(yōu)化器查詢樹或查詢圖),然后由DBMS制定-一個執(zhí)行策略 ,包括如何進行SQL語句優(yōu)化的數(shù)據(jù)庫管理系統(tǒng)部分稱為查詢優(yōu)化訪向內(nèi)部文件,如何存儲中間結(jié)果,如何獲得查詢結(jié)果。器。查詢優(yōu)化器將查詢分解成它的組成部分并重新安排運行，概括起來查詢處理的基本步驟為:①語法分析與翻譯。②通過計算-些搜索條件或訪問路徑來確定要使用的最有效索優(yōu)化。③執(zhí)行。引,即查詢優(yōu)化器生成一個計劃來完成指定的任務。2.2 查詢優(yōu)化中國煤化工器有兩種優(yōu)化方法:基于規(guī)則基于成本的優(yōu)化,數(shù)據(jù)作者簡介:1)張興,男 ,1973年出生,太原理工大學計算.庫查詢HC N M H G,找出最低估算成本的機與軟件學院在讀碩士研究生,30009,山西省太原市執(zhí)行計劃，自動將向sQL提交的數(shù)據(jù)查詢操作進行優(yōu)化。收稿日期:2008-05- 16.3 SQL Server查詢優(yōu)化山西科技SHANXI SCIENCE AND TECHNOLOGY2008年第5期9月 20日出版SQL Server優(yōu)化可以通過3個階段完成:查詢分析,索引選相關嵌套查詢:seletx1.* from SP] x1撣和合并選擇。where exists (select # from SPJ x23.1查詢分析goup by x2. PNO在查詢分析階段,查詢優(yōu)化器查看每-一個由正規(guī)查詢語句having x1 .QTY = MAX(x2.QTY))。代表的子句,并判斷是否能被優(yōu)化。SQL Sever一般會盡量優(yōu)其執(zhí)行計劃如圖1?；切┫拗茠呙璧淖泳?。并不是所有合法的SQL語法都可以非相關嵌套查詢:selectx1.* from SPJ x1分成可優(yōu)化的子句,如含有SQL不等關系運算符的子句。當-where x1.QTY = some (select MAX(QTY)個關系型查詢中含有不可優(yōu)化的子句時,執(zhí)行計劃用表掃描來from SPJ x2訪問查詢的這個部分,對于查詢樹中可優(yōu)化的子句,則由優(yōu)化goup by x2. PNO)。器執(zhí)行索引選擇。.它們的執(zhí)行效率和執(zhí)行計劃也完全相同,說明在很多情況3.2 索引選擇下,系統(tǒng)對相關嵌套查詢和非相關嵌套查詢的實際內(nèi)部實現(xiàn)是對于每個可優(yōu)化的子句,查詢優(yōu)化器都會查看數(shù)據(jù)庫系統(tǒng)一樣的。表,以確定是否有相關的索引能用于訪問數(shù)據(jù)。只有當索引中的列的一個前綴與查詢子句中的列完全匹配時,這個索引才被認為是有用的。3.3 合并選擇在索引選擇結(jié)束并且所有子句都有一個基于其訪向計劃的處理成本時,查詢優(yōu)化器開始執(zhí)行合并選擇。它根據(jù)子句的-圈二不同排序,選出從物理磁盤U0的角度看處理成本最低的合并計劃。在合并選擇結(jié)束時,查詢優(yōu)化器已經(jīng)生成-一個基于成本的查詢執(zhí)行計劃,而且該計劃充分利用可用的索引，以最小的F日便+鎮(zhèn)用護電國o系統(tǒng)開支和良好的執(zhí)行性能訪問數(shù)據(jù)。4實例下面將通過幾個例子來分析SQL Server 2000對不同的查團1相關嵌套查詢執(zhí)行計劃圜詢語句的處理及其查詢執(zhí)行計劃。所使用的4張表定義如下:4.3復合條件查詢供應商(S)屬性有SNO.SNAME.STATUS、CITY.select distinctS. * from S,P where s. CITY= P. CITY and P.項目(J)屬性有JNOJNAME.CTTYPNAME= '螺絲刀'零件(P)屬性有PNO、PNAME、COLOR、 WEICHTCITY可以看到,系統(tǒng)并沒有按照語句中條件的順序來執(zhí)行,而以上三者的關系(SP)屬性有:SNO.PNO.JNO.QTY是先做選擇,后做連接,這正是系統(tǒng)對查詢進行優(yōu)化的結(jié)果。4.1嵌套查詢與非 嵌套查詢4.4引入視團1)非嵌套查詢: elect distinct J. * from J,SPJ where J. JNO=為查詢electx1. * from SPJ x1SPJ.JNO and J.CITY in(“北京','天津")。where exists (select # from SPJ 2嵌套查詢:electJ. * fom J where J.CITY in(“北京',‘天津') and J.JNO in( select JNO from SPI)。having x1.QTY= MAX(x2.QTY))從執(zhí)行情況看,系統(tǒng)在做優(yōu)化操作時將兩個查詢語句作為建立視圖:create view MAX(PNO, MAXQTY)同一種查詢進行。因此對于同一個查詢的不同查詢語句有時as selet PNO,MAX(QTY) from SPJ group by PNO; .系統(tǒng)內(nèi)部實現(xiàn)時可能會完全一樣。則可以在MAX上進行如下查詢:2)非嵌套查詢:ele x1. # from S xI,S x where x1.STATUSselect distinct SP]. * from SPJ, MAX where SPJ.Q1Y = MAX.= x2.STATUS and x2.CIY=‘北京'。MAXQTY。嵌套查詢:select s. # from s where s. STATUS in( selet其執(zhí)行計劃如圖2所示。STATUS from s where CTTY=‘北京')。可以看出它們的執(zhí)行效率和執(zhí)行計劃不同,前者用的是哈希匹配的方式,后者采用嵌套循環(huán)的方式。4.2相關嵌套查詢與 非相關嵌套查詢非相關嵌套查詢:electS. # frorm swhere s. STATUS in( selet STATUSfrom s where CTTY=‘北京')。.相關嵌套查詢:elctx1.* from Sx1where exits(select # from S x2中國煤化工.5.-where x2. CTTY=‘北京'and x1. STATUS= x2. STATUS)。YHCNMH G+%w結(jié)果是此相關嵌套查詢與非相關嵌套查詢執(zhí)行效率和執(zhí)(下轉(zhuǎn)第32頁)行計劃完全相同,再換-個查詢看結(jié)果如何:54●山西科技SHANXI SCIENCE AND TECHNOLOCY2008年第5期9月 20日出版設計職業(yè)生涯轉(zhuǎn)換,就是員工在不同崗位序列的縱向通道競 爭性,保證讓合適的人到合適的崗位上,提高晉升的有效性,轉(zhuǎn)換的橫向通道,以此來培養(yǎng)企業(yè)的中高層管理人員。從而降低企業(yè)領導人在員工提升問題上的主觀性,避免一個員2.2 建立與職業(yè)生涯規(guī)劃相配套的管理制度工的提升打擊-大片員工積極性,甚至導致有潛力的員工的離建立配套的管理制度并不是要把企業(yè)目前的管理制度全職。 通過建立有效的晉升制度,企業(yè)大部分中高級管理人員可部推翻,而是對現(xiàn)有的制度進- -步健全完善,有效支持職業(yè)生以通過內(nèi)部培養(yǎng)成長起來。涯規(guī)劃。應包括以下配套管理制度:2.3協(xié)調(diào)好組織規(guī)劃與員 工個人意愿之間的關系培訓制度。根據(jù)企業(yè)發(fā)展戰(zhàn)略及人力資源規(guī)劃,培訓制度企業(yè)發(fā)展對人員需求是職業(yè)生涯的落腳點,企業(yè)對此應有將職業(yè)生涯規(guī)劃與企業(yè)競爭力連接起來,支持員工長期持續(xù)發(fā)清醒的認識,企業(yè)指導 員工進行職業(yè)生涯規(guī)劃,其目的在于保展,實現(xiàn)培訓的個性化,讓員工主動培訓,通過員工能力發(fā)展椎證員工發(fā)展與企業(yè)發(fā)展需求相-致.使企業(yè)的規(guī)劃行為和各種動企業(yè)核心競爭力提升。投人更好的支持企業(yè)的發(fā)展,通過培訓,將員工打造成企業(yè)需考核制度。根據(jù)企業(yè)發(fā)展戰(zhàn)略與崗位工作需要,制定各崗要的人才。同時擺在員工面前的是預定的發(fā)展空間,員工在企位序列不同等級的能力標準,為考核能力評價提供基礎。同時業(yè)的發(fā)展需求指導下來調(diào)整個人發(fā)展方向,通過提升員工個人將職業(yè)生涯中的能力評價、溝通與制定發(fā)展計劃與績效管理中能 力促進企業(yè)的發(fā)展。的績效反饋.績效改善計劃有機地結(jié)合起來,既提高了管理效2.4 建 立合理有效的用人機制率,減少了重復勞動,又使員I能力發(fā)展與實際工作績效相互職業(yè)生涯規(guī)劃“雙贏‘效果的實現(xiàn),根本上依賴于用人機制印證,提高了制定工作計劃的針對性、有效性。的調(diào)整。企業(yè)用人機制調(diào)整的關鍵是要在企業(yè)內(nèi)形成以能力薪酬制度。通過考核,員工的能力評價應體現(xiàn)到薪酬中,為基礎的人力資源管理氛圍,建立競爭機制.選拔機制、激勵機給予相應的激勵。但應改善薪酬結(jié)構,增加薪酬寬帶,使同一制,打破“ 官本位”和“論資排輩”,讓員工建立個人發(fā)展與企業(yè)薪酬等級內(nèi)容納更多的員工能力差異,使員工的能力發(fā)展在薪發(fā)展的正相關連接,愿意與企業(yè)共同發(fā)展。酬制度中得以體現(xiàn)。參考文獻晉升制度。企業(yè)實行職業(yè)生涯規(guī)劃是與員工達成一個心[1] 趙西萍,等. 組織與人力資源管理[M].西安:西安交通大理契約,企業(yè)愿意為員工提供更大的職業(yè)發(fā)展空間,通過完善學出版社,999晉升制度,建立合理的甄選程序,提高晉升的公開性、公正性、[2] 黃雛德.人力資源管理[M].北京:高等教育出版社,00.Career Management of the Staff in Small and Medium - sized enterprisesLi Jinpoees measures to build a conplete career planning sytem, build a management system supporting career planning, coordinate the relations be-trween the organization panning and he will of the sa and build a reasonable and efective employment syatem.KEYWORDS: smal and medium - sized enterprises; saf; career management(上接第54頁)有時會生成完全相同的執(zhí)行計劃,有時又會選取不同的執(zhí)行計可以看出圖2與圖1完全相同，即它與不使用視圖的執(zhí)行劃,這受到諸多因素的影響,其中優(yōu)化的代價也是需要考慮的。計劃一樣,表明視圖只是一組sQL語句,當執(zhí)行時便去調(diào)用這組查詢語句,把在視圖中的查詢語句轉(zhuǎn)化為相應基本表上的查[1] 王珊,薩師煊.數(shù)據(jù)庫系統(tǒng)概論[M].北京:高等教育出版社,2006.5結(jié)束語[2]王俊偉,史創(chuàng)明,等. SQL Server 2000中文版數(shù)據(jù)庫管理與由于-一個查詢往往有多種執(zhí)行策略,策略好壞的差別也很應用[M].北京:清華大學出版社,2006.大,因此系統(tǒng)需要從中選擇- -個較好的執(zhí)行策略 ,即查詢優(yōu)化。[3] 姚春龍, 丁春欣,姜翠霞.數(shù)據(jù)庫系統(tǒng)基礎教程[M].北在SQL Sever 2000中,系統(tǒng)會根據(jù)實際情況對查詢進行優(yōu)化，京:北京航空航天大學出版社,2003.生成較優(yōu)的執(zhí)行計劃;而對于同一查詢的不同表示形式,系統(tǒng)Query Optimization of SQL ServerZhang Xing,Li Haifang中國煤化工ABSTRACT: After 8n itoduction of the performance, query and opinindYHC N M H G' analys the query opi-mizaion of SQL Server 2000 and compares the diferences of the running speed and. quco.KEYWORDS: systrm perdomance; query and optimization; SQL Server 2000