淺析SQL Server數(shù)據(jù)庫性能優(yōu)化

高水娟

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

關(guān)鍵詞：數(shù)據(jù)庫；性能；優(yōu)化

中圖分類號：TP311文獻(xiàn)標(biāo)識(shí)碼：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作為一種重要的關(guān)系型數(shù)據(jù)庫，具有和強(qiáng)大的管理功能和安全性能，被廣泛應(yīng)用于后臺(tái)數(shù)據(jù)庫，在數(shù)據(jù)庫應(yīng)用系統(tǒng)中，數(shù)據(jù)庫的性能對整個(gè)軟件的性能起著重要的決定作用，因此，如何提高數(shù)據(jù)庫的性能，是當(dāng)前研究的重點(diǎn)。

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

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

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

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

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

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

3）SQL Server數(shù)據(jù)庫管理系統(tǒng)提供了可以指定某個(gè)特定高速緩存的頁替換策略的命令，在利用多個(gè)高速緩存的時(shí)候，這個(gè)命令尤其有用。可以利用這個(gè)命令指定對高速緩存中的數(shù)據(jù)項(xiàng)進(jìn)行替換的恰當(dāng)策略。

2模式調(diào)優(yōu)

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

1）索引

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

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

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

（3）聚集把可能作為一個(gè)結(jié)果集輸出的行組合在一起是非常有用的。這些行能夠被組合在一起是因?yàn)檫@些行在一個(gè)屬性上具有相同的屬性值，或者這些行在一個(gè)屬性值上的屬性值都落在一個(gè)范圍內(nèi)。在各種情況下，當(dāng)必須選擇在哪個(gè)屬性上進(jìn)行聚集時(shí)，應(yīng)該基于對應(yīng)用程序?qū)?yīng)的結(jié)果集的大小的估計(jì)來進(jìn)行選擇。

2）反向規(guī)范化

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

3）分片

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

3數(shù)據(jù)操縱語言調(diào)優(yōu)

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

1）避免排序

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

2）避免不必要的掃描

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

3）使通信量最小化

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

4調(diào)優(yōu)工具

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

參考文獻(xiàn)：

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

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

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

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

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

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

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

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