淺議SQL Server數(shù)據(jù)庫(kù)的優(yōu)化

張 捷

[摘要]設(shè)計(jì)一個(gè)應(yīng)用系統(tǒng)似乎并不難,但是要想使系統(tǒng)達(dá)到最優(yōu)化的性能并不是一件容易的事。在開(kāi)發(fā)工具、數(shù)據(jù)庫(kù)設(shè)計(jì)、應(yīng)用程序的結(jié)構(gòu)、查詢(xún)?cè)O(shè)計(jì)、接口選擇等方面有多種選擇,這取決于特定的應(yīng)用需求以及開(kāi)發(fā)隊(duì)伍的技能。以SQL Server為例,從后臺(tái)數(shù)據(jù)庫(kù)的角度討論應(yīng)用程序性能優(yōu)化技巧,并且給出一些有益的建議。

[關(guān)鍵詞]SQL Server數(shù)據(jù)庫(kù)優(yōu)化高效索引

中圖分類(lèi)號(hào):TP3文獻(xiàn)標(biāo)識(shí)碼:A文章編號(hào):1671-7597(2009)1210114-01

一、使用良好的數(shù)據(jù)庫(kù)設(shè)計(jì)方案

(一)邏輯數(shù)據(jù)庫(kù)規(guī)范化問(wèn)題。一般來(lái)說(shuō),邏輯數(shù)據(jù)庫(kù)設(shè)計(jì)會(huì)滿(mǎn)足規(guī)范化的前3級(jí)標(biāo)準(zhǔn):第1規(guī)范:沒(méi)有重復(fù)的組或多值的列;第2規(guī)范:每個(gè)非關(guān)鍵字段必須依賴(lài)于主關(guān)鍵字,不能依賴(lài)于一個(gè)組合式主關(guān)鍵字的某些組成部分;第3規(guī)范:一個(gè)非關(guān)鍵字段不能依賴(lài)于另一個(gè)非關(guān)鍵字段。遵守這些規(guī)則的數(shù)據(jù)庫(kù)設(shè)計(jì)會(huì)產(chǎn)生較少的列和更多的表,因而也就減少了數(shù)據(jù)冗余,也減少了用于存儲(chǔ)數(shù)據(jù)的頁(yè)。

(二)生成物理數(shù)據(jù)庫(kù)。要想正確選擇基本物理實(shí)現(xiàn)策略,必須了解和利用好數(shù)據(jù)庫(kù)訪問(wèn)格式和硬件資源的操作特點(diǎn),特別是內(nèi)存和磁盤(pán)子系統(tǒng)I/O。以下是一些常用技巧:與每個(gè)表列相關(guān)的數(shù)據(jù)類(lèi)型應(yīng)該反映數(shù)據(jù)所需的最小存儲(chǔ)空間,特別是對(duì)于被索引的列更是如此。用SQLServer段把一個(gè)頻繁使用的大表分割開(kāi),并放在多個(gè)單獨(dú)的智能型磁盤(pán)控制器的數(shù)據(jù)庫(kù)設(shè)備上,這樣也可以提高性能。因?yàn)橛卸鄠€(gè)磁頭在查找,所以數(shù)據(jù)分離也能提高性能。

二、合理使用索引并簡(jiǎn)化排序

索引是數(shù)據(jù)庫(kù)中重要的數(shù)據(jù)結(jié)構(gòu),它的根本目的就是提高查詢(xún)效率。索引的使用要恰到好處,其使用原則如下:在經(jīng)常進(jìn)行連接,但是沒(méi)有指定為外鍵的列上建立索引,而不經(jīng)常連接的字段則由優(yōu)化器自動(dòng)生成索引;在頻繁進(jìn)行排序或分組(即進(jìn)行g(shù)roupby或orderby操作)的列上建立索引;在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立索引,在不同值少的列上不要建立索引。比如在雇員表的“性別”列上只有“男”與“女”兩個(gè)不同值,因此就無(wú)必要建立索引。如果建立索引不但不會(huì)提高查詢(xún)效率,反而會(huì)嚴(yán)重降低更新速度。如果待排序的列有多個(gè),可以在這些列上建立復(fù)合索引。

在數(shù)據(jù)庫(kù)應(yīng)用設(shè)計(jì)階段應(yīng)當(dāng)盡量簡(jiǎn)化或避免對(duì)大型表進(jìn)行重復(fù)的排序。當(dāng)能夠利用索引自動(dòng)以適當(dāng)?shù)拇涡虍a(chǎn)生輸出時(shí),優(yōu)化器就避免了排序這個(gè)步驟。為了避免不必要的排序,就要正確地增建索引,合理地合并數(shù)據(jù)庫(kù)表(盡管有時(shí)可能影響表的規(guī)范化,但相對(duì)于效率的提高是值得的)。如果排序不可避免,那么應(yīng)當(dāng)試圖簡(jiǎn)化它,如縮小排序的列的范圍等。

三、設(shè)計(jì)高效的查詢(xún)

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ì)于優(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.如果沒(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ǔ)句)。

四、創(chuàng)造良好的SQLServer數(shù)據(jù)庫(kù)應(yīng)用環(huán)境

(一)操作系統(tǒng)。操作系統(tǒng)性能的好壞直接影響數(shù)據(jù)庫(kù)的使用性能,如果操作系統(tǒng)存在問(wèn)題,如CPU過(guò)載、過(guò)度內(nèi)存交換、磁盤(pán)I/O瓶頸等,在這種情況下,單純進(jìn)行數(shù)據(jù)庫(kù)內(nèi)部性能調(diào)整是不會(huì)改善系統(tǒng)性能的。我們可以通過(guò)WindowsNT的系統(tǒng)監(jiān)視器(SystemMonitor)來(lái)監(jiān)控各種設(shè)備,發(fā)現(xiàn)性能瓶頸。

(二)CPU。一種常見(jiàn)的性能問(wèn)題就是缺乏處理能力。系統(tǒng)的處理能力是由系統(tǒng)的CPU數(shù)量、類(lèi)型和速度決定的。如果系統(tǒng)沒(méi)有足夠的CPU處理能力,它就不能足夠快地處理事務(wù)以滿(mǎn)足需要。我們可以使用System Monitor確定CPU的使用率,如果以75%或更高的速率長(zhǎng)時(shí)間運(yùn)行,就可能碰到了CPU瓶頸問(wèn)題,這時(shí)應(yīng)該升級(jí)CPU。而當(dāng)確定需要更強(qiáng)的處理能力,可以添加CPU或者用更快的CPU替換。

(三)內(nèi)存。SQLServer可使用的內(nèi)存量是SQLServer性能最關(guān)鍵因素之一。而內(nèi)存同I/O子系統(tǒng)的關(guān)系也是一個(gè)非常重要的因素。例如,在I/O操作頻繁的系統(tǒng)中,SQLServer用來(lái)緩存數(shù)據(jù)的可用內(nèi)存越多,必須執(zhí)行的物理I/O也就越少。這是因?yàn)閿?shù)據(jù)將從數(shù)據(jù)緩存中讀取而不是從磁盤(pán)讀取。同樣,內(nèi)存量的不足會(huì)引起明顯的磁盤(pán)讀寫(xiě)瓶頸,因?yàn)橄到y(tǒng)緩存能力不足會(huì)引起更多的物理磁盤(pán)I/O。

(四)I/O子系統(tǒng)。由I/O子系統(tǒng)發(fā)生的瓶頸問(wèn)題是數(shù)據(jù)庫(kù)系統(tǒng)可能遇到的最常見(jiàn)的同硬件有關(guān)的問(wèn)題。配置很差的I/O子系統(tǒng)引起性能問(wèn)題的嚴(yán)重程度僅次于編寫(xiě)很差的SQL語(yǔ)句。I/O子系統(tǒng)問(wèn)題是這樣產(chǎn)生的,一個(gè)磁盤(pán)驅(qū)動(dòng)器能夠執(zhí)行的I/O操作是有限的,一般一個(gè)普通的磁盤(pán)驅(qū)動(dòng)器每秒只能處理85次I/O操作,如果磁盤(pán)驅(qū)動(dòng)器超載,到這些磁盤(pán)驅(qū)動(dòng)器的I/O操作就要排隊(duì),SQL的I/O延遲將很長(zhǎng)。解決I/O子系統(tǒng)有關(guān)的問(wèn)題也許是最容易的,多數(shù)情況下,增加磁盤(pán)驅(qū)動(dòng)器就可以解決這個(gè)性能問(wèn)題。

當(dāng)然,影響性能的因素很多,而應(yīng)用又各不相同,找出一個(gè)通用的優(yōu)化方案是很困難的,只能是在系統(tǒng)開(kāi)發(fā)和維護(hù)的過(guò)程中針對(duì)運(yùn)行的具體情況,不斷加以調(diào)整。

參考文獻(xiàn):

[1]邵遠(yuǎn)山,基于DB2數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的性能優(yōu)化[D].安徽大學(xué),2004年.

[2]劉博,Oracle數(shù)據(jù)庫(kù)性能調(diào)整與優(yōu)化[D].大連理工大學(xué),2007年.

[3]李學(xué)強(qiáng)、羅省賢,基于ORACLE系統(tǒng)的數(shù)據(jù)庫(kù)性能優(yōu)化設(shè)計(jì)[J].北京印刷學(xué)院學(xué)報(bào),2006年06期.

作者簡(jiǎn)介:

張捷,男,湖北省武穴市人,黃岡職業(yè)計(jì)算機(jī)學(xué)院計(jì)算機(jī)系教師,軟件設(shè)計(jì)師,研究方向:軟件設(shè)計(jì)、數(shù)據(jù)庫(kù)管理。