常見的欄位類型選擇 1.字元類型建議採用varchar/nvarchar數據類型 2.金額貨幣建議採用money數據類型 3.科學計數建議採用numeric數據類型 4.自增長標識建議採用bigint數據類型 (數據量一大，用int類型就裝不下，那以後改造就麻煩了) 5.時間類型建議採用為datet ...
常見的欄位類型選擇
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會根據表格裡數據的一般分佈，“猜測”一個返回值。不管用戶在調用存儲過程或語句的時候代入的變數值是多少，生成的計劃都是一樣的。這樣的計劃一般會比較中庸一些，不一定是最優的計劃，但一般也不會是最差的計劃
l如果查詢中本地變數使用了不等式運算符，查詢分析器使用了一個簡單的 30% 的算式來預估Estimated Rows =(Total Rows * 30)/100 l如果查詢中本地變數使用了等式運算符，則查詢分析器使用：精確度 * 表記錄總數來預估
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自己內部申請和控制，用戶沒有辦法來干預，用來保證記憶體裡面數據結構的一致性，鎖級別是頁級鎖
