基于SQL Server的數(shù)據(jù)庫(kù)性能優(yōu)化方案研究

摘 要：性能是數(shù)據(jù)庫(kù)應(yīng)用的永恒話題，也是衡量一個(gè)項(xiàng)目成功與否的重要指標(biāo)。但是，想讓系統(tǒng)達(dá)到最優(yōu)性能從來(lái)不是一件簡(jiǎn)單的任務(wù)，涉及多個(gè)層面：如硬件、網(wǎng)絡(luò)、操作系統(tǒng)、數(shù)據(jù)庫(kù)、應(yīng)用軟件、涉及時(shí)間周期、系統(tǒng)涉及、開(kāi)發(fā)和測(cè)試、產(chǎn)品運(yùn)行和維護(hù)等。本文重點(diǎn)探討SQL Server 數(shù)據(jù)庫(kù)在系統(tǒng)方案設(shè)計(jì)過(guò)程中的優(yōu)化方案，主要從邏輯設(shè)計(jì)、主鍵、外鍵、物理設(shè)計(jì)、索引、鎖等多個(gè)方面著手。

關(guān)鍵詞： SQL Server；數(shù)據(jù)庫(kù)；優(yōu)化設(shè)計(jì)方案

性能調(diào)優(yōu)不是一件簡(jiǎn)單的事，一般來(lái)說(shuō)需要廣泛的經(jīng)驗(yàn)與知識(shí)，不單單是數(shù)據(jù)庫(kù)的經(jīng)驗(yàn)，還要對(duì)商業(yè)邏輯、系統(tǒng)架構(gòu)設(shè)計(jì)、編寫應(yīng)用程序、操作系統(tǒng)、架設(shè)網(wǎng)絡(luò)環(huán)境、使用各種監(jiān)測(cè)與監(jiān)控工具程序、安全與防毒等都有基本的了解，才能在復(fù)雜的系統(tǒng)中，找到癥結(jié)所在。本從先從衡量性能的指標(biāo)，然后對(duì)SQL Server數(shù)據(jù)庫(kù)設(shè)計(jì)中的多個(gè)方面進(jìn)行探討。

1、衡量性能問(wèn)題的關(guān)鍵指標(biāo)

1.1 響應(yīng)時(shí)間（ResponseTime）

一般指的是一條SQL語(yǔ)句執(zhí)行后得出結(jié)果耗費(fèi)的時(shí)間。而一般用戶使用來(lái)說(shuō)，比如BS結(jié)構(gòu)，響應(yīng)時(shí)間大家一般會(huì)認(rèn)為是訪問(wèn)頁(yè)面到頁(yè)面呈現(xiàn)結(jié)束，這樣的感官時(shí)間。這個(gè)時(shí)間就需要考慮更多的因素。比如網(wǎng)絡(luò)、瀏覽器等等。曾經(jīng)碰到的CASE頁(yè)面打開(kāi)速度超慢，但是數(shù)據(jù)庫(kù)正常，后來(lái)發(fā)現(xiàn)是頁(yè)面中潛入的一個(gè)很小的GIF影響了，所以要系統(tǒng)來(lái)分析。

而執(zhí)行SQL語(yǔ)句獲得的響應(yīng)時(shí)間是最為純粹的反饋，也是能夠得到準(zhǔn)備信息的步驟。在系統(tǒng)跟蹤的話，可以用SQLprofile來(lái)跟蹤響應(yīng)的內(nèi)容，分析語(yǔ)句的反饋時(shí)間，之后再來(lái)詳細(xì)講解。

1.2 吞吐量（Thougput）

反映系統(tǒng)到底有多繁忙的指標(biāo)，了解此指標(biāo)可以更為清晰的知曉系統(tǒng)的使用狀況。性能監(jiān)視器中可以用SQLBatchRequest/Sec，SQLTransactions/Sec等指標(biāo)來(lái)獲取。

1.3 基線（BaseLine）

反映系統(tǒng)日常狀況的指標(biāo)，如果知曉了系統(tǒng)的各種基線值。那么就清楚了底在哪里，頂在哪里。這樣才能更容易去判斷和解決問(wèn)題?；€值需要依靠長(zhǎng)期經(jīng)驗(yàn)和數(shù)據(jù)獲取的。

1.4 瓶頸（bottleneck）

系統(tǒng)一旦產(chǎn)生了瓶頸，我們就要去判斷瓶頸，而瓶頸一般來(lái)說(shuō)多會(huì)有關(guān)聯(lián)性。比如內(nèi)存不足可能導(dǎo)致IO過(guò)高，IO過(guò)高也可能導(dǎo)致CPU等待。所以準(zhǔn)確的知道瓶頸在哪里，這是需要去判斷的。使用性能監(jiān)視器和分析功能可以快捷的幫助大家分析瓶頸。

數(shù)據(jù)庫(kù)性能優(yōu)化的基本原則就是：通過(guò)盡可能少的磁盤訪問(wèn)獲得所需要的數(shù)據(jù)。 數(shù)據(jù)庫(kù)的優(yōu)化應(yīng)該采取自頂向下的優(yōu)化原則，從需求設(shè)計(jì)階段到數(shù)據(jù)庫(kù)運(yùn)行階段進(jìn)行控制。在設(shè)計(jì)階段：對(duì)其邏輯結(jié)構(gòu)和物理結(jié)構(gòu)進(jìn)行優(yōu)化設(shè)計(jì)，使之在滿足需求條件的情況下，系統(tǒng)性能達(dá)到最佳，系統(tǒng)開(kāi)銷達(dá)到最??；數(shù)據(jù)庫(kù)運(yùn)行階段：采取操作系統(tǒng)級(jí)、數(shù)據(jù)庫(kù)級(jí)的一些優(yōu)化措施來(lái)使系統(tǒng)性能最佳。

評(píng)價(jià)數(shù)據(jù)庫(kù)的性能，需要在數(shù)據(jù)庫(kù)調(diào)節(jié)前后比較其評(píng)價(jià)指標(biāo)即響應(yīng)時(shí)間和吞吐量之間的權(quán)衡、數(shù)據(jù)庫(kù)的可用性、數(shù)據(jù)庫(kù)的命中率以及內(nèi)存的使用效率，以此來(lái)衡量調(diào)節(jié)措施的效果和指導(dǎo)調(diào)整的方向。

2數(shù)據(jù)庫(kù)設(shè)計(jì)

2.1邏輯設(shè)計(jì)的規(guī)范化

數(shù)據(jù)庫(kù)邏輯設(shè)計(jì)的規(guī)范化就是我們一般所說(shuō)的范式，我們可以這樣來(lái)簡(jiǎn)單理解范式：

第一規(guī)范：是關(guān)于消除重復(fù)數(shù)據(jù)組和保證原子性（數(shù)據(jù)是自包含和獨(dú)立）的規(guī)范化信息，這是數(shù)據(jù)庫(kù)設(shè)計(jì)的最低要求。 第二規(guī)范： 是進(jìn)一步減少重復(fù)數(shù)據(jù)的出現(xiàn)（不一定是數(shù)據(jù)組），第二范式有以下兩個(gè)規(guī)則：表必須符合第一范式的規(guī)則（規(guī)范化是一個(gè)類似于堆積模塊的過(guò)程，如果沒(méi)有前面的積木就不能堆第三塊）；每列必須依賴于整個(gè)鍵。大部分情況下，數(shù)據(jù)庫(kù)設(shè)計(jì)都應(yīng)該達(dá)到第二范式。 第三規(guī)范：這只是規(guī)范化方面的一個(gè)相對(duì)終點(diǎn)，第三范式有三個(gè)規(guī)則：表必須符合第二范式；任何列都不能依賴于非鍵列；不可以有派生的數(shù)據(jù)。 達(dá)到第三范式應(yīng)該是系統(tǒng)中大部分表的要求，除非一些特殊作用的表。

更高的范式要求這里就不再作介紹了，個(gè)人認(rèn)為，如果全部達(dá)到第二范式，大部分達(dá)到第三范式，系統(tǒng)會(huì)產(chǎn)生較少的列和較多的表，因而減少了數(shù)據(jù)冗余，也利于性能的提高。

2.2冗余

完全按照規(guī)范化設(shè)計(jì)的系統(tǒng)幾乎是不可能的，除非系統(tǒng)特別的小，在規(guī)范化設(shè)計(jì)后，有計(jì)劃地加入冗余是必要的。

冗余可以是冗余數(shù)據(jù)庫(kù)、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。

冗余可以是為了編程方便而增加，也可以是為了性能的提高而增加。從性能角度來(lái)說(shuō)，冗余數(shù)據(jù)庫(kù)可以分散數(shù)據(jù)庫(kù)壓力，冗余表可以分散數(shù)據(jù)量大的表的并發(fā)壓力，也可以加快特殊查詢的速度，冗余字段可以有效減少數(shù)據(jù)庫(kù)表的連接，提高效率。

2.3主鍵的設(shè)計(jì)

主鍵是每行的唯一標(biāo)識(shí)符，必須包含唯一的值（因此不能為NULL）。由于主鍵在關(guān)系數(shù)據(jù)庫(kù)中的重要性，因此它是所有鍵和約束中最重要的。主鍵是必要的，SQL Server的主鍵同時(shí)是一個(gè)唯一索引，而且在實(shí)際應(yīng)用中，我們往往選擇最小的鍵組合作為主鍵，所以主鍵往往適合作為表的聚集索引。聚集索引對(duì)查詢的影響是比較大的，這個(gè)在下面索引的敘述。 在有多個(gè)鍵的表，主鍵的選擇也比較重要，一般選擇總的長(zhǎng)度小的鍵，小的鍵的比較速度快，同時(shí)小的鍵可以使主鍵的B樹(shù)結(jié)構(gòu)的層次更少。

主鍵的選擇還要注意組合主鍵的字段次序，對(duì)于組合主鍵來(lái)說(shuō)，不同的字段次序的主鍵的性能差別可能會(huì)很大，一般應(yīng)該選擇重復(fù)率低、單獨(dú)或者組合查詢可能性大的字段放在前面。

2.4 外鍵

外鍵既能確保數(shù)據(jù)完整性，也能表現(xiàn)表之間的關(guān)系。如果添加外鍵到一個(gè)表，就在定義的外鍵的表和外鍵引用的表之間創(chuàng)建依賴關(guān)系。外鍵是最高效的一致性維護(hù)方法，數(shù)據(jù)庫(kù)的一致性要求，依次可以用外鍵、CHECK約束、規(guī)則約束、觸發(fā)器、客戶端程序。

字段是數(shù)據(jù)庫(kù)最基本的單位，其設(shè)計(jì)對(duì)性能的影響是很大的。需要注意如下：

A、數(shù)據(jù)類型盡量用數(shù)字型，數(shù)字型的比較比字符型的快很多。

B、 數(shù)據(jù)類型盡量小，這里的盡量小是指在滿足可以預(yù)見(jiàn)的未來(lái)需求的前提下的。

C、 盡量不要允許NULL，除非必要，可以用NOT NULL+DEFAULT代替。

D、少用TEXT和IMAGE，二進(jìn)制字段的讀寫是比較慢的，而且，讀取的方法也不多，大部分情況下最好不用。

E、 自增字段要慎用，不利于數(shù)據(jù)遷移。

2.6數(shù)據(jù)庫(kù)物理存儲(chǔ)和環(huán)境的設(shè)計(jì)

在設(shè)計(jì)階段，可以對(duì)數(shù)據(jù)庫(kù)的物理存儲(chǔ)、操作系統(tǒng)環(huán)境、網(wǎng)絡(luò)環(huán)境進(jìn)行必要的設(shè)計(jì)，使得我們的系統(tǒng)在將來(lái)能適應(yīng)比較多的用戶并發(fā)和比較大的數(shù)據(jù)量。

這里需要注意文件組的作用，適用文件組可以有效把I/O操作分散到不同的物理硬盤，提高并發(fā)能力。

2.7 系統(tǒng)設(shè)計(jì)

整個(gè)系統(tǒng)的設(shè)計(jì)特別是系統(tǒng)結(jié)構(gòu)設(shè)計(jì)對(duì)性能是有很大影響的，對(duì)于一般的OLTP系統(tǒng)，可以選擇C/S結(jié)構(gòu)、三層的C/S結(jié)構(gòu)等，不同的系統(tǒng)結(jié)構(gòu)其性能的關(guān)鍵也有所不同。

系統(tǒng)設(shè)計(jì)階段應(yīng)該歸納一些業(yè)務(wù)邏輯放在數(shù)據(jù)庫(kù)編程實(shí)現(xiàn)，數(shù)據(jù)庫(kù)編程包括數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程、觸發(fā)器和函數(shù)。用數(shù)據(jù)庫(kù)編程實(shí)現(xiàn)業(yè)務(wù)邏輯的好處是減少網(wǎng)絡(luò)流量并可更充分利用數(shù)據(jù)庫(kù)的預(yù)編譯和緩存功能。

2.8 索引的設(shè)計(jì)

索引是數(shù)據(jù)庫(kù)規(guī)劃和系統(tǒng)維護(hù)的一個(gè)關(guān)鍵部分，他們?yōu)镾QL Server 以及任何其他的數(shù)據(jù)庫(kù)系統(tǒng)提供了查找數(shù)據(jù)和定位到數(shù)據(jù)物理位置的快捷方式的其他方法，通過(guò)添加正確的所以可以大大減少查詢執(zhí)行時(shí)間。

在設(shè)計(jì)階段，可以根據(jù)功能和性能的需求進(jìn)行初步的索引設(shè)計(jì)，這里需要根據(jù)預(yù)計(jì)的數(shù)據(jù)量和查詢來(lái)設(shè)計(jì)索引，可能與將來(lái)實(shí)際使用的時(shí)候會(huì)有所區(qū)別。

關(guān)于索引的選擇，應(yīng)改主意： A、 根據(jù)數(shù)據(jù)量決定哪些表需要增加索引，數(shù)據(jù)量小的可以只有主鍵。 B、 根據(jù)使用頻率決定哪些字段需要建立索引，選擇經(jīng)常作為連接條件、篩選條件、聚合查詢、排序的字段作為索引的候選字段。 C、 把經(jīng)常一起出現(xiàn)的字段組合在一起，組成組合索引，組合索引的字段順序與主鍵一樣，也需要把最常用的字段放在前面，把重復(fù)率低的字段放在前面。 D、 一個(gè)表不要加太多索引，因?yàn)樗饕绊懖迦牒透碌乃俣取?/p>

2.9物理結(jié)構(gòu)設(shè)計(jì)

為一個(gè)給定的邏輯數(shù)據(jù)模型設(shè)計(jì)一個(gè)最合適應(yīng)用要求的物理結(jié)構(gòu)的過(guò)程。數(shù)據(jù)庫(kù)的建立、數(shù)據(jù)表的建立、索引的建立、視圖的建立、 觸發(fā)器的建立、 存儲(chǔ)過(guò)程設(shè)計(jì)、用戶自定義函數(shù)設(shè)計(jì)、對(duì)關(guān)系模式的數(shù)據(jù)項(xiàng)加以約束，如檢查約束、主鍵約束、參照完整性約束以保證數(shù)據(jù)正確性等。

2.10 鎖升級(jí)和鎖對(duì)性能的影響

鎖是一種防止在某對(duì)象執(zhí)行動(dòng)作的一個(gè)進(jìn)程與已在該對(duì)象上執(zhí)行的其他進(jìn)程相沖突的機(jī)制。也就是說(shuō)，如果其他人在執(zhí)行某個(gè)對(duì)象，那么就不能在該對(duì)象上進(jìn)行操作。鎖也在描述正在執(zhí)行的操作的一種方式，因此系統(tǒng)可以知道第二個(gè)進(jìn)程動(dòng)作是否和第一個(gè)集成相兼容。如系統(tǒng)可處理的1個(gè)、2個(gè)、10個(gè)、100個(gè)或者任意多個(gè)用戶連接數(shù)，通?？赡茉谕粫r(shí)間共享同一塊數(shù)據(jù)，只要他們都是只讀訪問(wèn)記錄。SQL Server的鎖管理器就是管理多個(gè)用戶連接模式的。

升級(jí)是指能夠認(rèn)識(shí)到維持一個(gè)較細(xì)的粒度（例如，行鎖不是頁(yè)鎖）只在被鎖定的項(xiàng)數(shù)較少時(shí)有意義。而隨著越來(lái)越多的項(xiàng)被鎖定，維護(hù)這些鎖的系統(tǒng)開(kāi)銷實(shí)際上會(huì)影響性能。這會(huì)導(dǎo)致鎖持續(xù)更長(zhǎng)的時(shí)間（這樣會(huì)產(chǎn)生爭(zhēng)議問(wèn)題—鎖的時(shí)間越長(zhǎng)，其他用戶想使用這個(gè)特定記錄的可能性越大）。當(dāng)考慮這個(gè)問(wèn)題的時(shí)候，可能會(huì)需要一種權(quán)衡的辦法，這個(gè)就是鎖管理器使用升級(jí)的用意。當(dāng)維持鎖的數(shù)量達(dá)到一定的限度時(shí)，則鎖升級(jí)為下一個(gè)更高的層次，并且將不需要再如此緊密地管理低層次的鎖（釋放資源，而且有助于提高速度）。

3 結(jié)束語(yǔ)

優(yōu)化是一個(gè)持續(xù)的過(guò)程，永無(wú)止境，解決了當(dāng)前“最大”的瓶頸后，下一個(gè)“最大”的瓶頸又會(huì)出現(xiàn)要知道何時(shí)停止優(yōu)化優(yōu)化的內(nèi)容應(yīng)該是基于業(yè)務(wù)需求的優(yōu)化關(guān)注二投資回報(bào)率（ROI），工程師的時(shí)間也是投入，因此要懂得投資回報(bào)，需要懂得停止優(yōu)化！改變選項(xiàng)是最有意義的優(yōu)化策略，有的優(yōu)化是業(yè)務(wù)決定，那么無(wú)法改變的時(shí)候是否可以改變業(yè)務(wù)邏輯。實(shí)際上，足夠好的性能就足夠了。很多時(shí)候足夠即可，而不是去尋找極限！

作者簡(jiǎn)介：

苗改梅，女（1988 --），山西，碩士，工程師，研究方向：企業(yè)信息化.