怎樣提高數(shù)據(jù)庫查詢效率

怎樣提高數(shù)據(jù)庫查詢效率

2.應(yīng)盡量避免在where子句中對字段進行null值推斷，否則將導(dǎo)致引擎放棄使用索引而進

行全表掃描，如：

selectidfromtwherenumisnull

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

selectidfromtwherenum=0

3.應(yīng)盡量避免在where子句中使用!=或者v＞操作符，否則將引擎放棄使用索引而進行全表

掃描。

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

表掃描，如：

selectidfromtwherenum=10ornum=20

能夠這樣查詢：

selectidfromtwherenum=10

unionall

selectidfromtwherenum=20

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

selectidfromtwherenumin(l,2,3)

關(guān)于連續(xù)的數(shù)值，能用between就不要用in了：

selectidfromtwherenumbetween1and3

6.下面的查詢也將導(dǎo)致全表掃描：

selectidfromtwherenamelike'%abc%'

若要提高效率，能夠考慮全文檢索。

7.假如在where子句中使用參數(shù)，也會導(dǎo)致全表掃描。由于SQL只有在運行時才會解析局

部變量，但優(yōu)化程序不能將訪問計劃的選擇推遲到運行時；它務(wù)必在編譯時進行選擇。然而,

假如在編譯時建立訪問計?劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下

面語句將進行全表掃描：

selectidfromtwherenum=@num

能夠改為強制查詢使用索引：

selectidfromtwith(index(索弓|名))wherenum=@num

8.應(yīng)盡量避免在where子句中對字段進行表達式操作，這將導(dǎo)致引擎放棄使用索引而進行

全表掃描。如：

selectidfromtwherenum/2=100

應(yīng)改為：

selectidfromtwherenum=100*2

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

掃描。如：

selectidfromtwheresubstring(name,1,3)='abc,—name以abc開頭的id

selectidfromtwheredatediff(day,createdate,,2005-ll-30,)=0—'2005-11-30'生成的id

應(yīng)改為:

selectidfromtwherenamelike'abc%'

selectidfromtwherecreatedate>=,2005-11-30'andcreatedate<,2005-12-1,

10.不要在where子句中的“=”左邊進行函數(shù)、算術(shù)運算或者其他表達式運算，否則系統(tǒng)

將可能無法正確使用索引。

11.在使用索引字段作為條件時，假如該索引是復(fù)合索引，那么務(wù)必使用到該索引中的第一

個字段作為條件時才能保證系統(tǒng)使用該索引，否則該索引將不可能被使用，同時應(yīng)盡可能的

讓字段順序與索引順序相一致。

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

selectcoll,col2into#tfromtwhere1=0

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

createtable

13.很多時候用exists代替in是一個好的選擇:

selectnumfromawherenumin(selectnumfromb)

用下面的語句替換:

selectnumfromawhereexists(select1frombwherenum=a.num)

14.并不是所有索引對查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來進行查詢優(yōu)化的，當(dāng)索引列有大

量數(shù)據(jù)重復(fù)時，SQL查詢可能不可能去利用索引，如一表中有字段sex,male、female幾乎

各一半，那么即使在sex上建了索引也對查詢效率起不了作用。

15.索引并不是越多越好，索引固然能夠提高相應(yīng)的select的效率，但同時也降低了insert

及update的效率，由于insert或者update時有可能會重建索引，因此如何建索引需要慎

重考慮，視具體情況而定。一個表的索引數(shù)最好不要超過6個，若太多則應(yīng)考慮一些不常使

用到的列上建的索引是否有必要。

16.應(yīng)盡可能的避免更新clustered索引數(shù)據(jù)列，由于clustered索引數(shù)據(jù)列的順序就是表記

錄的物理存儲順序，一旦該列值改變將導(dǎo)致整個表記錄的順序的調(diào)整,會耗費相當(dāng)大的資源。

若應(yīng)用系統(tǒng)需要頻繁更新clustered索引數(shù)據(jù)列，那么需要考慮是否應(yīng)將該索引建為

clustered索弓I。

17.盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計為字符型，這會降低查詢與

連接的性能，并會增加存儲開銷。這是由于引擎在處理查詢與連接時會逐個比較字符串中每

一個字符，而關(guān)于數(shù)字型而言只需要比較一次就夠了。

18.盡可能的使用varchar/nvarchar代替char/nchar,由于首先變長字段存儲空間小，能夠

節(jié)約存儲空間，其次關(guān)于查詢來說，在一個相對較小的字段內(nèi)搜索效率顯然要高些。

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

何字段。

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

主鍵索引）。

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

22.臨時表并不是不可使用，適當(dāng)?shù)厥褂盟鼈兡軌蚴鼓承├谈行?，比如，?dāng)需要重復(fù)引

用大型表或者常用表中的某個數(shù)據(jù)集時。但是，關(guān)于一次性事件，最好使用導(dǎo)出表。

23.在新建臨時表時，假如一次性插入數(shù)據(jù)量很大，那么能夠使用selectinto代替create

table,避免造成大量log以提高速度；假如數(shù)據(jù)量不大，為了緩與系統(tǒng)表的資源，應(yīng)先

createtable,然后insert。

24.假如使用到了臨時表，在存儲過程的最后務(wù)必將所有的臨時表顯式刪除，先truncate

table,然后droptable,這樣能夠避免系統(tǒng)表的較長時間鎖定。

25.盡量避免使用游標(biāo)，由于游標(biāo)的效率較差，假如游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)

該考慮改寫。

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

于集的方法通常更有效。

27.與臨時表一樣，游標(biāo)并不是不可使用。對小型數(shù)據(jù)集使用FAST_FORWARD游標(biāo)通常要

優(yōu)于其他逐行處理方法，特別是在務(wù)必引用幾個表才能獲得所需的數(shù)據(jù)時。在結(jié)果集中包含

“合計”的例程通常要比使用游標(biāo)執(zhí)行的速度快。假如開發(fā)時間同意，基于游標(biāo)的方法與基

于集的方法都能夠嘗試一下，看哪一種方法的效果更好。

28.在所有的存儲過程與觸發(fā)器的開始處設(shè)置SETNOCOUNTON,在結(jié)束時設(shè)置SET

NOCOUNTOFF。無需在執(zhí)行存儲過程與觸發(fā)器的每個語句后向客戶端發(fā)送

DONE_IN_PROC消息、。

29.盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力。

30.盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大，應(yīng)該考慮相應(yīng)需求是否合理

1：影響最大的是在數(shù)據(jù)庫端家索引。

2：假如數(shù)據(jù)庫字段很多，建議使用Select字段列表的方式而不使用Select*

3:請使用TQuery或者TADOQuery,同時WHERE語句一定要建立索引，盡量少使用TTable

或者TADOTable.

4:多使用存儲過程。

5：關(guān)于ORACLE與SYBASE數(shù)據(jù)庫要使用BDE,而關(guān)于MSSQLSERVER使

用ADO方式連接。

同時，關(guān)于ORACLE與SQLSERVER,OLEDB的效率要比ADO效率高。BDE應(yīng)該

是效率比較低下的一種方式，這個在開發(fā)過程中我們有過比較。

另外：盡量保持建立的數(shù)據(jù)庫連接，不必每次都重新連接數(shù)據(jù)庫。這在SQLSERVER

與ACCESS下表現(xiàn)不明顯，但是在ORACLE中就非常明顯，由于ORACLE的建鏈時間非

常長，大約有2秒鐘，而SQL與ACCESS大約只需0.01秒。

在查詢語句中，要考慮where語句中的字段順序。

盡量不用ORDERBY語句（數(shù)據(jù)量比較大的時候）

關(guān)于一個有超過百萬條記錄的數(shù)據(jù)表，怎么才能提高效率。

VB+ADO+SQLSERVER2000.

我能想到的有建立索引、使用存儲過程。還有什么其他辦法嗎？

拆表，升級硬件。檢索時盡量帶參數(shù)，要用的數(shù)據(jù)才檢出來。

對關(guān)聯(lián)字段建索引：

優(yōu)化SQL語句，減少或者避免多表連結(jié)。

添加內(nèi)存、CPU

建立視圖

充分利用高速緩存

充分利用索引與SQL語句語法

無關(guān)緊要的查詢能夠進行臟讀

建索引的選擇務(wù)必結(jié)合SQL查詢、修改、刪除語句的需要，通常的說法是在WHERE里經(jīng)

常出現(xiàn)的字段建索引。假如在WHERE經(jīng)常是幾個字段一起出現(xiàn)而且是用AND連接的，那

就應(yīng)該建這幾個字段一起的聯(lián)合索引，而且次序也需要考慮，通常是最常出現(xiàn)的放前面，重

復(fù)率低的放前面。

如有一張員工表，與部門表.1、直接用select*from員工表，部門表where員

工表.部門ID=部門.ID。2、select*from員工表。然后當(dāng)程序選擇到某個員工

后，再根據(jù)部門的ID來選擇部門信息。

這兩種方法，哪個效率高

假如是需要同時顯示所有員工的部門信息，則第一種方法好；

假如是用戶選中一個員工再帶出來該員工的部門信息，則第二種方法好；

在真正需要的時候才從數(shù)據(jù)庫提取數(shù)據(jù)

1。比較具有相同類型的列。

2。比較中應(yīng)盡量使索引列獨立。

3。在like模式的起始處不要使用通配符

比如：wherenamelike"%string%"

優(yōu)化為：wherenamelike"string%"

4o幫助優(yōu)化程序更好的評估索引的有效性

可用isamchk或者myisamchk的--analyze選項給優(yōu)化程序提供更好的信

息，以便分析鍵值的分布。

5。利用EXPLAIN檢驗優(yōu)化程序操作。

檢查用于查詢中的索引是否能很快的排除行

一個表中大概有20萬以上的條目（做音樂查詢）/

每次查詢都需要在這個表的兩個字段中查尋/（歌手與歌名）

如何提高效率？需要達到至少每秒30次的查詢/而cpu盡量占用低一點

請問如何處理數(shù)據(jù)庫與程序才能達到好的效果？

perl寫的程序。謝謝了。

表如下

id歌手歌曲url專輯格式性別xxxxxxxxx

xxx.

創(chuàng)建索引

建索引的目的是為了更好的查詢，但是不要盲目建索引，有的時候候適得其反。

遵循這樣的原則：

1.搜索的索引列，不一定是索要選擇的列

比如：selectcol_afromtbllleftjointbl2on

tbl1,col_b=tbl2.col_c

wherecol_d=expr

在這查詢重視合作索引列的是tbll.col_bandtbl2.col_candcol_d

2.使用唯一索引：關(guān)于唯一值的列，索引的效果最好，而具有多個重復(fù)值的列

其索引效果最差。

3.使用短索引：應(yīng)該指定一個前綴的長度，只要有可能就應(yīng)該這樣做

比如：varchar（lOO）列，對前10個或者者20個字符內(nèi)，多數(shù)值是唯一的那就不要對

整個列索引。

4.利用最左索引：在創(chuàng)建n列的索引時，實際是創(chuàng)建了mysql可利用的n個索引，多列索

引能夠起幾個索引的作用，由于能夠利用索引中最左邊的列集來匹配。

ps:mysql不能使用不涉及左前綴的搜索。

5.不要過渡索引：

每個額外的索引都是要占額外的磁盤空間，并降低寫操作的性能。

6.考慮在列上進行比較的類型：

索引可用于與between運算。

查詢速度慢的原因很多，常見如下幾種：

1、沒有索引或者者沒有用到索引（這是查詢慢最常見的問題，是程序設(shè)計的缺陷）

2、I/O吞吐量小，形成了瓶頸效應(yīng)。

3、沒有創(chuàng)建計算列導(dǎo)致查詢不優(yōu)化。

4、內(nèi)存不足

5、網(wǎng)絡(luò)速度慢

6、查詢出的數(shù)據(jù)量過大（能夠使用多次查詢，其他的方法降低數(shù)據(jù)量）

7、鎖或者者死鎖（這也是查詢慢最常見的問題，是程序設(shè)計的缺陷）

8、sp」ock,sp_who,活動的用戶查看源因是讀寫競爭資源。

9、返回了不必要的行與列

10、查詢語句不好，沒有優(yōu)化

能夠通過如下方法來優(yōu)化查詢：

1、把數(shù)據(jù)、日志、索引放到不一致的UO設(shè)備上，增加讀取速度，往常能夠?qū)empdb應(yīng)

放在RAID0上，SQL2000不在支持。數(shù)據(jù)量（尺寸）越大，提高I/O越重要.

2、縱向、橫向分割表，減少表的尺寸（sp_spaceuse）

3、升級硬件

4、根據(jù)查詢條件，建立索引，優(yōu)化索引、優(yōu)化訪問方式，限制結(jié)果集的數(shù)據(jù)量。注意填充因子

要適當(dāng)（最好是使用默認(rèn)值0）。索引應(yīng)該盡量小，使用字節(jié)數(shù)小的列建索引好（參照索引

的創(chuàng)建），不要對有限的幾個值的字段建單一索引如性別字段

5、提高網(wǎng)速；

6、擴大服務(wù)器的內(nèi)存,Windows2000與SQLserver2000能支持4-8G的內(nèi)存。配置虛擬內(nèi)存：

虛擬內(nèi)存大小應(yīng)基于計算機上并發(fā)運行的服務(wù)進行配置。運行MicrosoftSQLServer?2000

時，可考慮將虛擬內(nèi)存大小設(shè)置為計算機中安裝的物理內(nèi)存的1.5倍。假如另外安裝了全

文檢索功能，并打算運行Microsoft搜索服務(wù)以便執(zhí)行全文索引與查詢，可考慮：將虛擬內(nèi)

存大小配置為至少是計算機中安裝的物理內(nèi)存的3倍。將SQLServermaxservermemory

服務(wù)器配置選項配置為物理內(nèi)存的1.5倍（虛擬內(nèi)存大小設(shè)置的一半）。

7、增加服務(wù)器CPU個數(shù);但是務(wù)必明白并行處理串行處理更需要資源比如內(nèi)存。使用并行

還是串行程是MsSQL自動評估選擇的。單個任務(wù)分解成多個任務(wù)，就能夠在處理器上運行。

比如耽擱查詢的排序、連接、掃描與GROUPBY字句同時執(zhí)行，SQLSERVER根據(jù)系統(tǒng)的

負(fù)載情況決定最優(yōu)的并行等級，復(fù)雜的需要消耗大量的CPU的查詢最適合并行處理。但是

更新操作UPDATE,INSERT,DELETE還不能并行處理。

8、假如是使用like進行查詢的話，簡單的使用index是不行的，但是全文索引，耗空間。like

'a%，使用索引Hke，％a，不使用索引用like'%a%'查詢時，查詢耗時與字段值總長度成正比,

因此不能用CHAR類型，而是VARCHAR-關(guān)于字段的值很長的建全文索引。

9、DBServer與APPLicationServer分離：OLTP與OLAP分離

10、分布式分區(qū)視圖可用于實現(xiàn)數(shù)據(jù)庫服務(wù)器聯(lián)合體。聯(lián)合體是一組分開管理的服務(wù)器，但

它們相互協(xié)作分擔(dān)系統(tǒng)的處理負(fù)荷。這種通過分區(qū)數(shù)據(jù)形成數(shù)據(jù)庫服務(wù)器聯(lián)合體的機制能夠

擴大一組服務(wù)器，以支持大型的多層Web站點的處理需要。有關(guān)更多信息，參見設(shè)計聯(lián)合

數(shù)據(jù)庫服務(wù)器。(參照SQL幫助文件，分區(qū)視圖，)

a、在實現(xiàn)分區(qū)視圖之前，務(wù)必先水平分區(qū)表

b、在創(chuàng)建成員表后，在每個成員服務(wù)器上定義一個分布式分區(qū)視圖，同時每個視圖具有相

同的名稱。這樣，引用分布式分區(qū)視圖名的查詢能夠在任何一個成員服務(wù)器上運行。系統(tǒng)操

作如同每個成員服務(wù)器上都有一個原始表的復(fù)本一樣，但事實上每個服務(wù)器上只有一個成員

表與一個分布式分區(qū)視圖。數(shù)據(jù)的位置對應(yīng)用程序是透明的。

11、重建索弓IDBCCREINDEX,DBCCINDEXDEFRAG,收縮數(shù)據(jù)與日志DBCC

SHRINKDBQBCCSHRINKFILE.設(shè)置自動收縮日志.關(guān)于大的數(shù)據(jù)庫不要設(shè)置數(shù)據(jù)庫自動

增長，它會降低服務(wù)器的性能。在T-sql的寫法上有很大的講究，下面列出常見的要點：首

先，DBMS處理查詢計劃的過程是這樣的：

I、查詢語句的詞法、語法檢查

2、將語句提交給DBMS的查詢優(yōu)化器

3、優(yōu)化器做代數(shù)優(yōu)化與存取路徑的優(yōu)化

4、由預(yù)編譯模塊生成查詢規(guī)劃

5、然后在合適的時間提交給系統(tǒng)處理執(zhí)行

6、最后將執(zhí)行結(jié)果返回給用戶其次，看一下SQLSERVER的數(shù)據(jù)存放的結(jié)構(gòu)：一個頁面

的大小為8K(8060)字節(jié)，8個頁面為一個盤區(qū)，按照B樹存放。

12、Commit與rollback的區(qū)別Rollback:回滾所有的事物。Commit:提交當(dāng)前的事物.沒有

必要在動態(tài)SQL里寫事物，假如要寫請寫在外面如：beginIranexec(@s)committrans或者

者將動態(tài)SQL寫成函數(shù)或者者存儲過程。

13、在查詢Select語句中用Where字句限制返回的行數(shù)，避免表掃描，假如返回不必要的數(shù)據(jù),

浪費了服務(wù)器的I/O資源，加重了網(wǎng)絡(luò)的負(fù)擔(dān)降低性能。假如表很大，在表掃描的期間將表

鎖住，禁止其他的聯(lián)接訪問表，后果嚴(yán)重。

14、SQL的注釋申明對執(zhí)行沒有任何影響

15、盡可能不使用光標(biāo)，它占用大量的資源。假如需要row-by-row地執(zhí)行，盡量使用非光

標(biāo)技術(shù)，如：在客戶端循環(huán)，用臨時表，Table變量，用子查詢，用Case語句等等。游標(biāo)能

夠按照它所支持的提取選項進行分類：只進務(wù)必按照從第一行到最后一行的順序提取行。

FETCHNEXT是唯一同意的提取操作，也是默認(rèn)方式?？蓾L動性能夠在游標(biāo)中任何地方隨

機提取任意行。游標(biāo)的技術(shù)在SQL2000下變得功能很強大，他的目的是支持循環(huán)。有四個

并發(fā)選項READ_ONLY：不同意通過游標(biāo)定位更新(Update),且在構(gòu)成結(jié)果集的行中沒有鎖。

OPTIMISTICWITHvalueS:樂觀并發(fā)操縱是事務(wù)操縱理論的一個標(biāo)準(zhǔn)部分。樂觀并發(fā)操縱用

于這樣的情形，即在打開游標(biāo)及更新行的間隔中，只有很小的機會讓第二個用戶更新某一行。

當(dāng)某個游標(biāo)以此選項打開時，沒有鎖操縱其中的行，這將有助于最大化其處理能力。假如用

戶試圖修改某一行，則此行的當(dāng)前值會與最后一次提取此行時獲取的值進行比較。假如任何

值發(fā)生改變，則服務(wù)器就會明白其他人已更新了此行，并會返回一個錯誤。假如值是一樣的，

服務(wù)器就執(zhí)行修改。選擇這個并發(fā)選項OPTIMISTICWITHROWVERSIONING:此樂觀

并發(fā)操縱選項基于行版本操縱。使用行版本操縱，其中的表務(wù)必具有某種版本標(biāo)識符，服務(wù)

器可用它來確定該行在讀入游標(biāo)后是否有所更換。在SQLServer中，這個性能由timestamp

數(shù)據(jù)類型提供，它是一個二進制數(shù)字，表示數(shù)據(jù)庫中更換的相對順序。每個數(shù)據(jù)庫都有一個

全局當(dāng)前時間戳值：@@DBTSo每次以任何方式更換帶有timestamp列的行時、SQLServer

先在時間戳列中存儲當(dāng)前的@@DBTS值，然后增加@@DBTS的值。假如某個表具有

timestamp歹則時間戳?xí)挥浀叫屑?。服?wù)器就能夠比較某行的當(dāng)前時間戳值與上次提取

時所存儲的時間戳值，從而確定該行是否已更新。服務(wù)器不必比較所有列的值，只需比較

timestamp列即可。假如應(yīng)用程序?qū)]有timestamp列的表要求基于行版本操縱的樂觀并

發(fā)，則游標(biāo)默認(rèn)為基于數(shù)值的樂觀并發(fā)操縱。SCROLLLOCKS這個選項實現(xiàn)悲觀并發(fā)操

縱。在悲觀并發(fā)操縱中，在把數(shù)據(jù)庫的行讀入游標(biāo)結(jié)果集時，應(yīng)用程序?qū)⒃噲D鎖定數(shù)據(jù)庫行。

在使用服務(wù)器游標(biāo)時，將行讀入游標(biāo)時會在其上放置一個更新鎖。假如在事務(wù)內(nèi)打開游標(biāo)，

則該事務(wù)更新鎖將一直保持到事務(wù)被提交或者回滾；當(dāng)提取下一行時，將除去游標(biāo)鎖。假如

在事務(wù)外打開游標(biāo)，則提取下一行時，鎖就被丟棄。因此，每當(dāng)用戶需要完全的悲觀并發(fā)操

縱時，游標(biāo)都應(yīng)在事務(wù)內(nèi)打開。更新鎖將阻止任何其它任務(wù)獲取更新鎖或者排它鎖，從而阻

止其它任務(wù)更新該行。然而，更新鎖并不阻止共享鎖，因此它不可能阻止其它任務(wù)讀取行，

除非第二個任務(wù)也在要求帶更新鎖的讀取。滾動鎖根據(jù)在游標(biāo)定義的SELECT語句中指定

的鎖提示，這些游標(biāo)并發(fā)選項能夠生成滾動鎖。滾動鎖在提取時在每行上獲取，并保持到下

次提取或者者游標(biāo)關(guān)閉，以先發(fā)生者為準(zhǔn)。下次提取時，服務(wù)器為新提取中的行獲取滾動鎖,

并釋放上次提取中行的滾動鎖。滾動鎖獨立于事務(wù)鎖，并能夠保持到一個提交或者回滾操作

之后。假如提交時關(guān)閉游標(biāo)的選項為關(guān)，則COMMIT語句并不關(guān)閉任何打開的游標(biāo)，而

且滾動鎖被保留到提交之后，以保護對所提取數(shù)據(jù)的隔離。所獲取滾動鎖的類型取決于游標(biāo)

并發(fā)選項與游標(biāo)SELECT語句中的鎖提示。鎖提示只讀樂觀數(shù)值樂觀行版本操縱鎖定

無提示未鎖定未鎖定未鎖定更新NOLOCK未鎖定未鎖定未鎖定未鎖定

HOLDLOCK共享共享共享更新UPDLOCK錯誤更新更新更新TABLOCKX錯誤

未鎖定未鎖定更新其它未鎖定未鎖定未鎖定更新*指定NOLOCK提示將使指定了

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

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

化索引

17、注意UNion與UNionall的區(qū)別。UNIONall好

18、注意使用DISTINCT,在沒有必要時不要用，它同UNION一樣會使查詢變慢。重復(fù)的

記錄在查詢里是沒有問題的

19、查詢時不要返回不需要的行、列

20、用sp_configure'querygovernorcostlimit'或者者SETQUERY_GOVERNOR_COST_LIMIT

來限制查詢消耗的資源。當(dāng)評估查詢消耗的資源超出限制時，服務(wù)器自動取消查詢,在查詢

之前就扼殺掉。SETLOCKTIME設(shè)置鎖的時間

21、用selecttop100/10Percent來限制用戶返回的行數(shù)或者者SETROWCOUNT來限制操

作的行

22、在SQL2000往常，通常不要用如下的字句:"ISNULL","NOT",

"NOTEXISTS","NOTIN","NOTLIKE",and"LIKE'%500'",由于他們不走索引全是表掃描。

也不要在WHere字句中的列名加函數(shù)，如Convert,substring等，假如務(wù)必用函數(shù)的時候，創(chuàng)

建計算列再創(chuàng)建索引來替代.還能夠變通寫法：WHERESUBSTRING(firstname,1,1)=改為

WHEREfirstnamelikehn%，(索引掃描)，一定要將函數(shù)與列名分開。同時索引不能建得太多

與太大。NOTIN會多次掃描表，使用EXISTS、NOTEXISTS,IN,LEFTOUTERJOIN來

替代，特別是左連接，而Exists比IN更快，最慢的是NOT操作.假如列的值含有空，往常它

的索引不起作用，現(xiàn)在2000的優(yōu)化器能夠處理了。相同的是ISNULL,"NOT","NOTEXISTS",

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

23、使用QueryAnalyzer,查看SQL語句的查詢計劃與評估分析是否是優(yōu)化的SQL。通常

的20%的代碼占據(jù)了80%的資源，我們優(yōu)化的重點是這些慢的地方。

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

SELECT*FROMPersonMember(INDEX=IX_Title)WHEREprocessidIN('男'，'女')

25、將需要查詢的結(jié)果預(yù)先計算好放在表中，查詢的時候再SELECT。這在SQL7.0往常是

最重要的手段。比如醫(yī)院的住院費計算。

26、MIN()與MAX()能使用到合適的索引。

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

Constraint(約束如外健主健CheckUNIQUE……，數(shù)據(jù)類型的最大長度等等都是約

束)，Procedure.這樣不僅保護工作小，編寫程序質(zhì)量高，同時執(zhí)行的速度快。

28、假如要插入大的二進制值到Image歹U，使用存儲過程，千萬不要用內(nèi)嵌INsert來插入(不

知JAVA是否）。由于這樣應(yīng)用程序首先將二進制值轉(zhuǎn)換成字符串（尺寸是它的兩倍），服務(wù)

器受到字符后又將他轉(zhuǎn)換成二進制值.存儲過程就沒有這些動作：方法：

Createprocedurep_insertasinsertintotable(Fimage)values(@image)

在前臺調(diào)用這個存儲過程傳入二進制參數(shù)，這樣處理速度明顯改善。

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

化器可見到差別。

select*fromchineseresumewheretitlein（'男女'）

Select*fromchineseresumewheretitlebetween'男’and'女’

是一樣的。由于in會在比較多次，因此有的時候會慢些。

30、在必要是對全局或者者局部臨時表創(chuàng)建索引，有的時候能夠提高速度，但不是一定會這

樣，由于索引也耗費大量的資源。他的創(chuàng)建同是實際表一樣。

31、不要建沒有作用的事物比如產(chǎn)生報表時，浪費資源。只有在必要使用事物時使用它。

32、用OR的字句能夠分解成多個查詢，同時通過UNION連接多個查詢。他們的速度只同

是否使用索引有關(guān)，假如查詢需要用到聯(lián)合索引，用UNIONall執(zhí)行的效率更高.多個OR的

字句沒有用到索引，改寫成UNION的形式再試圖與索引匹配。一個關(guān)鍵的問題是否用到索

引。

33、盡量少用視圖，它的效率低。對視圖操作比直接對表操作慢,能夠用storedprocedure來

代替她。特別的是不要用視圖嵌套，嵌套視圖增加了尋找原始資料的難度。我們看視圖的本

質(zhì)：它是存放在服務(wù)器上的被優(yōu)化好了的已經(jīng)產(chǎn)生了查詢規(guī)劃的SQL。對單個表檢索數(shù)據(jù)

時，不要使用指向多個表的視圖，直接從表檢索或者者僅僅包含這個表的視圖上讀，否則增

加了不必要的開銷,查詢受到干擾.為了加快視圖的查詢，MsSQL增加了視圖索引的功能。

34、沒有必要時不要用DISTINCT與ORDERBY,這些動作能夠改在客戶端執(zhí)行。它們增

加了額外的開銷。這同UNION與UNIONALL一樣的道理。

convert(varchar(10),ad.postDate,120)aspostDate1,workyear,degreedescriptionFROM

'JCNAD00333138','JCNAD00303570','JCNAD00303569',

,JCNAD00303568','JCNAD00306698','JCNAD00231935','JCNAD00231933,,

'JCNAD00254567','JCNAD00254585'；JCNAD00254608,,

'JCNAD00254607','JCNAD00258524'；JCNAD00332133','JCNAD00268618,,

'JCNAD00279196','JCNAD00268613')orderbypostdatedesc

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

少推斷的次數(shù)。

36、當(dāng)用SELECTINTO時，它會鎖住系統(tǒng)表（sysobjects,sysindexes等等），堵塞其他的連

接的存取。創(chuàng)建臨時表時用顯示申明語句，而不是

selectINTO,droptablet_lxhbegintranselect*intot_lxhfromchineseresume

wherename='XYZ'—commit

在另一個連接中SELECT*fromsysobjects能夠看到SELECTINTO會鎖住系統(tǒng)表，Create

table也會鎖系統(tǒng)表（不管是臨時表還是系統(tǒng)表）。因此千萬不要在事物內(nèi)使用它！”這樣的

話假如是經(jīng)常要用的臨時表請使用實表，或者者臨時表變量。

37、通常在GROUPBY個HAVING字句之前就能剔除多余的行，因此盡量不要用它們來做

剔除行的工作。他們的執(zhí)行順序應(yīng)該如下最優(yōu)：select的Where字句選擇所有合適的行，

GroupBy用來分組個統(tǒng)計行，Having字句用來剔除多余的分組。這樣GroupBy個Having

的開銷小，查詢快.關(guān)于大的數(shù)據(jù)行進行分組與Having十分消耗資源。假如GroupBY的目

的不包含計算，只是分組，那么用Distinct更快

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

39、少用臨時表，盡量用結(jié)果集與Table類性的變量來代替它,Table類型的變量比臨時表好

40、在SQL2000下，計算字段是能夠索引的，需要滿足的條件如下：

a、計算字段的表達是確定的

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

c、務(wù)必配制如下選項ANS1_NULLS=ON,ANSI_PADDINGS=ON,.

41、盡量將數(shù)據(jù)的處理工作放在服務(wù)器上，減少網(wǎng)絡(luò)的開銷，如使用存儲過程。存儲過程是

編譯好、優(yōu)化過、同時被組織到一個執(zhí)行規(guī)劃里、且存儲在數(shù)據(jù)庫中的SQL語句，是操縱

流語言的集合，速度當(dāng)然快。反復(fù)執(zhí)行的動態(tài)SQL,能夠使用臨時存儲過程，該過程（臨時

表）被放在Tempdb中。往常由于SQLSERVER對復(fù)雜的數(shù)學(xué)計算不支持，因此不得不將

這個工作放在其他的層上而增加網(wǎng)絡(luò)的開銷。SQL2000支持UDFs,現(xiàn)在支持復(fù)雜的數(shù)學(xué)計

算，函數(shù)的返回值不要太大，這樣的開銷很大。用戶自定義函數(shù)象光標(biāo)一樣執(zhí)行的消耗大量

的資源，假如返回大的結(jié)果使用存儲過程

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

43、SELECTCOUNT(*)的效率教低，盡量變通他的寫法，而EXISTS快.同時請注意區(qū)別：

selectcount(Fieldofnull)fromTable與selectcount(FieldofNOTnull)fromTable的返回值

是不一致的?。?！

44、當(dāng)服務(wù)器的內(nèi)存夠多時，配制線程數(shù)量=最大連接數(shù)+5,這樣能發(fā)揮最大的效率；否

則使用配制線程數(shù)量〈最大連接數(shù)啟用SQLSERVER的線程池來解決,假如還是數(shù)量=最

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

45、按照一定的次序來訪問你的表。假如你先鎖住表A,再鎖住表B,那么在所有的存儲過

程中都要按照這個順序來鎖定它們。假如你(不經(jīng)意的)某個存儲過程中先鎖定表B,再鎖

定表A,這可能就會導(dǎo)致一個死鎖。假如鎖定順序沒有被預(yù)先全面的設(shè)計好，死鎖很難被發(fā)

現(xiàn)

46、通過SQLServerPerformanceMonitor監(jiān)視相應(yīng)硬件的負(fù)載Memory:PageFaults/sec計

數(shù)器假如該值偶爾走高，說明當(dāng)時有線程競爭內(nèi)存。假如持續(xù)很高，則內(nèi)存可能是瓶頸。

Process:

1、%DPCTime指在范例間隔期間處理器用在緩延程序調(diào)用(DPC)接收與提供服務(wù)的百分

比。(DPC正在運行的為比標(biāo)準(zhǔn)間隔優(yōu)先權(quán)低的間隔)。由于DPC是以特權(quán)模式執(zhí)行的，

DPC時間的百分比為特權(quán)時間百分比的一部分。這些時間單獨計算同時不屬于間隔計算總

數(shù)的一部分。這個總數(shù)顯示了作為實例時間百分比的平均忙時。

2、％ProcessorTime計數(shù)器假如該參數(shù)值持續(xù)超過95%,說明瓶頸是CPU。能夠考慮增加

一個處理器或者換一個更快的處理器。

3、%PrivilegedTime指非閑置處理器時間用于特權(quán)模式的百分比。(特權(quán)模式是為操作系統(tǒng)

組件與操縱硬件驅(qū)動程序而設(shè)計的一種處理模式。它同意直接訪問硬件與所有內(nèi)存。另一種

模式為用戶模式,它是一種為應(yīng)用程序、環(huán)境分系統(tǒng)與整數(shù)分系統(tǒng)設(shè)計的一種有限處理模式。

操作系統(tǒng)將應(yīng)用程序線程轉(zhuǎn)換成特權(quán)模式以訪問操作系統(tǒng)服務(wù))。特權(quán)時間的%包含為間

斷與DPC提供服務(wù)的時間。特權(quán)時間比率高可能是由于失敗設(shè)備產(chǎn)生的大數(shù)量的間隔而引

起的。這個計數(shù)器將平均忙時作為樣本時間的一部分顯示。

4、％UserTime表示耗費CPU的數(shù)據(jù)庫操作，如排序，執(zhí)行aggregatefunctions等。假如該

值很高，可考慮增加索引，盡量使用簡單的表聯(lián)接，水平分割大表格等方法來降低該值。

PhysicalDisk:CurretnDiskQueueLength計數(shù)器該值應(yīng)不超過磁盤數(shù)的1.5~2倍。要提高性

能，可增加磁盤。SQLServer:CacheHilRatio計數(shù)器該值越高越好。假如持續(xù)低于80%,

應(yīng)考慮增加內(nèi)存。注意該參數(shù)值是從SQLServer啟動后，就一直累加記數(shù)，因此運行通過

一段時間后，該值將不能反映系統(tǒng)當(dāng)前值。

47、分析selectemp_nameformemployeewheresalary>3000在此語句中若salary是Float

類型的，則優(yōu)化器對其進行優(yōu)化為Convert(float,3000),由于3000是個整數(shù)，我們應(yīng)在編程

時使用3000.0而不要等運行時讓DBMS進行轉(zhuǎn)化。同樣字符與整型數(shù)據(jù)的轉(zhuǎn)換。

48、查詢的關(guān)聯(lián)同寫的順序

selecta.personMemberlD,*fromchineseresumea,personmcmberbwherepersonMemberlD

=b.referenceidanda.personMemberlD='JCNPRH39681'（A=B,B='號碼'）

selecta.personMemberlD,*fromchineseresumea,personmemberbwherea.personMemberlD

=b.referenceidanda.personMemberlD='JCNPRH39681'andb.referenceid='JCNPRH39681,

（八=82='號碼'，人='號碼'）

selecta.personMemberlD,*fromchineseresumea,personmemberbwhereb.referenceid

='JCNPRH39681'anda.personMemberlD='JCNPRH39681'（B=號碼'，A='號碼'）

49、

(1)1F沒有輸入負(fù)責(zé)人代碼THENcodel=0code2=9999ELSEcodel=code2=負(fù)責(zé)人代碼

ENDIF執(zhí)行SQL語句為：SELECT負(fù)責(zé)人名FROMP2000WHERE負(fù)責(zé)人代碼>=:codel

AND負(fù)責(zé)人代碼<=:code2

⑵IF沒有輸入負(fù)責(zé)人代碼THENSELECT負(fù)責(zé)人名FROMP2000ELSEcode=負(fù)責(zé)人代

碼SELECT負(fù)責(zé)人代碼FROMP2000WHERE負(fù)責(zé)人代碼=:codeENDIF第一種方法只

用了一條SQL語句,第二種方法用了兩條SQL語句。在沒有輸入負(fù)責(zé)人代碼時，第二種方法

顯然比第一種方法執(zhí)行效率高，由于它沒有限制條件;在輸入了負(fù)責(zé)人代碼時,第二種方法仍

然比第一種方法效率高，不僅是少了一個限制條件，還因相等運算是最快的查詢運算。我們寫

程序不要怕煩惱

50、關(guān)于JOBCN現(xiàn)在查詢分頁的新方法(如下)，用性能優(yōu)化器分析性能的瓶頸，假如在

I/O或者者網(wǎng)絡(luò)的速度上，如下的方法優(yōu)化切實有效，假如在CPU或者者內(nèi)存上，用現(xiàn)在的

方法更好。請區(qū)分如下的方法，說明索引越小越好。

begin

DECLARE@local_variabletable(FIDintidenlily(1,1),ReferenceIDvarchar(20))

insertinto@local_variable(ReferencelD)

selecttop100000ReferencelDfromchineseresumeorderbyReferencelD

select*from@local_variablewhereFid>40andfid<=60

end

與

begin

DECLARE@local_variabletable(FIDintidentity(1,1),ReferenceIDvarchar(20))

insertinto@local_variable(ReferencelD)

selecttop100000ReferencelDfromchineseresumeorderbyupdatedate

select*from@local_variablewhereFid>40andfid<=60

end

的不一致

begin

createtable#temp(FIDintidentity(1,1),ReferencelDvarchar(20))

insertinto#temp(ReferencelD)

selecttop100000ReferencelDfromchineseresumeorderbyupdatedate

select*from#tempwhereFid>40andfid<=60droptable#temp

end

數(shù)據(jù)庫性能調(diào)優(yōu)技術(shù)-索引調(diào)優(yōu)

一、概述

隨著數(shù)據(jù)庫在各個領(lǐng)域的使用不斷增長，越來越多的應(yīng)用提出了高性能的要求。數(shù)據(jù)庫性

能調(diào)優(yōu)是知識密集型的學(xué)科，需要綜合考慮各類復(fù)雜的因素：數(shù)據(jù)庫緩沖區(qū)的大小、索引的

創(chuàng)建、語句改寫等等。總之，數(shù)據(jù)庫性能調(diào)優(yōu)的目的在于使系統(tǒng)運行得更快。

調(diào)優(yōu)需要有廣泛的知識，這使得它既簡單又復(fù)雜。

說調(diào)優(yōu)簡單，是由于調(diào)優(yōu)者不必糾纏于復(fù)雜的公式與規(guī)則。許多學(xué)術(shù)界與業(yè)界的研究者都在

嘗試將調(diào)優(yōu)與查詢處理建立在數(shù)學(xué)基礎(chǔ)之上。

稱調(diào)優(yōu)復(fù)雜，是由于假如要完全懂得常識所依靠的原理，還需要對應(yīng)用、數(shù)據(jù)庫管理系統(tǒng)、

操作系統(tǒng)與硬件有廣泛而深刻的懂得。

數(shù)據(jù)庫調(diào)優(yōu)技術(shù)能夠在不一致的數(shù)據(jù)庫系統(tǒng)中使用。假如需要調(diào)優(yōu)數(shù)據(jù)庫系統(tǒng)，最好掌握如

下知識：1)查詢處理、并發(fā)操縱與數(shù)據(jù)庫恢復(fù)的知識；2)一些調(diào)優(yōu)的基本原則。

這里要緊描述索引調(diào)優(yōu)。

二、索引調(diào)優(yōu)

索引是建立在表上的一種數(shù)據(jù)組織，它能提高訪問表中一條或者多條記錄的特定查詢效

率。因此，適當(dāng)?shù)乃饕{(diào)優(yōu)是很重要的。

關(guān)于索引調(diào)優(yōu)存在如下的幾個誤區(qū)：

誤區(qū)1:索引創(chuàng)建得越多越好？

實際上：創(chuàng)建的索引可能建立后從來未使用。索引的創(chuàng)建也是需要代價的，關(guān)于刪除、

某些更新、插入操作，關(guān)于每個索引都要進行相應(yīng)的刪除、更新、插入操作。從而導(dǎo)致刪除、

某些更新、插入操作的效率變低。

誤區(qū)2：關(guān)于一個單表的查詢，能夠索引1進行過濾再使用索引2進行過濾？

實際上:假設(shè)查詢語句如下select*fromtlwherec1=1andc2=2,cl列與c2列上分別建

有索引icl、ic2。先使用icl(或者ic2)進行過濾，產(chǎn)生的結(jié)果集是臨時數(shù)據(jù)，不再具有索引，

因此不可使用ic2(或者icl)進行再次過濾。

索引優(yōu)化的基本原則：

1.將索引與數(shù)據(jù)存放到不一致的文件組

沒有將表數(shù)據(jù)與索引數(shù)據(jù)存儲到不一致的文件組，而不加區(qū)別地將它們存儲到同一文件

組。這樣，不但會造成I/O競爭，也為數(shù)據(jù)庫的保護工作帶來不變。

2.組合索引的使用

假設(shè)存在組合索引itlclc2(cl,c2),查詢語句select*fromtlwherecI=1andc2=2能夠使用

該索引。查詢語句select*fromtlwherecl=l也能夠使用該索引。但是，查詢語句select*from

tlwherec2=2不能夠使用該索引，由于沒有組合索引的引導(dǎo)列，即，要想使用c2列進行查

找，必需出現(xiàn)cl等于某值。

根據(jù)where條件的不一致，歸納如下：

l)cl=landc2=2：使用索引itlclc2進行等值查找。

2)cl=landc2>2：使用索引itlclc2進行范圍查找，能夠有兩種方法。

方法1，使用通過索引鍵(1,2)在B樹中命中一條記錄，然后向后掃描找出第一條符

合條件的記錄，從此記錄往后的每一條記錄都是符合條件的。這種方法的弊端在于：假如

cl=landc2=2對應(yīng)的記錄數(shù)很多，會產(chǎn)生很多無效的掃描。

方法2,假如c2對應(yīng)的int型數(shù)據(jù)，能夠使用索引鍵(1,3)在B樹中命中一條記錄，從此記

錄往后的每一條記錄都是符合條件的。

本文中的例子均使用方法1?

3)cl>landc2=2：由于索引的第一個列不是等于號的，索引即使后面出現(xiàn)了c2=2,也不能

將c2=2應(yīng)用于索引查找。這里，通過索引鍵(1,-8)在B樹中命中一條記錄，向后掃描找出

第一條符合cl>l的記錄，此后的每一條記錄推斷是否符合c2=2,假如符合則輸出，否則過

濾掉。這里我們稱c2=2沒有參與到索引運算中去。這種情況在實際應(yīng)用中經(jīng)常出現(xiàn)。

4)cl>l：通過索引鍵(1,-8)在B樹中命中一條記錄，以此向后掃描找出第一條符合cl>l

的記錄，此后的每條記錄都是符合條件的。

3.唯一索引與非唯一索引的差異

假設(shè)索引intlcl(cl)是唯一索引，關(guān)于查詢語句selectclfromtlwherecl=l,達夢數(shù)據(jù)庫

使用索引鍵(1)命中B樹中一條記錄，命中之后直接返回該記錄(由因此唯一索引，因此最

多只能有一條cl=l的記錄)。

假設(shè)索引itlc2(c2)是非唯一索引，關(guān)于查詢語句selectc2fromt2wherec2=2,達夢數(shù)據(jù)

庫使用索引鍵(2)命中B樹中一條記錄，返回該記錄，并繼續(xù)向后掃描，假如該記錄是滿足

c=2,返回該記錄，繼續(xù)掃描，直到遇到第一條不符合條件c2=2的記錄。

因此，我們能夠得知，關(guān)于不存在重復(fù)值的列，創(chuàng)建唯一索引優(yōu)于創(chuàng)建非唯一索引。

4.非聚集索引的作用

每張表只可能一個聚集索引，聚集索引用來組織真實數(shù)據(jù)。語句“createtableemployee(id

intclusterprimarykey,namevarchar(20),addrvarcharQO))"。表employee的數(shù)據(jù)用id來組織。

假如要查找id=1000的員工記錄，只要用索引鍵(1000)命中該聚集索引。但是，關(guān)于要查

找name='張三'的員工記錄就不能使用該索引了，需要進行全表掃描，關(guān)于每一條記錄推

斷是否滿足name='張三'，這樣會導(dǎo)致查詢效率非常低。

要使用聚集索弓I，必需提供id,我們只能提供name,因此需要引入一個輔助結(jié)構(gòu)實現(xiàn)name

到id的轉(zhuǎn)換，這就是非聚集索引的作用。該非聚集索引的鍵是name,值是id。因此語句“select

*fromemployeewherename=＞張三的執(zhí)行流程是：通過鍵('張三’)命中非聚集索引，

得到對應(yīng)的id值3(假設(shè)‘張三'對應(yīng)的id為3),然后用鍵(3)命中聚集索引，得到相應(yīng)的

記錄。

5.是不是使用非聚集索引的查詢都需要進行聚集的查詢？

不是的，盡管在上一點中查詢轉(zhuǎn)換為聚集索引的查找，有的時候候能夠只需要使用非聚

集索引。

創(chuàng)建表并創(chuàng)建相應(yīng)的索引：createtabletl(clint,c2int,c3int);createindexitlc2c3on

tl(c2,c3)o查詢語句為：selectc3from11wherec2=1?

由于索引itlc2c3(c2,c3)覆蓋查詢語句中的列(c2,c3).因此，該查詢語句的執(zhí)行流程為：通過

索引鍵(1,-8)命中索引itk2c3,關(guān)于該記錄直接返回c3對應(yīng)的值，繼續(xù)向后掃描，假如索

引記錄中cl還是等于1,那么輸出c3,以此類推，直到出現(xiàn)第一條cl不等于1的索引記錄,

結(jié)束查詢。

6.創(chuàng)建索引的規(guī)則

創(chuàng)建索引首先要考慮的是列的可選擇性。比較一下列中唯一鍵的數(shù)量與表中記錄的行數(shù)，

就能夠推斷該列的可選擇性。假如該列的“唯一鍵的數(shù)量/表中記錄行數(shù)”的比值越接近于1,

則該列的可選擇行越高。在可選擇性高的列上進行查詢，返回的數(shù)據(jù)就較少，比較適合索引

查詢。相反，比如性別列上只有兩個值，可選擇行就很小，不適合索引查詢。

SQLServer中調(diào)整自增字段的當(dāng)前初始值

前幾天在把一個CommuntiyServer的數(shù)據(jù)庫從SQL2000升級到SQL2005的時候，碰到一

個怪異的問題，報如下錯誤：

ViolationofPRIMARYKEYconstraint'PK_cs_Threads'.Cannotinsertduplicatekeyinobject

'dbo.cs_Threads'.

分析進去后，居然發(fā)現(xiàn)這個表的自增字段數(shù)據(jù)庫中已經(jīng)達到了6144,而數(shù)據(jù)庫保護的這個

表的初始自增值只到6109。

解決方法很簡單，利用下列SQL語句即可搞定:

DBCCCHECKIDENT('cs_Threads')

上述語句的意思就是：假如表'cs_Threads的當(dāng)前標(biāo)識值小于列中存儲的最大標(biāo)識值，則使用

標(biāo)識列中的最大值對其進行重置。

CHECKIDENT命令能夠有下列幾種寫法：

1、DBCCCHECKIDENT('table_name',NORESEED)

不重置當(dāng)前標(biāo)識值。DBCCCHECKIDENT返回一個報表，它指明當(dāng)前標(biāo)識值與應(yīng)有的標(biāo)識

值。

類似如下的報表：

Checkingidentityinformation:currentidentityvalue'6109',currentcolumnvalue'6144'.

2、DBCCCHECKIDENT('table_name')或者DBCCCHECKIDENT('table_name',RESEED)

假如表的當(dāng)前標(biāo)識值小于列中存儲的最大標(biāo)識值，則使用標(biāo)識列中的最大值對其進行重置。

上述命令執(zhí)行的時候，也會報類似上面的報表。

3、DBCCCHECKIDENT('table_name',RESEED,new_reseed_value)

當(dāng)前值設(shè)置為new_reseed_value。

假如自創(chuàng)建表后沒有將行插入該表，則在執(zhí)行DBCCCHECKIDENT后插入的第一行將使

用new_reseed_value作為標(biāo)識。否則，下一個插入的行將使用new_reseed_value+1o

假如new_reseed_value的值小于標(biāo)識列中的最大值，以后引用該表時將產(chǎn)生2627號錯誤

信息。

使用時容易忽略SQLServer的21個問題

假如你正在負(fù)責(zé)一個基于SQLServer的項目，或者者你剛剛接觸SQLServer,你都有可能

要面臨一些數(shù)據(jù)庫性能的問題，這篇文章會為你提供一些有用的指導(dǎo)(其中大多數(shù)也能夠用

于其它的DBMS)。

在這里，我不打算介紹使用SQLServer的竅門，也不能提供一個包治百病的方案，我

所做的是總結(jié)一些經(jīng)驗--關(guān)于如何形成一個好的設(shè)計。這些經(jīng)驗來自我過去幾年中經(jīng)受的

教訓(xùn)，一直來，我看到許多同樣的設(shè)計錯誤被一次又一次的重復(fù)。

你熟悉工具嗎？

不要輕視這一點，這是我在這篇文章中講述的最關(guān)鍵的一條。也許你也看到有很多的

SQLServer程序員沒有掌握全部的T-SQL命令與SQLServer提供的那些有用的工具。

“什么？我要浪費一個月的時間來學(xué)習(xí)那些我永遠也不可能用到的SQL命令？？？”，

你也許會這樣說。對的，你不需要這樣做。但是你應(yīng)該用一個周末瀏覽所有的T-SQL命令。

在這里，你的任務(wù)是熟悉，將來，當(dāng)你設(shè)計一個查詢時，你會記起來：“對了，這里有一個

命令能夠完全實現(xiàn)我需要的功能”，因此，到MSDN查看這個命令的確切語法。

不要使用光標(biāo)

讓我再重復(fù)一遍：不要使用光標(biāo)。假如你想破壞整個系統(tǒng)的性能的話，它們倒是你最有

效的首選辦法。大多數(shù)的初學(xué)者都使用光標(biāo)，而沒有意識到它們對性能造成的影響。它們占

用內(nèi)存，還用它們那些不可思議的方式鎖定表，另外，它們簡直就像蝸牛。而最糟糕的是，

它們能夠使你的DBA所能做的一切性能優(yōu)化等于沒做。不知你是否明白每執(zhí)行一次FETCH

就等于執(zhí)行一次SELECT命令？這意味著假如你的光標(biāo)有10000條記錄，它將執(zhí)行10000

次SELECT!假如你使用一組SELECT、UPDATE或者者DELETE來完成相應(yīng)的工作，那

將有效率的多。

初學(xué)者通常認(rèn)為使用光標(biāo)是一種比較熟悉與舒適的編程方式，可很不幸，這會導(dǎo)致糟糕

的性能。顯然，SQL的總體目的是你要實現(xiàn)什么，而不是如何實現(xiàn)。

我曾經(jīng)用T-SQL重寫了一個基于光標(biāo)的存儲過程，那個表只有100,000條記錄，原先的

存儲過程用了40分鐘才執(zhí)行完畢，而新的存儲過程只用了10秒鐘。在這里，我想你應(yīng)該能

夠看到一個不稱職的程序員畢竟在干了什么!!!

我們能夠?qū)懸粋€小程序來取得與處理數(shù)據(jù)同時更新數(shù)據(jù)庫，這樣做有的時候會更有效。

記住：關(guān)于循環(huán)，T-SQL無能為力。

我再重新提醒一下：使用光標(biāo)沒有好處。除了DBA的工作外，我從來沒有看到過使用

光標(biāo)能夠有效的完成任何工作。

規(guī)范化你的資料表

為什么不規(guī)范化數(shù)據(jù)庫？大概有兩個借口：出于性能的考慮與純粹由于懶惰。至于第二

點，你遲早得為此付出代價。而關(guān)于性能的問題，你不需要優(yōu)化根本就不慢的東西。我經(jīng)常

看到一些程序員“反規(guī)范化”數(shù)據(jù)庫，他們的理由是“原先的設(shè)計太慢了”，可結(jié)果卻常常

是他們讓系統(tǒng)更慢了。DBMS被設(shè)計用來處理規(guī)范數(shù)據(jù)庫的，因此，記?。喊凑找?guī)范化的

要求設(shè)計數(shù)據(jù)庫。

不要使用SELECT*

這點不太容易做到，我太熟悉了，由于我自己就經(jīng)常這樣干。但是，假如在SELECT

中指定你所需要的列，那將會帶來下列的好處：

1減少內(nèi)存耗費與網(wǎng)絡(luò)的帶寬

2你能夠得到更安全的設(shè)計

3給查詢優(yōu)化器機會從索引讀取所有需要的列

熟悉你將要對數(shù)據(jù)進行的操作

為你的數(shù)據(jù)庫創(chuàng)建一個健壯的索引，那但是功德一件?？梢龅竭@一點簡直就是一門藝

術(shù)。每當(dāng)你為一個表添加一個索引，SELECT會更快了，可INSERT與DELETE去［1大大的

變慢了，由于創(chuàng)建了保護索引需要許多額外的工作。顯然，這里問題的關(guān)鍵是：你要對這張

表進行什么樣的操作。這個問題不太好把握，特別是涉及DELETE與