SQL Server的查詢(xún)優(yōu)化

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