[轉載]常用 SQL Server 規範集錦

轉載者注:據說是某公司(攜程)内部規範.

常見的字段類型選擇

　　1.字元類型建議采用varchar/nvarchar資料類型

　　2.金額貨币建議采用money資料類型

　　3.科學計數建議采用numeric資料類型

　　4.自增長辨別建議采用bigint資料類型   (資料量一大，用int類型就裝不下，那以後改造就麻煩了)

　　5.時間類型建議采用為datetime資料類型

　　6.禁止使用text、ntext、image老的資料類型

　　7.禁止使用xml資料類型、varchar(max)、nvarchar(max)

　限制與索引

　　每張表必須有主鍵

• 每張表必須有主鍵，用于強制實體完整性
• 單表隻能有一個主鍵（不允許為空及重複資料）
• 盡量使用單字段主鍵

　　不允許使用外鍵

• 外鍵增加了表結構變更及資料遷移的複雜性
• 外鍵對插入，更新的性能有影響，需要檢查主外鍵限制
• 資料完整性由程式控制

　　NULL屬性

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

　　（新表為什麼不允許NULL？ 

　　允許NULL值，會增加應用程式的複雜性。你必須得增加特定的邏輯代碼，以防止出現各種意外的bug

　　三值邏輯，所有等号（“=”）的查詢都必須增加isnull的判斷。

　　Null=Null、Null!=Null、not(Null=Null)、not(Null!=Null)都為unknown，不為true）

　　舉例來說明一下：

　　如果表裡面的資料如圖所示：

　　你想來找查找除了name等于aa的所有資料，然後你就不經意間用了SELECT * FROM NULLTEST WHERE NAME<>’aa’

　　結果發現與預期不一樣，事實上它隻查出了name=bb而沒有查找出name=NULL的資料記錄

　　那我們如何查找除了name等于aa的所有資料，隻能用ISNULL函數了

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

　　但是大家可能不知道ISNULL會引起很嚴重的性能瓶頸 ,是以很多時候最好是在應用層面限制使用者的輸入，確定使用者輸入有效的資料再進行查詢。

　　舊表新加字段，需要允許為NULL（避免全表資料更新 ，長期持鎖導緻阻塞）（這個主要是考慮之前表的改造問題）

　索引設計準則

• 應該對 WHERE 子句中經常使用的列建立索引
• 應該對經常用于連接配接表的列建立索引
• 應該對 ORDER BY 子句中經常使用的列建立索引
• 不應該對小型的表（僅使用幾個頁的表）建立索引，這是因為完全表掃描操作可能比使用索引執行的查詢快
• 單表索引數不超過6個
• 不要給選擇性低的字段建單列索引
• 充分利用唯一限制
• 索引包含的字段不超過5個（包括include列）

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

• SQL SERVER對索引字段的選擇性有要求，如果選擇性太低SQL SERVER會放棄使用
• 不适合建立索引的字段：性别、0/1、TRUE/FALSE
• 适合建立索引的字段：ORDERID、UID等

　充分利用唯一索引

　　唯一索引給SQL Server提供了確定某一列絕對沒有重複值的資訊，當查詢分析器通過唯一索引查找到一條記錄則會立刻退出，不會繼續查找索引

　　表索引數不超過6個

　表索引數不超過6個（這個規則隻是攜程DBA經過試驗之後制定的。。。）

• 索引加快了查詢速度，但是卻會影響寫入性能
• 一個表的索引應該結合這個表相關的所有SQL綜合建立，盡量合并
• 組合索引的原則是，過濾性越好的字段越靠前
• 索引過多不僅會增加編譯時間，也會影響資料庫選擇最佳執行計劃

　SQL查詢

• 禁止在資料庫做複雜運算
• 禁止使用SELECT *
• 禁止在索引列上使用函數或計算
• 禁止使用遊标
• 禁止使用觸發器
• 禁止在查詢裡指定索引
• 變量/參數/關聯字段類型必須與字段類型一緻
• 參數化查詢
• 限制JOIN個數
• 限制SQL語句長度及IN子句個數
• 盡量避免大事務操作
• 關閉影響的行計數資訊傳回
• 除非必要SELECT語句都必須加上NOLOCK
• 使用UNION ALL替換UNION
• 查詢大量資料使用分頁或TOP
• 遞歸查詢層級限制
• NOT EXISTS替代NOT IN
• 臨時表與表變量
• 使用本地變量選擇中庸執行計劃
• 盡量避免使用OR運算符
• 增加事務異常處理機制
• 輸出列使用二段式命名格式

　禁止在資料庫做複雜運算

• XML解析
• 字元串相似性比較
• 字元串搜尋（Charindex）
• 複雜運算在程式端完成

　禁止使用SELECT *

• 減少記憶體消耗和網絡帶寬
• 給查詢優化器有機會從索引讀取所需要的列
• 表結構變化時容易引起查詢出錯

　　禁止在索引列上使用函數或計算

　禁止在索引列上使用函數或計算

　　在where子句中,如果索引是函數的一部分,優化器将不再使用索引而使用全表掃描

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

　　ABS[Col1]=1

　　[Col1]+1>9

　　再舉例說明一下

　　像上面這樣的查詢，将無法用到O_OrderProcess表上的PrintTime索引，是以我們應用使用如下所示的查詢SQL

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

　　[Col1]=3.14

　　[Col1]>100

　　[Col1] BETWEEN 0 AND 99

　　[Col1] LIKE ‘abc%’

　　[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 這個也并未用到索引查詢

　　我想從上而三個例子中，大家應該明白，最好不要在LIKE條件前面用模糊比對，否則就用不到索引查詢。

　禁止使用遊标

　　關系資料庫适合集合操作，也就是對由WHERE子句和選擇列确定的結果集作集合操作，遊标是提供的一個非集合操作的途徑。一般情況下，遊标實作的功能往往相當于用戶端的一個循環實作的功能。

　　遊标是把結果集放在伺服器記憶體，并通過循環一條一條處理記錄，對資料庫資源（特别是記憶體和鎖資源）的消耗是非常大的。

（再加上遊标真心比較複雜，挺不好用的，盡量少用吧）

　禁止使用觸發器

　　觸發器對應用不透明（應用層面都不知道會什麼時候觸發觸發器，發生也也不知道，感覺莫名……）

　禁止在查詢裡指定索引

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

• 随着資料的變化查詢語句指定的索引性能可能并不最佳
• 索引對應用應是透明的，如指定的索引被删除将會導緻查詢報錯，不利于排障
• 建立的索引無法被應用立即使用，必須通過釋出代碼才能生效

　變量/參數/關聯字段類型必須與字段類型一緻（這是我之前不太關注的）

　　避免類型轉換額外消耗的CPU，引起的大表scan尤為嚴重

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

　　如果資料庫字段類型為VARCHAR，在應用裡面最好類型指定為AnsiString并明确指定其長度

　　如果資料庫字段類型為CHAR，在應用裡面最好類型指定為AnsiStringFixedLength并明确指定其長度

　　如果資料庫字段類型為NVARCHAR，在應用裡面最好類型指定為String并明确指定其長度

　參數化查詢

　　以下方式可以對查詢SQL進行參數化：

　　sp_executesql

　　Prepared Queries

　　Stored procedures

　　用圖來說明一下，哈哈。

　限制JOIN個數

• 單個SQL語句的表JOIN個數不能超過5個
• 過多的JOIN個數會導緻查詢分析器走錯執行計劃
• 過多JOIN在編譯執行計劃時消耗很大

　限制IN子句中條件個數

　　在 IN 子句中包括數量非常多的值（數以千計）可能會消耗資源并傳回錯誤 8623 或 8632，要求IN子句中條件個數限制在100個以内

　盡量避免大事務操作

• 隻在資料需要更新時開始事務，減少資源鎖持有時間
• 增加事務異常捕獲預處理機制
• 禁止使用資料庫上的分布式事務

　　用圖來說明一下

　　也就是說我們不應該在1000行資料都更新完成之後再commit tran,你想想你在更新這一千行資料的時候是不是獨占資源導緻其它事務無法處理。

　關閉影響的行計數資訊傳回

　　在SQL語句中顯示設定Set Nocount On，取消影響的行計數資訊傳回，減少網絡流量

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

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

　　指定允許髒讀。不釋出共享鎖來阻止其他事務修改目前事務讀取的資料，其他事務設  置的排他鎖不會阻礙目前事務讀取鎖定資料。允許髒讀可能産生較多的并發操作，但其代價是讀取以後會被其他事務復原的資料修改。這可能會使您的事務出錯，向使用者顯示從未送出過的資料，或者導緻使用者兩次看到記錄（或根本看不到記錄）

　　使用UNION ALL替換UNION

　使用UNION ALL替換UNION

　　UNION會對SQL結果集去重排序，增加CPU、記憶體等消耗

　查詢大量資料使用分頁或TOP

　　合理限制記錄傳回數，避免IO、網絡帶寬出現瓶頸

　遞歸查詢層次限制

　　使用 MAXRECURSION 來防止不合理的遞歸 CTE 進入無限循環

　臨時表與表變量

　使用本地變量選擇中庸執行計劃

　　在存儲過程或查詢中，通路了一張資料分布很不平均的表格，這樣往往會讓存儲過程或查詢使用了次優甚至于較差的執行計劃上，造成High CPU及大量IO Read等問題，使用本地變量防止走錯執行計劃。

　　采用本地變量的方式，SQL在編譯的時候是不知道這個本地變量的值，這時候SQL會根據表格裡資料的一般分布，“猜測”一個傳回值。不管使用者在調用存儲過程或語句的時候代入的變量值是多少，生成的計劃都是一樣的。這樣的計劃一般會比較中庸一些，不一定是最優的計劃，但一般也不會是最差的計劃

　　如果查詢中本地變量使用了不等式運算符，查詢分析器使用了一個簡單的 30% 的算式來預估

　　Estimated Rows =(Total Rows * 30)/100

　　如果查詢中本地變量使用了等式運算符，則查詢分析器使用：精确度 * 表記錄總數來預估

　　Estimated Rows = Density * Total Rows

　盡量避免使用OR運算符

　　對于OR運算符，通常會使用全表掃描，考慮分解成多個查詢用UNION/UNION ALL來實作，這裡要确認查詢能走到索引并傳回較少的結果集

　增加事務異常處理機制

　　應用程式做好意外處理，及時做Rollback。

　　設定連接配接屬性 “set xact_abort on”

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

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

　　有JOIN關系的TSQL，字段必須指明字段是屬于哪個表的，否則未來表結構變更後，有可能發生Ambiguous column name的程式相容錯誤

　架構設計

• 讀寫分離
• schema解耦
• 資料生命周期

　讀寫分離

• 設計之初就考慮讀寫分離，哪怕讀寫同一個庫，有利于快速擴容
• 按照讀特征把讀分為實時讀和可延遲讀分别對應到寫庫和讀庫
• 讀寫分離應該考慮在讀不可用情況下自動切換到寫端

　Schema解耦

　　禁止跨庫JOIN

　資料生命周期

　　根據資料的使用頻繁度，對大表定期分庫歸檔

　　主庫/歸檔庫實體分離

　日志類型的表應分區或分表

　　對于大的表格要進行分區，分區操作将表和索引分在多個分區，通過分區切換能夠快速實作新舊分區替換，加快資料清理速度，大幅減少IO資源消耗

　頻繁寫入的表，需要分區或分表

　　自增長與Latch Lock

　　闩鎖是sql Server自己内部申請和控制，使用者沒有辦法來幹預，用來保證記憶體裡面資料結構的一緻性，鎖級别是頁級鎖