數(shù)據(jù)庫(kù)開發(fā)規(guī)范Ver2.0.doc

數(shù)據(jù)庫(kù)開發(fā)規(guī)范創(chuàng)建者：阿福版本記錄版本修改者修改時(shí)間內(nèi)容V1.0 起草阿福2005-03-17建立最初版本V1.1阿福2005-03-31增加數(shù)據(jù)庫(kù)物理優(yōu)化方案V2.0阿福2005-05-23增加數(shù)據(jù)訪問(wèn)模式根據(jù)網(wǎng)友意見，修改部分錯(cuò)誤感謝勇者無(wú)敵()提供BUG說(shuō)明 本文檔整理于一些SQL Server開發(fā)中的經(jīng)驗(yàn)，可能對(duì)于其他類型的數(shù)據(jù)庫(kù)并不合適。筆者會(huì)在稍后繼續(xù)增加其他數(shù)據(jù)庫(kù)的規(guī)范部分。本文檔的很多知識(shí)來(lái)源于網(wǎng)絡(luò)，如果發(fā)現(xiàn)其中有涉及版權(quán)的問(wèn)題，請(qǐng)與我聯(lián)系，我將在第一時(shí)間刪除非法引用的內(nèi)容。本文檔旨在和大家交流經(jīng)驗(yàn)，任何人均可對(duì)此文檔進(jìn)行使用、修改或增加。歡迎大家積極參與，共同交流經(jīng)驗(yàn)。 我叫阿福，我的MSN是：ah_，我的QQ是：12304685。歡迎大家和我一起討論，無(wú)論是批評(píng)還是交流，我都會(huì)非常樂(lè)意。目錄說(shuō)明2目錄31、命名規(guī)范51.1、命名總規(guī)則51.2、表命名規(guī)范51.3、字段命名規(guī)范62、SQL語(yǔ)句規(guī)范63、存儲(chǔ)過(guò)程編碼規(guī)范73.1、只允許應(yīng)用程序通過(guò)存儲(chǔ)過(guò)程訪問(wèn)數(shù)據(jù)庫(kù)73.2、命名規(guī)范93.3、存儲(chǔ)過(guò)程的參數(shù)命名93.4、存儲(chǔ)過(guò)程返回的記錄集93.5、格式約定93.6、事務(wù)使用約定103.7、游標(biāo)使用約定103.8、表鎖定114、數(shù)據(jù)庫(kù)設(shè)計(jì)規(guī)范134.1數(shù)據(jù)完整性規(guī)范（編碼期）134.2、設(shè)計(jì)工具和版本劃分146、數(shù)據(jù)庫(kù)優(yōu)化146.1、數(shù)據(jù)庫(kù)性能優(yōu)化規(guī)范（運(yùn)行期）146.2、拆分表示例146.3、冗余字段建立示例156.4、冗余表建立示例156.5、存儲(chǔ)過(guò)程中分頁(yè)方案157、整體優(yōu)化和物理優(yōu)化167.1、查詢優(yōu)化方法167.1.1、原因167.1.2、查詢優(yōu)化方法168、數(shù)據(jù)訪問(wèn)模式（Data Access Patterns）238.1、解耦模式238.1.1、數(shù)據(jù)訪問(wèn)器248.1.2、主動(dòng)域?qū)ο?48.1.3、對(duì)象/關(guān)系映射248.1.4、層248.2、資源模式248.2.1、資源修飾器248.2.2、資源池248.2.3、資源定時(shí)器248.2.4、資源描述器258.2.5、重試器258.3、輸入輸出模式258.3.1、選擇工廠258.3.2、域?qū)ο蠊S258.3.3、更新工廠258.3.4、域?qū)ο笱b配器258.3.5、分頁(yè)迭代器258.4、緩存模式258.4.1、緩存訪問(wèn)器268.4.2、即時(shí)緩存268.4.3、填裝緩存268.4.4、緩存查找序列268.4.5、緩存收集器268.4.6、緩存復(fù)制器268.4.7、緩存統(tǒng)計(jì)268.5、并發(fā)模式268.5.1、事務(wù)278.5.2、樂(lè)觀鎖定278.5.3、悲觀鎖定278.5.4、補(bǔ)償事務(wù)27附錄27附錄1：T-SQL 編碼標(biāo)準(zhǔn)271、命名規(guī)范1.1、命名總規(guī)則1、 所有名稱的字符范圍為：A-Z, a-z, 0-9 和_(下劃線)。不允許使用其他字符作為名稱。2、 采用英文單詞或英文短語(yǔ)（包括縮寫）作為名稱，不能使用無(wú)意義的字符或漢語(yǔ)拼音。3、 名稱應(yīng)該清晰明了，能夠準(zhǔn)確表達(dá)事物的含義，最好可讀，遵循“見名知義”的原則。1.2、表命名規(guī)范1、不使用tab或tbl作為表前綴（本來(lái)就是一個(gè)表，為什么還要說(shuō)明）2、表名以代表表內(nèi)的內(nèi)容的一個(gè)和多個(gè)名詞組成，以下劃線分隔，每個(gè)名詞的第一個(gè)字母大寫。3、使用表的內(nèi)容分類作為表名的前綴：如，與用戶信息相關(guān)的表使用前綴User_，與內(nèi)容相關(guān)的信息使用前綴Content_。4、表的前綴以后，是表的具體內(nèi)容的描述。如：用戶登錄信息的表名為：User_Login，用戶在論壇中的信息的表名為：User_BBS_Info5、一些作為多對(duì)多連接的表，可以使用兩個(gè)表的前綴作為表名：如：用戶登錄表User_Login，用戶分組表Group_Info，這兩個(gè)表建立多對(duì)多關(guān)系的表名為：User_Group_Relation6、當(dāng)系統(tǒng)中有一些少量的，重復(fù)出現(xiàn)的值時(shí)，使用字典表來(lái)節(jié)約存儲(chǔ)空間和優(yōu)化查詢。如地區(qū)、系統(tǒng)中用戶類型的代號(hào)等。這類值不會(huì)在程序的運(yùn)行期變化，但是需要存儲(chǔ)在數(shù)據(jù)庫(kù)中。 就地區(qū)而言，如果我們要查詢某個(gè)地區(qū)的記錄，則數(shù)據(jù)庫(kù)需要通過(guò)字符串匹配的方式來(lái)查詢；如果將地區(qū)改為一個(gè)地區(qū)的代號(hào)保存在表中，查詢時(shí)通過(guò)地區(qū)的代號(hào)來(lái)查詢，則查詢的效率將大大提高。程序中宜大量的使用字典表來(lái)表示這類值。字典表中保存這類值的代號(hào)和實(shí)體的集合，以外鍵的方式關(guān)聯(lián)到使用這類值的表中。然而，在編碼階段，程序員并不使用字典表，因?yàn)槭紫炔樵冏值浔碇袑?shí)體的代號(hào)，違背了提高查詢效率的初衷。程序員在數(shù)據(jù)字典的幫助下，直接使用代號(hào)來(lái)代表實(shí)體，從而提高效率。雖然字典表在實(shí)際上并不使用，但是仍應(yīng)該保留在數(shù)據(jù)庫(kù)中（起碼是在開發(fā)期內(nèi)保留）。字典表作為另一種形式上的“數(shù)據(jù)字典文檔”出現(xiàn)，以說(shuō)明數(shù)據(jù)庫(kù)中哪些表的哪些字段是使用了字典表的。為了提高數(shù)據(jù)庫(kù)的數(shù)據(jù)完整性，在開發(fā)階段可以保留完整的字典表和普通表的外鍵約束。但是在數(shù)據(jù)庫(kù)的運(yùn)行階段，應(yīng)該將普通表和字典表的外鍵刪除，以提高運(yùn)行效率，特別是某些表使用了很多字典表的情況。 案例：某數(shù)據(jù)庫(kù)中有百萬(wàn)條用戶信息，應(yīng)用系統(tǒng)中常常需要按照地區(qū)要查詢用戶的信息。用戶信息表以前是按照具體的地區(qū)名稱來(lái)保存的，現(xiàn)在將具體的名稱改為字典表中的地區(qū)代號(hào)，查詢效率大大提高。 字典表統(tǒng)一以Dic_作為前綴。1.3、字段命名規(guī)范1、字段不使用任何前綴（表名代表了一個(gè)名稱空間，字段前面再加前綴顯得羅嗦）2、字典名也避免采用過(guò)于普遍過(guò)于簡(jiǎn)單的名稱：例如，用戶表中，用戶名的字段為UserName比Name更好。3、布爾型的字段，以一些助動(dòng)詞開頭，更加直接生動(dòng)：如，用戶是否有留言HasMessage，用戶是否通過(guò)檢查IsChecked等。4、字段名為英文短語(yǔ)、形容詞+名詞或助動(dòng)詞+動(dòng)詞時(shí)態(tài)的形式表示，大小寫混合，遵循“見名知義”的原則。2、SQL語(yǔ)句規(guī)范1、不允許寫SELECT * FROM ，必須指明需要讀取的具體字段。2、不允許在應(yīng)用程序代碼中直接寫SQL語(yǔ)句訪問(wèn)數(shù)據(jù)庫(kù)。3、避免在一行內(nèi)寫太長(zhǎng)的SQL語(yǔ)句，在SQL關(guān)鍵字的地方將SQL語(yǔ)句分成多行會(huì)更加清晰。 如：SELECT UserID,UserName,UserPwd FROM User_Login WHERE AreaID=20修改成：SELECT UserID,UserName,UserPwdFROM User_LoginWHERE AreaID=20更加直觀4、在一些塊形式的SQL語(yǔ)句中，就算只有一行代碼，也要加上BEGINEND塊。 如：IF EXISTS()SET nVar = 100應(yīng)該寫成：IF EXISTS()BEGINSET nVar = 100END5、SQL批處理語(yǔ)句的空行和縮進(jìn)與一般的結(jié)構(gòu)化程序語(yǔ)言一致，應(yīng)該保持良好的代碼格式。6、所有的SQL關(guān)鍵字大寫3、存儲(chǔ)過(guò)程編碼規(guī)范3.1、只允許應(yīng)用程序通過(guò)存儲(chǔ)過(guò)程訪問(wèn)數(shù)據(jù)庫(kù) 只允許應(yīng)用程序通過(guò)存儲(chǔ)過(guò)程訪問(wèn)數(shù)據(jù)庫(kù)，而不允許直接在代碼中寫SQL語(yǔ)句訪問(wèn)數(shù)據(jù)庫(kù)。在數(shù)據(jù)庫(kù)開發(fā)項(xiàng)目中，大量使用存儲(chǔ)過(guò)程有很多的好處，首先看微軟提供信息：使用 SQL Server 中的存儲(chǔ)過(guò)程而不使用存儲(chǔ)在客戶計(jì)算機(jī)本地的 Transact-SQL 程序的優(yōu)勢(shì)有： 允許模塊化程序設(shè)計(jì)： 只需創(chuàng)建過(guò)程一次并將其存儲(chǔ)在數(shù)據(jù)庫(kù)中，以后即可在程序中調(diào)用該過(guò)程任意次。存儲(chǔ)過(guò)程可由在數(shù)據(jù)庫(kù)編程方面有專長(zhǎng)的人員創(chuàng)建，并可獨(dú)立于程序源代碼而單獨(dú)修改。允許更快執(zhí)行： 如果某操作需要大量 Transact-SQL 代碼或需重復(fù)執(zhí)行，存儲(chǔ)過(guò)程將比 Transact-SQL 批代碼的執(zhí)行要快。將在創(chuàng)建存儲(chǔ)過(guò)程時(shí)對(duì)其進(jìn)行分析和優(yōu)化，并可在首次執(zhí)行該過(guò)程后使用該過(guò)程的內(nèi)存中版本。每次運(yùn)行 Transact-SQL 語(yǔ)句時(shí)，都要從客戶端重復(fù)發(fā)送，并且在 SQL Server 每次執(zhí)行這些語(yǔ)句時(shí)，都要對(duì)其進(jìn)行編譯和優(yōu)化。減少網(wǎng)絡(luò)流量： 一個(gè)需要數(shù)百行 Transact-SQL 代碼的操作由一條執(zhí)行過(guò)程代碼的單獨(dú)語(yǔ)句就可實(shí)現(xiàn)，而不需要在網(wǎng)絡(luò)中發(fā)送數(shù)百行代碼?？勺鳛榘踩珯C(jī)制使用： 即使對(duì)于沒(méi)有直接執(zhí)行存儲(chǔ)過(guò)程中語(yǔ)句的權(quán)限的用戶，也可授予他們執(zhí)行該存儲(chǔ)過(guò)程的權(quán)限。 除此以外，使用存儲(chǔ)過(guò)程的好處還有：1、 在邏輯上，存儲(chǔ)過(guò)程將應(yīng)用程序?qū)雍蛿?shù)據(jù)庫(kù)物理結(jié)構(gòu)分離開來(lái)。存儲(chǔ)過(guò)程形成了一個(gè)應(yīng)用程序和數(shù)據(jù)庫(kù)之間的接口。這樣的接口抽象了復(fù)雜的數(shù)據(jù)庫(kù)結(jié)構(gòu)，符合極限編程中“基于接口編程”的思想。2、 將主要的業(yè)務(wù)邏輯封裝在存儲(chǔ)過(guò)程中，能夠避免在應(yīng)用程序?qū)訉懘罅康拇a（在應(yīng)用程序中通過(guò)字符串插入太長(zhǎng)的SQL語(yǔ)句影響效率，而且維護(hù)困難）。有助于提高開發(fā)效率，并且直接在查詢分析器中調(diào)試存儲(chǔ)過(guò)程，能夠更早的發(fā)現(xiàn)系統(tǒng)中的邏輯問(wèn)題，從而提高代碼的質(zhì)量。3、 在網(wǎng)站一類的應(yīng)用系統(tǒng)中，SQL注入式漏洞一直是難以完全杜絕的漏洞。如果只通過(guò)存儲(chǔ)過(guò)程來(lái)訪問(wèn)數(shù)據(jù)庫(kù)，能夠大大減少這類安全性問(wèn)題。（因此，就算是簡(jiǎn)單的只有一句的SQL語(yǔ)句，也應(yīng)該寫成存儲(chǔ)過(guò)程。）4、 由于采用存儲(chǔ)過(guò)程，應(yīng)用程序的層面可以不關(guān)心具體的數(shù)據(jù)庫(kù)結(jié)構(gòu)，而只關(guān)心存儲(chǔ)過(guò)程的接口調(diào)用。因此，在以下一些情況，存儲(chǔ)過(guò)程的優(yōu)勢(shì)非常明顯：需求變更，表的結(jié)構(gòu)必須要改變。使用存儲(chǔ)過(guò)程，只要參數(shù)不變，我們就只需要修改相應(yīng)的存儲(chǔ)過(guò)程，而不需要修改應(yīng)用程序的代碼。這樣的設(shè)計(jì)將減小需求變更對(duì)項(xiàng)目的影響。為提高效率，使部分字段冗余：一些經(jīng)常性訪問(wèn)的字段，我們可以在相關(guān)的表中進(jìn)行冗余存儲(chǔ)。這樣既提高了效率，又通過(guò)存儲(chǔ)過(guò)程屏蔽了冗余細(xì)節(jié)。為提高效率，使用冗余表（拆分表）：一些大的表，為了提高查詢效率，可能需要將記錄分別保存到多個(gè)表中去。使用存儲(chǔ)過(guò)程，有存儲(chǔ)過(guò)程來(lái)決定從哪些拆分的表中獲取或插入數(shù)據(jù)。這樣提高了效率，又不必在應(yīng)用程序?qū)用骊P(guān)心具體的拆分規(guī)則。5、 使用存儲(chǔ)過(guò)程，便于在項(xiàng)目后期或者運(yùn)行中集中優(yōu)化系統(tǒng)性能。在項(xiàng)目開發(fā)過(guò)程中，由于各種原因，往往無(wú)法編寫高效的代碼，這個(gè)問(wèn)題常常在項(xiàng)目后期或者在運(yùn)行期體現(xiàn)出來(lái)。通過(guò)存儲(chǔ)過(guò)程來(lái)封裝對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)，可以在項(xiàng)目集成以后，通過(guò)試運(yùn)行觀察系統(tǒng)的運(yùn)行效率，從而很容易找出系統(tǒng)的瓶頸，并能夠通過(guò)優(yōu)化存儲(chǔ)過(guò)程的代碼來(lái)提高系統(tǒng)的運(yùn)行效率。這樣的優(yōu)化，比在運(yùn)用程序中優(yōu)化更有效，更容易。6、在開發(fā)中的人員分工上，能夠更加發(fā)揮團(tuán)隊(duì)中個(gè)人的專長(zhǎng)。可以把存儲(chǔ)過(guò)程看成多層開發(fā)中的一個(gè)層面，這個(gè)層面可以由數(shù)據(jù)庫(kù)開發(fā)經(jīng)驗(yàn)豐富的（一個(gè)或多個(gè)）程序員擔(dān)任，其他程序員就可以專心處理系統(tǒng)中的其他環(huán)節(jié)，只需要關(guān)系存儲(chǔ)過(guò)程的調(diào)用接口即可。這樣，能夠使程序員的思維集中在某個(gè)專一的問(wèn)題上，能夠發(fā)揮更好的分工合作和效率。同時(shí)，過(guò)多的使用存儲(chǔ)過(guò)程，也存在以下一些疑慮：?jiǎn)栴}一：存儲(chǔ)過(guò)程編譯后，將作為數(shù)據(jù)庫(kù)的全局對(duì)象保存，太多的存儲(chǔ)過(guò)程將占用大量的數(shù)據(jù)庫(kù)服務(wù)器的內(nèi)存。問(wèn)題二：在存儲(chǔ)過(guò)程中實(shí)現(xiàn)大量的邏輯，將使大量的運(yùn)算在數(shù)據(jù)庫(kù)服務(wù)器上完成，而不是在應(yīng)用服務(wù)器上完成。當(dāng)訪問(wèn)量很大的時(shí)候，會(huì)大大消耗數(shù)據(jù)庫(kù)服務(wù)器的CPU占用率。在此還存在這個(gè)一個(gè)案例：有一個(gè)訪問(wèn)量巨大的網(wǎng)站，有多臺(tái)WEB服務(wù)器構(gòu)成一個(gè)負(fù)載均衡的服務(wù)器群集，但是只有一臺(tái)中心的數(shù)據(jù)庫(kù)服務(wù)器。當(dāng)訪問(wèn)量持續(xù)增加的時(shí)候，接入更多的WEB服務(wù)器來(lái)滿足高并發(fā)量的訪問(wèn)；但是數(shù)據(jù)庫(kù)服務(wù)器卻沒(méi)辦法一直增加。因此，就需要盡量在WEB服務(wù)器上完成業(yè)務(wù)邏輯，盡量避免消耗數(shù)據(jù)庫(kù)服務(wù)器的資源。 對(duì)于這兩個(gè)擔(dān)心，我的想法是：?jiǎn)栴}一的解決：存儲(chǔ)過(guò)程是經(jīng)過(guò)編譯后的SQL語(yǔ)句，在內(nèi)存中是二進(jìn)制的代碼，并不會(huì)消耗太多內(nèi)存。并且，存儲(chǔ)過(guò)程比起直接使用SQL語(yǔ)句來(lái)說(shuō)，效率大大提高。換個(gè)角度來(lái)說(shuō)，這是一個(gè)“以空間換時(shí)間”的方案，多消耗一點(diǎn)內(nèi)存來(lái)?yè)Q取效率的提高，是值得的。問(wèn)題二的解決：首先，在實(shí)現(xiàn)業(yè)務(wù)邏輯的問(wèn)題上，在存儲(chǔ)過(guò)程中實(shí)現(xiàn)比在應(yīng)用程序中實(shí)現(xiàn)更容易；其次，從開發(fā)效率上，存儲(chǔ)過(guò)程的開發(fā)比應(yīng)用程序更簡(jiǎn)單（就完成相同邏輯而言）。在高訪問(wèn)量的系統(tǒng)中，應(yīng)用服務(wù)器和數(shù)據(jù)庫(kù)服務(wù)器的資源分配的問(wèn)題，應(yīng)該從成本的角度來(lái)考慮：軟件開發(fā)中的成本，人工支出的費(fèi)用遠(yuǎn)遠(yuǎn)高于硬件支出的成本。我們可以很容易花錢購(gòu)買更好的服務(wù)器，但是很難花錢讓開發(fā)人員使程序性能有大幅度的提高。使用存儲(chǔ)過(guò)程來(lái)封裝業(yè)務(wù)邏輯，首先節(jié)省的是大量的開發(fā)時(shí)間和調(diào)試時(shí)間，并能夠大大提高代碼的質(zhì)量。因此，從成本來(lái)說(shuō)，應(yīng)該使用存儲(chǔ)過(guò)程。對(duì)于大訪問(wèn)量的情況，最簡(jiǎn)單的辦法是投入更多的硬件成本：更快的硬盤，更大的內(nèi)存和更多的CPU，還有更好的網(wǎng)卡等等。其次，在應(yīng)用程序的層面，可以大量的使用靜態(tài)文件緩存的辦法來(lái)減輕數(shù)據(jù)庫(kù)的壓力。如：不經(jīng)常變化的信息，可以從數(shù)據(jù)庫(kù)服務(wù)器中讀取，保存為應(yīng)用服務(wù)器上的XML靜態(tài)文件等。實(shí)在不行的話，應(yīng)該在系統(tǒng)設(shè)計(jì)之初，考慮可能的訪問(wèn)量，將系統(tǒng)設(shè)計(jì)成分布式的。這樣就能從根本上解決大訪問(wèn)量的問(wèn)題。3.2、命名規(guī)范1、存儲(chǔ)過(guò)程的前綴和表名的前綴類似：把一系列表看成一個(gè)對(duì)象，字段為對(duì)象的屬性，存儲(chǔ)過(guò)程則為訪問(wèn)對(duì)象的方法。如：添加用戶的存儲(chǔ)過(guò)程取名為：User_AddUser2、存儲(chǔ)過(guò)程使用模塊的前綴來(lái)命名。如，用戶管理的存儲(chǔ)過(guò)程使用前綴user_。3、存儲(chǔ)過(guò)程的前綴之后，是動(dòng)詞+名詞形式的存儲(chǔ)過(guò)程名（也可以是動(dòng)詞短語(yǔ)）。3.3、存儲(chǔ)過(guò)程的參數(shù)命名1、參數(shù)名采用匈牙利命名法，使用類型的前綴2、每個(gè)存儲(chǔ)過(guò)程都有：errno int和errmsg varchar(255)兩個(gè)輸出參數(shù)。應(yīng)用程序中可以根據(jù)這兩個(gè)參數(shù)得到存儲(chǔ)過(guò)程執(zhí)行的情況。(這兩個(gè)參數(shù)使用默認(rèn)值，可以忽略)errno為整型的錯(cuò)誤信息代碼，執(zhí)行成功返回0。Errno的值的具體含義通過(guò)errmsg參數(shù)說(shuō)明，或者通過(guò)代碼中的注釋或文檔。Errmsg為錯(cuò)誤信息的字符串描述，這個(gè)參數(shù)主要用于調(diào)試期作為說(shuō)明，避免在應(yīng)用程序中使用該值。同時(shí)，要注意英文版系統(tǒng)和中文版系統(tǒng)中，信息的語(yǔ)言選擇對(duì)程序的影響。3.4、存儲(chǔ)過(guò)程返回的記錄集1、存儲(chǔ)過(guò)程的輸出記錄集：為程序的結(jié)構(gòu)清晰，存儲(chǔ)過(guò)程最好只返回一個(gè)記錄集。但在某些為了提高性能的場(chǎng)合，還是可以輸出多個(gè)記錄集2、記錄集中，每個(gè)輸出的字段最后都指定字段的別名，以免真實(shí)的字段名信息流失到客戶端，從而加大黑客找到系統(tǒng)漏洞的可能。3.5、格式約定1、 所有SQL關(guān)鍵字大寫2、 使用良好的變量命名規(guī)范3、 保持良好的結(jié)構(gòu)，包括空行、縮進(jìn)和空格等。4、 塊狀的語(yǔ)句，一定要寫上BEGINEND5、 在每個(gè)存儲(chǔ)過(guò)程的開頭加上詳細(xì)的注釋：包括存儲(chǔ)過(guò)程名稱、參數(shù)說(shuō)明、功能說(shuō)明、返回?cái)?shù)據(jù)集說(shuō)明、以及作者和版權(quán)聲明。6、 每個(gè)存儲(chǔ)過(guò)程內(nèi)的代碼前后必須加上SET NOCOUNT ON 和SET NOCOUNT OFF。7、 存儲(chǔ)過(guò)程格式的示例如下：/* Name: User_AddUser* Purpose: Add a user to system* Parameters: strUserName the users login name* strPwd the users password, encrypted by MD5* errno the error number, its a “output” param* errmsg the error message, its a “output” param* Returns: the users ID, as a result set.* Author: ahfu, ah_* Copyright: CopyRights By SyNet 2005.All Rights Reserved.* Create At: 2005-03-18 Last Modify: 2005-03-19*/CREATE PROCEDURE User_AddUser(strUserName varchar(20),strPwd varchar(50),errno int = 0 OUTPUT,errmsg varchar(255)=NULL OUTPUT)ASBEGINSET NOCOUNT ON/*以下是存儲(chǔ)過(guò)程的代碼*/SET NOCOUNT OFFEND3.6、事務(wù)使用約定1、如果對(duì)記錄的更改操作不止一次，則需要把這些操作寫在事務(wù)中。2、事務(wù)必須顯式提交或者取消。3.7、游標(biāo)使用約定1、 若無(wú)必要，不要使用游標(biāo)2、 包含游標(biāo)的存儲(chǔ)過(guò)程，必須對(duì)性能進(jìn)行認(rèn)真測(cè)試。3.8、表鎖定對(duì)于依賴于SQL的客戶而言，如果不對(duì)同時(shí)使用數(shù)據(jù)庫(kù)的用戶數(shù)量進(jìn)行檢查，可能會(huì)對(duì)數(shù)據(jù)完整性造成嚴(yán)重的破壞。使用這些鎖提示，你可以確保能夠合理地處理數(shù)據(jù)修改。在過(guò)去的幾年中，SQL Server從一個(gè)小的辦公數(shù)據(jù)存儲(chǔ)逐漸發(fā)展為企業(yè)級(jí)的數(shù)據(jù)庫(kù)服務(wù)器。用戶同時(shí)訪問(wèn)數(shù)據(jù)庫(kù)的數(shù)量也隨著它的升級(jí)而遞增。SQL Server 2000的鎖資源標(biāo)準(zhǔn)似乎不是非常有效，但是，感謝上帝的是，它提供了可以重設(shè)標(biāo)準(zhǔn)鎖的功能。鎖提示可以告訴服務(wù)器該如何鎖資源，但是在我們重設(shè)它們之前先來(lái)檢查一下鎖。1、什么是鎖 像SQL server一樣的關(guān)系數(shù)據(jù)庫(kù)使用鎖來(lái)防止用戶“互相踩到對(duì)方的腳趾頭”。也就是說(shuō)，鎖可以防止用戶造成修改數(shù)據(jù)時(shí)的碰撞。當(dāng)一個(gè)用戶鎖住一段代碼時(shí)候，其它的用戶都不能修改這段數(shù)據(jù)。另外，一個(gè)鎖阻止了用戶觀看未被授權(quán)的數(shù)據(jù)修改。用戶必須等待到數(shù)據(jù)修改并保存之后才能夠查看它。數(shù)據(jù)必須使用不同的方法來(lái)加鎖。SQL Server 2000使用鎖來(lái)實(shí)現(xiàn)多用戶同時(shí)修改數(shù)據(jù)庫(kù)同一數(shù)據(jù)時(shí)的同步控制。2、死鎖 一個(gè)數(shù)據(jù)庫(kù)的死鎖是發(fā)生在兩個(gè)或多于兩個(gè)訪問(wèn)一些資源的數(shù)據(jù)庫(kù)會(huì)話中的，并且這些會(huì)話相互之間有依賴關(guān)系。死鎖是可以在任意一個(gè)多線程的系統(tǒng)成出現(xiàn)的一個(gè)情況，不僅僅局限于關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)。一個(gè)多線程系統(tǒng)中的線程可能需要一個(gè)或多個(gè)資源(例如，鎖)。如果申請(qǐng)的資源正在被另外一個(gè)線程所使用，那么第一個(gè)線程就需要等待持有該資源的線程的釋放它所需要的資源。假設(shè)等待線程持有一個(gè)那個(gè)正擁有線程所依賴的資源。下面的這一段代碼就可以造成死鎖異常現(xiàn)象的發(fā)生：System.Data.SqlClient.SqlException: Transaction (Process ID 12) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.當(dāng)一個(gè)SQL Server的調(diào)用和另外一個(gè)資源發(fā)生沖突時(shí)就會(huì)拋出異常，這個(gè)資源持有一個(gè)必要的資源。結(jié)果是，一個(gè)進(jìn)程就被終止了。當(dāng)進(jìn)程的ID號(hào)成為系統(tǒng)的唯一標(biāo)識(shí)的時(shí)候，這會(huì)是一個(gè)很平常死鎖的消息錯(cuò)誤。3、鎖的類型 一個(gè)數(shù)據(jù)庫(kù)系統(tǒng)在許多情況下都有可能鎖數(shù)據(jù)項(xiàng)。其可能性包括：Rows數(shù)據(jù)庫(kù)表中的一整行 Pages行的集合（通常為幾kb） Extents通常是幾個(gè)頁(yè)的集合 Table整個(gè)數(shù)據(jù)庫(kù)表 Database被鎖的整個(gè)數(shù)據(jù)庫(kù)表 除非有其它的說(shuō)明，數(shù)據(jù)庫(kù)根據(jù)情況自己選擇最好的鎖方式。不過(guò)值得感謝的是，SQL Server提供了一種避免默認(rèn)行為的方法。這是由鎖提示來(lái)完成的。4、提示 或許你有過(guò)許多如下的經(jīng)歷：需要重設(shè)SQL Server的鎖計(jì)劃，并且加強(qiáng)數(shù)據(jù)庫(kù)表中鎖范圍。TansactSQL提供了一系列不同級(jí)別的鎖提示，你可以在SELECT,INSERT,UPDATE和DELETE中使用它們來(lái)告訴SQL Server你需要如何通過(guò)重設(shè)任何的系統(tǒng)或事務(wù)級(jí)別來(lái)鎖表格?？梢詫?shí)現(xiàn)的提示包括：FASTFIRSTROW選取結(jié)果集中的第一行，并將其優(yōu)化 HOLDLOCK持有一個(gè)共享鎖直至事務(wù)完成 NOLOCK不允許使用共享鎖或獨(dú)享鎖。這可能會(huì)造成數(shù)據(jù)重寫或者沒(méi)有被確認(rèn)就返回的情況；因此，就有可能使用到臟數(shù)據(jù)。這個(gè)提示只能在SELECT中使用。 PAGLOCK鎖表格 READCOMMITTED只讀取被事務(wù)確認(rèn)的數(shù)據(jù)。這就是SQL Server的默認(rèn)行為。 READPAST跳過(guò)被其它進(jìn)程鎖住的行，所以返回的數(shù)據(jù)可能會(huì)忽略行的內(nèi)容。這也只能在SELECT中使用。 READUNCOMMITTED等價(jià)于NOLOCK. REPEATABLEREAD在查詢語(yǔ)句中，對(duì)所有數(shù)據(jù)使用鎖。這可以防止其它的用戶更新數(shù)據(jù)，但是新的行可能被其它的用戶插入到數(shù)據(jù)中，并且被最新訪問(wèn)該數(shù)據(jù)的用戶讀取。 ROWLOCK按照行的級(jí)別來(lái)對(duì)數(shù)據(jù)上鎖。SQL Server通常鎖到頁(yè)或者表級(jí)別來(lái)修改行，所以當(dāng)開發(fā)者使用單行的時(shí)候，通常要重設(shè)這個(gè)設(shè)置。 SERIALIZABLE等價(jià)于HOLDLOCK. TABLOCK按照表級(jí)別上鎖。在運(yùn)行多個(gè)有關(guān)表級(jí)別數(shù)據(jù)操作的時(shí)候，你可能需要使用到這個(gè)提示。 UPDLOCK當(dāng)讀取一個(gè)表的時(shí)候，使用更新鎖來(lái)代替共享鎖，并且保持一直擁有這個(gè)鎖直至事務(wù)結(jié)束。它的好處是，可以允許你在閱讀數(shù)據(jù)的時(shí)候可以不需要鎖，并且以最快的速度更新數(shù)據(jù)。 XLOCK給所有的資源都上獨(dú)享鎖，直至事務(wù)結(jié)束。 微軟將提示分為兩類：granularity和isolation-level。Granularity提示包括PAGLOCK, NOLOCK, ROWLOCK和TABLOCK。而isolation-level提示包括HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD和SERIALIZABLE。這些提示允許管理者通過(guò)使用SQL Server來(lái)控制鎖定，并且，在Transact-SQL聲明中這包含有這些提示。它們被放在聲明的FROM部分中，位于WITH之后。WITH聲明在SQL Server 2000中是可選部分，但是微軟強(qiáng)烈要求將它包含在內(nèi)。這就使得許多人都認(rèn)為在未來(lái)的SQL Server發(fā)行版中，就可能會(huì)包含這個(gè)聲明。下面是提示應(yīng)用于FROM從句中的例子： FROM ,.n :=table_name AS table_alias WITH ( ,.n ) := INDEX ( index_val ,.n )| FASTFIRSTROW| HOLDLOCK| NOLOCK| PAGLOCK| READCOMMITTED| READPAST| READUNCOMMITTED| REPEATABLEREAD| ROWLOCK| SERIALIZABLE| TABLOCK| TABLOCKX| UPDLOCK| XLOCK 下面是Transact-SQL聲明中從Northwind數(shù)據(jù)庫(kù)的Emplyees表中選擇所有的數(shù)據(jù)的語(yǔ)句： SELECT *FROM Employees WITH (nolock)這個(gè)語(yǔ)句就提供出了所有的數(shù)據(jù)，包括正在被其它處理器使用的數(shù)據(jù)，所以，得出的數(shù)據(jù)可能是臟數(shù)據(jù)，但是對(duì)于任務(wù)而言并沒(méi)有很大的影響。另外一個(gè)例子是更新所有表中的行，并設(shè)定一個(gè)特定值。UPDATEEmployees WITH (tablock)SET Title=Test這個(gè)例子就是更新表中所有的行，所以使用了一個(gè)表鎖。5、題外話我必須強(qiáng)調(diào)一個(gè)事實(shí)，就是即使在代碼中使用了一個(gè)表提示，查詢優(yōu)化器也有可能會(huì)忽略這個(gè)提示。如果當(dāng)查詢優(yōu)化器表提示沒(méi)有選擇該表，而是使用在后來(lái)的查詢中，就會(huì)忽略這個(gè)表提示。同樣，查詢優(yōu)化器通常會(huì)選擇表的索引視圖而不是表。在表包含有計(jì)算出的列的時(shí)候，表提示也有可能被忽略。6、使用你的判斷力 在你的應(yīng)用程序中使用表提示依賴于你需要查詢什么。所以，是否使用這些提示將完全依賴于你的需要。例如，許多管理者喜歡使用FASTFIRSTROW提示來(lái)快速返回第一行的東西。這就使得在查詢完成后，可以提供給他們其所需要的東西。當(dāng)數(shù)據(jù)不可能被修改的時(shí)候（例如，存檔數(shù)據(jù)），使用NOLOCK 提示是一個(gè)很好的選擇，因?yàn)閿?shù)據(jù)基本上是靜態(tài)的。然而，從另一方面說(shuō)，在使用到財(cái)務(wù)方面等對(duì)精度要求高的方面的時(shí)候，這種方法就不適用。4、數(shù)據(jù)庫(kù)設(shè)計(jì)規(guī)范4.1數(shù)據(jù)完整性規(guī)范（編碼期）1、為便于在程序的編碼期查錯(cuò)，可以在設(shè)計(jì)數(shù)據(jù)庫(kù)的時(shí)候盡可能多的加上約束(check)。如，整型的字段的取值范圍等，常常為field0。2、同理，盡可能地在開發(fā)期間使用觸發(fā)器來(lái)驗(yàn)證數(shù)據(jù)的完整性。3、如果字段之間存在冗余，應(yīng)該編寫觸發(fā)器來(lái)管理冗余的字段3、在開發(fā)階段保存完整的主鍵、外鍵和唯一索引的約束。4、原則：編碼期間，數(shù)據(jù)完整性優(yōu)先于性能。在保障系統(tǒng)正確運(yùn)行的前提下盡可能的提高效率。4.2、設(shè)計(jì)工具和版本劃分1、使用ER-WIN設(shè)計(jì)數(shù)據(jù)庫(kù)，同時(shí)設(shè)計(jì)邏輯視圖和物理視圖（也可以是PowerDesigner等工具，通過(guò)ER圖形表示出數(shù)據(jù)表之間的關(guān)系）2、開發(fā)過(guò)程中盡量保證設(shè)計(jì)文檔和物理表結(jié)構(gòu)的一致變化3、數(shù)據(jù)庫(kù)分開發(fā)期和運(yùn)行期兩個(gè)版本：開發(fā)期版本主要體現(xiàn)完整的約束，運(yùn)行期版本體現(xiàn)效率的優(yōu)化。6、數(shù)據(jù)庫(kù)優(yōu)化6.1、數(shù)據(jù)庫(kù)性能優(yōu)化規(guī)范（運(yùn)行期）1、在運(yùn)行階段刪除不必要的約束(check)。2、盡量不要使用觸發(fā)器3、盡量保留主鍵約束4、適當(dāng)刪除外鍵，以提高性能5、在運(yùn)行期間，通過(guò)分析系統(tǒng)的訪問(wèn)量，創(chuàng)建索引來(lái)優(yōu)化性能6、分析每個(gè)表可能的數(shù)據(jù)增長(zhǎng)量，定義自動(dòng)拆分表規(guī)則。將大表進(jìn)行拆分來(lái)提高性能。7、預(yù)先考慮數(shù)據(jù)清理規(guī)則：在什么情況下刪除數(shù)據(jù)庫(kù)中的舊數(shù)據(jù)，以此來(lái)提高性能。8、制定數(shù)據(jù)庫(kù)備份和災(zāi)難恢復(fù)計(jì)劃。9、為效率考慮，可以在系統(tǒng)測(cè)試階段適當(dāng)增加冗余字段，或者冗余表。10、分頁(yè)的記錄輸出必須通過(guò)存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)，不能使用API游標(biāo)來(lái)分頁(yè)，這樣可以提高分頁(yè)的效率。6.2、拆分表示例案例：網(wǎng)站有200萬(wàn)用戶，有很多模塊圍繞用戶提供服務(wù)。 為提高效率，每個(gè)表最多只保存與用戶有關(guān)的10萬(wàn)記錄，200萬(wàn)條記錄拆分到20個(gè)表中。編號(hào)為1-10萬(wàn)的用戶將記錄保存到表一，100001-200000編號(hào)的記錄保存到表二，以此類推。建立一個(gè)拆分信息表，表中保存了哪些表是經(jīng)過(guò)拆分的，拆分到什么程度，拆分規(guī)則是什么。當(dāng)插入記錄的時(shí)候，首先判斷插入這條記錄的用戶的ID。存儲(chǔ)過(guò)程根據(jù)ID的范圍，自動(dòng)把數(shù)據(jù)插入到相應(yīng)的拆分表中去。當(dāng)按照條件查詢，存儲(chǔ)過(guò)程自動(dòng)連接所有的拆分表，叢中篩選出記錄。（一般情況下：同類型的查詢遠(yuǎn)遠(yuǎn)大于按照條件的全體查詢）6.3、冗余字段建立示例案例：留言本表中，要保存用戶的ID作為外鍵。通常，通過(guò)連接留言表和用戶表來(lái)得知是哪個(gè)用戶發(fā)布了留言。為提高效率，在留言本表中增建用戶名的字段。插入記錄的時(shí)候，同時(shí)保存用戶ID和用戶名。這樣，當(dāng)查詢時(shí)，就不必連接兩個(gè)表，使效率大大提高。但是，當(dāng)用戶修改用戶名時(shí)，要嗎更新其他表中的用戶名，要嗎忽略這種用戶名不一致的影響。如何處理取決于用戶名在模塊中的重要程度。6.4、冗余表建立示例案例：有用戶表和分組表，兩個(gè)表之間是多對(duì)多的關(guān)系，建立一個(gè)用戶與組的關(guān)系表來(lái)實(shí)現(xiàn)這種關(guān)系。用戶表中有百萬(wàn)條記錄，組表中幾千條記錄。如果每個(gè)用戶都屬于多個(gè)組的化，關(guān)聯(lián)表中將存在幾百萬(wàn)條記錄?，F(xiàn)在將用戶表和關(guān)聯(lián)表進(jìn)行拆分，拆分規(guī)則為用戶的ID范圍。當(dāng)查詢某用戶的組時(shí)，效率大大提高。但是當(dāng)查詢某組下的用戶時(shí)，需要關(guān)聯(lián)所有的拆分表，效率很低。為提高效率，建立一個(gè)冗余的用戶和組的關(guān)系表，這個(gè)關(guān)系表中保存第一個(gè)關(guān)系表中統(tǒng)一的內(nèi)容，但是拆分規(guī)則為組ID的范圍。這樣，當(dāng)查詢組中的用戶時(shí)，叢第二個(gè)關(guān)系表中查詢，效率大大提高。6.5、存儲(chǔ)過(guò)程中分頁(yè)方案方案一：1、首先統(tǒng)計(jì)得到符合條件的記錄數(shù)2、定義表變量：表變量的第一個(gè)字段為自增長(zhǎng)類型，第二個(gè)字段為記錄集中的唯一值字段（一般是主鍵）3、使用INSERT () SELECT 語(yǔ)句將符合條件的記錄的唯一值字段保存在表變量中。4、使用WHERE ID IN (SELECT ID FROM 表變量 WHERE ) 的方法從表變量中讀出需要的唯一值字段。方案二：1、首先統(tǒng)計(jì)符合條件的記錄數(shù)，并根據(jù)頁(yè)大小計(jì)算頁(yè)數(shù)2、如果讀取第一頁(yè)，直接使用TOP子句讀取3、如果頁(yè)數(shù)在前一半：結(jié)果集1：SELECT TOP CurPage*PageSize Fields FROM Table ORDER BY ID ASC結(jié)果集2：SELECT TOP PageSize * FROM (結(jié)果集1) ORDER BY ID DESC最終結(jié)果：SELECT * FROM (結(jié)果集2) ORDER BY ID ASC4、如果頁(yè)數(shù)在后一半：結(jié)果集1：SELECT TOP (PageCount-CurPage + 1)*PageSize Fields FROM Table ORDER BY ID DESC最終結(jié)果：SELECT TOP PageSize * FROM Table ORDER BY ID ASC7、整體優(yōu)化和物理優(yōu)化7.1、查詢優(yōu)化方法7.1.1、原因查詢速度慢的原因很多，常見如下幾種： 1、沒(méi)有索引或者沒(méi)有用到索引(這是查詢慢最常見的問(wèn)題，是程序設(shè)計(jì)的缺陷)。2、I/O吞吐量小，形成了瓶頸效應(yīng)。 3、沒(méi)有創(chuàng)建計(jì)算列導(dǎo)致查詢不優(yōu)化。 4、內(nèi)存不足。5、網(wǎng)絡(luò)速度慢。6、查詢出的數(shù)據(jù)量過(guò)大（可以采用多次查詢，其他的方法降低數(shù)據(jù)量）。7、鎖或者死鎖(這也是查詢慢最常見的問(wèn)題，是程序設(shè)計(jì)的缺陷)。8、sp_lock,sp_who,活動(dòng)的用戶查看,原因是讀寫競(jìng)爭(zhēng)資源。9、返回了不必要的行和列。10、查詢語(yǔ)句不好，沒(méi)有優(yōu)化。7.1.2、查詢優(yōu)化方法1、把數(shù)據(jù)、日志、索引放到不同的I/O設(shè)備上，增加讀取速度，以前可以將Tempdb應(yīng)放在RAID0上，SQL2000不在支持。數(shù)據(jù)量（尺寸）越大，提高I/O越重要。2、縱向、橫向分割表，減少表的尺寸(sp_spaceuse) 。3、升級(jí)硬件。4、根據(jù)查詢條件，建立索引，優(yōu)化索引、優(yōu)化訪問(wèn)方式，限制結(jié)果集的數(shù)據(jù)量。注意填充因子要適當(dāng)（最好是使用默認(rèn)值0）。索引應(yīng)該盡量小，使用字節(jié)數(shù)小的列建索引好（參照索引的創(chuàng)建）,不要對(duì)有限的幾個(gè)值的字段建單一索引如性別字段。5、提高網(wǎng)速。6、擴(kuò)大服務(wù)器的內(nèi)存,Windows 2000和SQL server 2000能支持4-8G的內(nèi)存。配置虛擬內(nèi)存：虛擬內(nèi)存大小應(yīng)基于計(jì)算機(jī)上并發(fā)運(yùn)行的服務(wù)進(jìn)行配置。運(yùn)行 Microsoft SQL Server 2000 時(shí)，可考慮將虛擬內(nèi)存大小設(shè)置為計(jì)算機(jī)中安裝的物理內(nèi)存的 1.5 倍。如果另外安裝了全文檢索功能，并打算運(yùn)行 Microsoft 搜索服務(wù)以便執(zhí)行全文索引和查詢，可考慮：將虛擬內(nèi)存大小配置為至少是計(jì)算機(jī)中安裝的物理內(nèi)存的 3 倍。將 SQL Server max server memory 服務(wù)器配置選項(xiàng)配置為物理內(nèi)存的 1.5 倍（虛擬內(nèi)存大小設(shè)置的一半）。7、增加服務(wù)器CPU個(gè)數(shù)；但是必須明白并行處理串行處理更需要資源例如內(nèi)存。使用并行還是串行程是MsSQL自動(dòng)評(píng)估選擇的。單個(gè)任務(wù)分解成多個(gè)任務(wù)，就可以在處理器上運(yùn)行。例如耽擱查詢的排序、連接、掃描和GROUP BY字句同時(shí)執(zhí)行，SQL SERVER根據(jù)系統(tǒng)的負(fù)載情況決定最優(yōu)的并行等級(jí)，復(fù)雜的需要消耗大量的CPU的查詢最適合并行處理。但是更新操作UPDATE，INSERT，DELETE還不能并行處理。8、如果是使用like進(jìn)行查詢的話，簡(jiǎn)單的使用index是不行的，但是全文索引，耗空間。 like a% 使用索引；like %a 不使用索引；用 like %a% 查詢時(shí)，查詢耗時(shí)和字段值總長(zhǎng)度成正比，所以不能用CHAR類型，而是VARCHAR。對(duì)于字段的值很長(zhǎng)的建全文索引。 9、DB Server 和APPLication Server 分離；OLTP和OLAP分離。10、分布式分區(qū)視圖可用于實(shí)現(xiàn)數(shù)據(jù)庫(kù)服務(wù)器聯(lián)合體。聯(lián)合體是一組分開管理的服務(wù)器，但它們相互協(xié)作分擔(dān)系統(tǒng)的處理負(fù)荷。這種通過(guò)分區(qū)數(shù)據(jù)形成數(shù)據(jù)庫(kù)服務(wù)器聯(lián)合體的機(jī)制能夠擴(kuò)大一組服務(wù)器，以支持大型的多層 Web 站點(diǎn)的處理需要。有關(guān)更多信息，參見設(shè)計(jì)聯(lián)合數(shù)據(jù)庫(kù)服務(wù)器。（參照SQL幫助文件分區(qū)視圖）：a、在實(shí)現(xiàn)分區(qū)視圖之前，必須先水平分區(qū)表。b、在創(chuàng)建成員表后，在每個(gè)成員服務(wù)器上定義一個(gè)分布式分區(qū)視圖，并且每個(gè)視圖具有相同的名稱。這樣，引用分布式分區(qū)視圖名的查詢可以在任何一個(gè)成員服務(wù)器上運(yùn)行。系統(tǒng)操作如同每個(gè)成員服務(wù)器上都有一個(gè)原始表的復(fù)本一樣，但其實(shí)每個(gè)服務(wù)器上只有一個(gè)成員表和一個(gè)分布式分區(qū)視圖。數(shù)據(jù)的位置對(duì)應(yīng)用程序是透明的。 11、重建索引DBCC REINDEX，DBCC INDEXDEFRAG，收縮數(shù)據(jù)和日志 DBCC SHRINKDB，DBCC SHRINKFILE設(shè)置自動(dòng)收縮日志。對(duì)于大的數(shù)據(jù)庫(kù)不要設(shè)置數(shù)據(jù)庫(kù)自動(dòng)增長(zhǎng)，它會(huì)降低服務(wù)器的性能。在T-sql的寫法上有很大的講究，下面列出常見的要點(diǎn)：首先，DBMS處理查詢計(jì)劃的過(guò)程是這樣的： a、 查詢語(yǔ)句的詞法、語(yǔ)法檢查； b、 將語(yǔ)句提交給DBMS的查詢優(yōu)化器； c、 優(yōu)化器做代數(shù)優(yōu)化和存取路徑的優(yōu)化； d、 由預(yù)編譯模塊生成查詢規(guī)劃； e、 然后在合適的時(shí)間提交給系統(tǒng)處理執(zhí)行； f、 最后將執(zhí)行結(jié)果返回給用戶其次，看一下SQL SERVER的數(shù)據(jù)存放的結(jié)構(gòu)：一個(gè)頁(yè)面的大小為8K(8060)字節(jié)，8個(gè)頁(yè)面為一個(gè)盤區(qū)，按照B樹存放。 12、Commit和rollback的區(qū)別：Rollback:回滾所有的事務(wù)。 Commit:提交當(dāng)前的事務(wù)。沒(méi)有必要在動(dòng)態(tài)SQL里寫事務(wù)，如果要寫請(qǐng)寫在外面如： BEGIN TRANS EXEC(s) COMMIT TRANS或者將動(dòng)態(tài)SQL 寫成函數(shù)或者存儲(chǔ)過(guò)程。 13、在查詢SELECT語(yǔ)句中用WHERE字句限制返回的行數(shù)，避免表掃描，如果返回不必要的數(shù)據(jù)，浪費(fèi)了服務(wù)器的I/O資源，加重了網(wǎng)絡(luò)的負(fù)擔(dān)降低性能。如果表很大，在表掃描的期間將表鎖住，禁止其他的聯(lián)接訪問(wèn)表，后果嚴(yán)重。 14、SQL的注釋申明對(duì)執(zhí)行沒(méi)有任何影響。15、盡可能不使用光標(biāo)，它占用大量的資源。如果需要row-by-row地執(zhí)行，盡量采用非光標(biāo)技術(shù)，如：在客戶端循環(huán)，用臨時(shí)表，Table變量，用子查詢，用CASE語(yǔ)句等等。游標(biāo)可以按照它所支持的提取選項(xiàng)進(jìn)行分類： 只進(jìn)：必須按照從第一行到最后一行的順序提取行。FETCH NEXT 是唯一允許的提取操作,也是默認(rèn)方式?？蓾L動(dòng)性：可以在游標(biāo)中任何地方隨機(jī)提取任意行。游標(biāo)的技術(shù)在SQL2000下變得功能很強(qiáng)大，他的目的是支持循環(huán)。有四個(gè)并發(fā)選項(xiàng) READ_ONLY：不允許通過(guò)游標(biāo)定位更新(Update)，且在組成結(jié)果集的行中沒(méi)有鎖。 OPTIMISTIC WITH valueS：樂(lè)觀并發(fā)控制是事務(wù)控制理論的一個(gè)標(biāo)準(zhǔn)部分。樂(lè)觀并發(fā)控制用于這樣的情形，即在打開游標(biāo)及更新行的間隔中，只有很小的機(jī)會(huì)讓第二個(gè)用戶更新某一行。當(dāng)某個(gè)游標(biāo)以此選項(xiàng)打開時(shí)，沒(méi)有鎖控制其中的行，這將有助于最大化其處理能力。如果用戶試圖修改某一行，則此行的當(dāng)前值會(huì)與最后一次提取此行時(shí)獲取的值進(jìn)行比較。如果任何值發(fā)生改變，則服務(wù)器就會(huì)知道其他人已更新了此行，并會(huì)返回一個(gè)錯(cuò)誤。如果值是一樣的，服務(wù)器就執(zhí)行修改。選擇這個(gè)并發(fā)選項(xiàng)。OPTIMISTIC WITH ROW VERSIONING：此樂(lè)觀并發(fā)控制選項(xiàng)基于行版本控制。使用行版本控制，其中的表必須具有某種版本標(biāo)識(shí)符，服務(wù)器可用它來(lái)確定該行在讀入游標(biāo)后是否有所更改。在 SQL Server 中，這個(gè)性能由 timestamp 數(shù)據(jù)類型提供，它是一個(gè)二進(jìn)制數(shù)字，表示數(shù)據(jù)庫(kù)中更改的相對(duì)順序。每個(gè)數(shù)據(jù)庫(kù)都有一個(gè)全局當(dāng)前時(shí)間戳值：DBTS。每次以任何方式更改帶有 timestamp 列的行時(shí)，SQL Server 先在時(shí)間戳列中存儲(chǔ)當(dāng)前的 DBTS 值，然后增加 DBTS 的值。如果某 個(gè)表具有 timestamp 列，則時(shí)間戳?xí)挥浀叫屑?jí)。服務(wù)器就可以比較某行的當(dāng)前時(shí)間戳值和上次提取時(shí)所存儲(chǔ)的時(shí)間戳值，從而確定該行是否已更新。服務(wù)器不必比較所有列的值，只需比較 timestamp 列即可。如果應(yīng)用程序?qū)](méi)有 timestamp 列的表要求基于行版本控制的樂(lè)觀并發(fā)，則游標(biāo)默認(rèn)為基于數(shù)值的樂(lè)觀并發(fā)控制。 SCROLL LOCKS：這個(gè)選項(xiàng)實(shí)現(xiàn)悲觀并發(fā)控制。在悲觀并發(fā)控制中，在把數(shù)據(jù)庫(kù)的行讀入游標(biāo)結(jié)果集時(shí)，應(yīng)用程序?qū)⒃噲D鎖定數(shù)據(jù)庫(kù)行。在使用服務(wù)器游標(biāo)時(shí)，將行讀入游標(biāo)時(shí)會(huì)在其上放置一個(gè)更新鎖。如果在事務(wù)內(nèi)打開游標(biāo)，則該事務(wù)更新鎖將一直保持到事務(wù)被提交或回滾；當(dāng)提取下一行時(shí)，將除去游標(biāo)鎖。如果在事務(wù)外打開游標(biāo)，則提取下一行時(shí)，鎖就被丟棄。因此，每當(dāng)用戶需要完全的悲觀并發(fā)控制時(shí)，游標(biāo)都應(yīng)在事務(wù)內(nèi)打開。更新鎖將阻止任何其它任務(wù)獲取更新鎖或排它鎖，從而阻止其它任務(wù)更新該行。然而，更新鎖并不阻止共享鎖，所以它不會(huì)阻止其它任務(wù)讀取行，除非第二個(gè)任務(wù)也在要求帶更新鎖的讀取。滾動(dòng)鎖根據(jù)在游標(biāo)定義的 SELECT 語(yǔ)句中指定的鎖提示，這些游標(biāo)并發(fā)選項(xiàng)可以生成滾動(dòng)鎖。滾動(dòng)鎖在提取時(shí)在每行上獲取，并保持到下次提取或者游標(biāo)關(guān)閉，以先發(fā)生者為準(zhǔn)。下次提取時(shí)，服務(wù)器為新提取中的行獲取滾動(dòng)鎖，并釋放上次提取中行的滾動(dòng)鎖。滾動(dòng)鎖獨(dú)立于事務(wù)鎖，并可以保持到一個(gè)提交或回滾操作之后。如果提交時(shí)關(guān)閉游標(biāo)的選項(xiàng)為關(guān)，則 COMMIT 語(yǔ)句并不關(guān)閉任何打開的游標(biāo)，而且滾動(dòng)鎖被保留到提交之后，以維護(hù)對(duì)所提取數(shù)據(jù)的隔離。所獲取滾動(dòng)鎖的類型取決于游標(biāo)并發(fā)選項(xiàng)和游標(biāo) SELECT 語(yǔ)句中的鎖提示。16、用Profiler來(lái)跟蹤查詢，得到查詢所需的時(shí)間，找出SQL的問(wèn)題所在;用索引優(yōu)化器優(yōu)化索引。17、注意UNION和UNION ALL 的區(qū)別。UNION ALL好。18、注意使用DISTINCT，在沒(méi)有必要時(shí)不要用，它同UNION一樣會(huì)使查詢變慢。重復(fù)的記錄在查詢里是沒(méi)有問(wèn)題的。19、查詢時(shí)不要返回不需要的行、列。20、用sp_configure query governor cost limit或者SET QUERY_GOVERNOR_COST_LIMIT來(lái)限制查詢消耗的資源。當(dāng)評(píng)估查詢消耗的資源超出限制時(shí)，服務(wù)器自動(dòng)取消查詢,在查詢之前就扼殺掉。SET LOCKTIME設(shè)置鎖的時(shí)間。21、用SELECT TOP 100 / 10 PERCENT 來(lái)限制用戶返回的行數(shù)或者SET ROWCOUNT來(lái)限制操作的行。22、在SQL2000以前，一般不要用如下的字句: IS NULL, , !=, !, !, NOT, NOT EXISTS, NOT IN, NOT LIKE, and LIKE %500，因?yàn)樗麄儾蛔咚饕潜頀呙?。也不要在WHere字句中的列名加函數(shù)，如Convert，substring等,如果必須用函數(shù)的時(shí)候，創(chuàng)建計(jì)算列再創(chuàng)建索引來(lái)替代.還可以變通寫法：WHERE SUBSTRING(firstname,1,1) = m改為WHERE firstname like m%（索引掃描），一定要將函數(shù)和列名分開。并且索引不能建得太多和太大。NOT IN會(huì)多次掃描表，使用EXISTS、NOT EXISTS ，IN , LEFT OUTER JOIN 來(lái)替代，特別是左連接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用，現(xiàn)在2000的優(yōu)化器能夠處理了。相同的是IS NULL，“NOT, NOT EXISTS, NOT IN能優(yōu)化她，而”等還是不能優(yōu)化，用不到索引。 23、使用Query Analyzer，查看SQL語(yǔ)句的查詢計(jì)劃和評(píng)估分析是否是優(yōu)化的SQL。一般的20%的代碼占據(jù)了80%的資源，我們優(yōu)化的重點(diǎn)是這些慢的地方。 24、如果使用了IN或者OR等時(shí)發(fā)現(xiàn)查詢沒(méi)有走索引，使用顯示申明指定索引： SELECT * FROM PersonMember (INDEX = IX_Title) WHERE processid IN (男，女)25、將需要查詢的結(jié)果預(yù)先計(jì)算好放在表中，查詢的時(shí)候再SELECT。這在SQL7.0以前是最重要的手段。例如醫(yī)院的住院費(fèi)計(jì)算。 26、MIN() 和 MAX()能使用到合適的索引。 27、數(shù)據(jù)庫(kù)有一個(gè)原則是代碼離數(shù)據(jù)越近越好，所以優(yōu)先選擇Default,依次為Rules,Triggers, Constraint（約束如外健主健CheckUNIQUE,數(shù)據(jù)類型的最大長(zhǎng)度等等都是約束）,Procedure.這樣不僅維護(hù)工作小，編寫程序質(zhì)量高，并且執(zhí)行的速度快。 28、如果要插入大的二進(jìn)制值到Image列，使用存儲(chǔ)過(guò)程，千萬(wàn)不要用內(nèi)嵌INsert來(lái)插入(不知JAVA是否)。因?yàn)檫@樣應(yīng)用程序首先將二進(jìn)制值轉(zhuǎn)換