SQL Server資料庫效能最佳化技術第1/2頁

設計1個應用系統似乎並不難，但是要想使系統達到最佳化的效能並不是一件容易的事。在開發工具、資料庫設計、應 
用程式的結構、查詢設計、介面選擇等方面有多種選擇，這取決於特定的應用需求以及開發隊伍的技能。本文以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子句，則由最佳化器執行索引選擇。