?

作為一個(gè)使用sqlserver的項(xiàng)目leader,你第一需要知道的是“sqlerver能做什么，不能作什么”。也許數(shù)據(jù)遷移，也許你遇到數(shù)據(jù)優(yōu)化問(wèn)題不

知道該怎么做，或者您僅僅想知道使用sqlserver的進(jìn)行數(shù)據(jù)訪問(wèn)層的設(shè)計(jì)指南。這篇文章適合你。
即使你沒(méi)有使用sqlserver,絕大多數(shù)設(shè)計(jì)指南也適用其它數(shù)據(jù)庫(kù)管理系統(tǒng)。對(duì)程序員來(lái)說(shuō)sybase是一個(gè)很熟悉的環(huán)境，oracle也能從中受益。

我并不想顯示具體的使用t-sql技巧，也對(duì)你的問(wèn)題給不了神奇的解決方案。這里并沒(méi)有完成，或解決的問(wèn)題。我只想從多年的教訓(xùn)中學(xué)來(lái)的經(jīng)

驗(yàn)給你一些好的設(shè)計(jì)建議。發(fā)現(xiàn)一遍又一遍出現(xiàn)的設(shè)計(jì)錯(cuò)誤。

1.了解你的工具

請(qǐng)不要低估這個(gè)建議，它是所有建議中最好的。你驚訝的發(fā)現(xiàn)多少程序員并不知道tsql所有的命令和sqlserver都又那些有效的工具。
“什么？我要花費(fèi)一個(gè)月的時(shí)間學(xué)習(xí)一些將來(lái)不用的命令？”你可能說(shuō)no,你不需要。但花費(fèi)一個(gè)周末時(shí)間在msdn。瀏覽所有的tsql命令：它能

說(shuō)什么，不能做什么。在未來(lái)，當(dāng)你設(shè)計(jì)一個(gè)查詢時(shí)候，你將記得：“嘿，有個(gè)命令能有效完成我們所需要的”。然后你在去參考msdn查看它

的準(zhǔn)確語(yǔ)法。
在這里我假定你以及了解了T-SQL語(yǔ)法或者你能在MSDN上找到它。

2.不要使用游標(biāo)

再次提醒：不要使用游標(biāo)。它是扼殺整個(gè)系統(tǒng)性能的第一途徑。絕大多數(shù)初學(xué)者使用游標(biāo)并沒(méi)有認(rèn)識(shí)到它對(duì)性能的傷害。他們使用大量的內(nèi)存

，以很怪異的方式鎖表，而且執(zhí)行速度巨慢。最可惡的是它讓DBA所做的所有優(yōu)化無(wú)效。你知道執(zhí)行游標(biāo)和相似的select之間的區(qū)別嗎？這意味

著如果你的游標(biāo)又10000條記錄，它將執(zhí)行大約10000次select.如果你單獨(dú)執(zhí)行這些select ,update,delete,它將非常塊。

初學(xué)者感覺(jué)游標(biāo)是一種熟悉，舒服的編碼方式，不幸的是它導(dǎo)致低下的性能，sql的目的是找出你需要的，而不是它應(yīng)該怎么做。

有次我重寫(xiě)一個(gè)基于游標(biāo)的存儲(chǔ)過(guò)程,替換了一些傳統(tǒng)的sql查詢,這個(gè)表僅10萬(wàn)條記錄這個(gè)存儲(chǔ)過(guò)程執(zhí)行了40分鐘，而事實(shí)是最差勁的程序員也

能寫(xiě)一個(gè)執(zhí)行10秒鐘的存儲(chǔ)過(guò)程。

如果你正讀這篇文章，我需要提醒的是這里沒(méi)有好的游標(biāo)使用建議。除了DBA工作的需要，我將不會(huì)使用游標(biāo)。

3.規(guī)范化你的表

兩個(gè)通常的借口是：性能或延遲。你遲早要為延遲付出代價(jià)。如果不慢就不要優(yōu)化，常常我發(fā)現(xiàn)程序員反規(guī)范化數(shù)據(jù)庫(kù)。因?yàn)樗麄冋f(shuō)這將慢，

然而常常相反。結(jié)果這種設(shè)計(jì)更慢，DBMS被設(shè)計(jì)來(lái)使用規(guī)范化數(shù)據(jù)庫(kù)，所以要規(guī)范化設(shè)計(jì)

4.不要使用select *

這個(gè)規(guī)則執(zhí)行起來(lái)有點(diǎn)難。我承認(rèn)我常使用它。試著指定你需要的列，他將：
a.減少內(nèi)存消耗和帶寬
b.容易安全設(shè)計(jì)
c.可以利用查詢優(yōu)化，這樣可以從索引中讀取所需的列。

5.了解你的數(shù)據(jù)怎么被訪問(wèn)

強(qiáng)健的索引設(shè)計(jì)是數(shù)據(jù)庫(kù)設(shè)計(jì)的好方法之一。索引設(shè)計(jì)常常是很藝術(shù)的。每次你在一個(gè)表上加一個(gè)索引，加快了select,變慢了delete,update.

維護(hù)索引需要做很多的事情。如果你給一個(gè)表加好幾個(gè)索引，你很會(huì)就會(huì)注意到當(dāng)更新索引時(shí)將鎖很長(zhǎng)時(shí)間。所以問(wèn)題是：這個(gè)表在干什么？

讀，更新數(shù)據(jù)？這個(gè)問(wèn)題很巧妙，特別對(duì)于update和delete.因?yàn)樗麄兂３Ｊ褂脀here部分調(diào)用一個(gè)select，然后才更新表。

6.不要在“性別”列上鍵索引

只是無(wú)用的。讓我們理解索引怎么加快表的訪問(wèn)的。你理解索引快速部分。如果我們建立一個(gè)索引在sex列，你將只有兩部分：男女。那你怎么

優(yōu)化1000萬(wàn)條記錄呢？記住維護(hù)索引是很慢的。一直設(shè)計(jì)你的索引最多稀少的列先，最少稀少的列最后如：Name + Province + Sex.

7.使用事務(wù)：

特別是長(zhǎng)時(shí)間執(zhí)行的查詢。當(dāng)執(zhí)行錯(cuò)誤時(shí)使用事務(wù)將挽救你。當(dāng)操作數(shù)據(jù)時(shí)候，你將很快發(fā)現(xiàn)一下可以讓存儲(chǔ)過(guò)程崩潰的異常情況。

8.注意死鎖。

如果你訪問(wèn)表以相同的順序，當(dāng)執(zhí)行存儲(chǔ)過(guò)程或事務(wù)時(shí)，你很快發(fā)現(xiàn)死鎖。如果你鎖表A接著鎖表B，一直鎖他們以相同的順序在所有的存儲(chǔ)過(guò)程。某天在另外一個(gè)存儲(chǔ)過(guò)程中，如果你偶然鎖表b接著鎖表A，那么將產(chǎn)生死鎖。死鎖很難被發(fā)現(xiàn)。死鎖常是由粗心的設(shè)計(jì)導(dǎo)致的。

9.不要獲取大記錄

在技術(shù)論壇中常看到這樣的提問(wèn)：“怎么把100000條記錄快速填充到組合框中”。這是個(gè)錯(cuò)誤。你不能也不應(yīng)該這么做。首先你的用戶討厭在10，000條記錄中找它需要的。看來(lái)需要一個(gè)好的ui設(shè)計(jì)。因?yàn)槟憬o用戶最多呈現(xiàn)100到200條記錄。

10.使用參數(shù)化查詢語(yǔ)句

有時(shí)我們?cè)诩夹g(shù)類論壇常看到這樣的問(wèn)題：“我的查詢操作引號(hào)等字符時(shí)失敗。我怎么能避免它呢”。常見(jiàn)的回答是：“用一個(gè)雙引號(hào)替換”。錯(cuò)誤的！這僅是一個(gè)工作場(chǎng)景，如果遇到另外一個(gè)字符時(shí)仍舊失敗。這將導(dǎo)致嚴(yán)重的安全bug.除了這個(gè)，它將破壞SQL Server緩存系統(tǒng)（緩存相似的查詢）。學(xué)怎么使用參數(shù)化查詢（在ADO,通過(guò)使用Command對(duì)象，在ADO.NET使用SqlCommand對(duì)象），那么上邊的問(wèn)題就不會(huì)出現(xiàn)。

11. 一直測(cè)試大數(shù)據(jù)庫(kù)

通常我們開(kāi)小數(shù)據(jù)量的數(shù)據(jù)上開(kāi)發(fā)，而終端用戶使用大量數(shù)據(jù)量的數(shù)據(jù)庫(kù)。這里有個(gè)問(wèn)題：磁盤(pán)時(shí)便宜的而性能問(wèn)題被發(fā)現(xiàn)時(shí)已經(jīng)太遲了。?

12.不要使用insert導(dǎo)入大量數(shù)據(jù)

如果不是非常必要，請(qǐng)使用DTS或者BCP工具，這是一個(gè)既富有擴(kuò)展性又快速的解決方案。?

13.注意超時(shí)：

當(dāng)查詢數(shù)據(jù)庫(kù)時(shí)，缺省超時(shí)常很小15秒或30秒。記住查詢報(bào)告常比這個(gè)常。特別是當(dāng)數(shù)據(jù)增長(zhǎng)時(shí)。??

14.不要忽視并發(fā)修改

有時(shí)兩個(gè)用戶同時(shí)修改相同記錄。最后一個(gè)修改者修改成功，但前一個(gè)用戶的一些更新卻丟失了。檢查這種情況也很容易：產(chǎn)生一個(gè)時(shí)間戳列，當(dāng)修改時(shí)候檢查它。如果可能的話合并修改。如果有沖突，則提示用戶一些動(dòng)作。

15.當(dāng)想詳細(xì)表插入記錄時(shí)，不要使用從主表中SELECT max(ID)

這是一個(gè)常見(jiàn)錯(cuò)誤。當(dāng)兩個(gè)用戶同時(shí)插入數(shù)據(jù)時(shí)將失敗。用SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY其中一個(gè)。盡可能避免使用@@IDENTITY。因?yàn)槭褂糜|發(fā)器可能產(chǎn)生bug.

16.避免使用可空列

他們使用一個(gè)多于的字節(jié)在每個(gè)可空列。當(dāng)查詢時(shí)將產(chǎn)生更多的開(kāi)銷。DAL也更難編碼。因?yàn)槊看问褂每煽樟卸夹枰獧z查。我并不是說(shuō)null是邪惡的化身。當(dāng)空數(shù)據(jù)是你商業(yè)規(guī)則的一部分時(shí)，我相信他們有好的用法和簡(jiǎn)化代碼。一些空列使用在下列情況：
CustomerName1
CustomerAddress1
CustomerEmail1
CustomerName2
CustomerAddress2
CustomerEmail3
CustomerName1
CustomerAddress2
CustomerEmail3
這是糟糕的。經(jīng)歷避免。請(qǐng)規(guī)范化你的，它將更擴(kuò)展性和快速，減少空列。

17.不要使用TEXT數(shù)據(jù)類型

除非你為了大數(shù)據(jù)而使用它。Text數(shù)據(jù)類型對(duì)查詢來(lái)說(shuō)不靈活，慢，而且浪費(fèi)大量的空間。經(jīng)常varchar處理數(shù)據(jù)更好。

18.不要使用臨時(shí)表

除非必要。常使用子查詢替換臨時(shí)表。它常增加開(kāi)銷，當(dāng)運(yùn)行于COM+給你帶來(lái)麻煩。因?yàn)樗褂脭?shù)據(jù)庫(kù)連接池而臨時(shí)表將永遠(yuǎn)保持。在SQL Server 2000中，表變量可作為可選方案。

19.學(xué)會(huì)怎么讀執(zhí)行計(jì)劃

SQL Server分析器是你的朋友。你將學(xué)會(huì)他們?cè)趺垂ぷ鞯摹２榭床樵?#xff0c;索引怎么影響性能。

20.使用參照完整性

這將為你節(jié)省大量時(shí)間，定義你所有的鍵，唯一約束，外鍵。

總結(jié)

以上是生活随笔為你收集整理的在SQL Server上该做的和不该做的的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。