數(shù)據(jù)庫性能概要規(guī)范

數(shù)據(jù)庫性能概要規(guī)范

N

DocumentInformation

LastUpdated6/6/21

Filename:數(shù)據(jù)庫性能概要規(guī)范.doc

AuthorInformation

AuthorDavidDong

GroupMIS

目錄

1目的..........................................................................5

2PerformanceAuditChecklist...............................................................5

3常用的數(shù)據(jù)庫優(yōu)化工具.........................................................6

4性能調(diào)整......................................................................6

4.1TSQL...........................................................................................6

4.1.1不要訪問多于你需要的數(shù)據(jù).......................................6

4.1.2所有者.對象名(objectownername.objectname)...............6

4.1.3Union&UnionAll...............................................................6

4.1.4Union&Jion......................................................................8

4.1.5Distinct..............................................................................9

4.1.6TOPN...............................................................................10

4.1.7InAndExist.....................................................................11

4.1.8InandBetween...............................................................11

4.1.9Like...................................................................................11

4.1.10OR....................................................................................12

4.1.11Orderby/sorting............................................................12

4.1.12Existandcount(*)...........................................................13

4.1.13使用Case...........................................................................14

4.1.14NullVsNotNull.................................................................15

4.1.15Bit.....................................................................................15

4.1.16字符串操作.....................................................15

4.1.17在Where語句中存在冗余條件..................................15

4.1.18在WHERE子句中的列上使用函數(shù)..............................16

4.1.19避免在WHERE中使用no-sargable....................................16

4.1.20避免使用游標(biāo)...................................................17

4.1.21使用存儲過程...................................................24

4.1.22不要包括不做任何事情的代碼....................................25

4.1.23sp_?.................................................................................25

4.1.24CHECKSUM索引..............................................25

4.1.25索引提示(Indexhint)......................................................26

4.2事務(wù)和死鎖............................................................26

4.2.1Transaction......................................................................27

4.2.2Complextransaction........................................................28

4.2.3Deadlock...........................................................................28

4.3表操作................................................................30

4.3.1AvoidTablescan..............................................................30

4.3.2SELECTINTO....................................................................30

4.3.3Update..............................................................................30

4.3.4大批量更新，刪除，插入數(shù)據(jù)....................................31

4.3.5TempTable&DerivedTables..........................................31

4.3.6有效的使用JOIN..................................................................34

4.3.7父子表..........................................................35

4.3.8關(guān)于統(tǒng)計(jì)信息(Statistics)...................................................35

4.3.9為列選擇合適數(shù)據(jù)類型...........................................36

5參考資料.....................................................................36

1目的

通過對?些常見的影響數(shù)據(jù)庫性能的查詢部分進(jìn)行分析，提出相應(yīng)的解決方法，以提高公司業(yè)務(wù)

數(shù)據(jù)庫業(yè)務(wù)系統(tǒng)的高可用性及可并發(fā)性。

本文基本分為兩部分，第一部分會給出一個簡單的checklst,列出了常見的一些影響性能的問

題，第二部分則強(qiáng)調(diào)在設(shè)計(jì)，開發(fā)方面的性能調(diào)整問題。

另外，由于個人經(jīng)驗(yàn)的關(guān)系，本文檔可能會有很多的缺陷，如果各位能夠提供相關(guān)不足，將非常

感激，請發(fā)送郵件至DavidDonq@

說明:

在不同的數(shù)據(jù)庫環(huán)境中，相同的數(shù)據(jù)庫性能設(shè)置技巧可能會有完全不同的表現(xiàn)結(jié)果，所以性能方

面的技巧一般都是與特定的環(huán)境相關(guān)的，不能盲目的相信所謂的性能調(diào)整技巧，而在我們每次進(jìn)

行相關(guān)性能調(diào)整的過程中，我們也需要進(jìn)行多種測試來達(dá)到最理想的效果。

2PerformanceAuditChecklist

Transact-SQLChecklistYourCheck

查詢時(shí)不返回多余的數(shù)據(jù)(比如沒有SELECT*)

盡量不使用游標(biāo)

如果確實(shí)需要使用游標(biāo)，能否有其他的解決方法

注意T-SQL語句中UNION和UNIONALL區(qū)別

盡量避免使用SELECTDISTINCT

注意T-SQL語句中Exist。和Count(*)區(qū)別

注意T-SQL語句中IN和EXISTS區(qū)別

在Where語句中不存在no-sargable("ISNULL",

n

"!>"z"NOTz"NOTIN","NOTLIKE'；和"LIKE

'%500'")

注意在WHERE子句中不存在數(shù)據(jù)類型的轉(zhuǎn)換

注意在WHERE語句中不存在對列進(jìn)行函數(shù)操作

注意查詢中盡力避免使用臨時(shí)表

注意到臨時(shí)表不可由derivedtable替代

注意到查詢使用了合適的表提示

注意到?jīng)]有以sp_作為前綴的存儲過程

注意到存儲過程中是否使用了SETNOCOUNTON

注意到所有的數(shù)據(jù)庫對象是依dbo.objectname的形式來引用

盡力保證事務(wù)盡可能短小并盡可能不使用嵌套事務(wù)

對JOIN操作進(jìn)行了優(yōu)化

3常用的數(shù)據(jù)庫優(yōu)化工具

>QueryAnalyzer

>Profiler

>IndexWizard

>System(Performance)Monitor

4性能調(diào)整

以下分別從TSQL、事務(wù)和死鎖、表操作這三個方面來講性能調(diào)整的問題。

4.1TSQL

4.1.1不要訪問多于你需要的數(shù)據(jù)

這個聽起來是多余的，但是確實(shí)是非常必要的，做起來也不大容易。其中包括不要返回給客戶端

不需要的列和行。比如在SELECT語句中不要使用SELECT*,否則會經(jīng)常返回給客氣端多于

它們所需要的數(shù)據(jù)，這樣可以有效減輕網(wǎng)絡(luò)傳輸壓力，減少不必要的I/O,以及減少內(nèi)存耗費(fèi)，

并能夠使查詢優(yōu)化器最優(yōu)化我們的查詢執(zhí)行計(jì)劃，從而減少潛在的性能問題。

4.1.2所有者?對象名(objectownername.objectname)

建議在所有對表、視圖和存儲過程引用時(shí)，加上它們的前綴。下面討論一下為什么使用前綴可以

改進(jìn)查詢性能。

例子:有一存儲過程dbo.foo,此存儲過程運(yùn)行查詢

SELECTcollFROMtablel

當(dāng)用戶Lucy調(diào)用存儲過程時(shí),查詢優(yōu)化器必須決定是檢索Lucy.tablel還是檢索dbo.tablelo

然后，當(dāng)用戶Lily調(diào)用同一個存儲過程時(shí)，查詢優(yōu)化器必須對查詢計(jì)劃進(jìn)行重新編譯，以決定

用戶是需要Lily.tablel還是需要dbo.tablelo但是如果把上面的SELECT語句修改如下

SELECTcollFROMdbo.tablel

查詢優(yōu)化器將不會遇到任何模糊性，從而避免重新編譯，達(dá)到提升性能的目的。

4.1.3Union&UnionAll

首先講一下UNION的工作原理，當(dāng)使用UNION語句時(shí)，它的功能與在結(jié)果集上SELECT

DISTINCT類似，也就是說使用UNION時(shí)它會首先合并兩個結(jié)果集，然后執(zhí)行一個類似于

SELECTDISTINCT的操作，以避免重復(fù)行的出現(xiàn)。這個過程在兩上結(jié)果集沒有任何重復(fù)行的

情況下也會進(jìn)行DISTINCT處理，所以如果我們確認(rèn)在UNION的兩個結(jié)果集確實(shí)存在重復(fù)行,

并且要消除重復(fù)行的出現(xiàn)時(shí)，就可以使用UNION.從另一方面來說，如果我們知道在結(jié)果集中并

不會存在重復(fù)行，或者說出現(xiàn)重復(fù)行對我們的應(yīng)用程序沒有什么影響時(shí)，我們應(yīng)該使用UNION

ALL語句來替代UNION語句，UNIONALL和UNION相比的優(yōu)點(diǎn)在于它并不會對兩個結(jié)果集

進(jìn)行SELECTDISTINCT操作，這樣可以節(jié)省SQLServer的資源使用。

例子:比較UNION和UNIONALL的差別:

--query1,useUnionAll,thesecondresultwillspendtothefirstresultset.

SELECT1,2

SELECT1,2

--query2zuseunion,thiswilldothedistinctoperation

SELECT1,2

SELECT1,2

我們來看一下其結(jié)果集，兀以在第一個查詢中只是把第二個查詢的結(jié)果集附加到第一個結(jié)果集

中，而第二個杳詢就執(zhí)行了DISTINCT操作，消除了重復(fù)行：

口Results_jMessages屋Executionplan

(Nocolumnname)(Nocolumnname)

(Nocolumnname)(Nocolumnname)

1i'1.........................■2

卜面來看性能方面的對比，分析卜.面的執(zhí)行計(jì)劃，我們可以看到第二個操作非常消耗資源，如果

兩個查詢一塊執(zhí)行時(shí),第二個查詢幾乎使用了所有的數(shù)據(jù)庫資源（主要消耗在MERGEJOIN中

的消除重復(fù)行部分）：

Query1:Querycost(relativetothebatch):0%

select1,2unionallselect1,2

宣■一由

SELECTConcatenationConstantScan

Cost:0%Cost:IS%Cost:43%

ConstantScan

Query2:Querycost(relativetothebatch):100%

select1,2unionselect1,2

口大

MergeJoin;Tl

SELECTConstantScan

(Union)

Cost:0%Cost:0%

Cost:LOO+

_±1

ConstantScan

Cost:0%

結(jié)論：在對UNION和UNIONALL進(jìn)行選擇時(shí)，除非必要，推薦使用UNIONALL

4.1.4Union&Jion

在比較UNION和UNIONALL的基礎(chǔ)上，下面來看UNION合并結(jié)果集和使用JION查詢得到

結(jié)果集之間的差別.

例子:使用UNION來合并多個結(jié)果集的情況：

USEPUBS;

GO

SELECTFROMDBO.SALES

WHEREQTY-.10

UNION

SELECTFROMDBO.SALES

WHEREQTY>11

上面的查詢可以被重寫為下面的查詢，這樣一般就會有性能提升:

USEPUBS;

GO

SELECTdistinctFROMDBO.SALES

WHEREQTY<10CxQTY>11

NOTE：如果知道結(jié)果集中并不存在重復(fù)的數(shù)據(jù)行，我們也可以使用UNIONALL來提高性能。

但是UnionAll性能一般來說比Union要好，但是比Join要差。

杳看它們的運(yùn)行結(jié)果，可以發(fā)現(xiàn)運(yùn)行結(jié)果是相同的,現(xiàn)在來分析一卜它們的執(zhí)行計(jì)劃和性能差別:

SELECT*FROMDBO.SALESWHEREQTY<10UNIONSELECT-FROMDBO.SALESWHEREQTY>11

□

SortClusteredIndexScan

SELECT

(DistinctSort)[pubi].[dbo].[sales].[UPKCL^salts]

Cost:0%

Cost:L3%Cost:43%

CluBteredIndexScan

[pub?].[dbo].[Balei].[OTKCL_sale?]

Cost:43%

Query2:Querycost(relatiretothebatch):30%

SELECTdistinct*FROMDBO.SALESWHEREQTY<10ORQTY>11

□

ClusteredIndexScan

SELECT

[pubs].[^o].[sales].[UPKd_saies]

Cost:0%

Cost:100，

可見使用UNION時(shí)執(zhí)行兩次CLUSTERED索引的掃描，并且要把結(jié)果使用DISTINCT合并起

來，而第二個查詢只要進(jìn)行一次CLUSTERED索引進(jìn)行掃描，然后直接展現(xiàn)出來，從而大大提

高了性能。

但是如果能夠使用JOIN達(dá)到目的的話，我們建議不要使用Union和UnionALL而是直接使

用JOIN來取我們需要的數(shù)據(jù)。

4.1.5Distinct

有開發(fā)人員在寫查詢的時(shí)候不管是否需要都習(xí)慣性的在查詢中寫上DISTINCT,這是一個很不好

的習(xí)慣，特別是當(dāng)我們的查詢中包含很大數(shù)據(jù)量的時(shí)候更會大大消耗有限的數(shù)據(jù)庫資源并降低的

應(yīng)用程序性能。

DISTINCT應(yīng)該在確認(rèn)結(jié)具集中存在重復(fù)行，并且這些重復(fù)的確數(shù)據(jù)并不是我們所需要的數(shù)據(jù)

時(shí)才能夠使用。這是因?yàn)镈ISTINCT在數(shù)據(jù)庫上執(zhí)行了很多額外的操作從而消耗了很多數(shù)據(jù)庫

資源，這樣會減少別的查詢在執(zhí)行時(shí)所能夠使用的資源，增加數(shù)據(jù)庫出現(xiàn)性能問題的幾率,

例子：

USEPUBS;

GO

SELECTDISTINCT

au_fnamez

au_lname

FROMauthors

這是一個非常簡單的SELECTDISTINCT查詢語句的示例，但是當(dāng)我們執(zhí)行一個復(fù)雜的語句時(shí)

就應(yīng)該考慮重新編碼以達(dá)到性能的要求，如下面的語句，當(dāng)返回authore表中已經(jīng)出版過書的

作者時(shí)，很多人會像下面這樣寫：

SELECTDISTINCT

au_fnamez

au_lname

FROMauthorsaJOtitleAuthort

ONt.auid=a.auid

但這時(shí)只想得到作者名字，我們可以重寫這個語句來提升我們查詢語句的性能:

SELECTau_fname,

au_lname

FROMauthorsa

WHEREEXISTS(

SELECT*

FROMtitleAuthort

WHEREt.au_ida.au_id

上面的查詢能夠提升性能是因?yàn)槿绻硞€作者出過很多書，當(dāng)查到這個作者出版的第一條記錄

時(shí)，就會停止對這個作者的處理。

我們要慎重考慮是不是真的需要DISTINCT

4.1.6TOPN

如果我們的應(yīng)用程序要返回上千行乃至上萬行數(shù)據(jù)的時(shí)候，我們要考慮是不是真正的需要這么多

數(shù)據(jù)，是不是可以使用TOP操作符來限制返回給客戶端的行數(shù)或者返向給客戶端結(jié)果集行的百

分比，這樣可以減少資源的使用，提高數(shù)據(jù)庫性能并且有效節(jié)省帶寬

下面說一下關(guān)于減少網(wǎng)絡(luò)傳輸壓力的內(nèi)容。如果我們每次多返回給客戶端10行數(shù)據(jù)，每行多返

回200個字節(jié)，每天10000次被執(zhí)行，這就是一筆不小的網(wǎng)絡(luò)傳輸成本

(10*200*10000Byte),另外，如果再加上傳輸圖片等，將耍占用更多的帶寬。

例子:

USEPUBS;

GO

SELECTTOP1FNAME,LNAME,HIRE_DATEFROMDBO.EMPLOYEE

WHERELNAME'A*'

這時(shí)如果有100,000行數(shù)據(jù)符合WHERE條件，也只返回限制的1行結(jié)果集。因?yàn)镾QLServer

在處理時(shí)，當(dāng)結(jié)果集的行數(shù)達(dá)到TOP中指定的數(shù)目時(shí)，所有的處理都將停止，這樣就可以潛在

的提高SQLServer的負(fù)載，增加性能。

此外，TOP操作還可以讓我們指定返回給客戶端結(jié)果集行數(shù)的百分比，如：

USEPUBS;

GO

SELECTTOP1PERCENTFNAME,LNAMEfHIRE_DATEFROMDBO.EMPLOYEE

WHERELNAMELIKE*A%'

NOTE：如果?個SELECT語句既包含TOP又包含ORDERBY了?句，那么返回的行將會

從排序好的結(jié)果集中選擇，并且只返回已排好序結(jié)果集的前n行（或者前百分之N行）。

4.1.7InAndExist

當(dāng)我們能夠在我們的杳詢中選擇使用IN和Exist語句時(shí)，推薦使用EXISTS,因?yàn)镋XISTS一

般更加高效（EXIST只遇到附合條件的很第一個結(jié)果時(shí)，此記錄就退出處理）。

4.1.8InandBetween

在兗詢語句中選擇使用IN和BETWEEN時(shí)，建議使用BETWEEN,因?yàn)樗诤芏鄨龊舷赂咝А?/p>

例子:有一個customer表,在customer_number列上有一個非聚集索引，進(jìn)行如下查詢：

SELECTcustomer_number,customer_name

FROMcustomer

WHEREcustomer_numberin(1000,1001,1002,1003,1004)

查詢優(yōu)化器可以使我們下面使用BETWEEN的語句比使用IN的語句性能更高效:

SELECTcustomer_numberzcustomer_name

FROMcustomer

WHEREcustomer_numberBETWEEN1000and1004

4.1.9Like

當(dāng)在WHERE中使用Like時(shí)，盡可能的在Like語句中使用一個或者多個前導(dǎo)字符，比如:

使用

而不是:

%m

如果在Like中使用一個前導(dǎo)字符，此時(shí)查詢優(yōu)化器就會自動使用相應(yīng)索引（如果有合適索引存

在的話）來處理這個杳詢，但是如果我們Like語句中的前導(dǎo)字符使用通配符，杳聞優(yōu)化器不會

使用索引，而會對表進(jìn)行掃描，從而大大降低性能。這點(diǎn)在SQLServer2005卜.有所提升，

在SQLServer2005中，置詢優(yōu)化器可以為類似于LIKE，％m'語句使用索引。

NOTE:使用的前導(dǎo)字符越多，查詢優(yōu)化器就越有可能找到合適的索引，從而加快查詢。

另外，如果應(yīng)用程序的查詢中有很多對CHAR和VARCHAR列進(jìn)行Like操作時(shí)，我們可能要

考慮使用SQLServer的全文索引，全文索引對于處理這方面查詢會大大提升性能。

4.1.10OR

如果一個查詢語句中包括多個OR子句，為提升性能，一般情況下可以重寫為一系列查詢并使用

UNIONALL來合并結(jié)果集。

例子:

SELECTemployeelD,firstname,lastname

FROMnames

WHEREdept=*prod*orcity=*Orlando'ordivision=*food,

這個語句在WHERE中有三個獨(dú)立的條件，為了使用到索引，我們要在這三列上創(chuàng)建組合索引。

像這樣的語句可能被重寫為多個查詢并使用UNIONALL來合并結(jié)果集，從而替代OR操作。

如下：

SELECTemployeelD,firstname,lastnameFROMnamesWHEREdept'prod'

SELECTemployeelD,firstname,lastnameFROMnamesWHEREcity'Orlando,

SELECTemployeelD,firstname,lastnameFROMnamesWHEREdivision1food,

每一個查詢都得出一樣的結(jié)果集（元數(shù)據(jù)相同），如果在這個表中只有dept列上存在索引，此

時(shí)第一個SELECT就會使用此索引，而第二個第三個SELECT語句會進(jìn)行表的掃描，而不是全

部進(jìn)行表的掃描。

這只是一個非常簡單的例子，只是示范了如何重寫查詢來提高性能，如果這個查詢比較復(fù)雜，那

么使用UNIONALL就會大大提高性能，因?yàn)檫@樣我們可以對每個語句進(jìn)行調(diào)整，而如果是全

在一個OR子句中，這些我們是做不到的。

NOTE：注意在這里我們是使用UNIONALL而不是UNION,原因是UNION不光會對結(jié)果集進(jìn)

行合并，也會對結(jié)果集進(jìn)行排序并移除所有的重復(fù)行，所以性能會大大降低。

4.1.11Orderby/sorting

ORDERBY要占用很多額外的資源，所以除非在真正需要的情況下，否則不要在SELECT語句

中使用ORDERBY.我們可以考慮，這些數(shù)據(jù)在客戶端進(jìn)行排序是否會更好，或者客戶端是否

需要我們對結(jié)果集進(jìn)行排序。

SORT一般是伴隨著以下T-SQL語句出現(xiàn)：

>ORDERBY

>GROUPBY

>SELECTDISTINCT

>UNION

>CREATEINDEX(此時(shí)也需要對數(shù)據(jù)進(jìn)行排序)

一般情況下，這些命令不可能避免，但是另一方面，我們可能想辦法減少這些語句對資源的使用，

這包括：

>盡可能減少被排序的行數(shù),就是只對必要排序的數(shù)據(jù)行集進(jìn)行排序。

>盡可能減少結(jié)果臭中列的數(shù)目。

>盡可能減少所有行的物理寬度.

>盡可能對INT的列進(jìn)行排序，而不是Character或者Char列。

當(dāng)使用SORT操作時(shí)，一定要考慮上面的建議，并保持下面的原則“除非必要，否則不要來對

結(jié)果集進(jìn)行排序”。

4.1.12Existandcount(*)

對數(shù)據(jù)庫進(jìn)行更新時(shí)，很多時(shí)候要首先判斷被更新的記錄是否存在，或者對刪除對象之前，判斷

對象是否存在，此時(shí)不要使用SELECTCOUNT(*)來標(biāo)識，因?yàn)樗男阅苁欠浅５偷?。為了?/p>

升性能，我們可以使用IFEXISTS來實(shí)現(xiàn)相關(guān)的操作。IFEXISTS能提高性能是因?yàn)楫?dāng)有一條

記錄為真是，處理就會立即退出，但是COUNT(*)會檢查表中的每一條記錄的情況，不管符合

條件的數(shù)據(jù)有一條還是上萬條。

例子:

USEPUBS

--COUNT(*):

IF(SELECTCOUNT(*)FROMDBO.AUTHORS)>0

BEGIN

PRINT?ASDF,

END

--下面是使用EX工ST的語句，也是性能較好的語句：

IF■'XI(SELECTFROMDBO.AUTHORS

BEGIN

PRINT'ASDF'

END

看一下執(zhí)行計(jì)劃，可見使用EXIST確實(shí)能夠提高性能。

Query1:Querycost(relativetothebatch):85%

IF(SELECTCOUNT。FROMDSO.AUTHORS)>0

科一■5回

C3C&ScaXArCouutn3c?

Coae:0ICoat:0tiIDMZJoxalCom:0I

COM:0t

3鏘團(tuán)

:nS*SCAB

(Affxefawl①g,]」一】jMUmrg]

COM:0t

CF:01——100t

Query2:Querycost(relativetothebatch):15%

IFEXISTS(SELECT?FROMDBO.AUTHORS)

/q回

A___n

Loope

CONDConpureScalarConstantScan

Cost:0%Co?r:0?(LeftSeniJoin)Coir:0%

COST:0%

血

ZndaxScan

[pubs〕.IdboJ.(authors)(auniund]

Coit:100、

雖然這兩個杳詢語句都使用了一樣的索引，但是在COUN-(*)時(shí)返回的行的數(shù)據(jù)是表中符合條

件的所有行.然后才對這些行進(jìn)行Aggregate,這也就是為什么數(shù)據(jù)越多,使用COUNT(*)

性能越低的原因。

4.1.13使用Case

當(dāng)對一個表進(jìn)行多次更新時(shí)，盡量合并到一個更新語句中。

例子:如果要對PUBS數(shù)據(jù)庫中的相關(guān)書根據(jù)不同的條件來調(diào)整它的銷售價(jià)格，需要調(diào)高商務(wù)

類書的銷售價(jià)格至原來的130%,調(diào)高非商務(wù)類書的銷售價(jià)格至原來的110%,我們很多人在

UPDATE的時(shí)候是以一個事務(wù)中包括兩個獨(dú)立的UPDATE語句來進(jìn)行的，如下：

BEGINTRAN

UPDATEtitlesSETpriceprice1.30WHEREtype'business'

UPDATEtitlesSETpriceprice1.10WHEREtype<>,business'

COMMITtran

上面語句的缺點(diǎn)是它會對表進(jìn)行兩次的讀寫，下面看一下如何把它們寫到一個語句中來:

UPDATEtitles

SETprice

CASE

WHENtype-1business

THENprice1.30

ELSEprice*1.10

END

比較一下下面的執(zhí)行計(jì)劃，發(fā)現(xiàn)第一個查詢語句的COST為66.7%,第二個查詢語句的COST

為33.3%.這樣就可以大大提高性能。

Query1:Querycost(relativetothebatch):33%

UPDATE[titles]SET[prxue]■[pirxce]?&】WHERE[type]?@2

CluydXndftxUpdateCIuiMz*dIndexScan

CcRpvScalasTop

[puba].[dbo].(tlclM).[U?KCLtitle[pubB].[dbo].CtlclM].nXPJCL

Cott:0%Cott:0%

Coe：22%-Co*t：78%

Query2:Querycost(relativetothebatch):33%

UPDATE[titles]SET[price]?[price]*81WHERE[type]<>82

n

UPDATEClusteredIndexUpdikeCospusScalarTopClusteredIndexScan

(pubs).(dbo].(titlts].EPKCL_xtl?」(pjbs).(dbol.(titles).

Coit:0?COST:0%Coir:0%

Coit:22%CoIX：78%

Query3:Querycost(relativetothebatch):33%

UPDATEtitlesSt工priu。.CAS￡WHENtype.'business'HHENpriup1.32ELSEpr；u。,l?10END

回n。

ClustorMIndaxUpda^o-*ClusteredIndexSean

VPDA7EP

[pubs].[d^o].(cities].[UPKCLJ.0.Cpub?)?[So：.(titlM].lUPKCL^ltlei-

Coet:0%

Coet：22QCo"："，

4.1.14NullVsNotNull

在創(chuàng)建表時(shí)應(yīng)該為每列創(chuàng)建默認(rèn)值而不是允許為NULL,因?yàn)閷煽樟羞M(jìn)行操作和對非可空列進(jìn)

行操作相比將需要更多額外的資源。如比較(comparisons)就是一個很好的例子。

所以我們應(yīng)該盡可能的使用NOTNULL來替代NULL。

4.1.15Bit

不推薦使用Bit歹人

如果已經(jīng)使用Bit歹U，請確保不要允許為NULL,如果此列有兩個以上的狀態(tài)，請考慮其它類型。

4.1.16字符串操作

盡量避免在Transact-SQL中進(jìn)行字符的串聯(lián)操作，因?yàn)檫@是個很慢的操作，會造成應(yīng)用程序

的減慢(TSQL是為集合操作而優(yōu)化的)。

4.1.17在Where語句中存在冗余條件

在WHERE語句中小心地使用OR,這經(jīng)常會得到一些多余的數(shù)據(jù)，同時(shí)也很容易在WHERE

語句中出現(xiàn)冗余的子句。

例子:

SELECTcompanyid,plantid,formulaid

FROMbatchrecords

WHERE(companyid10001'plantid,02021formulaid,39988773')

(companyid'0001'ndplantid=*0202,)

這兒的WHERE語句是冗余的:

companyid=100011andplantid-'0202*a：：dformulaid=139988773,

是下面子句的子集：

companyid'00011mdplantid*0202,

換句話來說，這個查詢是冗余的，但SQLServer2000的查詢優(yōu)化器并不會自動探測是否存

在冗余（這個在SQLServer2005中有所提高），它完全按你所寫的條件去執(zhí)行，此時(shí)SQL

Server就會首先得到所有的數(shù)據(jù)，然后使用SELECTDISTINCT來移除冗余的數(shù)據(jù)。如果刪

除以下的查詢子句：

companyid'0001*anciplantid*0202'

此時(shí)這個查詢語句就會加快很多。

4.1.18在WHERE子句中的列上使用函數(shù)

默認(rèn)情況下，在進(jìn)行比較時(shí)，經(jīng)常寫類似下面的語句：

SELECTcolumn_nameFROMtable_name

WHERELOWERicolumn_name*name*

也就是說，大家認(rèn)為SQLServer是大小寫敏感的，但一般情況下我們SQLServer的配置為

大小寫不敏感，所以不使用類似于上面的語句，就可以大大加快查詢的速度。

但如果數(shù)據(jù)庫確實(shí)是配置為大小寫敏感時(shí)，上面的代碼依然性能很低。因?yàn)樵瓌t是：不要在

WHERE中對列進(jìn)行任何函數(shù)操作，在列上進(jìn)行函數(shù)操作會導(dǎo)致此查詢并不能夠使用索引，如下:

SELECTmember_number,first_name,last_name

FROMmembers

WHEREDATEDIFF(yyzdatofbirth,GETDA7E())>21

如果需要進(jìn)行函數(shù)操作的話可以寫類似于下面的代碼，下面的代碼可以使用dateofbirth列上的

索引;

SELECTmember_number,first_name,last_name

FROMmembers

WHEREdateofbirthDATEADD(yy,-21,GETDATE())

4.1.19避免在WHERE中使用no-sargable

像“ISNULL","K'*,"NOT","NOTEXISTS","NOTIN","NOT

LIKE"，和"LIKE'%50G”的操作一般來說將導(dǎo)致查詢優(yōu)化器不能夠使用索引來進(jìn)行數(shù)據(jù)的查

找。一般情況下，我們可以重寫這樣的語句來達(dá)到性能的提升。

例子:

下面語句：

WHERESUBSTRINGifirstname,1,1?m,

可能被重寫為下面的查詢:

WHEREfirstname*m%

NOT其實(shí)并不是no-sargable,但是一般情況下盡可能在查詢中查詢中不使用NOT操作符,

如：

WHERE\0Tcolumn_nane>5

可以被重寫為:

WHEREcolumnname<-5

4.1.20避免使用游標(biāo)

游標(biāo)可以嚴(yán)重影響SQLServe「性能，但是一些場合下不可避免，不過更多場合下我們是可以

不使用游標(biāo)的。所以如果現(xiàn)在你的應(yīng)用程序使用T-SQL游標(biāo)，請重新檢杳你的代碼并盡可能的

重寫它們。

Transact-SQL是設(shè)計(jì)用來處理集合操作的，而不是對獨(dú)立的數(shù)據(jù)記錄行進(jìn)行操作。不過SQL

提供了一種對數(shù)據(jù)行進(jìn)行操作的選項(xiàng)，在Transact-SQL中游標(biāo)就可以被用來處理獨(dú)立的行，

但是問題就是它的操作是如此慢，理想情況下，在高性能的基于SQLServer的應(yīng)用程序中，

游標(biāo)應(yīng)該被避免。

所以如果你要進(jìn)行一個基于行的操作，設(shè)法找到其它的方法來完成這項(xiàng)工作是一個非常好的開發(fā)

習(xí)慣?？紤]使用以下選項(xiàng)來替代游標(biāo)：

>使用臨時(shí)表

>使用WHILE循環(huán)

>使用derivedtables

>使用子查詢

>使用CASE語句

>使用多個查詢

上面的這些選項(xiàng)有時(shí)候可以替代游標(biāo)，從而大大提升性能。但并不是所有的情況下都能夠使用以

上的選項(xiàng)來替代游標(biāo)，如果發(fā)現(xiàn)不可能避免使用游標(biāo)，請參考下面的建議或許會有幫助：

?SQLServer提供了兒種不同種類的游標(biāo)，它們之間性能也存在著差異。我們要選擇對

服務(wù)器性能影響最小的能夠達(dá)到目標(biāo)的游標(biāo)。最高效的游標(biāo)就是fastforward-only游

標(biāo)。這個在我們的命名規(guī)范中也有提及

?當(dāng)使用游標(biāo)時(shí)，取盡可能少的數(shù)據(jù)

?當(dāng)用過游標(biāo)后，不僅CLOSE它，同時(shí)也要DEALLOCATE此游標(biāo)，也就是說游標(biāo)占用

的存儲單元也需要擇放，如果只是簡單的關(guān)閉而不擇放，雖然此游標(biāo)鎖死的資源被釋放，

但SQLServer的資源并沒有釋放，游標(biāo)使用的資源依然存在直到你釋放它為止。

SQLServer游標(biāo)非?常消耗數(shù)據(jù)庫資源，這是因?yàn)樗鼧O低的性能所致。但是根據(jù)“存在就是必然”

的原則(Montaque語錄)，它們也有存在的道理，因?yàn)樵谟行┑胤绞谴_實(shí)非常有用的，它們非

常靈活?，可以使我們對每行的數(shù)據(jù)進(jìn)行非常靈活的操作，但一般情況下我們使用的游標(biāo)可能使用

其它方法來代替，比如DerivedTable,集合查詢和臨時(shí)麥，下面會討論一下這個問題。

下面討論游標(biāo)的可選替代方案。

這里通過一些示例來演示一下如何不使用游標(biāo)來解決一些以前用游標(biāo)來解決的問題。

例子:

首先，先看一下簡單的游標(biāo)，此游標(biāo)在循環(huán)一個表，然后我們看一下如何不使用游標(biāo)來達(dá)到相同

的目標(biāo)。

使用游標(biāo)：

ifexists(selectAfromsysobjectswherenameN*prcCursorExample')

dropprocedureprcCursorExample

go

CREATEPROCEDUREprcCursorExample

AS

/*

**putyourcommenthere

★*

*/

SETNOCOUNTON

--declareallvariables!

DECLAREQiRowIdint,

@vchCustomerNamenvarchar255),

@vchCustomerNmbrnvarchar10

-declarethecursor

DECLARECustomerCURSORFOR

SELECTiRowId,

vchCustomerNmbr.

vchCustomerName

FROMCustomerTable

OPENCustomer

FETCHCustomerINTO@iRowId,

@vchCustomerNmbr,

QvchCustomerName

--startthAmainp-ssHng1ccp.

WHILE@@Fetch_Status=0

BEGIN

——Thisiswhereyouperformyourdetailedrow-by-row

-processing.

——Getthenextrow.

FETCHCustomerINTO@iRowId,

^vchCustomerNmbrz

@vchCustomerName

END

CLOSECustomer

DEALLOCATECustomer

RETURN

不使用游標(biāo):

ifexists(selectfromsysobjectswherename=N*prcLoopExample')

dropprocedureprcLoopExample

g。

CREATEPROCEDUREprcLoopExample

AS

/*

**putyourcommenthere

*/

SETNOCOUNTON

--declareallvariables!

DECLAREQiReLuxiiCudeinL,

SiNextRowZdint,

QiCurrentRowIdint,

@iLoopControlint,

QvchCustonerNamenvarchar(255),

@vchCustonerNmbrnvarchar(10)

QchProductNumbernchar(30)

--Initializevariables!

SELECT@iLoopControl1

SELECT@iNextRowIdMIN(iRowId

FROMCustomerTable

Makesurethetablehasdata.

IFSN：」L.@iNextRow：d,00

BEGIN

SELECT'Nodatainfoundintable!1

RETURN

END

Retrievethefirstrow

SELECT@iCurrentRowIdiRowId,

@vchCustomerNmbrvchCustonerNmbr

@vchCustomerNamevchCustonerName

FROMCustomerTable

WHEREiRowZd@iNextRowId

startthemainprocessingloop.

WHILEGiLoopControl-1

BEGIN

Thisiswhereyouperformyourdetailedrow-by-row

processing.

Resetloopingvariables.

SELECTGiNextRowId

getthenextiRowId

SELECTGiNextRowIdMIN(iRowId)

FROMCustomerTable

WHEREiRov/idUiCurrentRowid

didwegetavalidnextrowid?

IFTSNUI/@iNextRowId,00

BEGIN

BREAK

END

getthenextrow.

SELECT0iCurrentRowIdiRowId.

@vchCustomerNmbrvchCustomerNmbr

@vchCustomerNamevchCustomerName

FROMCustomerTable

WHEREiRowIdQiNextRowId

END

RETURN

我們現(xiàn)在來看一下上面的游標(biāo)，一般來說，為了性能的因素.我們的表上都有一個類似于RowID

的列，此列可以被用來做循環(huán)，并得到相關(guān)的數(shù)據(jù)。一般來說此列是IDENTITY歹U,主鍵并有

clustered索引。

但是很多情況下，我們的表中并不包括可以被用來循環(huán)的行ID,比如，可能在一個具有

uniqueindentifier屬性的列上創(chuàng)建了主鍵索引，這時(shí)候可以為這個表增加一個自增列并創(chuàng)建相

應(yīng)的索引，來實(shí)現(xiàn)此功能。

卜.而例子使用了MIN函數(shù)和〃>"來得到下一行我們需要的數(shù)據(jù)，當(dāng)然我們也可以使用MAX函

數(shù)然后使用〃達(dá)到相同的功能。

例子:

SELECT@iNextRowIdMAX(iRowId

FROMCustomerTable

WHEREiRowId<@iCurrentRowId

有一個比較重要的地方需要注意：要在取得下一次要循環(huán)的行ID之前設(shè)置此ID為NULL,是

因?yàn)楫?dāng)此循環(huán)實(shí)現(xiàn)所有的行循環(huán)后SELECT語句并不會為此ID設(shè)置為NULL,從而造成一個死

循環(huán)。當(dāng)循環(huán)變量為NULL后，意味著這個循環(huán)已經(jīng)完成了它需要實(shí)現(xiàn)的功能，此時(shí)我們可能

使用BREAK來退出WHILE循環(huán)，當(dāng)然也有其它的途徑來推出循環(huán)。

你可以在上面的存儲過程中的如下注釋處加上自己的基于行的操作。

-Thisiswhereyouperformyourdetailedrow-by-rovz

--processing.

可以看出，基于行的操作對性能非常有影響。舉例來說，如果你有一個非常復(fù)雜的任務(wù)需要進(jìn)行

嵌套的循環(huán)，此時(shí)你會使用嵌套的游標(biāo)，內(nèi)層的游標(biāo)根據(jù)外層游標(biāo)的條件進(jìn)行相應(yīng)的操作，這時(shí)

候如果我們使用游標(biāo)來進(jìn)行處理的話對服務(wù)器會有非常大的壓力。

例子:

ifexist(select-fromsysobjectswhe