SQL Server數(shù)據(jù)庫性能優(yōu)化技術(shù)
更新時(shí)間:2007年06月14日 00:00:00 作者:
2.索引選擇
對(duì)于每個(gè)可優(yōu)化的子句,優(yōu)化器都查看數(shù)據(jù)庫系統(tǒng)表,以確定是否有相關(guān)的索引能用于訪問數(shù)據(jù)。只有當(dāng)索引中的列
的1個(gè)前綴與查詢子句中的列完全匹配時(shí),這個(gè)索引才被認(rèn)為是有用的。因?yàn)樗饕歉鶕?jù)列的順序構(gòu)造的,所以要求匹配是
精確的匹配。對(duì)于分簇索引,原來的數(shù)據(jù)也是根據(jù)索引列順序排序的。想用索引的次要列訪問數(shù)據(jù),就像想在電話本中查
找所有姓為某個(gè)姓氏的條目一樣,排序基本上沒有什么用,因?yàn)槟氵€是得查看每一行以確定它是否符合條件。如果1個(gè)子句
有可用的索引,那么優(yōu)化器就會(huì)為它確定選擇性。
所以在設(shè)計(jì)過程中,要根據(jù)查詢?cè)O(shè)計(jì)準(zhǔn)則仔細(xì)檢查所有的查詢,以查詢的優(yōu)化特點(diǎn)為基礎(chǔ)設(shè)計(jì)索引。
(1)比較窄的索引具有比較高的效率。對(duì)于比較窄的索引來說,每頁上能存放較多的索引行,而且索引的級(jí)別也較少。
所以,緩存中能放置更多的索引頁,這樣也減少了I/O操作。
(2)SQL Server優(yōu)化器能分析大量的索引和合并可能性。所以與較少的寬索引相比,較多的窄索引能向優(yōu)化器提供更多
的選擇。但是不要保留不必要的索引,因?yàn)樗鼈儗⒃黾哟鎯?chǔ)和維護(hù)的開支。對(duì)于復(fù)合索引、組合索引或多列索引,SQL
Server優(yōu)化器只保留最重要的列的分布統(tǒng)計(jì)信息,這樣,索引的第1列應(yīng)該有很大的選擇性。
(3)表上的索引過多會(huì)影響UPDATE、INSERT和DELETE的性能,因?yàn)樗械乃饕急仨氉鱿鄳?yīng)的調(diào)整。另外,所有的分頁
操作都被記錄在日志中,這也會(huì)增加I/O操作?! ?nbsp;
(4)對(duì)1個(gè)經(jīng)常被更新的列建立索引,會(huì)嚴(yán)重影響性能。
(5)由于存儲(chǔ)開支和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ì)于小表來說表掃描往往更快而且費(fèi)用低。
(9)與“ORDER BY”或“GROUP BY”一起使用的列一般適于做分族索引。如果“ORDER BY”命令中用到的列上有分簇索
引,那么就不會(huì)再生成1個(gè)工作表了,因?yàn)樾幸呀?jīng)排序了?!癎ROUP 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è)基于它們的訪問計(jì)劃的處理費(fèi)用時(shí),優(yōu)化器開始執(zhí)行合并選擇。合并選
擇被用來找出一個(gè)用于合并子句訪問計(jì)劃的有效順序。為了做到這一點(diǎn),優(yōu)化器比較子句的不同排序,然后選出從物理磁
盤I/O的角度看處理費(fèi)用最低的合并計(jì)劃。因?yàn)樽泳浣M合的數(shù)量會(huì)隨著查詢的復(fù)雜度極快地增長(zhǎng),SQL Server查詢優(yōu)化器使
用樹剪枝技術(shù)來盡量減少這些比較所帶來的開支。當(dāng)這個(gè)合并選擇階段結(jié)束時(shí),SQL Server查詢優(yōu)化器已經(jīng)生成了1個(gè)基于
費(fèi)用的查詢執(zhí)行計(jì)劃,這個(gè)計(jì)劃充分利用了可用的索引,并以最小的系統(tǒng)開支和良好的執(zhí)行性能訪問原來的數(shù)據(jù)。
3.2 高效的查詢選擇
從以上查詢優(yōu)化的3個(gè)階段不難看出,設(shè)計(jì)出物理I/O和邏輯I/O最少的方案并掌握好處理器時(shí)間和I/O時(shí)間的平衡,是
高效查詢?cè)O(shè)計(jì)的主要目標(biāo)。也就是說,希望設(shè)計(jì)出這樣的查詢:充分利用索引、磁盤讀寫最少、最高效地利用了內(nèi)存和CPU
資源。
以下建議是從SQL Server優(yōu)化器的優(yōu)化策略中總結(jié)出來的,對(duì)于設(shè)計(jì)高效的查詢是很有幫助的。
1.如果有獨(dú)特的索引,那么帶有“=”操作符的WHERE子句性能最好,其次是封閉的區(qū)間(范圍),再其次是開放的區(qū)
間。
2.從數(shù)據(jù)庫訪問的角度看,含有不連續(xù)連接詞(OR和IN)的WHERE子句一般來說性能不會(huì)太好。所以,優(yōu)化器可能會(huì)采用
R策略,這種策略會(huì)生成1個(gè)工作表,其中含有每個(gè)可能匹配的執(zhí)行的標(biāo)識(shí)符,優(yōu)化器把這些行標(biāo)志符(頁號(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ì)于優(yōu)化器的索引選擇來說沒有什么用處。因?yàn)檫@樣的子句是排斥性的,而不是
包括性的,所以在掃描整個(gè)原來數(shù)據(jù)表之前無法確定子句的選擇性。
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)化,可以改寫上面的條件表達(dá)式為:
paycheck<36000/12 or lastname like “L%”
5.WHERE子句中的本地變量被認(rèn)為是不被優(yōu)化器知道和考慮的,例外的情況是定義為儲(chǔ)備過程輸入?yún)?shù)的變量。
6.如果沒有包含合并子句的索引,那么優(yōu)化器構(gòu)造1個(gè)工作表以存放合并中最小的表中的行。然后再在這個(gè)表上構(gòu)造1
個(gè)分簇索引以完成一個(gè)高效的合并。這種作法的代價(jià)是工作表的生成和隨后的分族索引的生成,這個(gè)過程叫
REFORMATTING。 所以應(yīng)該注意RAM中或磁盤上的數(shù)據(jù)庫tempdb的大小(除了SELECT INTO語句)。另外,如果這些類型的
操作是很常見的,那么把tempdb放在RAM中對(duì)于提高性能是很有好處的。
4 性能優(yōu)化的其他考慮
上面列出了影響SQL Server的一些主要因素,實(shí)際上遠(yuǎn)不止這些。操作系統(tǒng)的影響也很大,在Windows NT下,文件系
統(tǒng)的選擇、網(wǎng)絡(luò)協(xié)議、開啟的服務(wù)、SQL Server的優(yōu)先級(jí)等選項(xiàng)也不同程度上影響了SQL Server的性能。
影響性能的因素是如此的多,而應(yīng)用又各不相同,找出1個(gè)通用的優(yōu)化方案是不現(xiàn)實(shí)的,在系統(tǒng)開發(fā)和維護(hù)的過程中必
須針對(duì)運(yùn)行的情況,不斷加以調(diào)整。事實(shí)上,絕大部分的優(yōu)化和調(diào)整工作是在與客戶端獨(dú)立的服務(wù)器上進(jìn)行的,因此也是
現(xiàn)實(shí)可行的。
您可能感興趣的文章:
- sqlserver數(shù)據(jù)庫規(guī)模膨脹太大怎么優(yōu)化
- SQL Server 聚焦存儲(chǔ)過程性能優(yōu)化、數(shù)據(jù)壓縮和頁壓縮提高IO性能方法(一)
- 日常收集整理SqlServer數(shù)據(jù)庫優(yōu)化經(jīng)驗(yàn)和注意事項(xiàng)
- SQL server 2008 數(shù)據(jù)庫優(yōu)化常用腳本
- sqlserver數(shù)據(jù)庫優(yōu)化解析(圖文剖析)
- sqlserver關(guān)于分頁存儲(chǔ)過程的優(yōu)化【讓數(shù)據(jù)庫按我們的意思執(zhí)行查詢計(jì)劃】
- SQL Server數(shù)據(jù)庫的高性能優(yōu)化經(jīng)驗(yàn)總結(jié)
- 開啟SQLSERVER數(shù)據(jù)庫緩存依賴優(yōu)化網(wǎng)站性能
- SQL Server 數(shù)據(jù)庫優(yōu)化
- SQL Server 數(shù)據(jù)太多優(yōu)化的方法
相關(guān)文章
JetBrains出品一款好用到爆的DataGrip數(shù)據(jù)庫工具使用入門
這篇文章主要介紹了JetBrains出品一款好用到爆的DataGrip數(shù)據(jù)庫工具使用入門,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-01-01DataGrip 連接 HiveServer2 報(bào)錯(cuò)的問題
這篇文章主要介紹了DataGrip 連接 HiveServer2 報(bào)錯(cuò)的問題,本文通過圖文實(shí)例相結(jié)合給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-09-09OLEDB和ODBC的區(qū)別(優(yōu)缺點(diǎn))
ODBC是一種連接數(shù)據(jù)庫的開放標(biāo)準(zhǔn),OLEDB(對(duì)象鏈接和嵌入數(shù)據(jù)庫)位于ODBC層與應(yīng)用程序之間. 在你的ASP頁面里,ADO是位于OLEDB之上的應(yīng)用程序. 你的ADO調(diào)用先被送到OLEDB,然后再交由ODBC處理2012-09-09分布式數(shù)據(jù)存儲(chǔ)系統(tǒng)的三要素
大家好,本篇文章主要講的是分布式數(shù)據(jù)存儲(chǔ)系統(tǒng)的三要素,感興趣的同學(xué)趕快來看一看吧,對(duì)你有幫助的話記得收藏一下,方便下次瀏覽2021-12-12為什么你不要收縮數(shù)據(jù)庫文件(國(guó)外翻譯)
這幾天查看了很多關(guān)于SQL SERVER收縮數(shù)據(jù)文件方面的文章,準(zhǔn)備寫一篇關(guān)于收縮日志方面的文章,但是突然有種沖動(dòng)將看過經(jīng)典的文章翻譯出來,需要的朋友可以參考下2018-03-03SQL中where子句與having子句的區(qū)別小結(jié)
這篇文章主要給大家介紹了關(guān)于SQL中where子句與having子句的區(qū)別的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-12-12Access轉(zhuǎn)換成SQL Server需要注意事項(xiàng)整理
很多朋友想用SQL2000數(shù)據(jù)庫的編程方法,但是卻又苦于自己是學(xué)ACCESS的,對(duì)SQL只是一點(diǎn)點(diǎn)的了解而已,這里我給大家提供以下參考---將ACCESS轉(zhuǎn)化成SQL2000的方法和注意事項(xiàng)2008-04-04