程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> SQL Server 數據庫優化

SQL Server 數據庫優化

編輯:更多數據庫知識

在開發工具、數據庫設計、應用程序的結構、查詢設計、接口選擇等方面有多種選擇,這取決於特定的應用需求以及開發隊伍的技能。本文以SQL Server為例,從後台數據庫的角度討論應用程序性能優化技巧,並且給出了一些有益的建議。
1 數據庫設計

  要在良好的SQL Server方案中實現最優的性能,最關鍵的是要有1個很好的數據庫設計方案。在實際工作中,許多SQL Server方案往往是由於數據庫設計得不好導致性能很差。所以,要實現良好的數據庫設計就必須考慮這些問題。

  1.1 邏輯庫規范化問題

  一般來說,邏輯數據庫設計會滿足規范化的前3級標准:

  1.第1規范:沒有重復的組或多值的列。

  2.第2規范:每個非關鍵字段必須依賴於主關鍵字,不能依賴於1個組合式主關鍵字的某些組成部分。

  3.第3規范:1個非關鍵字段不能依賴於另1個非關鍵字段。

  遵守這些規則的設計會產生較少的列和更多的表,因而也就減少了數據冗余,也減少了用於存儲數據的頁。但表關系也許需要通過復雜的合並來處理,這樣會降低系統的性能。某種程度上的非規范化可以改善系統的性能,非規范化過程可以根據性能方面不同的考慮用多種不同的方法進行,但以下方法經實踐驗證往往能提高性能。

  1.如果規范化設計產生了許多4路或更多路合並關系,就可以考慮在數據庫實體(表)中加入重復屬性(列)。

  2.常用的計算字段(如總計、最大值等)可以考慮存儲到數據庫實體中。

  比如某一個項目的計劃管理系統中有計劃表,其字段為:項目編號、年初計劃、二次計劃、調整計劃、補列計劃…,而計劃總數(年初計劃+二次計劃+調整計劃+補列計劃)是用戶經常需要在查詢和報表中用到的,在表的記錄量很大時,有必要把計劃總數作為1個獨立的字段加入到表中。這裡可以采用觸發器以在客戶端保持數據的一致性。

  3.重新定義實體以減少外部屬性數據或行數據的開支。相應的非規范化類型是:

  (1)把1個實體(表)分割成2個表(把所有的屬性分成2組)。這樣就把頻繁被訪問的數據同較少被訪問的數據分開了。這種方法要求在每個表中復制首要關鍵字。這樣產生的設計有利於並行處理,並將產生列數較少的表。

  (2)把1個實體(表)分割成2個表(把所有的行分成2組)。這種方法適用於那些將包含大量數據的實體(表)。在應用中常要保留歷史記錄,但是歷史記錄很少用到。因此可以把頻繁被訪問的數據同較少被訪問的歷史數據分開。而且如果數據行是作為子集被邏輯工作組(部門、銷售分區、地理區域等)訪問的,那麼這種方法也是很有好處的。

  1.2 生成物理數據庫

  要想正確選擇基本物理實現策略,必須懂得數據庫訪問格式和硬件資源的操作特點,主要是內存和磁盤子系統I/O。這是一個范圍廣泛的話題,但以下的准則可能會有所幫助。

  1.與每個表列相關的數據類型應該反映數據所需的最小存儲空間,特別是對於被索引的列更是如此。比如能使用smallint類型就不要用integer類型,這樣索引字段可以被更快地讀取,而且可以在1個數據頁上放置更多的數據行,因而也就減少了I/O操作。

  2.把1個表放在某個物理設備上,再通過SQL Server段把它的不分簇索引放在1個不同的物理設備上,這樣能提高性能。尤其是系統采用了多個智能型磁盤控制器和數據分離技術的情況下,這樣做的好處更加明顯。

  3.用SQL Server段把一個頻繁使用的大表分割開,並放在2個單獨的智能型磁盤控制器的數據庫設備上,這樣也可以提高性能。因為有多個磁頭在查找,所以數據分離也能提高性能。

  4.用SQL Server段把文本或圖像列的數據存放在1個單獨的物理設備上可以提高性能。1個專用的智能型的控制器能進一步提高性能。

  2 與SQL Server相關的硬件系統

  與SQL Server有關的硬件設計包括系統處理器、內存、磁盤子系統和網絡,這4個部分基本上構成了硬件平台,Windows NT和SQL Server運行於其上。

  2.1 系統處理器(CPU)

  根據自己的具體需要確定CPU結構的過程就是估計在硬件平台上占用CPU的工作量的過程。從以往的經驗看,CPU配置最少應是1個80586/100處理器。如果只有2~3個用戶,這就足夠了,但如果打算支持更多的用戶和關鍵應用,推薦采用Pentium Pro或PⅡ級CPU。

  2.2 內存(RAM)

  為SQL Server方案確定合適的內存設置對於實現良好的性能是至關重要的。SQL Server用內存做過程緩存、數據和索引項緩存、靜態服務器開支和設置開支。SQL Server最多能利用2GB虛擬內存,這也是最大的設置值。還有一點必須考慮的是Windows NT和它的所有相關的服務也要占用內存。

  Windows NT為每個WIN32應用程序提供了4GB的虛擬地址空間。這個虛擬地址空間由Windows NT虛擬內存管理器(VMM)映射到物理內存上,在某些硬件平台上可以達到4GB。SQL Server應用程序只知道虛擬地址,所以不能直接訪問物理內存,這個訪問是由VMM控制的。Windows NT允許產生超出可用的物理內存的虛擬地址空間,這樣當給SQL Server分配的虛擬內存多於可用的物理內存時,會降低SQL Server的性能。

  這些地址空間是專門為SQL Server系統設置的,所以如果在同一硬件平台上還有其它軟件(如文件和打印共享,應用程序服務等)在運行,那麼應該考慮到它們也占用一部分內存。一般來說硬件平台至少要配置32MB的內存,其中,Windows NT至少要占用16MB。1個簡單的法則是,給每一個並發的用戶增加100KB的內存。例如,如果有100個並發的用戶,則至少需要32MB+100用戶*100KB=42MB內存,實際的使用數量還需要根據運行的實際情況調整。可以說,提高內存是提高系統性能的最經濟的途徑。

  2.3 磁盤子系統

  設計1個好的磁盤I/O系統是實現良好的SQL Server方案的一個很重要的方面。這裡討論的磁盤子系統至少有1個磁盤控制設備和1個或多個硬盤單元,還有對磁盤設置和文件系統的考慮。智能型SCSI-2磁盤控制器或磁盤組控制器是不錯的選擇,其特點如下:

  (1)控制器高速緩存。

  (2)總線主板上有處理器,可以減少對系統CPU的中斷。

  (3)異步讀寫支持。

  (4)32位RAID支持。

  (5)快速SCSI—2驅動。

  (6)超前讀高速緩存(至少1個磁道)。

  3 檢索策略

  在精心選擇了硬件平台,又實現了1個良好的數據庫方案,並且具備了用戶需求和應用方面的知識後,現在應該設計查詢和索引了。有2個方面對於在SQL Server上取得良好的查詢和索引性能是十分重要的,第1是根據SQL Server優化器方面的知識生成查詢和索引;第2是利用SQL Server的性能特點,加強數據訪問操作。

  3.1 SQL Server優化器

  Microsoft SQL Server數據庫內核用1個基於費用的查詢優化器自動優化向SQL提交的數據查詢操作。數據操作查詢是指支持SQL關鍵字WHERE或HAVING的查詢,如SELECT、DELETE和UPDATE。基於費用的查詢優化器根據統計信息產生子句的費用估算。

  了解優化器數據處理過程的簡單方法是檢測SHOWPLAN命令的輸出結果。如果用基於字符的工具(例如isql),可以通過鍵入SHOW SHOWPLAN ON來得到SHOWPLAN命令的輸出。如果使用圖形化查詢,比如SQL Enterprise Manager中的查詢工具或isql/w,可以設定配置選項來提供這一信息。

  SQL Server的優化通過3個階段完成:查詢分析、索引選擇、合並選擇。

  1.查詢分析

  在查詢分析階段,SQL Server優化器查看每一個由正規查詢樹代表的子句,並判斷它是否能被優化。SQL Server一般會盡量優化那些限制掃描的子句。例如,搜索和/或合並子句。但是不是所有合法的SQL語法都可以分成可優化的子句,如含有SQL不等關系符“<>”的子句。因為“<>”是1個排斥性的操作符,而不是1個包括性的操作符,所在掃描整個表之前無法確定子句的選擇范圍會有多大。當1個關系型查詢中含有不可優化的子句時,執行計劃用表掃描來訪問查詢的這個部分,對於查詢樹中可優化的SQL Server子句,則由優化器執行索引選擇。

  2.索引選擇

  對於每個可優化的子句,優化器都查看數據庫系統表,以確定是否有相關的索引能用於訪問數據。只有當索引中的列的1個前綴與查詢子句中的列完全匹配時,這個索引才被認為是有用的。因為索引是根據列的順序構造的,所以要求匹配是精確的匹配。對於分簇索引,原來的數據也是根據索引列順序排序的。想用索引的次要列訪問數據,就像想在電話本中查找所有姓為某個姓氏的條目一樣,排序基本上沒有什麼用,因為你還是得查看每一行以確定它是否符合條件。如果1個子句有可用的索引,那麼優化器就會為它確定選擇性。

  所以在設計過程中,要根據查詢設計准則仔細檢查所有的查詢,以查詢的優化特點為基礎設計索引。

  (1)比較窄的索引具有比較高的效率。對於比較窄的索引來說,每頁上能存放較多的索引行,而且索引的級別也較少。所以,緩存中能放置更多的索引頁,這樣也減少了I/O操作。

  (2)SQL Server優化器能分析大量的索引和合並可能性。所以與較少的寬索引相比,較多的窄索引能向優化器提供更多的選擇。但是不要保留不必要的索引,因為它們將增加存儲和維護的開支。對於復合索引、組合索引或多列索引,SQL Server優化器只保留最重要的列的分布統計信息,這樣,索引的第1列應該有很大的選擇性。

  (3)表上的索引過多會影響UPDATE、INSERT和DELETE的性能,因為所有的索引都必須做相應的調整。另外,所有的分頁操作都被記錄在日志中,這也會增加I/O操作。

  (4)對1個經常被更新的列建立索引,會嚴重影響性能。

  (5)由於存儲開支和I/O操作方面的原因,較小的自組索引比較大的索引性能更好一些。但它的缺點是要維護自組的列。

  (6)盡量分析出每一個重要查詢的使用頻度,這樣可以找出使用最多的索引,然後可以先對這些索引進行適當的優化。

  (7)查詢中的WHERE子句中的任何列都很可能是個索引列,因為優化器重點處理這個子句。

  (8)對小於1個范圍的小型表進行索引是不劃算的,因為對於小表來說表掃描往往更快而且費用低。

  (9)與“ORDER BY”或“GROUP BY”一起使用的列一般適於做分族索引。如果“ORDER BY”命令中用到的列上有分簇索引,那麼就不會再生成1個工作表了,因為行已經排序了。“GROUP BY”命令則一定產生1個工作表。

  (10)分簇索引不應該構造在經常變化的列上,因為這會引起整行的移動。在實現大型交易處理系統時,尤其要注意這一點,因為這些系統中數據往往是頻繁變化的。

  3.合並選擇

  當索引選擇結束,並且所有的子句都有了一個基於它們的訪問計劃的處理費用時,優化器開始執行合並選擇。合並選擇被用來找出一個用於合並子句訪問計劃的有效順序。為了做到這一點,優化器比較子句的不同排序,然後選出從物理磁盤I/O的角度看處理費用最低的合並計劃。因為子句組合的數量會隨著查詢的復雜度極快地增長,SQL Server查詢優化器使用樹剪枝技術來盡量減少這些比較所帶來的開支。當這個合並選擇階段結束時,SQL Server查詢優化器已經生成了1個基於費用的查詢執行計劃,這個計劃充分利用了可用的索引,並以最小的系統開支和良好的執行性能訪問原來的數據。

  3.2 高效的查詢選擇

  從以上查詢優化的3個階段不難看出,設計出物理I/O和邏輯I/O最少的方案並掌握好處理器時間和I/O時間的平衡,是高效查詢設計的主要目標。也就是說,希望設計出這樣的查詢:充分利用索引、磁盤讀寫最少、最高效地利用了內存和CPU資源。

  以下建議是從SQL Server優化器的優化策略中總結出來的,對於設計高效的查詢是很有幫助的。

  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.WHERE子句中的本地變量被認為是不被優化器知道和考慮的,例外的情況是定義為儲備過程輸入參數的變量。

  6.如果沒有包含合並子句的索引,那麼優化器構造1個工作表以存放合並中最小的表中的行。然後再在這個表上構造1個分簇索引以完成一個高效的合並。這種作法的代價是工作表的生成和隨後的分族索引的生成,這個過程叫REFORMATTING。  所以應該注意RAM中或磁盤上的數據庫tempdb的大小(除了SELECT INTO語句)。另外,如果這些類型的操作是很常見的,那麼把tempdb放在RAM中對於提高性能是很有好處的。

  4 性能優化的其他考慮

  上面列出了影響SQL Server的一些主要因素,實際上遠不止這些。操作系統的影響也很大,在Windows NT下,文件系統的選擇、網絡協議、開啟的服務、SQL Server的優先級等選項也不同程度上影響了SQL Server的性能。

  影響性能的因素是如此的多,而應用又各不相同,找出1個通用的優化方案是不現實的,在系統開發和維護的過程中必須針對運行的情況,不斷加以調整。事實上,絕大部分的優化和調整工作是在與客戶端獨立的服務器上進行的,因此也是現實可行的。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved