常用 SQL Server 規(guī)范集錦

　常見的字段類型選擇

　　1.字符類型建議采用varchar/nvarchar數(shù)據(jù)類型
　　2.金額貨幣建議采用money數(shù)據(jù)類型
　　3.科學(xué)計(jì)數(shù)建議采用numeric數(shù)據(jù)類型
　　4.自增長標(biāo)識建議采用bigint數(shù)據(jù)類型 ? (數(shù)據(jù)量一大，用int類型就裝不下，那以后改造就麻煩了)
　　5.時間類型建議采用為datetime數(shù)據(jù)類型
　　6.禁止使用text、ntext、image老的數(shù)據(jù)類型
　　7.禁止使用xml數(shù)據(jù)類型、varchar(max)、nvarchar(max)

　約束與索引

　　每張表必須有主鍵

• 每張表必須有主鍵，用于強(qiáng)制實(shí)體完整性

• 單表只能有一個主鍵（不允許為空及重復(fù)數(shù)據(jù)）

• 盡量使用單字段主鍵

　　不允許使用外鍵

• 外鍵增加了表結(jié)構(gòu)變更及數(shù)據(jù)遷移的復(fù)雜性

• 外鍵對插入，更新的性能有影響，需要檢查主外鍵約束

• 數(shù)據(jù)完整性由程序控制

　　NULL屬性

　　新加的表，所有字段禁止NULL

　?。ㄐ卤頌槭裁床辉试SNULL？?
　　允許NULL值，會增加應(yīng)用程序的復(fù)雜性。你必須得增加特定的邏輯代碼，以防止出現(xiàn)各種意外的bug
　　三值邏輯，所有等號（“=”）的查詢都必須增加isnull的判斷。
　　Null=Null、Null!=Null、not(Null=Null)、not(Null!=Null)都為unknown，不為true）

　　舉例來說明一下：

　　如果表里面的數(shù)據(jù)如圖所示：

　　你想來找查找除了name等于aa的所有數(shù)據(jù)，然后你就不經(jīng)意間用了SELECT * FROM NULLTEST WHERE NAME’aa’

　　結(jié)果發(fā)現(xiàn)與預(yù)期不一樣，事實(shí)上它只查出了name=bb而沒有查找出name=NULL的數(shù)據(jù)記錄

　　那我們?nèi)绾尾檎页薾ame等于aa的所有數(shù)據(jù)，只能用ISNULL函數(shù)了

　　SELECT * FROM NULLTEST WHERE ISNULL(NAME,1)’aa’

　　但是大家可能不知道ISNULL會引起很嚴(yán)重的性能瓶頸?,所以很多時候最好是在應(yīng)用層面限制用戶的輸入，確保用戶輸入有效的數(shù)據(jù)再進(jìn)行查詢。

　　舊表新加字段，需要允許為NULL（避免全表數(shù)據(jù)更新 ，長期持鎖導(dǎo)致阻塞）（這個主要是考慮之前表的改造問題）

　索引設(shè)計(jì)準(zhǔn)則

• 應(yīng)該對 WHERE 子句中經(jīng)常使用的列創(chuàng)建索引

• 應(yīng)該對經(jīng)常用于連接表的列創(chuàng)建索引

• 應(yīng)該對 ORDER BY 子句中經(jīng)常使用的列創(chuàng)建索引

• 不應(yīng)該對小型的表（僅使用幾個頁的表）創(chuàng)建索引，這是因?yàn)橥耆頀呙璨僮骺赡鼙仁褂盟饕龍?zhí)行的查詢快

• 單表索引數(shù)不超過6個

• 不要給選擇性低的字段建單列索引

• 充分利用唯一約束

• 索引包含的字段不超過5個（包括include列）

　不要給選擇性低的字段創(chuàng)建單列索引

• SQL SERVER對索引字段的選擇性有要求，如果選擇性太低SQL SERVER會放棄使用

• 不適合創(chuàng)建索引的字段：性別、0/1、TRUE/FALSE

• 適合創(chuàng)建索引的字段：ORDERID、UID等

　充分利用唯一索引

　　唯一索引給SQL Server提供了確保某一列絕對沒有重復(fù)值的信息，當(dāng)查詢分析器通過唯一索引查找到一條記錄則會立刻退出，不會繼續(xù)查找索引

　　表索引數(shù)不超過6個

　表索引數(shù)不超過6個（這個規(guī)則只是攜程DBA經(jīng)過試驗(yàn)之后制定的。。。）

• 索引加快了查詢速度，但是卻會影響寫入性能

• 一個表的索引應(yīng)該結(jié)合這個表相關(guān)的所有SQL綜合創(chuàng)建，盡量合并

• 組合索引的原則是，過濾性越好的字段越靠前

• 索引過多不僅會增加編譯時間，也會影響數(shù)據(jù)庫選擇最佳執(zhí)行計(jì)劃

　SQL查詢

• 禁止在數(shù)據(jù)庫做復(fù)雜運(yùn)算

• 禁止使用SELECT *

• 禁止在索引列上使用函數(shù)或計(jì)算

• 禁止使用游標(biāo)

• 禁止使用觸發(fā)器

• 禁止在查詢里指定索引

• 變量/參數(shù)/關(guān)聯(lián)字段類型必須與字段類型一致

• 參數(shù)化查詢

• 限制JOIN個數(shù)

• 限制SQL語句長度及IN子句個數(shù)

• 盡量避免大事務(wù)操作

• 關(guān)閉影響的行計(jì)數(shù)信息返回

• 除非必要SELECT語句都必須加上NOLOCK

• 使用UNION ALL替換UNION

• 查詢大量數(shù)據(jù)使用分頁或TOP

• 遞歸查詢層級限制

• NOT EXISTS替代NOT IN

• 臨時表與表變量

• 使用本地變量選擇中庸執(zhí)行計(jì)劃

• 盡量避免使用OR運(yùn)算符

• 增加事務(wù)異常處理機(jī)制

• 輸出列使用二段式命名格式

　禁止在數(shù)據(jù)庫做復(fù)雜運(yùn)算

• XML解析

• 字符串相似性比較

• 字符串搜索（Charindex）

• 復(fù)雜運(yùn)算在程序端完成

　禁止使用SELECT *

• 減少內(nèi)存消耗和網(wǎng)絡(luò)帶寬

• 給查詢優(yōu)化器有機(jī)會從索引讀取所需要的列

• 表結(jié)構(gòu)變化時容易引起查詢出錯

　　禁止在索引列上使用函數(shù)或計(jì)算

　禁止在索引列上使用函數(shù)或計(jì)算

　　在where子句中,如果索引是函數(shù)的一部分,優(yōu)化器將不再使用索引而使用全表掃描

　　假設(shè)在字段Col1上建有一個索引，則下列場景將無法使用到索引：

　　ABS[Col1]=1

　　[Col1]+1>9

　　再舉例說明一下

　　像上面這樣的查詢，將無法用到O_OrderProcess表上的PrintTime索引，所以我們應(yīng)用使用如下所示的查詢SQL

　禁止在索引列上使用函數(shù)或計(jì)算

　　假設(shè)在字段Col1上建有一個索引，則下列場景將可以使用到索引：

　　[Col1]=3.14

　　[Col1]>100

　　[Col1] BETWEEN 0 AND 99

　　[Col1] LIKE ‘a(chǎn)bc%’

　　[Col1] IN(2,3,5,7)

　LIKE查詢的索引問題

　　1.[Col1] like “abc%”? –index seek ?這個就用到了索引查詢
　　2.[Col1] like “%abc%”? –index scan ?而這個就并未用到索引查詢
　　3.[Col1] like “%abc”? –index scan 這個也并未用到索引查詢
　　我想從上而三個例子中，大家應(yīng)該明白，最好不要在LIKE條件前面用模糊匹配，否則就用不到索引查詢。

　禁止使用游標(biāo)

　　關(guān)系數(shù)據(jù)庫適合集合操作，也就是對由WHERE子句和選擇列確定的結(jié)果集作集合操作，游標(biāo)是提供的一個非集合操作的途徑。一般情況下，游標(biāo)實(shí)現(xiàn)的功能往往相當(dāng)于客戶端的一個循環(huán)實(shí)現(xiàn)的功能。

　　游標(biāo)是把結(jié)果集放在服務(wù)器內(nèi)存，并通過循環(huán)一條一條處理記錄，對數(shù)據(jù)庫資源（特別是內(nèi)存和鎖資源）的消耗是非常大的。
（再加上游標(biāo)真心比較復(fù)雜，挺不好用的，盡量少用吧）

　禁止使用觸發(fā)器

　　觸發(fā)器對應(yīng)用不透明（應(yīng)用層面都不知道會什么時候觸發(fā)觸發(fā)器，發(fā)生也也不知道，感覺莫名……）

　禁止在查詢里指定索引

　　With(index=XXX)（ ?在查詢里我們指定索引一般都用With(index=XXX) ??）

• 隨著數(shù)據(jù)的變化查詢語句指定的索引性能可能并不最佳

• 索引對應(yīng)用應(yīng)是透明的，如指定的索引被刪除將會導(dǎo)致查詢報(bào)錯，不利于排障

• 新建的索引無法被應(yīng)用立即使用，必須通過發(fā)布代碼才能生效

　變量/參數(shù)/關(guān)聯(lián)字段類型必須與字段類型一致（這是我之前不太關(guān)注的）

　　避免類型轉(zhuǎn)換額外消耗的CPU，引起的大表scan尤為嚴(yán)重

　　看了上面這兩個圖，我想我不用解釋說明，大家都應(yīng)該已經(jīng)清楚了吧。

　　如果數(shù)據(jù)庫字段類型為VARCHAR，在應(yīng)用里面最好類型指定為AnsiString并明確指定其長度

　　如果數(shù)據(jù)庫字段類型為CHAR，在應(yīng)用里面最好類型指定為AnsiStringFixedLength并明確指定其長度

　　如果數(shù)據(jù)庫字段類型為NVARCHAR，在應(yīng)用里面最好類型指定為String并明確指定其長度

　參數(shù)化查詢

　　以下方式可以對查詢SQL進(jìn)行參數(shù)化：
　　sp_executesql
　　Prepared Queries
　　Stored procedures

　　用圖來說明一下，哈哈。

　限制JOIN個數(shù)

• 單個SQL語句的表JOIN個數(shù)不能超過5個

• 過多的JOIN個數(shù)會導(dǎo)致查詢分析器走錯執(zhí)行計(jì)劃

• 過多JOIN在編譯執(zhí)行計(jì)劃時消耗很大

　限制IN子句中條件個數(shù)

　　在 IN 子句中包括數(shù)量非常多的值（數(shù)以千計(jì)）可能會消耗資源并返回錯誤 8623 或 8632，要求IN子句中條件個數(shù)限制在100個以內(nèi)

　盡量避免大事務(wù)操作

• 只在數(shù)據(jù)需要更新時開始事務(wù)，減少資源鎖持有時間

• 增加事務(wù)異常捕獲預(yù)處理機(jī)制

• 禁止使用數(shù)據(jù)庫上的分布式事務(wù)

　　用圖來說明一下

　　也就是說我們不應(yīng)該在1000行數(shù)據(jù)都更新完成之后再commit tran,你想想你在更新這一千行數(shù)據(jù)的時候是不是獨(dú)占資源導(dǎo)致其它事務(wù)無法處理。

　關(guān)閉影響的行計(jì)數(shù)信息返回

　　在SQL語句中顯示設(shè)置Set Nocount On，取消影響的行計(jì)數(shù)信息返回，減少網(wǎng)絡(luò)流量

　　除非必要SELECT語句都必須加上NOLOCK

　除非必要，盡量讓所有的select語句都必須加上NOLOCK

　　指定允許臟讀。不發(fā)布共享鎖來阻止其他事務(wù)修改當(dāng)前事務(wù)讀取的數(shù)據(jù)，其他事務(wù)設(shè)? 置的排他鎖不會阻礙當(dāng)前事務(wù)讀取鎖定數(shù)據(jù)。允許臟讀可能產(chǎn)生較多的并發(fā)操作，但其代價是讀取以后會被其他事務(wù)回滾的數(shù)據(jù)修改。這可能會使您的事務(wù)出錯，向用戶顯示從未提交過的數(shù)據(jù)，或者導(dǎo)致用戶兩次看到記錄（或根本看不到記錄）

　　使用UNION ALL替換UNION

　使用UNION ALL替換UNION

　　UNION會對SQL結(jié)果集去重排序，增加CPU、內(nèi)存等消耗

　查詢大量數(shù)據(jù)使用分頁或TOP

　　合理限制記錄返回?cái)?shù)，避免IO、網(wǎng)絡(luò)帶寬出現(xiàn)瓶頸

　遞歸查詢層次限制

　　使用 MAXRECURSION 來防止不合理的遞歸 CTE 進(jìn)入無限循環(huán)

　臨時表與表變量

　使用本地變量選擇中庸執(zhí)行計(jì)劃

　　在存儲過程或查詢中，訪問了一張數(shù)據(jù)分布很不平均的表格，這樣往往會讓存儲過程或查詢使用了次優(yōu)甚至于較差的執(zhí)行計(jì)劃上，造成High CPU及大量IO Read等問題，使用本地變量防止走錯執(zhí)行計(jì)劃。

　　采用本地變量的方式，SQL在編譯的時候是不知道這個本地變量的值，這時候SQL會根據(jù)表格里數(shù)據(jù)的一般分布，“猜測”一個返回值。不管用戶在調(diào)用存儲過程或語句的時候代入的變量值是多少，生成的計(jì)劃都是一樣的。這樣的計(jì)劃一般會比較中庸一些，不一定是最優(yōu)的計(jì)劃，但一般也不會是最差的計(jì)劃

　　如果查詢中本地變量使用了不等式運(yùn)算符，查詢分析器使用了一個簡單的 30% 的算式來預(yù)估
　　Estimated Rows =(Total Rows * 30)/100

　　如果查詢中本地變量使用了等式運(yùn)算符，則查詢分析器使用：精確度 * 表記錄總數(shù)來預(yù)估
　　Estimated Rows = Density * Total Rows

　盡量避免使用OR運(yùn)算符

　　對于OR運(yùn)算符，通常會使用全表掃描，考慮分解成多個查詢用UNION/UNION ALL來實(shí)現(xiàn)，這里要確認(rèn)查詢能走到索引并返回較少的結(jié)果集

　增加事務(wù)異常處理機(jī)制

　　應(yīng)用程序做好意外處理，及時做Rollback。
　　設(shè)置連接屬性 “set xact_abort on”

　輸出列使用二段式命名格式

　　二段式命名格式：表名.字段名

　　有JOIN關(guān)系的TSQL，字段必須指明字段是屬于哪個表的，否則未來表結(jié)構(gòu)變更后，有可能發(fā)生Ambiguous column name的程序兼容錯誤

　架構(gòu)設(shè)計(jì)

• 讀寫分離

• schema解耦

• 數(shù)據(jù)生命周期

　讀寫分離

• 設(shè)計(jì)之初就考慮讀寫分離，哪怕讀寫同一個庫，有利于快速擴(kuò)容

• 按照讀特征把讀分為實(shí)時讀和可延遲讀分別對應(yīng)到寫庫和讀庫

• 讀寫分離應(yīng)該考慮在讀不可用情況下自動切換到寫端

　Schema解耦

　　禁止跨庫JOIN

　數(shù)據(jù)生命周期

　　根據(jù)數(shù)據(jù)的使用頻繁度，對大表定期分庫歸檔

　　主庫/歸檔庫物理分離

　日志類型的表應(yīng)分區(qū)或分表

　　對于大的表格要進(jìn)行分區(qū)，分區(qū)操作將表和索引分在多個分區(qū)，通過分區(qū)切換能夠快速實(shí)現(xiàn)新舊分區(qū)替換，加快數(shù)據(jù)清理速度，大幅減少IO資源消耗

　頻繁寫入的表，需要分區(qū)或分表

　　自增長與Latch Lock

　　閂鎖是sql Server自己內(nèi)部申請和控制，用戶沒有辦法來干預(yù)，用來保證內(nèi)存里面數(shù)據(jù)結(jié)構(gòu)的一致性，鎖級別是頁級鎖