淺議SQL Server數據庫的優化

張 捷

[摘要]設計一個應用系統似乎并不難,但是要想使系統達到最優化的性能并不是一件容易的事。在開發工具、數據庫設計、應用程序的結構、查詢設計、接口選擇等方面有多種選擇,這取決于特定的應用需求以及開發隊伍的技能。以SQL Server為例,從后臺數據庫的角度討論應用程序性能優化技巧,并且給出一些有益的建議。

[關鍵詞]SQL Server數據庫優化高效索引

中圖分類號:TP3文獻標識碼:A文章編號:1671-7597(2009)1210114-01

一、使用良好的數據庫設計方案

(一)邏輯數據庫規范化問題。一般來說,邏輯數據庫設計會滿足規范化的前3級標準:第1規范:沒有重復的組或多值的列;第2規范:每個非關鍵字段必須依賴于主關鍵字,不能依賴于一個組合式主關鍵字的某些組成部分;第3規范:一個非關鍵字段不能依賴于另一個非關鍵字段。遵守這些規則的數據庫設計會產生較少的列和更多的表,因而也就減少了數據冗余,也減少了用于存儲數據的頁。

(二)生成物理數據庫。要想正確選擇基本物理實現策略,必須了解和利用好數據庫訪問格式和硬件資源的操作特點,特別是內存和磁盤子系統I/O。以下是一些常用技巧:與每個表列相關的數據類型應該反映數據所需的最小存儲空間,特別是對于被索引的列更是如此。用SQLServer段把一個頻繁使用的大表分割開,并放在多個單獨的智能型磁盤控制器的數據庫設備上,這樣也可以提高性能。因為有多個磁頭在查找,所以數據分離也能提高性能。

二、合理使用索引并簡化排序

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

在數據庫應用設計階段應當盡量簡化或避免對大型表進行重復的排序。當能夠利用索引自動以適當的次序產生輸出時,優化器就避免了排序這個步驟。為了避免不必要的排序,就要正確地增建索引,合理地合并數據庫表(盡管有時可能影響表的規范化,但相對于效率的提高是值得的)。如果排序不可避免,那么應當試圖簡化它,如縮小排序的列的范圍等。

三、設計高效的查詢

1.如果有獨特的索引,那么帶有“=”操作符的WHERE子句性能最好,其次是封閉的區間(范圍),再其次是開放的區間。

2.從數據庫訪問的角度看,含有不連續連接詞(OR和IN)的WHERE子句一般來說性能不會太好。所以,優化器可能會采用R策略,這種策略會生成1個工作表,其中含有每個可能匹配的執行的標識符,優化器把這些行標志符(頁號和行號)看做是指向1個表中匹配的行的“動態索引”。優化器只需掃描工作表,取出每一個行標志符,再從數據表中取得相應的行,所以R策略的代價是生成工作表。

3.包含NOT、<>、或!=的WHERE子句對于優化器的索引選擇來說沒有什么用處。因為這樣的子句是排斥性的,而不是包括性的,所以在掃描整個原來數據表之前無法確定子句的選擇性。

4.限制數據轉換和串操作,優化器一般不會根據WHERE子句中的表達式和數據轉換式生成索引選擇。例如:

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

如果該表建立了針對paycheck和lastname的索引,就不能利用索引進行優化,可以改寫上面的條件表達式為:

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

5.如果沒有包含合并子句的索引,那么優化器構造1個工作表以存放合并中最小的表中的行。然后再在這個表上構造1個分簇索引以完成一個高效的合并。這種作法的代價是工作表的生成和隨后的分族索引的生成,這個過程叫REFORMATTING。所以應該注意RAM中或磁盤上的數據庫tempdb的大小(除了SELECT INTO語句)。

四、創造良好的SQLServer數據庫應用環境

(一)操作系統。操作系統性能的好壞直接影響數據庫的使用性能,如果操作系統存在問題,如CPU過載、過度內存交換、磁盤I/O瓶頸等,在這種情況下,單純進行數據庫內部性能調整是不會改善系統性能的。我們可以通過WindowsNT的系統監視器(SystemMonitor)來監控各種設備,發現性能瓶頸。

(二)CPU。一種常見的性能問題就是缺乏處理能力。系統的處理能力是由系統的CPU數量、類型和速度決定的。如果系統沒有足夠的CPU處理能力,它就不能足夠快地處理事務以滿足需要。我們可以使用System Monitor確定CPU的使用率,如果以75%或更高的速率長時間運行,就可能碰到了CPU瓶頸問題,這時應該升級CPU。而當確定需要更強的處理能力,可以添加CPU或者用更快的CPU替換。

(三)內存。SQLServer可使用的內存量是SQLServer性能最關鍵因素之一。而內存同I/O子系統的關系也是一個非常重要的因素。例如,在I/O操作頻繁的系統中,SQLServer用來緩存數據的可用內存越多,必須執行的物理I/O也就越少。這是因為數據將從數據緩存中讀取而不是從磁盤讀取。同樣,內存量的不足會引起明顯的磁盤讀寫瓶頸,因為系統緩存能力不足會引起更多的物理磁盤I/O。

(四)I/O子系統。由I/O子系統發生的瓶頸問題是數據庫系統可能遇到的最常見的同硬件有關的問題。配置很差的I/O子系統引起性能問題的嚴重程度僅次于編寫很差的SQL語句。I/O子系統問題是這樣產生的,一個磁盤驅動器能夠執行的I/O操作是有限的,一般一個普通的磁盤驅動器每秒只能處理85次I/O操作,如果磁盤驅動器超載,到這些磁盤驅動器的I/O操作就要排隊,SQL的I/O延遲將很長。解決I/O子系統有關的問題也許是最容易的,多數情況下,增加磁盤驅動器就可以解決這個性能問題。

當然,影響性能的因素很多,而應用又各不相同,找出一個通用的優化方案是很困難的,只能是在系統開發和維護的過程中針對運行的具體情況,不斷加以調整。

參考文獻:

[1]邵遠山,基于DB2數據庫應用系統的性能優化[D].安徽大學,2004年.

[2]劉博,Oracle數據庫性能調整與優化[D].大連理工大學,2007年.

[3]李學強、羅省賢,基于ORACLE系統的數據庫性能優化設計[J].北京印刷學院學報,2006年06期.

作者簡介:

張捷,男,湖北省武穴市人,黃岡職業計算機學院計算機系教師,軟件設計師,研究方向:軟件設計、數據庫管理。