一壮韭、數(shù)據(jù)庫(kù)結(jié)構(gòu)的設(shè)計(jì)

如果不能設(shè)計(jì)一個(gè)合理的數(shù)據(jù)庫(kù)模型，不僅會(huì)增加客戶端和服務(wù)器段程序的編程和維護(hù)的難度纹因，而且將會(huì)影響系統(tǒng)實(shí)際運(yùn)行的性能喷屋。所以，在一個(gè)系統(tǒng)開始實(shí)施之前瞭恰，完備的數(shù)據(jù)庫(kù)模型的設(shè)計(jì)是必須的屯曹。

在一個(gè)系統(tǒng)分析、設(shè)計(jì)階段惊畏，因?yàn)閿?shù)據(jù)量較小恶耽，負(fù)荷較低。我們往往只注意到功能的實(shí)現(xiàn)颜启，而很難注意到性能的薄弱之處驳棱，等到系統(tǒng)投入實(shí)際運(yùn)行一段時(shí)間后，才發(fā)現(xiàn)系統(tǒng)的性能在降低农曲，這時(shí)再來考慮提高系統(tǒng)性能則要花費(fèi)更多的人力物力社搅，而整個(gè)系統(tǒng)也不可避免的形成了一個(gè)打補(bǔ)丁工程。

所以在考慮整個(gè)系統(tǒng)的流程的時(shí)候乳规，我們必須要考慮形葬，在高并發(fā)大數(shù) 據(jù)量的訪問情況下，我們的系統(tǒng)會(huì)不會(huì)出現(xiàn)極端的情況暮的。（例如：對(duì)外統(tǒng)計(jì)系統(tǒng)在7月16日出現(xiàn)的數(shù)據(jù)異常的情況笙以，并發(fā)大數(shù)據(jù)量的的訪問造成，數(shù)據(jù)庫(kù)的響應(yīng)時(shí) 間不能跟上數(shù)據(jù)刷新的速度造成冻辩。具體情況是：在日期臨界時(shí)（00：00：00）猖腕，判斷數(shù)據(jù)庫(kù)中是否有當(dāng)前日期的記錄，沒有則插入一條當(dāng)前日期的記錄恨闪。在低 并發(fā)訪問的情況下倘感，不會(huì)發(fā)生問題，但是當(dāng)日期臨界時(shí)的訪問量相當(dāng)大的時(shí)候咙咽，在做這一判斷的時(shí)候老玛，會(huì)出現(xiàn)多次條件成立，則數(shù)據(jù)庫(kù)里會(huì)被插入多條當(dāng)前日期的記 錄，從而造成數(shù)據(jù)錯(cuò)誤蜡豹。）麸粮，數(shù)據(jù)庫(kù)的模型確定下來之后，我們有必要做一個(gè)系統(tǒng)內(nèi)數(shù)據(jù)流向圖镜廉，分析可能出現(xiàn)的瓶頸弄诲。

為了保證數(shù)據(jù)庫(kù)的一致性和完整性，在邏輯設(shè)計(jì)的時(shí)候往往會(huì)設(shè)計(jì)過 多的表間關(guān)聯(lián)娇唯，盡可能的降低數(shù)據(jù)的冗余威根。（例如用戶表的地區(qū)，我們可以把地區(qū)另外存放到一個(gè)地區(qū)表中）如果數(shù)據(jù)冗余低视乐，數(shù)據(jù)的完整性容易得到保證，提高了 數(shù)據(jù)吞吐速度敢茁，保證了數(shù)據(jù)的完整性佑淀，清楚地表達(dá)數(shù)據(jù)元素之間的關(guān)系。而對(duì)于多表之間的關(guān)聯(lián)查詢（尤其是大數(shù)據(jù)表）時(shí)彰檬，其性能將會(huì)降低伸刃，同時(shí)也提高了客戶端 程序的編程難度，因此逢倍，物理設(shè)計(jì)需折衷考慮捧颅，根據(jù)業(yè)務(wù)規(guī)則，確定對(duì)關(guān)聯(lián)表的數(shù)據(jù)量大小较雕、數(shù)據(jù)項(xiàng)的訪問頻度碉哑，對(duì)此類數(shù)據(jù)表頻繁的關(guān)聯(lián)查詢應(yīng)適當(dāng)提高數(shù)據(jù)冗余 設(shè)計(jì)但增加了表間連接查詢的操作，也使得程序的變得復(fù)雜亮蒋，為了提高系統(tǒng)的響應(yīng)時(shí)間扣典，合理的數(shù)據(jù)冗余也是必要的。設(shè)計(jì)人員在設(shè)計(jì)階段應(yīng)根據(jù)系統(tǒng)操作的類型慎玖、 頻度加以均衡考慮贮尖。

另外，最好不要用自增屬性字段作為主鍵與子表關(guān)聯(lián)趁怔。不便于系統(tǒng)的遷移和數(shù)據(jù)恢復(fù)湿硝。對(duì)外統(tǒng)計(jì)系統(tǒng)映射關(guān)系丟失（******************）。

原來的表格必須可以通過由它分離出去的表格重新構(gòu)建润努。使用這個(gè)規(guī) 定的好處是关斜，你可以確保不會(huì)在分離的表格中引入多余的列，所有你創(chuàng)建的表格結(jié)構(gòu)都與它們的實(shí)際需要一樣大铺浇。應(yīng)用這條規(guī)定是一個(gè)好習(xí)慣蚤吹，不過除非你要處理一 個(gè)非常大型的數(shù)據(jù)，否則你將不需要用到它。（例如一個(gè)通行證系統(tǒng)裁着，我可以將USERID繁涂，USERNAME，USERPASSWORD二驰，單獨(dú)出來作個(gè)表扔罪， 再把USERID作為其他表的外鍵）

表的設(shè)計(jì)具體注意的問題：

1、數(shù)據(jù)行的長(zhǎng)度不要超過8020字節(jié)桶雀，如果超過這個(gè)長(zhǎng)度的話在物理頁(yè)中這條數(shù)據(jù)會(huì)占用兩行從而造成存儲(chǔ)碎片矿酵，降低查詢效率。

2矗积、能夠用數(shù)字類型的字段盡量選擇數(shù)字類型而不用字符串類型的（電話號(hào)碼）全肮，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷棘捣。這是因?yàn)橐嬖谔幚聿樵兒瓦B接回逐個(gè)比較字符串中每一個(gè)字符辜腺，而對(duì)于數(shù)字型而言只需要比較一次就夠了。

3乍恐、對(duì)于不可變字符類型char和可變字符類型varchar 都是8000字節(jié),char查詢快评疗，但是耗存儲(chǔ)空間，varchar查詢相對(duì)慢一些但是節(jié)省存儲(chǔ)空間茵烈。在設(shè)計(jì)字段的時(shí)候可以靈活選擇百匆，例如用戶名、密碼等 長(zhǎng)度變化不大的字段可以選擇CHAR呜投，對(duì)于評(píng)論等長(zhǎng)度變化大的字段可以選擇VARCHAR加匈。

4、字段的長(zhǎng)度在最大限度的滿足可能的需要的前提下仑荐，應(yīng)該盡可能的設(shè)得短一些矩动，這樣可以提高查詢的效率，而且在建立索引的時(shí)候也可以減少資源的消耗释漆。

二悲没、查詢的優(yōu)化

保證在實(shí)現(xiàn)功能的基礎(chǔ)上，盡量減少對(duì)數(shù)據(jù)庫(kù)的訪問次數(shù)(可以用緩 存保存查詢結(jié)果男图，減少查詢次數(shù))示姿；通過搜索參數(shù)，盡量減少對(duì)表的訪問行數(shù),最小化結(jié)果集逊笆，從而減輕網(wǎng)絡(luò)負(fù)擔(dān)栈戳；能夠分開的操作盡量分開處理，提高每次的響應(yīng) 速度难裆；在數(shù)據(jù)窗口使用SQL時(shí)子檀，盡量把使用的索引放在選擇的首列镊掖；算法的結(jié)構(gòu)盡量簡(jiǎn)單；在查詢時(shí)褂痰，不要過多地使用通配符如SELECT * FROM T1語(yǔ)句亩进，要用到幾列就選擇幾列如：SELECTCOL1,COL2 FROM T1；在可能的情況下盡量限制盡量結(jié)果集行數(shù)如：SELECT TOP 300 COL1,COL2,COL3 FROM T1,因?yàn)槟承┣闆r下用戶是不需要那么多的數(shù)據(jù)的缩歪。

在沒有建索引的情況下归薛，數(shù)據(jù)庫(kù)查找某一條數(shù)據(jù)，就必須進(jìn)行全表掃描了匪蝙，對(duì)所有數(shù)據(jù)進(jìn)行一次遍歷主籍，查找出符合條件的記錄。在數(shù)據(jù)量比較小的情況下逛球，也許看不出明顯的差別千元，但是當(dāng)數(shù)據(jù)量大的情況下，這種情況就是極為糟糕的了颤绕。

SQL語(yǔ)句在SQL SERVER中是如何執(zhí)行的幸海，他們擔(dān)心自己所寫的SQL語(yǔ)句會(huì)被SQL SERVER誤解。比如：

select * from table1 where name='zhangsan' and tID > 10000 和執(zhí)行:

select * from table1 where tID > 10000 and name='zhangsan'

一些人不知道以上兩條語(yǔ)句的執(zhí)行效率是否一樣屋厘，因?yàn)槿绻?jiǎn)單的從 語(yǔ)句先后上看，這兩個(gè)語(yǔ)句的確是不一樣月而，如果tID是一個(gè)聚合索引汗洒，那么后一句僅僅從表的10000條以后的記錄中查找就行了；而前一句則要先從全表中查 找看有幾個(gè)name='zhangsan'的父款，而后再根據(jù)限制條件條件tID>10000來提出查詢結(jié)果溢谤。

事實(shí)上，這樣的擔(dān)心是不必要的憨攒。SQL SERVER中有一個(gè)“查詢分析優(yōu)化器”世杀，它可以計(jì)算出where子句中的搜索條件并確定哪個(gè)索引能縮小表掃描的搜索空間，也就是說肝集，它能實(shí)現(xiàn)自動(dòng)優(yōu)化瞻坝。 雖然查詢優(yōu)化器可以根據(jù)where子句自動(dòng)的進(jìn)行查詢優(yōu)化，但有時(shí)查詢優(yōu)化器就會(huì)不按照您的本意進(jìn)行快速查詢杏瞻。

在查詢分析階段所刀，查詢優(yōu)化器查看查詢的每個(gè)階段并決定限制需要掃描的數(shù)據(jù)量是否有用。如果一個(gè)階段可以被用作一個(gè)掃描參數(shù)（SARG）捞挥，那么就稱之為可優(yōu)化的浮创，并且可以利用索引快速獲得所需數(shù)據(jù)。

SARG的定義：用于限制搜索的一個(gè)操作砌函，因?yàn)樗ǔＪ侵敢粋€(gè)特定的匹配斩披，一個(gè)值的范圍內(nèi)的匹配或者兩個(gè)以上條件的AND連接溜族。形式如下：

列名 操作符 <常數(shù) 或 變量> 或 <常數(shù) 或 變量> 操作符 列名

列名可以出現(xiàn)在操作符的一邊，而常數(shù)或變量出現(xiàn)在操作符的另一邊垦沉。如：

Name=’張三’

價(jià)格>5000

5000<價(jià)格

Name=’張三’ and 價(jià)格>5000

如果一個(gè)表達(dá)式不能滿足SARG的形式煌抒，那它就無法限制搜索的范圍了，也就是SQL SERVER必須對(duì)每一行都判斷它是否滿足WHERE子句中的所有條件乡话。所以一個(gè)索引對(duì)于不滿足SARG形式的表達(dá)式來說是無用的摧玫。

所以，優(yōu)化查詢最重要的就是绑青，盡量使語(yǔ)句符合查詢優(yōu)化器的規(guī)則避免全表掃描而使用索引查詢诬像。

具體要注意的：

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

select id from t where num is null

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

select id from t where num=0

2.應(yīng)盡量避免在 where 子句中使用!=或<>操作符邪乍，否則將引擎放棄使用索引而進(jìn)行全表掃描降狠。優(yōu)化器將無法通過索引來確定將要命中的行數(shù),因此需要搜索該表的所有行。

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

select id from t where num=10 or num=20

可以這樣查詢：

select id from t where num=10

union all

select id from t where num=20

4.in 和 not in 也要慎用，因?yàn)镮N會(huì)使系統(tǒng)無法使用索引,而只能直接搜索表中的數(shù)據(jù)吕晌。如：

select id from t where num in(1,2,3)

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

select id from t where num between 1 and 3

5.盡量避免在索引過的字符數(shù)據(jù)中，使用非打頭字母搜索睛驳。這也使得引擎無法利用索引烙心。

見如下例子：

SELECT * FROM T1 WHERE NAME LIKE ‘%L%’

SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’

SELECT * FROM T1 WHERE NAME LIKE ‘L%’

即使NAME字段建有索引，前兩個(gè)查詢依然無法利用索引完成加快操作乏沸，引擎不得不對(duì)全表所有數(shù)據(jù)逐條操作來完成任務(wù)淫茵。而第三個(gè)查詢能夠使用索引來加快操作。

6.必要時(shí)強(qiáng)制查詢優(yōu)化器使用某個(gè)索引蹬跃，如在 where 子句中使用參數(shù)匙瘪，也會(huì)導(dǎo)致全表掃描。因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量蝶缀，但優(yōu)化程序不能將訪問計(jì)劃的選擇推遲到運(yùn)行時(shí)辆苔；它必須在編譯時(shí)進(jìn)行選擇。然 而扼劈，如果在編譯時(shí)建立訪問計(jì)劃驻啤，變量的值還是未知的，因而無法作為索引選擇的輸入項(xiàng)荐吵。如下面語(yǔ)句將進(jìn)行全表掃描：

select id from t where num=@num

可以改為強(qiáng)制查詢使用索引：

select id from t with(index(索引名)) where num=@num

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

SELECT * FROM T1 WHERE F1/2=100

應(yīng)改為:

SELECT * FROM T1 WHERE F1=100*2

SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’

應(yīng)改為:

SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’

SELECT member_number, first_name, last_name FROM members

WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21

應(yīng)改為:

SELECT member_number, first_name, last_name FROM members

WHERE dateofbirth < DATEADD(yy,-21,GETDATE())

即：任何對(duì)列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫(kù)函數(shù)贼涩、計(jì)算表達(dá)式等等巧涧，查詢時(shí)要盡可能將操作移至等號(hào)右邊。

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

select id from t where substring(name,1,3)='abc'--name以abc開頭的id

select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id

應(yīng)改為:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

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

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

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

elect num from a where num in(select num from b)

用下面的語(yǔ)句替換：

select num from a where exists(select 1 from b where num=a.num)

SELECT SUM(T1.C1)FROM T1 WHERE(

(SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0)

SELECT SUM(T1.C1) FROM T1WHERE EXISTS(

SELECT * FROM T2 WHERE T2.C2=T1.C2)

兩者產(chǎn)生相同的結(jié)果桐臊，但是后者的效率顯然要高于前者。因?yàn)楹笳卟粫?huì)產(chǎn)生大量鎖定的表掃描或是索引掃描晓殊。

如果你想校驗(yàn)表里是否存在某條紀(jì)錄断凶，不要用count(*)那樣效率很低，而且浪費(fèi)服務(wù)器資源巫俺∪纤福可以用EXISTS代替。如：

IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')

可以寫成：

IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

經(jīng)常需要寫一個(gè)T_SQL語(yǔ)句比較一個(gè)父結(jié)果集和子結(jié)果集识藤，從而找到是否存在在父結(jié)果集中有而在子結(jié)果集中沒有的記錄砚著，如：

SELECT a.hdr_key FROM hdr_tbl a---- tbl a 表示tbl用別名a代替

WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)

SELECT a.hdr_key FROM hdr_tbl a

LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULL

SELECT hdr_key FROM hdr_tbl

WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)

三種寫法都可以得到同樣正確的結(jié)果次伶，但是效率依次降低痴昧。

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

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

14.臨時(shí)表并不是不可使用豪娜，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行В缬纯?dāng)需要重復(fù)引用大型表或常用表中的某個(gè)數(shù)據(jù)集時(shí)瘤载。但是，對(duì)于一次性事件卖擅，最好使用導(dǎo)出表鸣奔。

15.在新建臨時(shí)表時(shí)墨技，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table挎狸，避免造成大量 log 扣汪，以提高速度；如果數(shù)據(jù)量不大锨匆，為了緩和系統(tǒng)表的資源崭别，應(yīng)先create table，然后insert恐锣。

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

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

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

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

20. 避免使用不兼容的數(shù)據(jù)類型。例如float和int蜒蕾、char和varchar稠炬、binary和varbinary是不兼容的（條件判斷時(shí)）。數(shù)據(jù)類型的不兼容可能使優(yōu)化器無法執(zhí)行一些本來可以進(jìn)行的優(yōu)化操作咪啡。例如:

SELECT name FROM employee WHERE salary > 60000

在這條語(yǔ)句中,如salary字段是money型的,則優(yōu)化器很難對(duì)其進(jìn)行優(yōu)化,因?yàn)?0000是個(gè)整型數(shù)首启。我們應(yīng)當(dāng)在編程時(shí)將整型轉(zhuǎn)化成為錢幣型,而不要等到運(yùn)行時(shí)轉(zhuǎn)化。

21.充分利用連接條件（條件越多越快）撤摸，在某種情況下毅桃，兩個(gè)表之間可能不只一個(gè)的連接條件，這時(shí)在 WHERE 子句中將連接條件完整的寫上准夷，有可能大大提高查詢速度钥飞。

例：

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO

第二句將比第一句執(zhí)行快得多。

22衫嵌、使用視圖加速查詢

把表的一個(gè)子集進(jìn)行排序并創(chuàng)建視圖读宙，有時(shí)能加速查詢。它有助于避免多重排序 操作楔绞，而且在其他方面還能簡(jiǎn)化優(yōu)化器的工作结闸。例如：

SELECT cust.name掖棉，rcvbles.balance，……other columns

FROM cust膀估，rcvbles

WHERE cust.customer_id = rcvlbes.customer_id

AND rcvblls.balance>0

AND cust.postcode>“98000”

ORDER BY cust.name

如果這個(gè)查詢要被執(zhí)行多次而不止一次幔亥，可以把所有未付款的客戶找出來放在一個(gè)視圖中，并按客戶的名字進(jìn)行排序：

CREATE VIEW DBO.V_CUST_RCVLBES

AS

SELECT cust.name察纯，rcvbles.balance帕棉，……other columns

FROM cust，rcvbles

WHERE cust.customer_id = rcvlbes.customer_id

AND rcvblls.balance>0

ORDER BY cust.name

然后以下面的方式在視圖中查詢：

SELECT ＊ FROM V_CUST_RCVLBES

WHERE postcode>“98000”

視圖中的行要比主表中的行少饼记，而且物理順序就是所要求的順序香伴，減少了磁盤I/O，所以查詢工作量可以得到大幅減少具则。

23即纲、能用DISTINCT的就不用GROUP BY （group by 操作特別慢）

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

可改為：

SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

24.能用UNION ALL就不要用UNION

UNION ALL不執(zhí)行SELECT DISTINCT函數(shù)，這樣就會(huì)減少很多不必要的資源

35.盡量不要用SELECT INTO語(yǔ)句博肋。

SELECT INOT 語(yǔ)句會(huì)導(dǎo)致表鎖定低斋，阻止其他用戶訪問該表。

上面我們提到的是一些基本的提高查詢速度的注意事項(xiàng),但是在更多 的情況下,往往需要反復(fù)試驗(yàn)比較不同的語(yǔ)句以得到最佳方案匪凡。最好的方法當(dāng)然是測(cè)試膊畴，看實(shí)現(xiàn)相同功能的SQL語(yǔ)句哪個(gè)執(zhí)行時(shí)間最少，但是數(shù)據(jù)庫(kù)中如果數(shù)據(jù)量 很少病游，是比較不出來的唇跨，這時(shí)可以用查看執(zhí)行計(jì)劃，即：把實(shí)現(xiàn)相同功能的多條SQL語(yǔ)句考到查詢分析器衬衬，按CTRL+L看查所利用的索引买猖，表掃描次數(shù)（這兩 個(gè)對(duì)性能影響最大），總體上看詢成本百分比即可滋尉。

三玉控、算法的優(yōu)化

盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差兼砖，如果游標(biāo)操作的數(shù)據(jù)超過 1萬行奸远，那么就應(yīng)該考慮改寫既棺。.使用基于游標(biāo)的方法或臨時(shí)表方法之前讽挟，應(yīng)先尋找基于集的解決方案來解決問題，基于集的方法通常更有效丸冕。與臨時(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)的方法和基于集的方法都可以嘗試一下众旗，看哪一種方法的效果更好罢杉。

游標(biāo)提供了對(duì)特定集合中逐行掃描的手段，一般使用游標(biāo)逐行遍歷數(shù)據(jù)贡歧，根據(jù)取出的數(shù)據(jù)不同條件進(jìn)行不同的操作滩租。尤其對(duì)多表和大表定義的游標(biāo)（大的數(shù)據(jù)集合）循環(huán)很容易使程序進(jìn)入一個(gè)漫長(zhǎng)的等特甚至死機(jī)。

在有些場(chǎng)合利朵，有時(shí)也非得使用游標(biāo)律想，此時(shí)也可考慮將符合條件的數(shù)據(jù)行轉(zhuǎn)入臨時(shí)表中，再對(duì)臨時(shí)表定義游標(biāo)進(jìn)行操作绍弟，可時(shí)性能得到明顯提高技即。

（例如：對(duì)內(nèi)統(tǒng)計(jì)第一版）

封裝存儲(chǔ)過程

四、建立高效的索引

創(chuàng)建索引一般有以下兩個(gè)目的：維護(hù)被索引列的唯一性和提供快速訪 問表中數(shù)據(jù)的策略樟遣。大型數(shù)據(jù)庫(kù)有兩種索引即簇索引和非簇索引而叼，一個(gè)沒有簇索引的表是按堆結(jié)構(gòu)存儲(chǔ)數(shù)據(jù)，所有的數(shù)據(jù)均添加在表的尾部豹悬，而建立了簇索引的表澈歉， 其數(shù)據(jù)在物理上會(huì)按照簇索引鍵的順序存儲(chǔ)，一個(gè)表只允許有一個(gè)簇索引屿衅，因此埃难，根據(jù)B樹結(jié)構(gòu)，可以理解添加任何一種索引均能提高按索引列查詢的速度涤久，但會(huì)降 低插入涡尘、更新、刪除操作的性能响迂，尤其是當(dāng)填充因子（Fill Factor）較大時(shí)考抄。所以對(duì)索引較多的表進(jìn)行頻繁的插入、更新蔗彤、刪除操作川梅，建表和索引時(shí)因設(shè)置較小的填充因子，以便在各數(shù)據(jù)頁(yè)中留下較多的自由空間然遏，減 少頁(yè)分割及重新組織的工作贫途。

索引是從數(shù)據(jù)庫(kù)中獲取數(shù)據(jù)的最高效方式之一。95% 的數(shù)據(jù)庫(kù)性能問題都可以采用索引技術(shù)得到解決待侵。作為一條規(guī)則丢早，我通常對(duì)邏輯主鍵使用唯一的成組索引，對(duì)系統(tǒng)鍵（作為存儲(chǔ)過程）采用唯一的非成組索引，對(duì)任 何外鍵列[字段]采用非成組索引怨酝。不過傀缩，索引就象是鹽，太多了菜就咸了农猬。你得考慮數(shù)據(jù)庫(kù)的空間有多大赡艰，表如何進(jìn)行訪問，還有這些訪問是否主要用作讀寫斤葱。

實(shí)際上瞄摊，您可以把索引理解為一種特殊的目錄。微軟的SQL SERVER提供了兩種索引：聚集索引（clustered index苦掘，也稱聚類索引换帜、簇集索引）和非聚集索引（nonclustered index，也稱非聚類索引鹤啡、非簇集索引）惯驼。下面，我們舉例來說明一下聚集索引和非聚集索引的區(qū)別：

其實(shí)递瑰，我們的漢語(yǔ)字典的正文本身就是一個(gè)聚集索引祟牲。比如，我們要查“安”字抖部，就會(huì)很自然地翻開字典的前幾頁(yè)说贝，因?yàn)椤鞍病钡钠匆羰恰癮n”，而按照拼音排序 漢字的字典是以英文字母“a”開頭并以“z”結(jié)尾的慎颗，那么“安”字就自然地排在字典的前部乡恕。如果您翻完了所有以“a”開頭的部分仍然找不到這個(gè)字，那么就 說明您的字典中沒有這個(gè)字俯萎；同樣的傲宜，如果查“張”字，那您也會(huì)將您的字典翻到最后部分夫啊，因?yàn)椤皬垺钡钠匆羰恰皕hang”函卒。也就是說，字典的正文部分本身 就是一個(gè)目錄撇眯，您不需要再去查其他目錄來找到您需要找的內(nèi)容报嵌。

我們把這種正文內(nèi)容本身就是一種按照一定規(guī)則排列的目錄稱為“聚集索引”。

如果您認(rèn)識(shí)某個(gè)字熊榛，您可以快速地從自動(dòng)中查到這個(gè)字锚国。但您也可能 會(huì)遇到您不認(rèn)識(shí)的字，不知道它的發(fā)音来候，這時(shí)候跷叉，您就不能按照剛才的方法找到您要查的字逸雹，而需要去根據(jù)“偏旁部首”查到您要找的字营搅，然后根據(jù)這個(gè)字后的頁(yè)碼 直接翻到某頁(yè)來找到您要找的字云挟。但您結(jié)合“部首目錄”和“檢字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“張”字转质，我們可以看到在查部首 之后的檢字表中“張”的頁(yè)碼是672頁(yè)园欣，檢字表中“張”的上面是“馳”字，但頁(yè)碼卻是63頁(yè)休蟹，“張”的下面是“弩”字沸枯，頁(yè)面是390頁(yè)。很顯然赂弓，這些字并 不是真正的分別位于“張”字的上下方，現(xiàn)在您看到的連續(xù)的“馳、張窑业、弩”三字實(shí)際上就是他們?cè)诜蔷奂饕械呐判虮蚣睿亲值湔闹械淖衷诜蔷奂饕械挠?射。我們可以通過這種方式來找到您所需要的字杨耙，但它需要兩個(gè)過程赤套，先找到目錄中的結(jié)果，然后再翻到您所需要的頁(yè)碼珊膜。

我們把這種目錄純粹是目錄容握，正文純粹是正文的排序方式稱為“非聚集索引”。

進(jìn)一步引申一下车柠，我們可以很容易的理解：每個(gè)表只能有一個(gè)聚集索引剔氏，因?yàn)槟夸浿荒馨凑找环N方法進(jìn)行排序。

（一）何時(shí)使用聚集索引或非聚集索引

下面的表總結(jié)了何時(shí)使用聚集索引或非聚集索引（很重要）竹祷。

動(dòng)作描述 使用聚集索引 使用非聚集索引

列經(jīng)常被分組排序 應(yīng) 應(yīng)

返回某范圍內(nèi)的數(shù)據(jù) 應(yīng) 不應(yīng)

一個(gè)或極少不同值 不應(yīng) 不應(yīng)

小數(shù)目的不同值 應(yīng) 不應(yīng)

大數(shù)目的不同值 不應(yīng) 應(yīng)

頻繁更新的列 不應(yīng) 應(yīng)

外鍵列 應(yīng) 應(yīng)

主鍵列 應(yīng) 應(yīng)

頻繁修改索引列 不應(yīng) 應(yīng)

事實(shí)上介蛉，我們可以通過前面聚集索引和非聚集索引的定義的例子來理 解上表。如：返回某范圍內(nèi)的數(shù)據(jù)一項(xiàng)溶褪。比如您的某個(gè)表有一個(gè)時(shí)間列币旧，恰好您把聚合索引建立在了該列，這時(shí)您查詢2004年1月1日至2004年10月1日 之間的全部數(shù)據(jù)時(shí)猿妈，這個(gè)速度就將是很快的吹菱，因?yàn)槟倪@本字典正文是按日期進(jìn)行排序的，聚類索引只需要找到要檢索的所有數(shù)據(jù)中的開頭和結(jié)尾數(shù)據(jù)即可彭则；而不像 非聚集索引鳍刷，必須先查到目錄中查到每一項(xiàng)數(shù)據(jù)對(duì)應(yīng)的頁(yè)碼，然后再根據(jù)頁(yè)碼查到具體內(nèi)容俯抖。

（二）結(jié)合實(shí)際输瓜，談索引使用的誤區(qū)

理論的目的是應(yīng)用。雖然我們剛才列出了何時(shí)應(yīng)使用聚集索引或非聚集索引，但在實(shí)踐中以上規(guī)則卻很容易被忽視或不能根據(jù)實(shí)際情況進(jìn)行綜合分析尤揣。下面我們將根據(jù)在實(shí)踐中遇到的實(shí)際問題來談一下索引使用的誤區(qū)搔啊，以便于大家掌握索引建立的方法。

1北戏、主鍵就是聚集索引

這種想法筆者認(rèn)為是極端錯(cuò)誤的负芋，是對(duì)聚集索引的一種浪費(fèi)。雖然SQL SERVER默認(rèn)是在主鍵上建立聚集索引的嗜愈。

通常旧蛾，我們會(huì)在每個(gè)表中都建立一個(gè)ID列，以區(qū)分每條數(shù)據(jù)蠕嫁，并且這個(gè)ID列是自動(dòng)增大的锨天，步長(zhǎng)一般為1。我們的這個(gè)辦公自動(dòng)化的實(shí)例中的列Gid就是如此剃毒。此時(shí)绍绘，如果我們將這個(gè)列設(shè)為主鍵，SQL SERVER會(huì)將此列默認(rèn)為聚集索引迟赃。這樣做有好處陪拘，就是可以讓您的數(shù)據(jù)在數(shù)據(jù)庫(kù)中按照ID進(jìn)行物理排序，但筆者認(rèn)為這樣做意義不大纤壁。

顯而易見左刽，聚集索引的優(yōu)勢(shì)是很明顯的，而每個(gè)表中只能有一個(gè)聚集索引的規(guī)則酌媒，這使得聚集索引變得更加珍貴欠痴。

從我們前面談到的聚集索引的定義我們可以看出，使用聚集索引的最 大好處就是能夠根據(jù)查詢要求秒咨，迅速縮小查詢范圍喇辽，避免全表掃描。在實(shí)際應(yīng)用中雨席，因?yàn)镮D號(hào)是自動(dòng)生成的菩咨，我們并不知道每條記錄的ID號(hào)，所以我們很難在實(shí) 踐中用ID號(hào)來進(jìn)行查詢陡厘。這就使讓ID號(hào)這個(gè)主鍵作為聚集索引成為一種資源浪費(fèi)抽米。其次，讓每個(gè)ID號(hào)都不同的字段作為聚集索引也不符合“大數(shù)目的不同值情 況下不應(yīng)建立聚合索引”規(guī)則糙置；當(dāng)然云茸，這種情況只是針對(duì)用戶經(jīng)常修改記錄內(nèi)容，特別是索引項(xiàng)的時(shí)候會(huì)負(fù)作用谤饭，但對(duì)于查詢速度并沒有影響标捺。

在辦公自動(dòng)化系統(tǒng)中懊纳，無論是系統(tǒng)首頁(yè)顯示的需要用戶簽收的文件、會(huì)議還是用戶進(jìn)行文件查詢等任何情況下進(jìn)行數(shù)據(jù)查詢都離不開字段的是“日期”還有用戶本身的“用戶名”亡容。

通常嗤疯，辦公自動(dòng)化的首頁(yè)會(huì)顯示每個(gè)用戶尚未簽收的文件或會(huì)議。雖 然我們的where語(yǔ)句可以僅僅限制當(dāng)前用戶尚未簽收的情況萍倡，但如果您的系統(tǒng)已建立了很長(zhǎng)時(shí)間身弊，并且數(shù)據(jù)量很大辟汰，那么列敲，每次每個(gè)用戶打開首頁(yè)的時(shí)候都進(jìn)行 一次全表掃描，這樣做意義是不大的帖汞，絕大多數(shù)的用戶1個(gè)月前的文件都已經(jīng)瀏覽過了戴而，這樣做只能徒增數(shù)據(jù)庫(kù)的開銷而已。事實(shí)上翩蘸，我們完全可以讓用戶打開系統(tǒng) 首頁(yè)時(shí)所意，數(shù)據(jù)庫(kù)僅僅查詢這個(gè)用戶近3個(gè)月來未閱覽的文件，通過“日期”這個(gè)字段來限制表掃描催首，提高查詢速度扶踊。如果您的辦公自動(dòng)化系統(tǒng)已經(jīng)建立的2年，那么 您的首頁(yè)顯示速度理論上將是原來速度8倍郎任，甚至更快秧耗。

2、只要建立索引就能顯著提高查詢速度

事實(shí)上舶治，我們可以發(fā)現(xiàn)上面的例子中分井，第2、3條語(yǔ)句完全相同霉猛，且建立索引的字段也相同尺锚；不同的僅是前者在fariqi字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引惜浅，但查詢速度卻有著天壤之別瘫辩。所以，并非是在任何字段上簡(jiǎn)單地建立索引就能提高查詢速度坛悉。

從建表的語(yǔ)句中杭朱，我們可以看到這個(gè)有著1000萬數(shù)據(jù)的表中 fariqi字段有5003個(gè)不同記錄。在此字段上建立聚合索引是再合適不過了吹散。在現(xiàn)實(shí)中弧械，我們每天都會(huì)發(fā)幾個(gè)文件，這幾個(gè)文件的發(fā)文日期就相同空民，這完全 符合建立聚集索引要求的：“既不能絕大多數(shù)都相同刃唐，又不能只有極少數(shù)相同”的規(guī)則羞迷。由此看來，我們建立“適當(dāng)”的聚合索引對(duì)于我們提高查詢速度是非常重要 的画饥。

3衔瓮、把所有需要提高查詢速度的字段都加進(jìn)聚集索引，以提高查詢速度

上面已經(jīng)談到：在進(jìn)行數(shù)據(jù)查詢時(shí)都離不開字段的是“日期”還有用戶本身的“用戶名”抖甘。既然這兩個(gè)字段都是如此的重要热鞍，我們可以把他們合并起來，建立一個(gè)復(fù)合索引（compound index）衔彻。

很多人認(rèn)為只要把任何字段加進(jìn)聚集索引薇宠，就能提高查詢速度，也有 人感到迷惑：如果把復(fù)合的聚集索引字段分開查詢艰额，那么查詢速度會(huì)減慢嗎澄港？帶著這個(gè)問題，我們來看一下以下的查詢速度（結(jié)果集都是25萬條數(shù)據(jù)）：（日期列 fariqi首先排在復(fù)合聚集索引的起始列柄沮，用戶名neibuyonghu排在后列）

我們可以看到如果僅用聚集索引的起始列作為查詢條件和同時(shí)用到復(fù) 合聚集索引的全部列的查詢速度是幾乎一樣的回梧，甚至比用上全部的復(fù)合索引列還要略快（在查詢結(jié)果集數(shù)目一樣的情況下）；而如果僅用復(fù)合聚集索引的非起始列作 為查詢條件的話祖搓，這個(gè)索引是不起任何作用的狱意。當(dāng)然，語(yǔ)句1拯欧、2的查詢速度一樣是因?yàn)椴樵兊臈l目數(shù)一樣详囤，如果復(fù)合索引的所有列都用上，而且查詢結(jié)果少的話哈扮， 這樣就會(huì)形成“索引覆蓋”纬纪，因而性能可以達(dá)到最優(yōu)。同時(shí)滑肉，請(qǐng)記装鳌：無論您是否經(jīng)常使用聚合索引的其他列，但其前導(dǎo)列一定要是使用最頻繁的列靶庙。

（三）其他注意事項(xiàng)

“水可載舟问畅，亦可覆舟”，索引也一樣六荒。索引有助于提高檢索性能护姆，但過多或不當(dāng)?shù)乃饕矔?huì)導(dǎo)致系統(tǒng)低效。因?yàn)橛脩粼诒碇忻考舆M(jìn)一個(gè)索引掏击，數(shù)據(jù)庫(kù)就要做更多的工作卵皂。過多的索引甚至?xí)?dǎo)致索引碎片。

所以說砚亭，我們要建立一個(gè)“適當(dāng)”的索引體系灯变，特別是對(duì)聚合索引的創(chuàng)建殴玛，更應(yīng)精益求精，以使您的數(shù)據(jù)庫(kù)能得到高性能的發(fā)揮