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