高水娟

摘要：該文根據作者多年從事數據庫應用系統(tǒng)開發(fā)及教學方面的經驗，分析了SQL Server數據庫在三個層次的性能優(yōu)化及其注意事項，并介紹了進行性能優(yōu)化所使用的工具。

關鍵詞：數據庫；性能；優(yōu)化

中圖分類號：TP311文獻標識碼：A文章編號：1009-3044(2012)23-5516-02

Analysis of SQL Server Database Performance Optimization

GAO Shui-juan

(Department of Electronic Information Engineering,Nanjing Communications Institute of Technology, Nanjing 211188,China)

Abstract:According to the author for many years engaged in the database application system development and teaching experience, analysis of the SQL Server database in three levels of performance optimization and its attention to matters, and introduced the optimization tools.

Key words：database；performance；optimization

SQL Server作為一種重要的關系型數據庫，具有和強大的管理功能和安全性能，被廣泛應用于后臺數據庫，在數據庫應用系統(tǒng)中，數據庫的性能對整個軟件的性能起著重要的決定作用，因此，如何提高數據庫的性能，是當前研究的重點。

一個應用軟件與數據庫管理系統(tǒng)結合起來構成了一個非常復雜的系統(tǒng)，它的許多不同的方面都可以被優(yōu)化。最低層次的性能優(yōu)化是硬件層次的調優(yōu)。為了提高性能，系統(tǒng)必須提供大量的主存空間、足夠多的CPU和二級存儲設備，以備足夠的通信能力。數據庫管理系統(tǒng)處于上一層，這一層的性能問題包括磁盤中的物理數據組織、緩沖區(qū)管理等。這個層次的決策很大程度上是數據庫管理員的管理范圍，因此應用程序員只能間接對其產生影響。SQL代碼和模式處于最高層，在這一層進行性能優(yōu)化涉及的問題包括如何表達一個查詢、創(chuàng)建什么樣的索引等。

該文并不是對如何優(yōu)化數據庫性能作詳盡的闡述，而是根據作者這幾年從事數據庫應用系統(tǒng)開發(fā)及教學方面的經驗，提出以下三個方面對數據庫性能優(yōu)化有所增益，以供大家參考。

1高速緩存調優(yōu)

我們先來了解一下高速緩存是如何工作的。高速緩存是數據庫管理系統(tǒng)內部的主存緩沖區(qū)，最近被訪問的數據庫頁被存放在高速緩存中，當事務訪問一個數據庫數據項的時候，數據庫管理系統(tǒng)將包含這一項的數據庫磁盤頁讀入高速緩存，然后把這一項的值從高速緩存拷入應用程序緩沖區(qū)。這一頁通常被保留在高速緩存中，我們可以合理假設，應用程序稍后更新這個數據項或讀取同一頁中另外一項的可能性非常大，或者另外一個應用程序可能在并行地訪問這一頁中的一項，由于可以直接從高速緩存中訪問這一頁，這兩種情況都可以節(jié)省一次磁盤訪問。如果一個數據庫數據項被更新，包含這一項的數據庫頁必須先被讀入高速緩存，高速緩存中的這一頁就是被修改的頁的高速緩存版本。最終高速緩存將被填滿，從數據庫中獲取的任何一個新頁必須覆蓋高速緩存中的一頁p，如果自從p被讀入高速緩存還沒有被修改過，高速緩存頁的內容與數據庫頁的內容是一致的，因此新的一頁可以直接覆蓋它。然而如果自從p被讀入高速緩存后被更新過，在其占據的高速緩存頁被覆蓋前必須將其寫回數據庫。那么應用程序設計人員及數據庫管理員如何優(yōu)化數據庫管理系統(tǒng)利用高速緩存的方式來提高應用程序的性能呢？

1）SQL Server數據庫管理系統(tǒng)允許高速緩存的一部分被分割出來，作為一個獨立的高速緩存。然后程序員可以把一個特定的數據庫數據項與這個分出來的高速緩存綁定在一起，以便這個數據庫數據項的所有頁都被緩沖在高速緩存中。例如，T1表和T2表被綁定在不同的高速緩存中，T1表的一頁便不可能覆蓋T2表的一頁。如果對T2表的訪問不太頻繁但訪問它的應用需要快速的響應，這個方法是可行的。

2）SQL Server數據庫管理系統(tǒng)允許把一個特定的高速緩存分割成幾個大小不同的緩沖區(qū)池。例如在默認情況下，高速緩存中的緩沖區(qū)大小可能為2K，我們可以重新分配高速緩存，分別創(chuàng)建大小為2K、4K、8K的緩沖區(qū)池。如果一個表被裝入這樣的高速緩存，查詢優(yōu)化器可以選擇每次I/O傳輸的數據量的大小，使其更加適應訪問這個表的查詢計劃。

3）SQL Server數據庫管理系統(tǒng)提供了可以指定某個特定高速緩存的頁替換策略的命令，在利用多個高速緩存的時候，這個命令尤其有用?？梢岳眠@個命令指定對高速緩存中的數據項進行替換的恰當策略。

2模式調優(yōu)

當我們?yōu)橐粋€特定應用設計的數據庫模式處于應用的核心時，如果模式設計得好，可能設計出高效的SQL語句。我們在應用層的調優(yōu)策略首先就應該是設計一個規(guī)范化的數據庫，并估計表的大小、屬性值的分布、查詢的特征及其執(zhí)行頻率，以及可能對數據庫執(zhí)行的更新等。對規(guī)范化的模式進行調整以提高操作的執(zhí)行效率是依賴于上述這些估計的。添加索引、反向規(guī)范化及分片都是重要的調優(yōu)方法。

1）索引

索引看上去可能是最重要的數據庫調優(yōu)技術，但索引也帶來了相關的存儲開銷。更為重要的是，額外的索引可能極大地增大對數據庫進行修改的語句的處理時間，這是因為每當索引對應的表被更新的時候，對應的索引也要更新。當創(chuàng)建索引而帶來性能增益時，我們也要思考這是否足以補償處理對表進行修改的語句的額外開銷。因此在創(chuàng)建索引時我們要作如下考慮：

（1）由于一個表最多只能有一個聚集索引，我們不能將這個索引浪費在不能利用聚集索引的優(yōu)勢的屬性上。數據庫管理系統(tǒng)通常在主碼上創(chuàng)建了一個聚集索引，但主碼上的非聚集索引也能確保主碼值的唯一性，因此如果我們不想按照主碼對行進行排序，我們就不應該在主碼上創(chuàng)建聚集索引。用另外一個聚集索引替換一個已經存在的聚集索引也是一件非常耗時工作，因為在這個過程中包含對存儲結構的完全重組。所以應該事先對應用進行分析，考慮應用可能會執(zhí)行哪些查詢以及每個查詢的執(zhí)行頻率，從而創(chuàng)建一個可能帶來最大收益的聚集索引。

（2）只有獲取整個表的很少一部分行的時候，利用非聚集索引才是恰當的。如果獲取整個表的大部分行，全表掃描是恰當的。另外如果屬性值傾向于在域內均勻分布，不要在對應一個較小的域的列上創(chuàng)建索引。

（3）聚集把可能作為一個結果集輸出的行組合在一起是非常有用的。這些行能夠被組合在一起是因為這些行在一個屬性上具有相同的屬性值，或者這些行在一個屬性值上的屬性值都落在一個范圍內。在各種情況下，當必須選擇在哪個屬性上進行聚集時，應該基于對應用程序對應的結果集的大小的估計來進行選擇。

2）反向規(guī)范化

反向規(guī)范化是指通過一個表中添加冗余信息來設法提高只讀查詢的性能。這個過程是關系規(guī)范化的逆過程。應用程序設計的開始步驟之一就是表的設計，表是數據存儲的載體，因此在設計之初就應該做好規(guī)范化工作。非規(guī)范化關系存在著許多的缺點，但有時候為了性能的優(yōu)化，卻不得不按昭反向規(guī)范化思想來設計關系模式。這主要是應用在某些特定的查詢，如不需要經常更新的關系，反向規(guī)范化反而能提高效率。因此在設計檔案數據時采用反向規(guī)范化方法，在設計在線數據時采用規(guī)范化方法。

3）分片

通過顯式地把一個表分成多個片段，這樣可以把表中頻繁訪問的數據與很少訪問的數據區(qū)分開來，從而降低訪問非常大的表的開銷。利用水平分片，每個片段具有相同的列集合，包含了整個表的所有行的一個子集。對行進行分片都基于把所有行劃分成不相交的子集，如在學生信息表中可以將已畢業(yè)的學生和當前在讀的學生分成兩個片段，因為對當前在讀的學生的訪問量要大得多的多。利用垂直分片，表中所有列的子集構成了一個片段。當一個表有很多列的時候，這可能是有用的，因為這樣的表包含很多行，每一行中的某些列都很少被訪問。如果沒有分片，當獲取所需要的列對應的數據的時候，還要把不需要的列對應的數據從磁盤中傳入緩沖區(qū)，這嚴重降低了性能。是否分片，必須要權衡，因為在分片的情況下，付出的代價就是增加了訪問及管理多個表的額外復雜性，因此只有在性能收益非常明顯的情況下才能使用這個技術。

3數據操縱語言調優(yōu)

數據庫的查詢效率是衡量數據庫性能的一個重要的指標，但SQL Server的查詢會消耗大量資源，在保證查詢效率的同時也產生了一些負面的影響，下面就如何優(yōu)化查詢和提高查詢性能提出以下見解。

1）避免排序

排序的開銷很大，所以盡量避免排序。我們首先要了解哪些類型的查詢可能導致查詢優(yōu)化器在查詢計劃中引入排序，這樣可以避免執(zhí)行這類的查詢。消除重復包含了排序操作，因此除非對應用來說非常重要，否則不要用DISTINCT關鍵詞；集合操作（如UNION及EXCEPT）也會引入排序操作以消除重復；對于處理ORDER BY子句來說排序是不可避免的，因此應該考慮清楚對結果排序是不是必須的。

2）避免不必要的掃描

在WHERE子句中包含“不等于”條件可能會導致一次掃描，對于WHERE LIKE等條件仍舊需要表掃描。因此在查詢子句中最好避免這樣的條件出現。

3）使通信量最小化

通常，客戶機與服務器之間的通信開銷非常大，因此應該盡可能地降低通信開銷。帶來通信開銷的主要原因之一是利用游標，每獲取一行都要引發(fā)一次通信，因此在更新一個表時設法利用UPDATE語句，而不是獲取一行后修改并寫回。另外在檢索聚集信息時，可以利用存儲過程計算聚集，只返回結果值到客戶端，如果必須在應用程序代碼中分析表中的每一行，就要檢查使用的數據庫管理系統(tǒng)是否支持一次獲取多行，我們所使用的SQL Server數據庫正是支持這一應用的，所以就可成組獲取以降低通信開銷。

4調優(yōu)工具

數據庫管理系統(tǒng)供應商提供了各種用于對數據庫進行調優(yōu)的工具。這些工具通常情況下需要創(chuàng)建一個試驗數據庫，在這個數據庫里對各種查詢執(zhí)行計劃進行試驗。在大多數數據庫管理系統(tǒng)中，一個這樣的典型工具是EXPLAIN PLAN語句，使數據庫管理系統(tǒng)生成一個查詢執(zhí)行計劃，并把這個查詢執(zhí)行計劃當成一個元組集存儲在PLAN_TABLE關系中，然后可以通過執(zhí)行對PLAN_TA BLE表的查詢來獲取這個查詢執(zhí)行計劃。基于文本的查詢執(zhí)行計劃的檢測功能非常強大，但是目前只有熱衷于這種方式的人才使用。大多數數據庫管理系統(tǒng)都提供了圖形界面的調優(yōu)工具，如微軟提供了Query Analyzer工具，這些工具不僅顯示查詢計劃，而且能夠建議你創(chuàng)建索引以提高各種查詢的執(zhí)行速度。

參考文獻：

[1]魏銀珍,陳征兵. SQL Server數據庫的查詢優(yōu)化策略研究[J].電腦知識與技術2011(7).

[2]徐麗媛,張亞賓.基于SQL Server數據庫查詢優(yōu)化的幾點思考[J].科技信息2010(20).

[3]李榮瀚.SQL Server數據庫性能優(yōu)化[J].科技探索,2011(3).

[4]余秋風.試論SQL Server數據庫性能的優(yōu)化[J].信息與電腦,2012(4).

[5]吳春慶.數據庫性能優(yōu)化主要方法[J].硅谷,2010(3).

[6] Robert Vieira.SQL Server2005高級程序設計[M].北京:人民郵電出版社,2008.

[7]楊暉.SQLServer數據庫性能優(yōu)調技術分析[J].信息與電腦,2011 (11).

[8]年瑋.SQLSERVER數據庫性能優(yōu)化分析[J].信息與電腦,2010 (8).