SQL Server 資料庫優化

設計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子句，則由優化器執行索引選擇。


　　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個通用的優化方案是不現實的，在系統開發和維護的過程中必須針對運行的情況，不斷加以調整。 事實上，絕大部分的優化和調整工作是在與用戶端獨立的伺服器上進行的，因此也是現實可行的。