[[206530]]

常見的字段類型選擇

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 自己內部申請和控制，用戶沒有辦法來干預，用來保證內存里面數據結構的一致性，鎖級別是頁級鎖