[[206530]]

常見的字段類型選擇

1. 字符類型建議采用 varchar/nvarchar 數(shù)據(jù)類型

2. 金額貨幣建議采用 money 數(shù)據(jù)類型

3. 科學計數(shù)建議采用 numeric 數(shù)據(jù)類型

4. 自增長標識建議采用 bigint 數(shù)據(jù)類型 (數(shù)據(jù)量一大，用 int 類型就裝不下，那以后改造就麻煩了)

5. 時間類型建議采用為 datetime 數(shù)據(jù)類型

6. 禁止使用 text、ntext、image 老的數(shù)據(jù)類型

7. 禁止使用 xml 數(shù)據(jù)類型、varchar(max)、nvarchar(max)

約束與索引

每張表必須有主鍵

• 每張表必須有主鍵，用于強制實體完整性
• 單表只能有一個主鍵(不允許為空及重復數(shù)據(jù))
• 盡量使用單字段主鍵

不允許使用外鍵

• 外鍵增加了表結構變更及數(shù)據(jù)遷移的復雜性
• 外鍵對插入，更新的性能有影響，需要檢查主外鍵約束
• 數(shù)據(jù)完整性由程序控制

NULL 屬性

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

(新表為什么不允許 NULL?

允許 NULL 值，會增加應用程序的復雜性。你必須得增加特定的邏輯代碼，以防止出現(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’

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

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

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

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

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

索引設計準則

• 應該對 WHERE 子句中經(jīng)常使用的列創(chuàng)建索引
• 應該對經(jīng)常用于連接表的列創(chuàng)建索引
• 應該對 ORDER BY 子句中經(jīng)常使用的列創(chuàng)建索引
• 不應該對小型的表(僅使用幾個頁的表)創(chuàng)建索引，這是因為完全表掃描操作可能比使用索引執(zhí)行的查詢快
• 單表索引數(shù)不超過 6 個
• 不要給選擇性低的字段建單列索引
• 充分利用唯一約束
• 索引包含的字段不超過 5 個(包括 include 列)

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

• SQL SERVER 對索引字段的選擇性有要求，如果選擇性太低 SQL SERVER 會放棄使用
• 不適合創(chuàng)建索引的字段：性別、0/1、TRUE/FALSE
• 適合創(chuàng)建索引的字段：ORDERID、UID 等

充分利用唯一索引

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

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

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

• 索引加快了查詢速度，但是卻會影響寫入性能
• 一個表的索引應該結合這個表相關的所有 SQL 綜合創(chuàng)建，盡量合并
• 組合索引的原則是，過濾性越好的字段越靠前
• 索引過多不僅會增加編譯時間，也會影響數(shù)據(jù)庫選擇***執(zhí)行計劃

SQL 查詢

• 禁止在數(shù)據(jù)庫做復雜運算
• 禁止使用 SELECT *
• 禁止在索引列上使用函數(shù)或計算
• 禁止使用游標
• 禁止使用觸發(fā)器
• 禁止在查詢里指定索引
• 變量 / 參數(shù) / 關聯(lián)字段類型必須與字段類型一致
• 參數(shù)化查詢
• 限制 JOIN 個數(shù)
• 限制 SQL 語句長度及 IN 子句個數(shù)
• 盡量避免大事務操作
• 關閉影響的行計數(shù)信息返回
• 除非必要 SELECT 語句都必須加上 NOLOCK
• 使用 UNION ALL 替換 UNION
• 查詢大量數(shù)據(jù)使用分頁或 TOP
• 遞歸查詢層級限制
• NOT EXISTS 替代 NOT IN
• 臨時表與表變量
• 使用本地變量選擇中庸執(zhí)行計劃
• 盡量避免使用 OR 運算符
• 增加事務異常處理機制
• 輸出列使用二段式命名格式

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

• XML 解析
• 字符串相似性比較
• 字符串搜索(Charindex)
• 復雜運算在程序端完成

禁止使用 SELECT *

• 減少內(nèi)存消耗和網(wǎng)絡帶寬
• 給查詢優(yōu)化器有機會從索引讀取所需要的列
• 表結構變化時容易引起查詢出錯

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

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

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

ABS[Col1]=1

[Col1]+1>9

再舉例說明一下 

 

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

 

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

假設在字段 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 條件前面用模糊匹配，否則就用不到索引查詢。

禁止使用游標

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

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

禁止使用觸發(fā)器

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

禁止在查詢里指定索引

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

• 隨著數(shù)據(jù)的變化查詢語句指定的索引性能可能并不***
• 索引對應用應是透明的，如指定的索引被刪除將會導致查詢報錯，不利于排障
• 新建的索引無法被應用立即使用，必須通過發(fā)布代碼才能生效

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

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

 

 

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

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

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

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

參數(shù)化查詢

以下方式可以對查詢 SQL 進行參數(shù)化：

sp_executesql

Prepared Queries

Stored procedures

用圖來說明一下，哈哈。

 

限制 JOIN 個數(shù)

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

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

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

盡量避免大事務操作

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

用圖來說明一下

 

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

關閉影響的行計數(shù)信息返回

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

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

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

使用 UNION ALL 替換 UNION

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

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

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

遞歸查詢層次限制

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

臨時表與表變量

 

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

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

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

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

Estimated Rows =(Total Rows * 30)/100

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

Estimated Rows = Density * Total Rows

盡量避免使用 OR 運算符

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

增加事務異常處理機制

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

設置連接屬性 “set xact_abort on”

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

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

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

架構設計

• 讀寫分離
• schema 解耦
• 數(shù)據(jù)生命周期

讀寫分離

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

Schema 解耦

禁止跨庫 JOIN

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

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

主庫 / 歸檔庫物理分離

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

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

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

自增長與 Latch Lock

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