SQL_Server開發(fā)通用規(guī)范

1、第 1 頁 共 22 頁sql 開發(fā)規(guī)范文件編號：文件版次：文檔編號版本號0.9 分冊名稱sqlserver開發(fā)規(guī)范第 1 冊/ 共 1 冊總頁數(shù)正文附錄編制孫啟琳審批生效日期東軟集團(tuán)股份有限公司第 2 頁 共 22 頁修改記錄版本號變更控制報告編號更改條款及內(nèi)容更改人審批人更改日期0.9 2010001 文檔創(chuàng)建孫啟琳2010-2-2 第 3 頁 共 22 頁1.1 目的規(guī)范 sql 的編寫格式，提高sql 語句的可讀性，共享性和執(zhí)行效率。1.2 使用范圍所有需要與數(shù)據(jù)庫交互的應(yīng)用系統(tǒng)。1.3 概述大部分業(yè)務(wù)系統(tǒng)需要與數(shù)據(jù)庫進(jìn)行交互， 與數(shù)據(jù)庫交互的主要方式就是sql語句，編寫規(guī)范的sql

2、語句不但利于閱讀，而且被數(shù)據(jù)庫重復(fù)使用的幾率也較大，執(zhí)行效率相對較高，編寫的好的sql 與編寫的差的 sql 在執(zhí)行性能上可能會差幾倍甚至幾千幾萬倍，因此養(yǎng)成好的sql 編寫規(guī)范對于提高項目質(zhì)量及提高開發(fā)人員自身素質(zhì)有著潛在的極大的影響。1.4 書寫sql 書寫遵守如下規(guī)范：在同一個項目中，為了最大限度實現(xiàn)sql的共享，要求書寫 sql 語句時大小寫要一致，為了閱讀方便和統(tǒng)一起見，所有sql語句全部小寫（如sql謂詞，字段名，表名等）, 常量除外，常量可以按需要書寫。舉例：下面兩個相同的語句除常量外都要統(tǒng)一起來。1)select name from emp; 2)select name fro

3、m emp where emp_no=qd001 sql 語句盡可能放在一行，若sql 太長放在一行中影響閱讀時可分多行，但要保持縮進(jìn)一致，縮進(jìn)可用tab 或者空格，但 tab 數(shù)和空格數(shù)最好一致。sql語句中，各謂詞之間以空格分割的，盡量保持空格數(shù)量一致，即若用一個空格分割，則全部都用一個空格分割，便于數(shù)據(jù)庫能夠共享。能使用綁定變量的，盡量使用綁定變量，尤其是在前臺程序中. 對下面列出的情況，慎重使用綁定變量：1）列值傾斜嚴(yán)重，如：某一狀態(tài)列大部分值是1 ，只有極少數(shù)值為2，這種情況不宜用綁定變量，而應(yīng)該用常量，便于數(shù)據(jù)庫使第 4 頁 共 22 頁用柱狀圖統(tǒng)計信息。2）日期時間列?？傊簳鴮?/p>

4、 sql的目標(biāo)是若 sql 的用途是一樣的，則sql 應(yīng)該完全一致， 包括空格，大小寫。下面的語句由于寫法不完全相同，數(shù)據(jù)庫會理解為4 條不同的語句從而導(dǎo)致重復(fù)編譯，降低了性能。1） select name from emp where emp_no=1112） select name from emp where emp_no=1113） select name from emp where emp_no= 1114） select name from emp where emp_no= 111下面的語句，由于語句規(guī)范，可以只編譯一次。1）select name from emp where

5、 emp_no=1112）select name from emp where emp_no=2223）select name from emp where emp_no=3334）select name from emp where emp_no=:b1 1.5 注釋1.5.1 開頭注釋所有的過程、函數(shù)、觸發(fā)器、包都應(yīng)該在開頭有注釋，注釋中要列出對象名稱；完成功能簡述；調(diào)用模塊，調(diào)用時機(jī)；創(chuàng)建日期；作者信息；歷次修改日期；歷次修改人；歷次修改原因和其它作者認(rèn)為重要的內(nèi)容，在每次修改的注釋之間建一空行。舉例如下：/* 名稱：pro_wo_multi_repair_job 功能：多次維修判定程序

6、調(diào)用：自動統(tǒng)計月結(jié)前，后臺調(diào)用作者：xxx 時間：2009-02-20 第 5 頁 共 22 頁修改人： xxx 修改時間 :2009-03-08 修改內(nèi)容：重寫部分sql 優(yōu)化性能修改人： xxx 修改日期： 2008-03-23 修改內(nèi)容：增加判斷條件*/ 1.5.2 塊注釋對于復(fù)雜的語句塊， 必須提供塊注釋，清晰描述該語句塊的功能邏輯、數(shù)據(jù)結(jié)構(gòu)以及算法； 塊注釋應(yīng)該和它們所描述的代碼具有一樣的縮進(jìn)格式；塊注釋之首應(yīng)該有一個空行，用于把塊注釋和代碼分割開來；塊注釋結(jié)構(gòu)如下所示：/* 計算配送能力系數(shù)：派工系數(shù)a*b+c*d+e*f a:考評成績，取 xx 的考評成績c:承擔(dān)度度，根據(jù)當(dāng)前遺

7、留量和總服務(wù)能力計算所得，c=當(dāng)前遺留配送量 /總配送能力. */ 1.5.3 行注釋對于復(fù)雜的 sql 語句，必須提供行注釋，清晰描述該sql 語句功能以及目的；行注釋結(jié)構(gòu)如下所示：-計算動態(tài)能力得分 ,當(dāng)負(fù)荷率超高時，可能出現(xiàn)負(fù)值，注意處理。第 6 頁 共 22 頁添加注釋時要注意：注釋中包含go 命令時會生成一個錯誤消息。1.5.4 過程和函數(shù)1.5.5 命名規(guī)范存儲過程 /函數(shù)的命名（代碼）使用以下方式：存儲過程名= sp_ + 模塊代碼+ 存儲過程代碼函數(shù)名 = f_ + 模塊代碼 + 函數(shù)代碼存儲過程以表現(xiàn)其功能的簡潔語言進(jìn)行命名，定義好后，放置于每一個diagram 的特定區(qū)域（

8、一般置于右邊部分的上邊） 。1.5.6 過程 /函數(shù)頭定義規(guī)范包括過程 /函數(shù)名稱定義、參數(shù)定義、注釋說明、變量定義四部分。參數(shù)定義所有的參數(shù)必須顯示指出變量類型，對于返回參數(shù)，必須指出其方向；參數(shù)變量定義格式為：“a“+參數(shù)類型簡寫“ _”參數(shù)名稱，其中 a 是單詞 argument( 參數(shù))的第一個字母，參數(shù)類型如下：文本型： s 日期時間型： d 數(shù)字型： n 整形： i 布爾型： b 舉例： as_name 標(biāo)識文本型ad_birthdate 表示日期時間型an_age 表示數(shù)字型注釋說明見前面描述。1.5.7 變量定義規(guī)范(1).變量的命名除游標(biāo)變量以外，所有的變量命名采用此方式：第

9、 7 頁 共 22 頁變量名 = “v_” + 變量名稱游標(biāo)變量采用：變量名 = “cur” + 變量名 (第一個字母大寫 )。其中變量名稱由代表變量確切含意的單詞代碼組合而成，每一個單詞的首字母根據(jù)閱讀的方便性決定是否需要大寫。舉例：變量名： v_empname 游標(biāo)名： curnetlist (2).變量類型定義變量的類型時，盡量采用顯示定義的方式。對于僅用于oracle 系列的數(shù)據(jù)庫應(yīng)用，也可以引用表、游標(biāo)的引用定義方式。盡量將變量的定義分開來，并進(jìn)行格式化，以便程序代碼的閱讀。例如：v_username char(10) v_sex char(1) 1.5.8 變量注釋原則上要求對于一

10、般變量定義時必須加上注釋，如變量有特定的值范圍， 則必須顯示描述各確切值及其含意說明；1.5.9 過程 /函數(shù)體定義規(guī)范在過程 /函數(shù)的定義中，最重要的是過程代碼的嚴(yán)謹(jǐn)性和可讀性，主要包括以下的注意事項：(1).如無特殊需要， sql 中涉及的所有內(nèi)容都要小寫。(2).原則上要求所有的sql 語句必須在其前面加上注釋， 對于 if/case 等流程控制語句，必須在語句前/后說明控制處理和可能的流程方向；(3).所有的賦值語句要求變量與運算符之間要有空格。如：v_count := v_count + 1，并保持適當(dāng)?shù)膶R；(4).盡量避免復(fù)雜 sql，尤其是關(guān)聯(lián)多個大表的sql，對于需要關(guān)聯(lián)多個

11、大表的 sql 最好分解成小的sql 分步處理，避免出現(xiàn)性能問題，對于復(fù)雜的語句塊之間，要求中間加入空行；(5).所有可能的返回結(jié)果，必須在過程體中顯示定義和說明，并在注釋中說第 8 頁 共 22 頁明。在其它的過程、代碼中調(diào)用本過程/函數(shù)時，必須在其代碼中處理所有的各種可能的返回結(jié)果；(6).鎖定數(shù)據(jù)時，盡可能只鎖定要操作的數(shù)據(jù)行，避免鎖定整個表，限制使用 local table 等 ddl 語句，若要對數(shù)據(jù)表、數(shù)據(jù)行加鎖時，需要考慮由此導(dǎo)致的并發(fā)操作失敗的處理；(7).所有的過程 /函數(shù)代碼在編寫之前，必須要有設(shè)計原型及其說明；存儲過程 /函數(shù)錯誤定義(1).所有的 dml語句必須考慮死鎖

12、、并發(fā)、主關(guān)鍵字不唯一等的出錯異常處理，應(yīng)該對所有可能出現(xiàn)的異常進(jìn)行捕獲、編碼處理，并注明異常的內(nèi)容 （注：異常捕獲及處理請參見1.2） ；(2).異常處理有兩種需要考慮的問題，一是事務(wù)的完整性；二是錯誤的出錯日志及返回處理。所有異常的根據(jù)其類型和等級進(jìn)行以下的處理：類型內(nèi)容處理系統(tǒng)級數(shù)據(jù)庫級發(fā)出的底層的異常，如主關(guān)鍵字不唯一沖突等；返回定義錯誤代碼為其它錯誤，完全回退事務(wù)，登錄異常日志；應(yīng)用級應(yīng)用系統(tǒng)定義并發(fā)出的異常，如指定記錄不存在；定義應(yīng)用級錯誤代碼，根據(jù)實際情況處理事務(wù)；使用原則(1).存儲過程功能盡量保持獨立、復(fù)用，但盡量避免嵌套調(diào)用，充分考慮其性能體現(xiàn)；(2).使用前臺代碼調(diào)用存儲

13、過程時，必須考慮返回傳入?yún)?shù)的有效性、代碼的處理及展示、事務(wù)完整性處理；(3).直接使用參數(shù)作為變量處理，不要在存儲過程中定義參數(shù)同樣含義的變量使用，避免由于值的不同導(dǎo)致性能問題。1.6 觸發(fā)器1.6.1 命名規(guī)范：觸發(fā)器不同類型的命名結(jié)構(gòu)如下操作時機(jī)命名結(jié)構(gòu)insert after ai_表名第 9 頁 共 22 頁update after au_表名delete after ad_表名1.6.2 觸發(fā)器編寫規(guī)范請參見存儲過程 /函數(shù)編寫規(guī)范。1.7 異常所有過程和函數(shù)處理必須考慮可能出現(xiàn)的錯誤，一是數(shù)據(jù)庫或操作系統(tǒng)底層錯誤；二是由于業(yè)務(wù)邏輯造成的錯誤；三是無反應(yīng)或超時； 四是輸入?yún)?shù)不在指

14、定范圍內(nèi)。設(shè)計所有的過程時，必須考慮出錯后的恢復(fù)。1.8 優(yōu)化1.8.1 sql 語句原則where子句盡量避免使用函數(shù)；避免在 order by 子句中使用表達(dá)式；限制在 group by 子句中使用表達(dá)式；慎用游標(biāo)；大小寫規(guī)范統(tǒng)一，變量綁定統(tǒng)一，避免重復(fù)編譯；盡可能少的返回結(jié)果集行的數(shù)量避免使用 select * 語句；減少結(jié)果集中的列的數(shù)量；視圖嵌套使用不能超過3 層；不要使用沒有意義的列作為聚集索引列，例如，加1 自增列；避免隱式類型轉(zhuǎn)換，例如字符型一定要用，數(shù)字型一定不要使用 ；查詢語句一定要有范圍的限定，避免全表掃描操作；合理對大表進(jìn)行分區(qū)；慎用 distinct 關(guān)鍵字；慎用 u

15、nion 關(guān)鍵字，可以用 or 替代；使用 top 1 替 count(*)來判斷是否存在記錄；第 10 頁 共 22 頁1.8.2 索引創(chuàng)建原則同一索引中的組成列最好不要超過3 列。把經(jīng)常一起出現(xiàn)的字段組合在一起，組成組合索引，組合索引的字段順序與主鍵一樣，也需要把最常用的字段放在前面，把重復(fù)率低的字段放在前面。根據(jù)使用頻率決定哪些字段需要建立索引，選擇經(jīng)常作為連接條件、篩選條件、聚合查詢、排序的字段作為索引的候選字段。根據(jù)數(shù)據(jù)量決定哪些表需要增加索引，數(shù)據(jù)量小的可以只有主鍵。若某列中有大量的值是空值，可以建立索引。要對值分布較寬的列建立索引。若表主要用來查詢，則可按需要建立索引， 若對表操

16、作主要是update，則盡可能少建索引。不要對值較窄的列建立索引，如性別不要索引較小的表（如表不足1000 行）若某列的值大部分是a，少數(shù)是別的值（如b,c,d），且經(jīng)常以該列的其它值 (如 b,c,d)為查詢條件，則可以將值a 設(shè)為空值，并在此列上創(chuàng)建索引。1.8.3 充分利用索引1.8.3.1 函數(shù)、表達(dá)式使用在 where語句中，盡量避免在運算符左邊對列進(jìn)行函數(shù)或者表達(dá)式操作，容易引起全表掃描，要盡可能將操作移至運算符右邊。1.8.3.2 in/or 子句使用in、or、not in sql server2005數(shù)據(jù)庫可以分析出應(yīng)該根據(jù)索引查找。屬于 2005 版本的新特性。1.8.3.

17、3 != 或 操作符子句使用!= 或 操作符可以用 index seek查找的，可以正常使用。第 11 頁 共 22 頁1.8.3.4 不要對索引字段進(jìn)行運算例如：select id from t where num/2=100 應(yīng)改為 : select id from t where num=100*2 select id from t where num/2=num1 如果 num 有索引應(yīng)改為 : select id from t where num=num1*2 如果 num1 有索引則不應(yīng)該改。1.8.3.5 不要對索引字段進(jìn)行格式轉(zhuǎn)換日期字段的例子：where convert(va

18、rchar(10), 日期字段 ,120)=2008-08-15應(yīng)該改為where 日期字段 =2008-08-15and 日期字段 2008-08-16isnull 轉(zhuǎn)換的例子：where isnull( 字段 , ) 應(yīng)改為 :where 字段 where isnull( 字段 , )= 不應(yīng)修改where isnull( 字段 ,f) =t應(yīng)改為 : where 字段 =twhere isnull( 字段 ,f)t不應(yīng)修改1.8.3.6 不要對索引字段使用函數(shù)where left(name, 3)=abc 或者 where substring(name,1, 3)=abc 應(yīng)改為 : w

19、here name like abc% 日期查詢的例子：where datediff(day , 日 期 ,2005-11-30)=0應(yīng) 改 為 :where 日 期=2005-11-30 and 日 期0 應(yīng)改為 :where 日期 =0 應(yīng)改為 :where 日期2005-12-01where datediff(day , 日期 ,2005-11-30)=2005-12-01第 12 頁 共 22 頁where datediff(day , 日期 ,2005-11-30)=2005-11-301.8.3.7 不要對索引字段進(jìn)行多字段連接例如：where fame+ . +lname= h.

20、y 應(yīng)改為 : where fname=h and lname= y1.8.3.8 like 的使用對索引列避免使用 like %xx ， 應(yīng)該使用 like xx% 。設(shè)計數(shù)據(jù)結(jié)構(gòu)時就應(yīng)該考慮這個問題， 不要出現(xiàn)必須要采用like %xx 才能滿足業(yè)務(wù)需要的情形。1.9 設(shè)計1.9.1 數(shù)據(jù)庫邏輯設(shè)計的規(guī)范化第 1 規(guī)范：沒有重復(fù)的組或多值的列，這是數(shù)據(jù)庫設(shè)計的最低要求。第 2 規(guī)范：每個非關(guān)鍵字段必須依賴于主關(guān)鍵字，不能依賴于一個組合式主關(guān)鍵字的某些組成部分。消除部分依賴，大部分情況下，數(shù)據(jù)庫設(shè)計都應(yīng)該達(dá)到第二范式。第 3 規(guī)范： 一個非關(guān)鍵字段不能依賴于另一個非關(guān)鍵字段。消除傳遞依賴，達(dá)

21、到第三范式應(yīng)該是系統(tǒng)中大部分表的要求，除非一些特殊作用的表。1.9.2 合理的冗余完全按照規(guī)范化設(shè)計的系統(tǒng)幾乎是不可能的，除非系統(tǒng)特別的小，在規(guī)范化設(shè)計后，有計劃地加入冗余是必要的。冗余可以是冗余數(shù)據(jù)庫、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。冗余可以是為了編程方便而增加，也可以是為了性能的提高而增加。從性能角度來說，冗余數(shù)據(jù)庫可以分散數(shù)據(jù)庫壓力，冗余表可以分散數(shù)據(jù)量大的表的并發(fā)壓力，也可以加快特殊查詢的速度，冗余字段可以有效減少數(shù)據(jù)庫表的連接，提高效率。1.9.3 主鍵的設(shè)計主鍵是必要的， sql server的主鍵同時是一個唯一索引，而且在實際應(yīng)用第 13 頁 共 22 頁中

22、，我們往往選擇最小的鍵組合作為主鍵，所以主鍵往往適合作為表的聚集索引。聚集索引對查詢的影響是比較大的，這個在下面索引的敘述。在有多個鍵的表，主鍵的選擇也比較重要，一般選擇總的長度小的鍵，小的鍵的比較速度快，同時小的鍵可以使主鍵的b 樹結(jié)構(gòu)的層次更少。 主鍵的選擇還要注意組合主鍵的字段次序，對于組合主鍵來說，不同的字段次序的主鍵的性能差別可能會很大，一般應(yīng)該選擇重復(fù)率低、單獨或者組合查詢可能性大的字段放在前面。1.9.4 外鍵的設(shè)計外鍵是最高效的一致性維護(hù)方法，數(shù)據(jù)庫的一致性要求，依次可以用外鍵、check 約束、規(guī)則約束、觸發(fā)器、客戶端程序，一般認(rèn)為，離數(shù)據(jù)越近的方法效率越高。謹(jǐn)慎使用級聯(lián)刪除

23、和級聯(lián)更新，級聯(lián)刪除和級聯(lián)更新作為sql server 2000當(dāng)年的新功能，在 2005 作了保留，應(yīng)該有其可用之處。因為級聯(lián)刪除和級聯(lián)更新有些突破了傳統(tǒng)的關(guān)于外鍵的定義，功能有點太過強(qiáng)大，使用前必須確定自己已經(jīng)把握好其功能范圍，否則，級聯(lián)刪除和級聯(lián)更新可能讓你的數(shù)據(jù)莫名其妙的被修改或者丟失。從性能看級聯(lián)刪除和級聯(lián)更新是比其他方法更高效的方法。1.9.5 字段的設(shè)計字段是數(shù)據(jù)庫最基本的單位， 其設(shè)計對性能的影響是很大的。 需要注意如下：a、數(shù)據(jù)類型盡量用數(shù)字型，數(shù)字型的比較比字符型的快很多。b、 數(shù)據(jù)類型盡量小， 這里的盡量小是指在滿足可以預(yù)見的未來需求的前提下的。c、 盡量不要允許 nul

24、l ，除非必要，可以用not null+default代替。d、少用 text 和 image ，二進(jìn)制字段的讀寫是比較慢的，而且，讀取的方法也不多，大部分情況下最好不用。e、 自增字段要慎用，不利于數(shù)據(jù)遷移。1.9.6 索引的設(shè)計a、根據(jù)數(shù)據(jù)量決定哪些表需要增加索引，數(shù)據(jù)量小的可以只有主鍵。b、根據(jù)使用頻率決定哪些字段需要建立索引，選擇經(jīng)常作為連接條件、篩第 14 頁 共 22 頁選條件、聚合查詢、排序的字段作為索引的候選字段。c、把經(jīng)常一起出現(xiàn)的字段組合在一起，組成組合索引，組合索引的字段順序與主鍵一樣，也需要把最常用的字段放在前面，把重復(fù)率低的字段放在前面。d、一個經(jīng)常插入更新的表不要加

25、太多索引，因為索引影響插入和更新的速度。e、一個多用于查詢的表可以適量增加索引，保證查詢效率，例如報表系統(tǒng)。1.10 編碼階段1.10.1 盡可能少的范圍數(shù)據(jù)a、 橫向來看，不要寫select *的語句，而是選擇你需要的字段。b、 縱向來看，合理寫where 子句，不要寫沒有where 的 sql 語句。c、 注意 select into 后的 where 子句，因為 select into 把數(shù)據(jù)插入到臨時表，這個過程會鎖定一些系統(tǒng)表， 如果這個 where 子句返回的數(shù)據(jù)過多或者速度太慢，會造成系統(tǒng)表長期鎖定，諸塞其他進(jìn)程。d、對于聚合查詢，可以用having 子句進(jìn)一步限定返回的行。1.

26、10.2 盡可能少的重復(fù)操作a、控制同一語句的多次執(zhí)行，特別是一些基礎(chǔ)數(shù)據(jù)的多次執(zhí)行是很多程序員很少注意的。b、減少多次的數(shù)據(jù)轉(zhuǎn)換，也許需要數(shù)據(jù)轉(zhuǎn)換是設(shè)計的問題，但是減少次數(shù)是程序員可以做到的。c、杜絕不必要的子查詢和連接表，子查詢在執(zhí)行計劃一般解釋成外連接，多余的連接表帶來額外的開銷。d、合并對同一表同一條件的多次update，比如update employee set fname=h where emp_id= f update employee set lname=y where emp_id= f 這兩個語句應(yīng)該合并成以下一個語句update employee set fname=h,

27、lname= y where emp_id= f 第 15 頁 共 22 頁e、update 操作不要拆成 delete 操作+insert 操作的形式， 雖然功能相同，但是性能差別是很大的。f、不要寫一些沒有意義的查詢，比如select * from employee where 1=2 1.10.3 注意事務(wù)和鎖事務(wù)是數(shù)據(jù)庫應(yīng)用中和重要的工具，它有原子性、一致性、隔離性、持久性這四個屬性，很多操作我們都需要利用事務(wù)來保證數(shù)據(jù)的正確性。在使用事務(wù)中我們需要做到盡量避免死鎖、盡量減少阻塞。具體以下方面需要特別注意：a、事務(wù)操作過程要盡量小，能拆分的事務(wù)要拆分開來。b、 事務(wù)操作過程不應(yīng)該有交互

28、，因為交互等待的時候，事務(wù)并未結(jié)束，可能鎖定了很多資源。c、 事務(wù)操作過程要按同一順序訪問對象。d、提高事務(wù)中每個語句的效率， 利用索引和其他方法提高每個語句的效率可以有效地減少整個事務(wù)的執(zhí)行時間。e、 盡量不要指定鎖類型和索引，sql server 允許我們自己指定語句使用的鎖類型和索引，但是一般情況下，sql server優(yōu)化器選擇的鎖類型和索引是在當(dāng)前數(shù)據(jù)量和查詢條件下是最優(yōu)的，我們指定的可能只是在目前情況下更有，但是數(shù)據(jù)量和數(shù)據(jù)分布在將來是會變化的。f、 查詢時可以用較低的隔離級別，特別是報表查詢的時候，可以選擇最低的隔離級別（未提交讀） 。1.10.4 注意臨時表和表變量的用法在復(fù)雜

29、系統(tǒng)中，臨時表和表變量很難避免，關(guān)于臨時表和表變量的用法，需要注意：a、如果語句很復(fù)雜，連接太多，可以考慮用臨時表和表變量分步完成。b、 如果需要多次用到一個大表的同一部分?jǐn)?shù)據(jù)，考慮用臨時表和表變量暫存這部分?jǐn)?shù)據(jù)。c、 如果需要綜合多個表的數(shù)據(jù)，形成一個結(jié)果，可以考慮用臨時表和表變量分步匯總這多個表的數(shù)據(jù)。第 16 頁 共 22 頁d、其他情況下，應(yīng)該控制臨時表和表變量的使用。e、 關(guān)于臨時表和表變量的選擇，很多說法是表變量在內(nèi)存，速度快，應(yīng)該首選表變量，但是在實際使用中發(fā)現(xiàn)，這個選擇主要考慮需要放在臨時表的數(shù)據(jù)量，在數(shù)據(jù)量較多的情況下，臨時表的速度反而更快。f、 關(guān)于臨時表產(chǎn)生使用selec

30、t into 和 create table + insert into 的選擇，我們做過測試，一般情況下，select into 會比 create table + insert into 的方法快很多，但是 select into 會鎖定 tempdb的系統(tǒng)表 sysobjects、sysindexes、syscolumns ，在多用戶并發(fā)環(huán)境下，容易阻塞其他進(jìn)程，所以我的建議是，在并發(fā)系統(tǒng)中，盡量使用create table + insert into，而大數(shù)據(jù)量的單個語句使用中，使用select into。g、 注意排序規(guī)則， 用 create table 建立的臨時表， 如果不指定字段

31、的排序規(guī)則，會選擇tempdb 的默認(rèn)排序規(guī)則，而不是當(dāng)前數(shù)據(jù)庫的排序規(guī)則。如果當(dāng)前數(shù)據(jù)庫的排序規(guī)則和tempdb 的排序規(guī)則不同，連接的時候就會出現(xiàn)排序規(guī)則的沖突錯誤。一般可以在create table 建立臨時表時指定字段的排序規(guī)則為database_default 來避免上述問題。1.10.5 注意子查詢的用法子查詢是一個select 查詢，它嵌套在select、insert、update、delete 語句或其它子查詢中。 任何允許使用表達(dá)式的地方都可以使用子查詢。子查詢可以使我們的編程靈活多樣，可以用來實現(xiàn)一些特殊的功能。但是在性能上，往往一個不合適的子查詢用法會形成一個性能瓶頸。如

32、果子查詢的條件中使用了其外層的表的字段，這種子查詢就叫作相關(guān)子查詢。相關(guān)子查詢可以用in、not in 、exists、not exists 引入。關(guān)于相關(guān)子查詢，應(yīng)該注意：a、not in、not exists 的相關(guān)子查詢可以改用left join 代替寫法。比如：select pub_name from publishers where pub_id not in 第 17 頁 共 22 頁(select pub_id from titles where type = business) 可以改寫成：select a.pub_name from publishers a left joi

33、n titles b on b.type = business and .pub_id=b. pub_id re b.pub_id is null select title rom titles here not exists (select title_id from sales where title_id = titles.title_id) 可以改寫成：select title om titles left join sales n sales.title_id = titles.title_id where sales.title_id is null b、如果保證子查詢沒有重復(fù)，

34、in、 exists 的相關(guān)子查詢可以用inner join 代替。比如：select pub_name om publishers where pub_id in (select pub_id from titles where type = business) 可以改寫成：select distinct a.pub_name from publishers a inner join titles b on b.type = business and a.pub_id=b. pub_id c、不要用 count(*) 的子查詢判斷是否存在記錄， 最好用 left join 或者 exists

35、，比如有人寫這樣的語句：select job_desc from jobs where (select count(*) from employee where job_id=jobs.job_id)=0 應(yīng)該改成：select jobs.job_desc from jobs left join employee on employee.job_id=jobs.job_id where employee.emp_id is null select job_desc from jobs where (select count(*) from employee where job_id=jobs.

36、job_id)0 應(yīng)該改成：select job_desc from jobs 第 18 頁 共 22 頁where exists (select 1 from employee where job_id=jobs.job_id) 1.10.6 注意連接條件的用法多表連接的連接條件對索引的選擇有著重要的意義，所以我們在寫連接條件的時候需要特別的注意。a、多表連接的時候，連接條件必須寫全，寧可重復(fù)，不要缺漏。b、 連接條件盡量使用聚集索引c、 注意 on 部分條件和 where 部分條件的區(qū)別1.10.7 查看執(zhí)行計劃及成本養(yǎng)成良好的編碼習(xí)慣， 每寫完一個 sql 語句，就查看執(zhí)行計劃及成本分析

37、。在大表上盡可能避免全表掃描及聚集索引掃描。1.11 索引的使用及維護(hù)在良好的數(shù)據(jù)庫設(shè)計基礎(chǔ)上， 能有效地使用索引是sql server取得高性能的基礎(chǔ)，sql server采用基于代價的優(yōu)化模型，它對每一個提交的有關(guān)表的查詢，決定是否使用索引或用哪一個索引。因為查詢執(zhí)行的大部分開銷是磁盤i/o，使用索引提高性能的一個主要目標(biāo)是避免全表掃描，因為全表掃描需要從磁盤上讀表的每一個數(shù)據(jù)頁，如果有索引指向數(shù)據(jù)值，則查詢只需讀幾次磁盤就可以了。所以如果建立了合理的索引，優(yōu)化器就能利用索引加速數(shù)據(jù)的查詢過程。但是，索引并不總是提高系統(tǒng)的性能，在增、刪、改操作中索引的存在會增加一定的工作量，因此，在適當(dāng)?shù)?/p>

38、地方增加適當(dāng)?shù)乃饕牟缓侠淼牡胤絼h除次優(yōu)的索引，將有助于優(yōu)化那些性能較差的sql server 應(yīng)用。1.11.1 聚簇索引 (clustered indexes)的使用聚簇索引是一種對磁盤上實際數(shù)據(jù)重新組織以按指定的一個或多個列的值排序。由于聚簇索引的索引頁面指針指向數(shù)據(jù)頁面，所以使用聚簇索引查找數(shù)據(jù)幾乎總是比使用非聚簇索引快。每張表只能建一個聚簇索引，并且建聚簇索引需要至少相當(dāng)該表120%的附加空間，以存放該表的副本和索引中間頁。建立聚簇索引的思想是：第 19 頁 共 22 頁1、大多數(shù)表都應(yīng)該有聚簇索引或使用分區(qū)來降低對表尾頁的競爭，在一個高事務(wù)的環(huán)境中，對最后一頁的封鎖嚴(yán)重影響系統(tǒng)的

39、吞吐量。2、在聚簇索引下，數(shù)據(jù)在物理上按順序排在數(shù)據(jù)頁上，重復(fù)值也排在一起，因而在那些包含范圍檢查(between 、=)或使用 group by 或order by 的查詢時，一旦找到具有范圍中第一個鍵值的行，具有后續(xù)索引值的行保證物理上毗連在一起而不必進(jìn)一步搜索，避免了大范圍掃描，可以大大提高查詢速度。3、在一個頻繁發(fā)生插入操作的表上建立聚簇索引時，不要建在具有單調(diào)上升值的列 (如 identity) 上，否則會經(jīng)常引起封鎖沖突。4、在聚簇索引中不要包含經(jīng)常修改的列，因為碼值修改后，數(shù)據(jù)行必須移動到新的位置。5、選擇聚簇索引應(yīng)基于where子句和連接操作的類型。聚簇索引的侯選列是：1、主鍵

40、列 ,該列在 where子句中使用并且插入是隨機(jī)的。2、按范圍存取的列，如pri_order 100 and pri_order 200 。3、在 group by 或 order by 中使用的列。4、不經(jīng)常修改的列。5、在連接操作中使用的列1.11.2 非聚簇索引 (nonclustered indexes)的使用sql server 缺省情況下建立的索引是非聚簇索引，由于非聚簇索引不重新組織表中的數(shù)據(jù)， 而是對每一行存儲索引列值并用一個指針指向數(shù)據(jù)所在的頁面。換句話說非聚簇索引具有在索引結(jié)構(gòu)和數(shù)據(jù)本身之間的一個額外級。一個表如果沒有聚簇索引時,可有 250個非聚簇索引。 每個非聚簇索引提

41、供訪問數(shù)據(jù)的不同排序順序。在建立非聚簇索引時，要權(quán)衡索引對查詢速度的加快與降低修改速度之間的利弊。另外，還要考慮這些問題：1、索引需要使用多少空間。2、合適的列是否穩(wěn)定。3、索引鍵是如何選擇的，掃描效果是否更佳。4、是否有許多重復(fù)值。第 20 頁 共 22 頁對更新頻繁的表來說，表上的非聚簇索引比聚簇索引和根本沒有索引需要更多的額外開銷。對移到新頁的每一行而言，指向該數(shù)據(jù)的每個非聚簇索引的頁級行也必須更新，有時可能還需要索引頁的分理。從一個頁面刪除數(shù)據(jù)的進(jìn)程也會有類似的開銷，另外，刪除進(jìn)程還必須把數(shù)據(jù)移到頁面上部，以保證數(shù)據(jù)的連續(xù)性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情況 : 1、某列常用于集合函數(shù) (如 sum,.)。2、某列常用于 join,order by,group by。3、查尋出的數(shù)據(jù)不超過表中數(shù)據(jù)量的20%。1.11.3 覆蓋索引 (covering indexes)的使用覆蓋索引是指那些索引項中包