以下的文章主要是介紹SQL Server優(yōu)化如何通過(guò)3個(gè)階段來(lái)完成，查詢分析、索引選擇以及合并選擇。以下就是正文的主要內(nèi)容的詳細(xì)內(nèi)容的介紹，望會(huì)大家以后的學(xué)習(xí)或是工作中帶來(lái)很大的幫助。

1.查詢分析

在查詢分析階段，SQL Server優(yōu)化器查看每一個(gè)由正規(guī)查詢樹(shù)代表的子句，并判斷它是否能被優(yōu)化。SQL Server優(yōu)化一般會(huì)盡量?jī)?yōu)化那些限制掃描的子句。例如，搜索和/或合并子句。但是不是所有合法的SQL語(yǔ)法都可以分成可優(yōu)化的子句，如含有SQL不等關(guān)系符“<>”的子句。

因?yàn)?ldquo;<>”是1個(gè)排斥性的操作符，而不是1個(gè)包括性的操作符，所在掃描整個(gè)表之前無(wú)法確定子句的選擇范圍會(huì)有多大。當(dāng)1個(gè)關(guān)系型查詢中含有不可SQL Server優(yōu)化的子句時(shí)，執(zhí)行計(jì)劃用表掃描來(lái)訪問(wèn)查詢的這個(gè)部分，對(duì)于查詢樹(shù)中可優(yōu)化的SQL Server子句，則由優(yōu)化器執(zhí)行索引選擇。

2.索引選擇

對(duì)于每個(gè)可優(yōu)化的子句，優(yōu)化器都查看數(shù)據(jù)庫(kù)系統(tǒng)表，以確定是否有相關(guān)的索引能用于訪問(wèn)數(shù)據(jù)。只有當(dāng)索引中的列的1個(gè)前綴與查詢子句中的列完全匹配時(shí)，這個(gè)索引才被認(rèn)為是有用的。因?yàn)樗饕歉鶕?jù)列的順序構(gòu)造的，所以要求匹配是精確的匹配。

對(duì)于分簇索引，原來(lái)的數(shù)據(jù)也是根據(jù)索引列順序排序的。想用索引的次要列訪問(wèn)數(shù)據(jù)，就像想在電話本中查找所有姓為某個(gè)姓氏的條目一樣，排序基本上沒(méi)有什么用，因?yàn)槟氵€是得查看每一行以確定它是否符合條件。如果1個(gè)子句有可用的索引，那么優(yōu)化器就會(huì)為它確定選擇性。

所以在設(shè)計(jì)過(guò)程中，要根據(jù)查詢?cè)O(shè)計(jì)準(zhǔn)則仔細(xì)檢查所有的查詢，以查詢的優(yōu)化特點(diǎn)為基礎(chǔ)設(shè)計(jì)索引。

(1)比較窄的索引具有比較高的效率。對(duì)于比較窄的索引來(lái)說(shuō)，每頁(yè)上能存放較多的索引行，而且索引的級(jí)別也較少。所以，緩存中能放置更多的索引頁(yè)，這樣也減少了I/O操作。

(2)SQL Server優(yōu)化器能分析大量的索引和合并可能性。所以與較少的寬索引相比，較多的窄索引能向優(yōu)化器提供更多的選擇。但是不要保留不必要的索引，因?yàn)樗鼈儗⒃黾哟鎯?chǔ)和維護(hù)的開(kāi)支。對(duì)于復(fù)合索引、組合索引或多列索引，SQL Server優(yōu)化器只保留最重要的列的分布統(tǒng)計(jì)信息，這樣，索引的第1列應(yīng)該有很大的選擇性。

(3)表上的索引過(guò)多會(huì)影響UPDATE、INSERT和DELETE的性能，因?yàn)樗械乃饕急仨氉鱿鄳?yīng)的調(diào)整。另外，所有的分頁(yè)操作都被記錄在日志中，這也會(huì)增加I/O操作。

(4)對(duì)1個(gè)經(jīng)常被更新的列建立索引，會(huì)嚴(yán)重影響性能。

(5)由于存儲(chǔ)開(kāi)支和I/O操作方面的原因，較小的自組索引比較大的索引性能更好一些。但它的缺點(diǎn)是要維護(hù)自組的列。

(6)盡量分析出每一個(gè)重要查詢的使用頻度，這樣可以找出使用最多的索引，然后可以先對(duì)這些索引進(jìn)行適當(dāng)?shù)膬?yōu)化。

(7)查詢中的WHERE子句中的任何列都很可能是個(gè)索引列，因?yàn)閮?yōu)化器重點(diǎn)處理這個(gè)子句。

(8)對(duì)小于1個(gè)范圍的小型表進(jìn)行索引是不劃算的，因?yàn)閷?duì)于小表來(lái)說(shuō)表掃描往往更快而且費(fèi)用低。

(9)與“ORDER BY”或“GROUP BY”一起使用的列一般適于做分族索引。如果“ORDER BY”命令中用到的列上有分簇索引，那么就不會(huì)再生成1個(gè)工作表了，因?yàn)樾幸呀?jīng)排序了。“GROUP BY”命令則一定產(chǎn)生1個(gè)工作表。

(10)分簇索引不應(yīng)該構(gòu)造在經(jīng)常變化的列上，因?yàn)檫@會(huì)引起整行的移動(dòng)。在實(shí)現(xiàn)大型交易處理系統(tǒng)時(shí)，尤其要注意這一點(diǎn)，因?yàn)檫@些系統(tǒng)中數(shù)據(jù)往往是頻繁變化的。

3.合并選擇

當(dāng)索引選擇結(jié)束，并且所有的子句都有了一個(gè)基于它們的訪問(wèn)計(jì)劃的處理費(fèi)用時(shí)，優(yōu)化器開(kāi)始執(zhí)行合并選擇。合并選擇被用來(lái)找出一個(gè)用于合并子句訪問(wèn)計(jì)劃的有效順序。為了做到這一點(diǎn)，優(yōu)化器比較子句的不同排序，然后選出從物理磁盤(pán)I/O的角度看處理費(fèi)用***的合并計(jì)劃。

因?yàn)樽泳浣M合的數(shù)量會(huì)隨著查詢的復(fù)雜度極快地增長(zhǎng)，SQL Server查詢優(yōu)化器使用樹(shù)剪枝技術(shù)來(lái)盡量減少這些比較所帶來(lái)的開(kāi)支。當(dāng)這個(gè)合并選擇階段結(jié)束時(shí)，SQL Server優(yōu)化查詢優(yōu)化器已經(jīng)生成了1個(gè)基于費(fèi)用的查詢執(zhí)行計(jì)劃，這個(gè)計(jì)劃充分利用了可用的索引，并以最小的系統(tǒng)開(kāi)支和良好的執(zhí)行性能訪問(wèn)原來(lái)的數(shù)據(jù)。

3.2　高效的查詢選擇

從以上查詢優(yōu)化的3個(gè)階段不難看出，設(shè)計(jì)出物理I/O和邏輯I/O最少的方案并掌握好處理器時(shí)間和I/O時(shí)間的平衡，是高效查詢?cè)O(shè)計(jì)的主要目標(biāo)。也就是說(shuō)，希望設(shè)計(jì)出這樣的查詢:充分利用索引、磁盤(pán)讀寫(xiě)最少、***效地利用了內(nèi)存和CPU資源。

以下建議是從SQL Server優(yōu)化器的優(yōu)化策略中總結(jié)出來(lái)的，對(duì)于設(shè)計(jì)高效的查詢是很有幫助的。

1.如果有獨(dú)特的索引，那么帶有“=”操作符的WHERE子句性能***，其次是封閉的區(qū)間(范圍)，再其次是開(kāi)放的區(qū)間。

2.從數(shù)據(jù)庫(kù)訪問(wèn)的角度看，含有不連續(xù)連接詞(OR和IN)的WHERE子句一般來(lái)說(shuō)性能不會(huì)太好。所以，優(yōu)化器可能會(huì)采用R策略，這種策略會(huì)生成1個(gè)工作表，其中含有每個(gè)可能匹配的執(zhí)行的標(biāo)識(shí)符，優(yōu)化器把這些行標(biāo)志符(頁(yè)號(hào)和行號(hào))看做是指向1個(gè)表中匹配的行的“動(dòng)態(tài)索引”。優(yōu)化器只需掃描工作表，取出每一個(gè)行標(biāo)志符，再?gòu)臄?shù)據(jù)表中取得相應(yīng)的行，所以R策略的代價(jià)是生成工作表。

3.包含NOT、<>、或!　=的WHERE子句對(duì)于SQL Server優(yōu)化器的索引選擇來(lái)說(shuō)沒(méi)有什么用處。因?yàn)檫@樣的子句是排斥性的，而不是包括性的，所以在掃描整個(gè)原來(lái)數(shù)據(jù)表之前無(wú)法確定子句的選擇性。

4.限制數(shù)據(jù)轉(zhuǎn)換和串操作，優(yōu)化器一般不會(huì)根據(jù)WHERE子句中的表達(dá)式和數(shù)據(jù)轉(zhuǎn)換式生成索引選擇。例如:

paycheck * 12>36000 or substring(lastname,1,1)=“L” 

如果該表建立了針對(duì)paycheck和lastname的索引，就不能利用索引進(jìn)行優(yōu)化，可以改寫(xiě)上面的條件表達(dá)式為:

paycheck<36000/12 or lastname like “L%” 

5.WHERE子句中的本地變量被認(rèn)為是不被優(yōu)化器知道和考慮的，例外的情況是定義為儲(chǔ)備過(guò)程輸入?yún)?shù)的變量。

6.如果沒(méi)有包含合并子句的索引，那么優(yōu)化器構(gòu)造1個(gè)工作表以存放合并中最小的表中的行。然后再在這個(gè)表上構(gòu)造1個(gè)分簇索引以完成一個(gè)高效的合并。這種作法的代價(jià)是工作表的生成和隨后的分族索引的生成，這個(gè)過(guò)程叫REFORMATTING。

所以應(yīng)該注意RAM中或磁盤(pán)上的數(shù)據(jù)庫(kù)tempdb的大小(除了SELECT INTO語(yǔ)句)。另外，如果這些類型的操作是很常見(jiàn)的，那么把tempdb放在RAM中對(duì)于提高性能是很有好處的。

4　性能優(yōu)化的其他考慮

上面列出了影響SQL Server優(yōu)化的一些主要因素，實(shí)際上遠(yuǎn)不止這些。操作系統(tǒng)的影響也很大，在Windows NT下，文件系統(tǒng)的選擇、網(wǎng)絡(luò)協(xié)議、開(kāi)啟的服務(wù)、SQL Server的優(yōu)先級(jí)等選項(xiàng)也不同程度上影響了SQL Server的性能。

影響性能的因素是如此的多，而應(yīng)用又各不相同，找出1個(gè)通用的SQL Server優(yōu)化方案是不現(xiàn)實(shí)的，在系統(tǒng)開(kāi)發(fā)和維護(hù)的過(guò)程中必須針對(duì)運(yùn)行的情況，不斷加以調(diào)整。事實(shí)上，絕大部分的優(yōu)化和調(diào)整工作是在與客戶端獨(dú)立的服務(wù)器上進(jìn)行的，因此也是現(xiàn)實(shí)可行的。

【編輯推薦】

1. SQL Server合并復(fù)制性能的提高有哪些方案？
2. SQL Server Compact中的DLL文件與工具
3. SQL Server數(shù)據(jù)庫(kù)在安裝時(shí)的注意事項(xiàng)
4. SQL Server 2005數(shù)據(jù)庫(kù)安裝實(shí)例演示
5. SQL Server 2000全文檢索的使用方案描述