[[206530]]

常見的字段類型選擇

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

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

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

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

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

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

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

約束與索引

每張表必須有主鍵

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

不允許使用外鍵

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

NULL 屬性

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

(新表為什么不允許 NULL?

允許 NULL 值，會(huì)增加應(yīng)用程序的復(fù)雜性。你必須得增加特定的邏輯代碼，以防止出現(xiàn)各種意外的 bug

三值邏輯，所有等號(hào)(“=”)的查詢都必須增加 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’

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

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

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

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

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

索引設(shè)計(jì)準(zhǔn)則

• 應(yīng)該對(duì) WHERE 子句中經(jīng)常使用的列創(chuàng)建索引
• 應(yīng)該對(duì)經(jīng)常用于連接表的列創(chuàng)建索引
• 應(yīng)該對(duì) ORDER BY 子句中經(jīng)常使用的列創(chuàng)建索引
• 不應(yīng)該對(duì)小型的表(僅使用幾個(gè)頁的表)創(chuàng)建索引，這是因?yàn)橥耆頀呙璨僮骺赡鼙仁褂盟饕龍?zhí)行的查詢快
• 單表索引數(shù)不超過 6 個(gè)
• 不要給選擇性低的字段建單列索引
• 充分利用唯一約束
• 索引包含的字段不超過 5 個(gè)(包括 include 列)

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

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

充分利用唯一索引

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

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

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

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

SQL 查詢

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

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

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

禁止使用 SELECT *

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

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

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

假設(shè)在字段 Col1 上建有一個(gè)索引，則下列場(chǎng)景將無法使用到索引：

ABS[Col1]=1

[Col1]+1>9

再舉例說明一下 

 

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

 

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

假設(shè)在字段 Col1 上建有一個(gè)索引，則下列場(chǎng)景將可以使用到索引：

[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 這個(gè)就用到了索引查詢

2.[Col1] like "%abc%" --index scan 而這個(gè)就并未用到索引查詢

3.[Col1] like "%abc" --index scan 這個(gè)也并未用到索引查詢

我想從上而三個(gè)例子中，大家應(yīng)該明白，***不要在 LIKE 條件前面用模糊匹配，否則就用不到索引查詢。

禁止使用游標(biāo)

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

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

禁止使用觸發(fā)器

觸發(fā)器對(duì)應(yīng)用不透明(應(yīng)用層面都不知道會(huì)什么時(shí)候觸發(fā)觸發(fā)器，發(fā)生也也不知道，感覺莫名……)

禁止在查詢里指定索引

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

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

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

避免類型轉(zhuǎn)換額外消耗的 CPU，引起的大表 scan 尤為嚴(yán)重

 

 

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

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

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

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

參數(shù)化查詢

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

sp_executesql

Prepared Queries

Stored procedures

用圖來說明一下，哈哈。

 

限制 JOIN 個(gè)數(shù)

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

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

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

盡量避免大事務(wù)操作

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

用圖來說明一下

 

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

關(guān)閉影響的行計(jì)數(shù)信息返回

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

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

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

使用 UNION ALL 替換 UNION

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

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

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

遞歸查詢層次限制

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

臨時(shí)表與表變量

 

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

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

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

如果查詢中本地變量使用了不等式運(yùn)算符，查詢分析器使用了一個(gè)簡(jiǎn)單的 30% 的算式來預(yù)估

Estimated Rows =(Total Rows * 30)/100

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

Estimated Rows = Density * Total Rows

盡量避免使用 OR 運(yùn)算符

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

增加事務(wù)異常處理機(jī)制

應(yīng)用程序做好意外處理，及時(shí)做 Rollback。

設(shè)置連接屬性 “set xact_abort on”

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

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

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

架構(gòu)設(shè)計(jì)

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

讀寫分離

• 設(shè)計(jì)之初就考慮讀寫分離，哪怕讀寫同一個(gè)庫(kù)，有利于快速擴(kuò)容
• 按照讀特征把讀分為實(shí)時(shí)讀和可延遲讀分別對(duì)應(yīng)到寫庫(kù)和讀庫(kù)
• 讀寫分離應(yīng)該考慮在讀不可用情況下自動(dòng)切換到寫端

Schema 解耦

禁止跨庫(kù) JOIN

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

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

主庫(kù) / 歸檔庫(kù)物理分離

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

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

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

自增長(zhǎng)與 Latch Lock

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