基于ＳＱＬ數據庫的性能優化問題分析

摘要：隨著數據庫應用系統中數據的增加，效率是將是數據庫應用開發與管理過程中必須解決的主要問題。因此必須對數據庫進行優化。其中既包括了對邏輯數據庫設計的優化、物理數據庫硬件的優化、服務器硬件平臺性能的優化、以及SQL語句本身查詢的優化。本文通過對SQL Server 數據庫應用中影響效率的主要問題進行相應的分析， 給出了數據庫的性能優化方法， 從而提高了整個數據庫的運行效率。

關鍵詞：SQL Server；性能優化；數據庫

中圖分類號：TP302.7文獻標識碼：A 文章編號：1009-3044(2008)15-21004-04

Based on SQL Database Performance Optimization Analysis

WANG Yong

(Huatian Engineering Technology Corporation，MCC， Ma'anshan 243005，China)

Abstract: With database applications in the data increased efficiency will be a database application development and management of the main issues must be resolved. Hence it is necessary to optimize the database. Which includes not only the logical database design for the optimization of the physical database hardware optimization， server hardware platform performance optimization， as well as SQL statements for the optimization itself. Based on the SQL Server database application in the main issues affecting the efficiency of the corresponding analysis is a method of optimizing the performance of the database， thus enhancing the operational efficiency of the entire database.

Key words: SQL Server; Performance optimization; Database

1 引言

隨著SQL數據庫中數據量的增加，運行效率將是數據庫應用與管理中的關鍵問題。而影響其效率的因素是多方面的，必須通過合理配置軟、硬件環境，有效設計數據庫結構，充分發揮SQL查詢語句功能，才可以對數據庫性能進行綜合性的優化處理。

2 邏輯數據庫性能優化

2.1 影響數據庫性能的因素

(1)數據庫設計方面。包括表的設計、表的數量和大小、表的設計規范程序、游標的使用等等。

(2)索引的設計方面。包括索引的數目及其在表上的分布和類型、鍵和使用、填充因子的利用、唯一性、約束的作用、索引頁面利用率、分布和密度統計的精確度以及對查詢的適用性。

(3)事務是數據庫的完整操作， 其包含一系列具體操作， 是一個不可分割的工作單元。事務保證了數據的一致性。數據的生成與復制、修改或刪除、以及復雜的查詢都會不同程度地影響數據庫的性能。

2.2 表的設計

可以使用大量的、有相互關系的窄表來代替很多列的寬數據表。由于表窄， 可使排序和建立索引更為迅速; 多表， 則可以使用多簇的索引; 同時， 每個表中可以包含少量的索引， 提升執行INSERT、UPDATE、和DELETE 語句的速度。設立更少的空值和更少的多余值， 來綜合增加數據庫的緊湊性。把用戶經常需要在查詢和報表中用到的， 在表的記錄量很大時， 有必要把計劃總數作為一個獨立的字段加入到表中。可采用觸發器以在客戶端保持數據的一致性。

2.3 非規范化設計

數據庫的規范化原則會產生較少的列和更多的表， 因而減少了數據冗余。但表關系也許需要通過復雜的合并來處理， 這樣會降低系統的性能。在某些情況下， 非規范化可以改善系統的性

能， 通過重新定義實體以減少外部屬性數據或行數據的開支。

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

(2) 把1個實體(表) 分割成2 個(把所有的行分成2 組)。這種方法適用于那些將包含大量數據的實體(表)。在應用中常要保留歷史記錄， 但是歷史記錄很少用到。因此可以把頻繁被訪問的數據同較少被訪問的歷史數據分開。

2.4 完整性約束的建立

利用SQL Server 的功能自動維護數據庫的完整性:

(1) 利用CHECK 約束， 保證字段的有效性;

(2) 利用DEFAULT 和NOT NULL 約束， 保證輸出必要字段值;

(3) 利用PRIMARY KEY 和UNIQUE 約束， 保證字段值的唯一性;

(4) 利用POREIGN KEY 約束， 保證記錄的參照完整性;

(5) 利用INENTITY 字段， 高效生成惟一行的標識符;

(6) 利用TIMESTAMP 字段確保在多用戶更新間進行高效并發檢查;

(7) 通過用戶定義的數據類型來確保到定義在數據庫內的一致性。

3 物理數據庫性能優化

3.1 利用基于硬件的RAID 技術

通過將磁盤陣列當作一個磁盤來對待， 基于硬件的RAID允許用戶管理多個磁盤， 使用基于硬件的RAID 與基于操作系統的RAID相比較， 基于硬件的RAID能夠提供更佳的性能。如果使用基于操作系統的RAID， 那么它將占據其他系統需求的CPU 周期。通過使用基于硬件的RAID， 用戶在不關閉系統的情況下能夠替換發生故障的驅動器。

(1)RAID 0是傳統的磁盤鏡像， 陣列中每一個磁盤都有一個或多個磁盤拷貝， 它主要用來提供最高級的可靠性， 使RAID 0成倍增加了寫操作卻可以并行處理多個讀操作， 從而提高了讀操作的性能。

(2)RAID 1是磁盤鏡像或磁盤雙工， 能夠為事務日志保證冗余性。

(3)RAID 5是帶奇偶的磁盤條帶化， 即將數據信息和校驗信息分散到陣列的所有磁盤中， 它可以消除一個校驗盤的瓶頸和單點失效問題。RAID 5 也會增加寫操作， 也可以并行處理一個讀操作， 還可以成倍地提高讀操作的性能。

3.2 利用文件組優化

文件組是對數據文件集合的一種稱呼。如果用戶硬件系統包括多個磁盤驅動器， 那么就可以將特定的對象和文件定位到單個磁盤中， 且將數據庫文件劃分成單個或多個用戶自定義的文件組。SQL Server 包括一個主文件組， 可能包括若干個用戶自定義文件組。通過創建文件組， 用戶可以將查詢負擔重和修改負擔重的文件組相互分離， 即通過將數據負載平穩到多個磁盤中， 并且利用并行線程來提高數據訪問速度， 文件組能夠在一定程序上提高性能。

4 服務器硬件平臺性能的優化

與SQL Server有關的硬件設計包括CPU、內存、磁盤子系統和網絡。

4.1 系統處理器(CPU)

根據自己的具體需要確定CPU結構的過程就是估計在硬件平臺上占用CPU的工作量的過程。我們可以使用System Monitor確定CPU的使用率，如果以75%或更高的速率長時間運行，就可能碰到了CPU瓶頸問題，這時應該升級CPU。

4.2 內存(RAM)

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

4.3 磁盤I/O系統

設計1個好的磁盤I/O系統是實現良好的SQL Server方案的一個很重要的方面。這里討論的磁盤I/O系統至少有1個磁盤控制設備和1個或多個硬盤單元，考慮到磁盤設置和文件系統，其特點如下:

(1)控制器有高速緩存。

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

(3)異步讀寫支持。

(4)32位RAID支持。

(5)高速SCSI U320控制器，讀寫速度320MB/S

由I/O子系統發生的瓶頸問題是數據庫系統可能遇到的最常見的同硬件有關的問題。配置很差的I/O子系統引起性能問題的嚴重程度僅次于編寫很差的SQL語句。解決I/O子系統有關的問題最簡單的辦法就是增加磁盤驅動器。

5 SQL查詢語句的優化

5.1 索引

索引是常見的數據庫對象，它的設置好壞、使用是否得當，極大地影響數據庫應用程序和數據庫的性能。在良好的數據庫設計基礎上，能有效地使用索引是SQL SERVER取得高性能的基礎。如果對于一個未建立索引的表執行查詢操作， SQL SERVER必須進行表掃描，從磁盤上讀表的每一個數據頁，從而挑選出所有符合條件的數據行。特別是當一個表有很多行時，就會浪費大量時間，效率太低。然而在建立索引之后， SQL SERVER將根據索引的指示，直接定位到需要查詢的數據行，從而加快SQL SERVER的數據檢索操作。這樣利用索引可以避免表掃描，并減少因查詢而造成的I/O開銷。

(1)簇索引

簇索引是對磁盤上實際數據重新組織，以按指定的一個或多個列的值排序。一個簇索引是一個B 樹，其底層包含了表中所有的數據頁，并且數據的物理存儲順序與索引順序完全相同，亦即簇索引的數據是按照一定的物理排序方式來保存的。由于簇索引的索引頁面指針指向數據頁面，所以使用簇索引檢索數據要比非簇索引快，而且它適用于檢索連續鍵值。

由于數據存儲于數據頁中，所以只能為每個表建立一個簇索引。而且創建簇索引要求數據庫有足夠的空間來容納大約1.2倍于表中實際數據的數據。在簇索引下，數據在物理上按順序排在數據頁上，重復值也排在一起，所以查詢時一旦找到符合條件的第一條記錄，具有相同鍵值或后續鍵值的行一定在物理上與它連在一起，而不必進一步搜索，從而縮小了查詢范圍，提高了查詢速度。由于每個表只能建一個簇索引，因此必須明智地選擇簇索引，以下一些情況比較適合創建簇索引:

①用于范圍查詢的列;

②用于Order By或Group By查詢的列;

③用于連接操作的列;

④返回大量結果集的查詢;

⑤不經常修改的列(對經常變動的列，列值修改后，數據行必須移動到新的位置) 。

(2)非簇索引

對于非簇索引，葉級頁包括了到數據頁和行的行定位器，而不像簇索引中那樣是真正的數據。它不對表中的物理數據頁進行排序。因此，創建一個非簇索引不要求必須有大的剩余空間。一個表最多可建立249個非簇索引，但要注意，表中索引數目太多，會影響到其它操作的性能，例如Update，Delete和Insert等。因此，不要試圖使用過多的索引，一般而言，對于一個表擁有一個簇索引和2－6個非簇索引就已經足夠了(數據倉庫例外) 。每個非簇索引提供訪問數據的不同排序順序。以下為一些創建非簇索引比較合適的場合:

①用于集合功能的列;

②外部鍵;

③返回數據量小的結果集的查詢;

④經常要通過在表連接中指定列的方式訪問的信息，或者查詢中排序和組合需要的列。

(3)復合索引

復合索引是通過兩個或兩個以上的列創建的鍵(最大列數為16列) ，索引值的最大長度為900字節。不要試圖創建列數過多的復合索引，過多的列會影響性能并使索引鍵變大，這樣在讀取索引鍵時要掃描更多的數據頁。在復合索引中應首先定義最可能具有唯一性的列。那么SQL SERVER何時能充分利用索引的優勢，何時不能使用索引? 當對一個大型表進行操作時，如果滿足下列條件，優化器就能充分利用復合索引:

①復合索引中的第一個字段或所有字段是在Where子句中引用的字段，并且包含有用的搜索參數。

②復合索引中的字段，在Where子句中不參與任何形式的計算。

現在舉例如下，用戶guest1想要查詢表table0中滿足條件的記錄，首先建立以前三列為索引的復合索引:

Create Index Col1_2_3_index On table0 ( col1， col2， col3)

則如下查詢不能充分利用復合索引的優勢:

Select * From Table0 Where col2=value2 And col3=value3

或條件為單獨的col2或col3。這類查詢是很不合理的，因為它的前導列是col1，而以上查詢語句沒有引用col1，也就是沒有利用索引。而如下查詢則能充分利用復合索引的優勢:

Select * From table0 Where col1=value1 and col2=value2 and col3=value3或條件為單獨的col1或為col1與col2、col3的任意組合。這類查詢語句使用了col1，所以查詢速度很快。

(4)使用索引的注意事項

索引可以加快數據訪問速度，減少I/0操作的次數，對于查詢大有益處。但對于更新的操作性能會是一個障礙，這是由于在數據修改、插入或刪除時需要更新索引，因此索引頁的更新將增加額外的I/0操作。而且表中過多的索引可能影響到性能。創建索引時注意以下情況:

①對于行數很少的表，沒有必要建立索引。當表中只有幾個數據時， SQL SERVER執行表掃描的速度可能會快于索引。

②對于一個經常需要變動而決策支持操作很少的表，應盡量限制表中索引的使用。

③盡量避免使用長鍵作為索引列。這樣可以減少數據頁占用，并減少了在一次查詢中SQL SERVER需要讀取的數據頁數目。

④不要創建SQL SERVER不使用的索引，否則該索引只會浪費空間，并且數據更新時產生不必要的開銷。

⑤不能對只有很少唯一值的字段生成索引。

因此，在使用索引的時候一定要謹慎，要選擇恰當的索引，盡量減少磁盤訪問的次數，提高整個系統的性能。

5.2 視圖

(1)標準視圖

視圖是一種數據庫對象，它是由用戶從一個或多個表中建立的一個虛擬表。視圖是SQL查詢語句而不是用數據構造的。它可以用來控制用戶對數據的訪問，限制用戶從表中所檢索的內容，并能簡化數據的顯示。而且在大多數情況下用戶所查詢的信息，可能存儲在多個表中，而對多表操作比較繁瑣，那么可通過視圖將所需的信息設計到一個視圖中，以此來簡化數據查詢和處理操作。另外，視圖中的數據都來自于基表，是在視圖被引用時動態生成的，使用視圖可以集中、簡化和定制用戶的數據庫顯示，用戶可以通過視圖來訪問數據，而不必直接去訪問視圖的基表。在SQL SERVER中可通過視圖檢索數據，對視圖可以使用連接、Group By子句、子查詢等，以及它們的任意組合來檢索視圖數據。

例如從Stud_Score數據表中檢索成績大于90的同學。首先建立一個視圖view1，然后使用視圖來檢索符合條件的數據，代碼如下:

Create View view1 as

Select name from Stud_Score where score > 90

當執行這個Create View語句時， SQL server在數據庫的系統表中創建該視圖的定義。一旦創建了該視圖，就可以像在其它SQL語句中使用表一樣使用該視圖。例如:

Select * From View1

因此，可以把視圖看作一個只包含那些指定條件行的表。當需要查看多個表中的數據時，使用視圖就顯得極為方便。例如要連接Stud_Score和Stud_regedit表中相關行的視圖，并查看數據，代碼如下:

Create View view2 as

Select stud_score.number， stud_ score.score， stud_regedit.name

From stud_score， stud_regedit

On stud_score.number = stud_regedit.number

Select * From View2

(2)索引視圖

索引視圖在數據庫中存儲視圖結果集。索引視圖之后，視圖的虛擬成為真實:視圖包含數據。索引視圖可縮短對多個表和進行多個復雜連接的視圖的查詢時間，來直接訪問所需數據，成為跨多個表格的超索引。此外，查詢優化器開始在查詢中使用視圖優化器，而不是直接從From子句中命名視圖，這樣可以從索引視圖中檢索數據而無需重新編碼，由此為查詢帶來高效率。但基礎表更新數據時， SQL SERVER需要更新索引視圖中的數據，這個更新可能影響性能。只有當視圖的結果檢索速度的效率超過了修改所需的開銷時，才應在視圖上創建索引。

(3)分區視圖

分區視圖可以提高分布式數據的查詢效率。在各個區域的服務器中都存有本區域倉庫信息的Warehouse表，這樣在本地服務器上進行查詢時可以大大地提高檢索的效率。使用這種方法，在進行很多查詢時避免了和其它服務器通信。但是，有些查詢不僅要訪問本地倉庫信息，還要訪問一個或多個遠程倉庫信息。分區視圖提供了簡單的解決方案，因為它是含有分區數據的表的聯合。每個倉庫都可根據倉庫的ID來辨別屬于哪個服務器。

例如， SERVER1服務器的倉庫的ID在10001與19999之間， SERVER2服務器的倉庫的ID 在20001 與29999 之間。下列的語句說明如何在SERVER1服務器上為倉庫數據創建一個分區視圖:

Create View Allwarehouse As

Select * From Mydatabase. tableowner. warehouse

Union all

Select * From server2. Mydatabase. tableowner. warehouse

因為本地的查詢很少需要訪問遠端的數據，因此這種優化器大大地提高了查詢的效率。例如下列語句實現了SERVER1服務器上的一個查詢:

Select * From Allwarehouse where warehouse ID = 10088

分區視圖使服務器組中的多個服務器之間可以實現并行處理，這樣，數據可以分布在多個服務器之間，查詢時根據需要動態合并。它提供了訪問不同地址保存的數據的強大功能，但是管理和使用視圖很復雜，視圖生成和使用的規則很多，因此，只有遵守所有規則，才可利用其強大特性。

5.3 異步查詢

異步查詢是遠程數據庫對象(RDO)的一個特征(RDO用于開發SQL SERVER數據庫應用程序) ，它允許應用程序在等待完成長時間運行的查詢時，能夠執行其它任務。這樣就使得用戶在執行其它操作之前，不必等待查詢的完成，實現并行操作。

6 結束語

本文從邏輯、物理、服務器配置、SQL語句查詢四個方面提出了一些對數據庫的優化技術， 當然實現優化的方法還有很多，要根據具體情況而定。對于不同的應用情況，我們應該具體情況具體分析， 各方面優化措施綜合運用，以使數據庫性能得到提高。

參考文獻：

[1] 陳永強，等. SQL Server數據庫企業應用系統開發[M]. 清華大學出版.

[2] 鄭謙益. SQL SERVER 性能優化技術及應用研究[J].微機發展， 2003.

[3] 張莉， 王強. SQL SERVER 數據庫原理及應用教程[M]. 北京: 清華大學出版社， 2003.