資料庫開發 常用sql server 規範集錦

標籤：輸出   cti   bss   date   idt   nio   tor   訪問   集合   

常用 SQL Server 規範集錦

來源：靜逸

連結：www.cnblogs.com/liyunhua/p/4534442.html

 

 

常見的欄位類型選擇

 

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

　　

指定允許髒讀。不發布共用鎖定來阻止其他事務修改當前事務讀取的資料，其他事務設定的獨佔鎖定不會阻礙當前事務讀取鎖定資料。允許髒讀可能產生較多的並行作業，但其代價是讀取以後會被其他交易回復的資料修改。這可能會使您的事務出錯，向使用者顯示從未提交過的資料，或者導致使用者兩次看到記錄（或根本看不到記錄）

　

使用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自己內部申請和控制，使用者沒有辦法來幹預，用來保證記憶體裡面資料結構的一致性，鎖層級是頁級鎖

 

 

關注「資料庫開發」

看更多精選資料庫技術文章

↓↓↓

閱讀 499318投訴精選留言

寫留言

•  3水邊這篇基本都是乾貨，實際應用都要參考！不過原因說的略簡單

  2天前

•  2??yezie禁止用nvarchar(max)，那大於4000的字串用什嗎？

  昨天

•  1曉道幹活

  昨天

•  1啊潮乾貨，666

  昨天

•  1Kurt有時候需要給很小的表建索引，而且填滿因數設定的很小，為了減少latch等待。 有時候會給,0/1或者 狀態 列上建索引，如果查詢的結果數極小。比如十萬條資料，只有十條是0,而就需要查詢這十條，那就需要建索引了。

  昨天

以上留言由公眾號篩選後顯示

瞭解留言功能詳情

資料庫開發 常用sql server 規範集錦