數(shù)據(jù)庫中如何處理大型數(shù)據(jù)

處理百萬級(jí)以上的數(shù)據(jù)提高查詢速度的方法：

1.應(yīng)盡量避免在where子句中使用!=或<>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。

2.對(duì)查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在where及orderby涉及的列上建立索引。

3.應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行null值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

selectidfromtwherenumisnull

可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值，然后這樣查詢：

selectidfromtwherenum=0

4.應(yīng)盡量避免在where子句中使用or來連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

selectidfromtwherenum=10ornum=20

可以這樣查詢：

selectidfromtwherenum=10

unionall

selectidfromtwherenum=20

5.下面的查詢也將導(dǎo)致全表掃描：(不能前置百分號(hào))

selectidfromtwherenamelike‘%abc%’

若要提高效率，可以考慮全文檢索。

6.in和notin也要慎用，否則會(huì)導(dǎo)致全表掃描，如：

selectidfromtwherenumin(1,2,3)

對(duì)于連續(xù)的數(shù)值，能用between就不要用in了：

selectidfromtwherenumbetween1and3

8.應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

selectidfromtwherenum/2=100

應(yīng)改為:

selectidfromtwherenum=100*2

9.應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

selectidfromtwheresubstring(name,1,3)=’abc’–name以abc開頭的id

selectidfromtwheredatediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id

應(yīng)改為:

selectidfromtwherenamelike‘a(chǎn)bc%’

selectidfromtwherecreatedate>=’2005-11-30′andcreatedate<’2005-12-1′

10.不要在where子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引。

11.在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會(huì)被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致。

12.不要寫一些沒有意義的查詢，如需要生成一個(gè)空表結(jié)構(gòu)：

selectcol1,col2into#tfromtwhere1=0

這類代碼不會(huì)返回任何結(jié)果集，但是會(huì)消耗系統(tǒng)資源的，應(yīng)改成這樣：

createtable#t(…)

13.很多時(shí)候用exists代替in是一個(gè)好的選擇：

selectnumfromawherenumin(selectnumfromb)

用下面的語句替換：

selectnumfromawhereexists(select1frombwherenum=a.num)

14.并不是所有索引對(duì)查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來進(jìn)行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)，SQL查詢可能不會(huì)去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對(duì)查詢效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相應(yīng)的select的效率，但同時(shí)也降低了insert及update的效率，因?yàn)閕nsert或update時(shí)有可能會(huì)重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個(gè)表的索引數(shù)最好不要超過6個(gè)，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。

16.應(yīng)盡可能的避免更新clustered索引數(shù)據(jù)列，因?yàn)閏lustered索引數(shù)據(jù)列的順序就是表記錄的物理存儲(chǔ)順序，一旦該列值改變將導(dǎo)致整個(gè)表記錄的順序的調(diào)整，會(huì)耗費(fèi)相當(dāng)大的資源。若應(yīng)用系統(tǒng)需要頻繁更新clustered索引數(shù)據(jù)列，那么需要考慮是否應(yīng)將該索引建為clustered索引。

17.盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符，而對(duì)于數(shù)字型而言只需要比較一次就夠了。

18.盡可能的使用varchar/nvarchar代替char/nchar，因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間，其次對(duì)于查詢來說，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。

19.任何地方都不要使用select*fromt，用具體的字段列表代替“*”，不要返回用不到的任何字段。

20.盡量使用表變量來代替臨時(shí)表。如果表變量包含大量數(shù)據(jù)，請(qǐng)注意索引非常有限（只有主鍵索引）。

21.避免頻繁創(chuàng)建和刪除臨時(shí)表，以減少系統(tǒng)表資源的消耗。

22.臨時(shí)表并不是不可使用，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行?，例如，?dāng)需要重復(fù)引用大型表或常用表中的某個(gè)數(shù)據(jù)集時(shí)。但是，對(duì)于一次性事件，最好使用導(dǎo)出表。

23.在新建臨時(shí)表時(shí)，如果一次性插入數(shù)據(jù)量很大，那么可以使用selectinto代替createtable，避免造成大量log，以提高速度；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先createtable，然后insert。

24.如果使用到了臨時(shí)表，在存儲(chǔ)過程的最后務(wù)必將所有的臨時(shí)表顯式刪除，先truncatetable，然后droptable，這樣可以避免系統(tǒng)表的較長(zhǎng)時(shí)間鎖定。

25.盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該考慮改寫。

26.使用基于游標(biāo)的方法或臨時(shí)表方法之前，應(yīng)先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。

27.與臨時(shí)表一樣，游標(biāo)并不是不可使用。對(duì)小型數(shù)據(jù)集使用FAST_FORWARD游標(biāo)通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個(gè)表才能獲得所需的數(shù)據(jù)時(shí)。在結(jié)果集中包括“合計(jì)”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開發(fā)時(shí)間允許，基于游標(biāo)的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。

28.在所有的存儲(chǔ)過程和觸發(fā)器的開始處設(shè)置SETNOCOUNTON，在結(jié)束時(shí)設(shè)置SETNOCOUNTOFF。無需在執(zhí)行存儲(chǔ)過程和觸發(fā)器的每個(gè)語句后向客戶端發(fā)送DONE_IN_PROC消息。的

@@DBTS

值，然后增加

@@DBTS

的值。如果某

個(gè)表具有

timestamp

列，則時(shí)間戳?xí)挥浀叫屑?jí)。服務(wù)器就可以比較某行的當(dāng)前時(shí)間戳值和上次提取時(shí)所存儲(chǔ)的時(shí)間戳值，從而確定該行是否已更新。服務(wù)器不必比較所有列的值，只需比較

timestamp

列即可。如果應(yīng)用程序?qū)]有

timestamp

列的表要求基于行版本控制的樂觀并發(fā)，則游標(biāo)默認(rèn)為基于數(shù)值的樂觀并發(fā)控制。

SCROLL

LOCKS

這個(gè)選項(xiàng)實(shí)現(xiàn)悲觀并發(fā)控制。在悲觀并發(fā)控制中，在把數(shù)據(jù)庫的行讀入游標(biāo)結(jié)果集時(shí)，應(yīng)用程序?qū)⒃噲D鎖定數(shù)據(jù)庫行。在使用服務(wù)器游標(biāo)時(shí)，將行讀入游標(biāo)時(shí)會(huì)在其上放置一個(gè)更新鎖。如果在事務(wù)內(nèi)打開游標(biāo)，則該事務(wù)更新鎖將一直保持到事務(wù)被提交或回滾；當(dāng)提取下一行時(shí)，將除去游標(biāo)鎖。如果在事務(wù)外打開游標(biāo)，則提取下一行時(shí)，鎖就被丟棄。因此，每當(dāng)用戶需要完全的悲觀并發(fā)控制時(shí)，游標(biāo)都應(yīng)在事務(wù)內(nèi)打開。更新鎖將阻止任何其它任務(wù)獲取更新鎖或排它鎖，從而阻止其它任務(wù)更新該行。

然而，更新鎖并不阻止共享鎖，所以它不會(huì)阻止其它任務(wù)讀取行，除非第二個(gè)任務(wù)也在要求帶更新鎖的讀取。滾動(dòng)鎖根據(jù)在游標(biāo)定義的

SELECT

語句中指定的鎖提示，這些游標(biāo)并發(fā)選項(xiàng)可以生成滾動(dòng)鎖。滾動(dòng)鎖在提取時(shí)在每行上獲取，并保持到下次提取或者游標(biāo)關(guān)閉，以先發(fā)生者為準(zhǔn)。下次提取時(shí)，服務(wù)器為新提取中的行獲取滾動(dòng)鎖，并釋放上次提取中行的滾動(dòng)鎖。滾動(dòng)鎖獨(dú)立于事務(wù)鎖，并可以保持到一個(gè)提交或回滾操作之后。如果提交時(shí)關(guān)閉游標(biāo)的選項(xiàng)為關(guān)，則

COMMIT

語句并不關(guān)閉任何打開的游標(biāo)，而且滾動(dòng)鎖被保留到提交之后，以維護(hù)對(duì)所提取數(shù)據(jù)的隔離。所獲取滾動(dòng)鎖的類型取決于游標(biāo)并發(fā)選項(xiàng)和游標(biāo)

SELECT

語句中的鎖提示。

鎖提示

只讀

樂觀數(shù)值

樂觀行版本控制

鎖定無提示

未鎖定

未鎖定

未鎖定

更新

NOLOCK

未鎖定

未鎖定

未鎖定

未鎖定

HOLDLOCK

共享

共享

共享

更新

UPDLOCK

錯(cuò)誤

更新

更新

更新

TABLOCKX

錯(cuò)誤

未鎖定

未鎖定

更新其它

未鎖定

未鎖定

未鎖定

更新

*指定

NOLOCK

提示將使指定了該提示的表在游標(biāo)內(nèi)是只讀的。

16、用Profiler來跟蹤查詢，得到查詢所需的時(shí)間，找出SQL的問題所在;用索引優(yōu)化器優(yōu)化索引

17、注意UNion和UNion

all

的區(qū)別。UNION

all好

18、注意使用DISTINCT，在沒有必要時(shí)不要用，它同UNION一樣會(huì)使查詢變慢。重復(fù)的記錄在查詢里是沒有問題的

19、查詢時(shí)不要返回不需要的行、列

20、用sp_configure

'query

governor

cost

limit'或者SET

QUERY_GOVERNOR_COST_LIMIT來限制查詢消耗的資源。當(dāng)評(píng)估查詢消耗的資源超出限制時(shí)，服務(wù)器自動(dòng)取消查詢,在查詢之前就扼殺掉。SET

LOCKTIME設(shè)置鎖的時(shí)間

21、用select

top

100

/

10

Percent

來限制用戶返回的行數(shù)或者SET

ROWCOUNT來限制操作的行

22、在SQL2000以前，一般不要用如下的字句“IS

NULL",

"<>",

"!=",

"!>",

"!<",

"NOT",

"NOT

EXISTS",

"NOT

IN",

"NOT

LIKE",

and

"LIKE

'%500'"，因?yàn)樗麄儾蛔咚饕潜頀呙琛?/p>

也不要在WHere字句中的列名加函數(shù)，如Convert，substring等,如果必須用函數(shù)的時(shí)候，創(chuàng)建計(jì)算列再創(chuàng)建索引來替代.還可以變通寫法：WHERE

SUBSTRING(firstname,1,1)

=

'm'改為WHERE

firstname

like

'm%'（索引掃描），一定要將函數(shù)和列名分開。并且索引不能建得太多和太大。

NOT

IN會(huì)多次掃描表，使用EXISTS、NOT

EXISTS

，IN

,

LEFT

OUTER

JOIN

來替代，特別是左連接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用，現(xiàn)在2000的優(yōu)化器能夠處理了。相同的是IS

NULL，“NOT",

"NOT

EXISTS",

"NOT

IN"能優(yōu)化她，而”<>”等還是不能優(yōu)化，用不到索引。

23、使用Query

Analyzer，查看SQL語句的查詢計(jì)劃和評(píng)估分析是否是優(yōu)化的SQL。一般的20%的代碼占據(jù)了80%的資源，我們優(yōu)化的重點(diǎn)是這些慢的地方。

24、如果使用了IN或者OR等時(shí)發(fā)現(xiàn)查詢沒有走索引，使用顯示申明指定索引：

SELECT

*

FROM

PersonMember

(INDEX

=

IX_Title)

WHERE

processid

IN

(‘男’，‘女’)

25、將需要查詢的結(jié)果預(yù)先計(jì)算好放在表中，查詢的時(shí)候再SELECT。這在SQL7.0以前是最重要的手段。例如醫(yī)院的住院費(fèi)計(jì)算。

26、MIN()

和

MAX()能使用到合適的索引

27、數(shù)據(jù)庫有一個(gè)原則是代碼離數(shù)據(jù)越近越好，所以優(yōu)先選擇Default,依次為Rules,Triggers,

Constraint（約束如外健主健CheckUNIQUE……,數(shù)據(jù)類型的最大長(zhǎng)度等等都是約束）,Procedure.這樣不僅維護(hù)工作小，編寫程序質(zhì)量高，并且執(zhí)行的速度快。

28、如果要插入大的二進(jìn)制值到Image列，使用存儲(chǔ)過程，千萬不要用內(nèi)嵌INsert來插入(不知JAVA是否)。因?yàn)檫@樣應(yīng)用程序首先將二進(jìn)制值轉(zhuǎn)換成字符串（尺寸是它的兩倍），服務(wù)器受到字符后又將他轉(zhuǎn)換成二進(jìn)制值.存儲(chǔ)過程就沒有這些動(dòng)作:

方法：Create

procedure

p_insert

as

insert

into

table(Fimage)

values

(@image),

在前臺(tái)調(diào)用這個(gè)存儲(chǔ)過程傳入二進(jìn)制參數(shù)，這樣處理速度明顯改善。

29、Between在某些時(shí)候比IN速度更快,Between能夠更快地根據(jù)索引找到范圍。用查詢優(yōu)化器可見到差別。

select

*

from

chineseresume

where

title

in

('男','女')

Select

*

from

chineseresume

where

between

'男'

and

'女'

是一樣的。由于in會(huì)在比較多次，所以有時(shí)會(huì)慢些。

30、在必要是對(duì)全局或者局部臨時(shí)表創(chuàng)建索引，有時(shí)能夠提高速度，但不是一定會(huì)這樣，因?yàn)樗饕埠馁M(fèi)大量的資源。他的創(chuàng)建同是實(shí)際表一樣。

31、不要建沒有作用的事物例如產(chǎn)生報(bào)表時(shí)，浪費(fèi)資源。只有在必要使用事物時(shí)使用它。

32、用OR的字句可以分解成多個(gè)查詢，并且通過UNION

連接多個(gè)查詢。他們的速度只同是否使用索引有關(guān),如果查詢需要用到聯(lián)合索引，用UNION

all執(zhí)行的效率更高.多個(gè)OR的字句沒有用到索引，改寫成UNION的形式再試圖與索引匹配。一個(gè)關(guān)鍵的問題是否用到索引。

33、盡量少用視圖，它的效率低。對(duì)視圖操作比直接對(duì)表操作慢,可以用stored

procedure來代替她。特別的是不要用視圖嵌套,嵌套視圖增加了尋找原始資料的難度。我們看視圖的本質(zhì)：它是存放在服務(wù)器上的被優(yōu)化好了的已經(jīng)產(chǎn)生了查詢規(guī)劃的SQL。對(duì)單個(gè)表檢索數(shù)據(jù)時(shí)，不要使用指向多個(gè)表的視圖，直接從表檢索或者僅僅包含這個(gè)表的視圖上讀，否則增加了不必要的開銷,查詢受到干擾.為了加快視圖的查詢，MsSQL增加了視圖索引的功能。

34、沒有必要時(shí)不要用DISTINCT和ORDER

BY，這些動(dòng)作可以改在客戶端執(zhí)行。它們?cè)黾恿祟~外的開銷。這同UNION

和UNION

ALL一樣的道理。

SELECT

top

20

panyname,comid,position,ad.referenceid,worklocation,

convert(varchar(10),ad.postDate,120)

as

postDate1,workyear,degreedescription

FROM

jobcn_query.dbo.COMPANYAD_query

ad

where

referenceID

in('JCNAD00329667','JCNAD132168','JCNAD00337748','JCNAD00338345','JCNAD00333138','JCNAD00303570',

'JCNAD00303569','JCNAD00303568','JCNAD00306698','JCNAD00231935','JCNAD00231933','JCNAD00254567',

'JCNAD00254585','JCNAD00254608','JCNAD00254607','JCNAD00258524','JCNAD00332133','JCNAD00268618',

'JCNAD00279196','JCNAD00268613')

order

by

postdate

desc

35、在IN后面值的列表中，將出現(xiàn)最頻繁的值放在最前面，出現(xiàn)得最少的放在最后面，減少判斷的次數(shù)

36、當(dāng)用SELECT

INTO時(shí)，它會(huì)鎖住系統(tǒng)表(sysobjects，sysindexes等等)，阻塞其他的連接的存取。創(chuàng)建臨時(shí)表時(shí)用顯示申明語句，而不是select

INTO.

drop

table

t_lxh

begin

tran

select

*

into

t_lxh

from

chineseresume

where

name

=

'XYZ'

--commit

在另一個(gè)連接中SELECT

*

from

sysobjects可以看到

SELECT

INTO

會(huì)鎖住系統(tǒng)表，Create

table

也會(huì)鎖系統(tǒng)表(不管是臨時(shí)表還是系統(tǒng)表)。所以千萬不要在事物內(nèi)使用它?。?！這樣的話如果是經(jīng)常要用的臨時(shí)表請(qǐng)使用實(shí)表，或者臨時(shí)表變量。

37、一般在GROUP

BY

個(gè)HAVING字句之前就能剔除多余的行，所以盡量不要用它們來做剔除行的工作。他們的執(zhí)行順序應(yīng)該如下最優(yōu)：select

的Where字句選擇所有合適的行，Group

By用來分組個(gè)統(tǒng)計(jì)行，Having字句用來剔除多余的分組。這樣Group

By

個(gè)Having的開銷小，查詢快.對(duì)于大的數(shù)據(jù)行進(jìn)行分組和Having十分消耗資源。如果Group

BY的目的不包括計(jì)算，只是分組，那么用Distinct更快

38、一次更新多條記錄比分多次更新每次一條快,就是說批處理好

39、少用臨時(shí)表，盡量用結(jié)果集和Table類性的變量來代替它,Table

類型的變量比臨時(shí)表好

40、在SQL2000下，計(jì)算字段是可以索引的，需要滿足的條件如下：

a、計(jì)算字段的表達(dá)是確定的

b、不能用在TEXT,Ntext，Image數(shù)據(jù)類型

c、必須配制如下選項(xiàng)

ANSI_NULLS

=

ON,

ANSI_PADDINGS

=

ON,

…….

41、盡量將數(shù)據(jù)的處理工作放在服務(wù)器上，減少網(wǎng)絡(luò)的開銷，如使用存儲(chǔ)過程。存儲(chǔ)過程是編譯好、優(yōu)化過、并且被組織到一個(gè)執(zhí)行規(guī)劃里、且存儲(chǔ)在數(shù)據(jù)庫中的SQL語句，是控制流語言的集合，速度當(dāng)然快。反復(fù)執(zhí)行的動(dòng)態(tài)SQL,可以使用臨時(shí)存儲(chǔ)過程，該過程（臨時(shí)表）被放在Tempdb中。以前由于SQL

SERVER對(duì)復(fù)雜的數(shù)學(xué)計(jì)算不支持，所以不得不將這個(gè)工作放在其他的層上而增加網(wǎng)絡(luò)的開銷。SQL2000支持UDFs,現(xiàn)在支持復(fù)雜的數(shù)學(xué)計(jì)算，函數(shù)的返回值不要太大，這樣的開銷很大。用戶自定義函數(shù)象光標(biāo)一樣執(zhí)行的消耗大量的資源，如果返回大的結(jié)果采用存儲(chǔ)過程

42、不要在一句話里再三的使用相同的函數(shù)，浪費(fèi)資源,將結(jié)果放在變量里再調(diào)用更快

43、SELECT

COUNT(*)的效率教低，盡量變通他的寫法，而EXISTS快.同時(shí)請(qǐng)注意區(qū)別：

select

count(Field

of

null)

from

Table

和

select

count(Field

of

NOT

null)

from

Table

的返回值是不同的。

44、當(dāng)服務(wù)器的內(nèi)存夠多時(shí)，配制線程數(shù)量

=

最大連接數(shù)+5，這樣能發(fā)揮最大的效率；否則使用

配制線程數(shù)量<最大連接數(shù)啟用SQL

SERVER的線程池來解決,如果還是數(shù)量

=

最大連接數(shù)+5，嚴(yán)重的損害服務(wù)器的性能。

45、按照一定的次序來訪問你的表。如果你先鎖住表A，再鎖住表B，那么在所有的存儲(chǔ)過程中都要按照這個(gè)順序來鎖定它們。如果你（不經(jīng)意的）某個(gè)存儲(chǔ)過程中先鎖定表B，再鎖定表A，這可能就會(huì)導(dǎo)致一個(gè)死鎖。如果鎖定順序沒有被預(yù)先詳細(xì)的設(shè)計(jì)好，死鎖很難被發(fā)現(xiàn)

46、通過SQL

Server

Performance

Monitor監(jiān)視相應(yīng)硬件的負(fù)載

Memory:

Page

Faults

/

sec計(jì)數(shù)器如果該值偶爾走高，表明當(dāng)時(shí)有線程競(jìng)爭(zhēng)內(nèi)存。如果持續(xù)很高，則內(nèi)存可能是瓶頸。

Process:

1、%

DPC

Time

指在范例間隔