ＳＱＬ　Ｓｅｒｖｅｒ數據庫性能優化

梁　銘

[摘要]從數據庫設計人員的角度出發討論SQL Server數據庫性能優化的問題，非常詳細地從數據庫的邏輯設計到物理設計，再到數據庫查詢三個層次詳細的描述數據庫設計中性能優化的實現。

[關鍵詞]數據庫設計 SQL Server 優化

中圖分類號：TP3文獻標識碼：A文章編號：1671－7597（2009）0120079－01

數據庫系統是企業整個管理信息系統的核心和基礎，它的任務就是把系統中大量的數據按一定模型組織起來，以便及時、準確地提供給用戶使用。

設計一個數據庫應用系統似乎并不難，但是要想使系統達到最優化的性能并不是一件容易的事。在開發工具、數據庫設計、應用程序的結構、查詢設計、接口選擇等方面有多種選擇。總體來看，要提高數據庫的應用系統運行效率，必須從數據庫系統級優化、數據庫設計級優化、程序實現級優化這三個層次上同時下工夫。本文主要以SQL Server為例，從后臺數據庫的設計角度討論數據庫應用程序性能優化技巧。數據庫性能的優化主要從以下幾個方面入手。

一、邏輯數據庫設計的規范化問題

合理運用規范化設計邏輯數據庫。規范化是數據庫設計的基本指導原則。我們知道，從最基本的1NF到2NF、3NF是從不同的層次描述了關系中存在的函數依賴關系，那么我們在設計數據庫的時候范式的級別到底怎么來把握呢？范式級別越高邏輯數據庫就越好嗎？下面我們針對這兩個問題進行論述。

數據庫的目的是進行數據處理的，它是為應用系統服務的。一個應用系統是為了解決現實中生產、生活需要的，所以，我們首先必須明白的一個問題就是數據庫的設計必須緊密圍繞實際的應用需求，應用需求包括功能需求，性能需求，甚至用戶的操作需求。功能需求解決的是系統需要那些數據；而性能需求和用戶的操作需求涉及的就是這些數據如何科學、合理的組織的問題。所以數據庫的邏輯設計必須要解決好這兩個問題。由于我們討論的是數據庫邏輯設計的優化問題，那么后者就是我們要重點討論的。

數據庫系統性能要求是什么是決定數據庫設計規范化層次的一個重要依據。系統的需求分析階段我們必須仔細分析系統的性能要求。特別是數據庫操作性能要求。

另外，數據庫的邏輯設計必須考慮到用戶的操作要求。也就是業務處理的流程要求和程序界面設計要求。我們知道，數據庫的設計是軟件系統需求分析完成以后進行的，而程序界面設計是在數據庫設計之后完成的。那么數據庫的設計過程必須在充分基于需求分析的基礎上、同時充分考慮后續程序界面的設計來完成。如果完全脫離用戶的操作要求而從純粹追求高級別規范化角度去設計數據庫是不合理的。這就是“功能第一、性能第二”的原則。一般來講對于事務型數據庫來說滿足3NF的關系既能滿足用戶的應用需求，也能滿足規范化普遍要求。但是針對具體的關系還得進一步具體分析。

二、物理數據庫生成策略

數據庫優化的目標無非是避免磁盤I/O瓶頸、減少CPU利用率和減少資源競爭，提高數據庫的讀寫效率。基于硬件的物理數據庫的性能自然離不開高性能的硬件支持，特別是需要大容量內存和一個好的磁盤I/O子系統，智能型SCSI-2磁盤控制器或磁盤組控制器是不錯的選擇。但是從軟件(DBMS)角度，SQL SERVER數據庫的實施過程中以下準則是我們必須遵循的：

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

2．盡可能地把數據庫的所有基本表分布到多個不同的磁盤系統。尤其是系統采用了多個智能型磁盤控制器和數據分離技術的情況下，這樣做可以明顯的提高數據的操作性能。

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

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

5．合理定義主鍵與外鍵，合理定義索引。在基本表設計中，表的主鍵、外鍵、索引設計占有非常重要的地位，但系統設計人員往往只注重于滿足用戶要求，而沒有從系統優化的高度來認識和重視它們。實際上，它們與系統的運行性能密切相關。現在從系統數據庫優化角度討論主鍵和索引的問題：

（1）主鍵(Primary Key)：主鍵被用于復雜的SQL語句時，頻繁地在數據訪問中被用到。一個表只有一個主鍵。設計主鍵時短主鍵最佳（小于25bytes），主鍵的長短影響索引的大小，索引的大小影響索引頁的大小，從而影響磁盤I/O。如果使用復合主鍵，要求主鍵列不能太多，復合主鍵使得Join操作復雜化、也增加了外鍵表的大小。

（2）索引(Index)：利用索引優化系統性能是顯而易見的，索引的主要優點是對所有常用于查詢中的Where子句的列和所有用于排序的列創建索引，可以避免整表掃描或訪問，在不改變表的物理結構的情況下，直接訪問特定的數據列，這樣減少數據存取時間；利用索引可以優化或排除耗時的分類操作。索引碼越小，定位就越直接，索引性能越好。在進行Insert、Delete和Update操作時，必須對索引進行維護，所以定期更新索引非常必要。當然索引也要付出代價，一是增加存儲空間開銷，二是建立索引也要花費時間。所以一個基本表建立索引的多少、索引碼的選擇（除聚集索引外）就非常關鍵。

三、查詢優化技巧討論

查詢是數據庫的一個最為重要的操作，查詢的優化對整個數據庫系統的性能來講最為關鍵。一個性能優良的數據庫系統必然在查詢優化方面做得很優秀。

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

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

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

（一）查詢分析

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

（二）索引選擇

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

（三）合并選擇

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

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

經驗告訴我們，在優化查詢時，必須做到：

1．盡可能少的記錄行；

2．避免排序或盡可能少的排序，若要做大量數據排序，最好將相關數據放在臨時表中操作；用簡單的鍵（列）排序，如整型或短字符串排序；

3．避免表內的相關子查詢，多表查詢盡可能使用連接查詢；

4．避免在Where子句中使用復雜的表達式或非起始的子字符串、用長字符串連接；

5．在Where子句中多使用“與”(And)連接，少使用“或”(Or)連接；

6．利用臨時數據庫。在查詢多表、有多個連接、查詢復雜、數據要過濾時，可以建臨時表（索引）以減少I/O，但缺點是增加了空間開銷；

7．除非每個列都有索引支持，否則在有連接的查詢時分別找出兩個動態索引，放在工作表中重新排序。

參考文獻：

[1]鄭阿奇，《SQL Server實用教程》.

[2]王珊、薩師瑄，《數據庫系統概論》.

作者簡介：

梁銘，講師，十堰職業技術學院計算機工程系，主研領域：數據庫應用技術。