sqlserver優化

『壹』 sql Server鏁版嵁搴撶殑楂樻ц兘浼樺寲緇忛獙鎬葷粨

鏈鏂囦富瑕佸悜澶у朵粙緇嶇殑鏄姝ｇ『浼樺寲SQL
Server鏁版嵁搴撶殑緇忛獙鎬葷粨錛屽叾涓鍖呮嫭鍦ㄥ瑰叾榪涜屼紭鍖栫殑瀹為檯鎿嶄綔涓鍊煎緱澶у舵敞鎰忕殑鍦版柟鎻忚堪錛屼互鍙婂筍QL璇鍙ヨ繘琛屼紭鍖栫殑鏈鍩烘湰鍘熷垯錛屼互涓嬪氨鏄鏂囩珷鐨勪富瑕佸唴瀹規弿榪般
銆銆浼樺寲鏁版嵁搴撶殑娉ㄦ剰浜嬮」錛
銆銆1銆佸叧閿瀛楁靛緩絝嬬儲寮曘
銆銆2銆佷嬌鐢ㄥ瓨鍌ㄨ繃紼嬶紝瀹冧嬌SQL鍙樺緱鏇村姞鐏墊椿鍜岄珮鏁堛
銆銆3銆佸囦喚鏁版嵁搴撳拰娓呴櫎鍨冨溇鏁版嵁銆
銆銆4銆丼QL璇鍙ヨ娉曠殑浼樺寲銆(鍙浠ョ敤Sybase鐨凷QL
Expert錛屽彲鎯滄垜娌℃壘鍒皍nexpired鐨勫簭鍒楀彿)
銆銆5銆佹竻鐞嗗垹闄ゆ棩蹇椼
銆銆SQL璇鍙ヤ紭鍖栫殑鍩烘湰鍘熷垯錛
銆銆1銆佷嬌鐢ㄧ儲寮曟潵鏇村揩鍦伴亶鍘嗚〃銆
銆銆緙虹渷鎯呭喌涓嬪緩絝嬬殑緔㈠紩鏄闈炵兢闆嗙儲寮曪紝浣嗘湁鏃跺畠騫朵笉鏄鏈浣崇殑銆傚湪闈炵兢闆嗙儲寮曚笅錛屾暟鎹鍦ㄧ墿鐞嗕笂闅忔満瀛樻斁鍦ㄦ暟鎹欏典笂銆傚悎鐞嗙殑緔㈠紩璁捐¤佸緩絝嬪湪瀵瑰悇縐嶆煡璇㈢殑鍒嗘瀽鍜岄勬祴涓娿
銆銆涓鑸鏉ヨ達細
銆銆鈶.鏈夊ぇ閲忛噸澶嶅箋佷笖緇忓父鏈夎寖鍥存煡璇(between,
>,<
錛>=,<
=)鍜宱rder
by銆乬roup
by鍙戠敓鐨勫垪錛屽彲鑰冭檻寤虹珛緹ら泦緔㈠紩
銆銆鈶.緇忓父鍚屾椂瀛樺彇澶氬垪錛屼笖姣忓垪閮藉惈鏈夐噸澶嶅煎彲鑰冭檻寤虹珛緇勫悎緔㈠紩;
銆銆鈶.緇勫悎緔㈠紩瑕佸敖閲忎嬌鍏抽敭鏌ヨ㈠艦鎴愮儲寮曡嗙洊錛屽叾鍓嶅煎垪涓瀹氭槸浣跨敤鏈棰戠箒鐨勫垪銆
銆銆2銆両S
NULL
涓
IS
NOT
NULL
銆銆涓嶈兘鐢╪ull浣滅儲寮曪紝浠諱綍鍖呭惈null鍊肩殑鍒楅兘灝嗕笉浼氳鍖呭惈鍦ㄧ儲寮曚腑銆傚嵆浣跨儲寮曟湁澶氬垪榪欐牱鐨勬儏鍐典笅錛屽彧瑕佽繖浜涘垪涓鏈変竴鍒楀惈鏈塶ull錛岃ュ垪灝變細浠庣儲寮曚腑鎺掗櫎銆備篃灝辨槸璇村傛灉鏌愬垪瀛樺湪絀哄礆紝鍗充嬌瀵硅ュ垪寤虹儲寮曚篃涓嶄細鎻愰珮鎬ц兘銆備換浣曞湪where瀛愬彞涓浣跨敤is
null鎴杋s
not
null鐨勮鍙ヤ紭鍖栧櫒鏄涓嶅厑璁鎬嬌鐢ㄧ儲寮曠殑銆
銆銆3銆両N鍜孍XISTS
銆銆EXISTS瑕佽繙姣擨N鐨勬晥鐜囬珮銆傞噷闈㈠叧緋誨埌full
table
scan鍜宺ange
scan銆傚嚑涔庡皢鎵鏈夌殑IN鎿嶄綔絎﹀瓙鏌ヨ㈡敼鍐欎負浣跨敤EXISTS鐨勫瓙鏌ヨ銆
銆銆4銆佸湪嫻烽噺鏌ヨ㈡椂灝介噺灝戠敤鏍煎紡杞鎹銆
銆銆5銆佸綋鍦⊿QL
SERVER
2000涓
銆銆濡傛灉瀛樺偍榪囩▼鍙鏈変竴涓鍙傛暟錛屽苟涓旀槸OUTPUT綾誨瀷鐨勶紝蹇呴』鍦ㄨ皟鐢ㄨ繖涓瀛樺偍榪囩▼鐨勬椂鍊欑粰榪欎釜鍙傛暟涓涓鍒濆嬬殑鍊礆紝鍚﹀垯浼氬嚭鐜拌皟鐢ㄩ敊璇銆
銆銆6銆丱RDER
BY鍜孏ROPU
BY
銆銆浣跨敤ORDER
BY鍜孏ROUP
BY鐭璇錛屼換浣曚竴縐嶇儲寮曢兘鏈夊姪浜嶴ELECT鐨勬ц兘鎻愰珮銆傛敞鎰忓傛灉緔㈠紩鍒楅噷闈㈡湁NULL鍊礆紝Optimizer灝嗘棤娉曚紭鍖栥
銆銆7銆佷換浣曞瑰垪鐨勬搷浣滈兘灝嗗艱嚧琛ㄦ壂鎻忥紝瀹冨寘鎷琒QL
Server鏁版嵁搴撳嚱鏁般佽＄畻琛ㄨ揪寮忕瓑絳夛紝鏌ヨ㈡椂瑕佸敖鍙鑳藉皢鎿嶄綔縐昏嚦絳夊彿鍙寵竟銆
銆銆8銆両N銆丱R瀛愬彞甯鎬細浣跨敤宸ヤ綔琛錛屼嬌緔㈠紩澶辨晥銆傚傛灉涓嶄駭鐢熷ぇ閲忛噸澶嶅礆紝鍙浠ヨ冭檻鎶婂瓙鍙ユ媶寮銆傛媶寮鐨勫瓙鍙ヤ腑搴旇ュ寘鍚緔㈠紩銆
銆銆9銆丼ET
SHOWPLAN_ALL>10銆佽皚鎱庝嬌鐢ㄦ父鏍
銆銆鍦ㄦ煇浜涘繀欏諱嬌鐢ㄦ父鏍囩殑鍦哄悎錛屽彲鑰冭檻灝嗙﹀悎鏉′歡鐨勬暟鎹琛岃漿鍏ヤ復鏃惰〃涓錛屽啀瀵逛復鏃惰〃瀹氫箟娓告爣榪涜屾搷浣滐紝榪欐牱鍙浣挎ц兘寰楀埌鏄庢樉鎻愰珮銆
銆銆娉ㄩ噴錛氭墍璋撶殑浼樺寲灝辨槸WHERE瀛愬彞鍒╃敤浜嗙儲寮曪紝涓嶅彲浼樺寲鍗沖彂鐢熶簡琛ㄦ壂鎻忔垨棰濆栧紑閿銆傜粡楠屾樉紺猴紝SQL
Server鏁版嵁搴撴ц兘鐨勬渶澶ф敼榪涘緱鐩婁簬閫昏緫鐨勬暟鎹搴撹捐°佺儲寮曡捐″拰鏌ヨ㈣捐℃柟闈銆傚弽榪囨潵璇達紝鏈澶х殑鎬ц兘闂棰樺父甯告槸鐢卞叾涓榪欎簺鐩稿悓鏂歸潰涓鐨勪笉瓚沖紩璧風殑銆
銆銆鍏跺疄SQL浼樺寲鐨勫疄璐ㄥ氨鏄鍦ㄧ粨鏋滄ｇ『鐨勫墠鎻愪笅錛岀敤浼樺寲鍣ㄥ彲浠ヨ瘑鍒鐨勮鍙ワ紝鍏呬喚鍒╃敤緔㈠紩錛屽噺灝戣〃鎵鎻忕殑I/O嬈℃暟錛屽敖閲忛伩鍏嶈〃鎼滅儲鐨勫彂鐢熴傚叾瀹濻QL鐨勬ц兘浼樺寲鏄涓涓澶嶆潅鐨勮繃紼嬶紝涓婅堪榪欎簺鍙鏄鍦ㄥ簲鐢ㄥ眰嬈＄殑涓縐嶄綋鐜幫紝娣卞叆鐮旂┒榪樹細娑夊強SQL
Server鏁版嵁搴撳眰鐨勮祫婧愰厤緗銆佺綉緇滃眰鐨勬祦閲忔帶鍒朵互鍙婃搷浣滅郴緇熷眰鐨勬諱綋璁捐°

『貳』 win7緋葷粺SQLServer杞浠跺崰鐢ㄥぇ閲忓唴瀛樼┖闂存庝箞鍔

緇嗗績鐢ㄦ埛鎵撳紑win7緋葷粺浠誨姟綆＄悊鍣錛屽彂鐜癝QLServerCPU鍗犵敤澶ч噺鍐呭瓨絀洪棿錛屽艱嚧鐢佃剳榪愯岄熷害鍙樻參錛屼弗閲嶈繕鍑虹幇鍗℃満鐜拌薄錛屾庝箞鍥炰簨鍛錛熶竴鑸鏄鍥犱負鏌ヨ㈡暟鎹閲忓ぇ錛屾墽琛屾椂闂撮暱閫犳垚鐨勩傞偅涔堟湁浠涔堝姙娉曡В鍐硍in7緋葷粺SQLServer杞浠跺崰鐢ㄥぇ閲忓唴瀛樼┖闂寸殑闂棰橈紝涓嶈佺潃鎬ワ紝鎺ヤ笅鍘誨皬緙栧憡璇夊ぇ瀹跺叿浣撹В鍐蟲柟娉曘
鎺ㄨ崘錛闆ㄦ灄鏈ㄩ巜in7鏃楄埌鐗堢郴緇熶笅杞
鍏蜂綋鏂規硶濡備笅:
1銆佹墦寮SQLServerProfiler錛
2銆佹柊寤鴻窡韙
a.鐐瑰嚮鏂板緩璺熻釜錛屽苟璁劇疆濂芥暟鎹搴撹繛鎺
b.璁劇疆璺熻釜灞炴э紝閫夋嫨妯℃澘鈥淪tandard鈥
c.鍒囧埌鈥滀簨浠墮夋嫨鈥濊繘琛岃窡韙璁劇疆
1銆佸彧淇濈暀濡備笅涓や釜浜嬩歡閫夐」
2銆佺偣鍑誨垪絳涢
3銆佽繘琛岃︾粏絳涢夎劇疆
璁劇疆CPU鏃墮棿浣滀負絳涢夋潯浠訛紝鍗曚綅姣縐掞紙鐢ㄤ簬璺熻釜鑰桟PU鍗犵敤杈冮暱鐨勬煡璇錛屽彲璁劇疆涓哄ぇ浜庣瓑浜20000錛屾寜CPU鍐呮牳鏁懊1000錛屽彲浠ヨ窡韙狢PU鍗犵敤100%澶т簬1縐掔殑鏌ヨ錛
3銆佺偣鍑昏繍琛岋紝璺熻釜璇鍙ワ紝瀹氫綅CPU鍗犵敤杈冨氱殑璇璦錛屽備笅鍥炬墍紺猴紝CPU鍗犵敤2660146姣縐掞紝CPU涓24鏍革紝鍒欒嚦灝慍PU鍗犵敤100%鑰楁椂2660146/24/1000=110縐掓墠鍙浠ユ墽琛屽ソ鐩稿簲鐨勬搷浣滐紱
4銆佹牴鎹璇鍙ョ壒寰侊紝鍦ㄤ綘鐨勬湇鍔″櫒紼嬪簭涓鎵懼埌鐩稿簲鐨勫姛鑳斤紝浣滃嚭淇姝ｏ紱
鍒嗘瀽鏌ヨ涓闇瑕佹緔㈡暟鎹閲忚緝澶х殑閮ㄥ垎錛屼綔鍑虹畝鍗曚慨姝(濡傛敞閿)
鏇存柊鍚庨噸鏂版墽琛屾ゆ煡璇錛屾煡鐪媝rofiler涓鏄鍚CPU鍗犵敤娑堝け
濡傛灉宸叉秷澶辮存槑闂棰樺畾浣嶆ｇ『錛屽彲浠ヤ紭鍖栨煡璇錛岃嫢CPU鍗犵敤浠葷劧寰堝氾紝鍒欏洖婊氫慨鏀癸紝緇х畫4.1鎿嶄綔
涓婅堪浠嬬粛鍏充簬win7緋葷粺SQLServer杞浠跺崰鐢ㄥぇ閲忓唴瀛樼┖闂寸殑瑙ｅ喅鏂規硶錛屾湁闇瑕佺殑鐢ㄦ埛鍙浠ュ弬鑰冧嬌鐢錛屽笇鏈涙湰鏁欑▼鍙浠ュ府鍔╁埌澶у訛紒

『叄』 如何利用索引提高SQLServer數據處理的效率

在良好的資料庫設計基礎上，能有效地使用索引是SQL Server取得高性能的基礎，SQL Server採用基於代價的優化模型，它對每一個提交的有關表的查詢，決定是否使用索引或用哪一個索引。因為查詢執行的大部分開銷是磁碟I/O，使用索引提高性能的一個主要目標是避免全表掃描，因為全表掃描需要從磁碟上讀表的每一個數據頁，如果有索引指向數據值，則查詢只需讀幾次磁碟就可以了。
所以如果建立了合理的索引，優化器就能利用索引加速數據的查詢過程。但是，索引並不總是提高系統的性能，在增、刪、改操作中索引的存在會增加一定的工作量，因此，在適當的地方增加適當的索引並從不合理的地方刪除次優的索引，將有助於優化那些性能較差的SQL Server應用。實踐表明，合理的索引設計是建立在對各種查詢的分析和預測上的，只有正確地使索引與程序結合起來,才能產生最佳的優化方案。本文就SQL Server索引的性能問題進行了一些分析和實踐。
一、聚簇索引(clustered indexes)的使用
聚簇索引是一種對磁碟上實際數據重新組織以按指定的一個或多個列的值排序。由於聚簇索引的索引頁面指針指向數據頁面，所以使用聚簇索引查找數據幾乎總是比使用非聚簇索引快。每張表只能建一個聚簇索引，並且建聚簇索引需要至少相當該表120%的附加空間，以存放該表的副本和索引中間頁。建立聚簇索引的思想是：
1、大多數表都應該有聚簇索引或使用分區來降低對表尾頁的競爭，在一個高事務的環境中，對最後一頁的封鎖嚴重影響系統的吞吐量。
2、在聚簇索引下，數據在物理上按順序排在數據頁上，重復值也排在一起，因而在那些包含范圍檢查(between、<、<=、>、>=)或使用group by或order by的查詢時，一旦找到具有范圍中第一個鍵值的行，具有後續索引值的行保證物理上毗連在一起而不必進一步搜索，避免了大范圍掃描，可以大大提高查詢速度。
3、在一個頻繁發生插入操作的表上建立聚簇索引時，不要建在具有單調上升值的列(如IDENTITY)上，否則會經常引起封鎖沖突。
4、在聚簇索引中不要包含經常修改的列，因為碼值修改後，數據行必須移動到新的位置。
5、選擇聚簇索引應基於where子句和連接操作的類型。
聚簇索引的侯選列是：
1、主鍵列,該列在where子句中使用並且插入是隨機的。
2、按范圍存取的列，如pri_order > 100 and pri_order < 200。
3、在group by或order by中使用的列。
4、不經常修改的列。
5、在連接操作中使用的列。
二、非聚簇索引(nonclustered indexes)的使用
SQL Server預設情況下建立的索引是非聚簇索引，由於非聚簇索引不重新組織表中的數據，而是對每一行存儲索引列值並用一個指針指向數據所在的頁面。換句話說非聚簇索引具有在索引結構和數據本身之間的一個額外級。一個表如果沒有聚簇索引時,可有250個非聚簇索引。每個非聚簇索引提供訪問數據的不同排序順序。在建立非聚簇索引時，要權衡索引對查詢速度的加快與降低修改速度之間的利弊。另外，還要考慮這些問題：
1、索引需要使用多少空間。
2、合適的列是否穩定。
3、索引鍵是如何選擇的，掃描效果是否更佳。
4、是否有許多重復值。
對更新頻繁的表來說，表上的非聚簇索引比聚簇索引和根本沒有索引需要更多的額外開銷。對移到新頁的每一行而言，指向該數據的每個非聚簇索引的頁級行也必須更新，有時可能還需要索引頁的分理。從一個頁面刪除數據的進程也會有類似的開銷，另外，刪除進程還必須把數據移到頁面上部，以保證數據的連續性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情況：
1、某列常用於集合函數(如Sum,....)。
2、某列常用於join,order by,group by。
3、查尋出的數據不超過表中數據量的20%。
三、覆蓋索引(covering indexes)的使用
覆蓋索引是指那些索引項中包含查尋所需要的全部信息的非聚簇索引，這種索引之所以比較快也正是因為索引頁中包含了查尋所必須的數據,不需去訪問數據頁。如果非聚簇索引中包含結果數據,那麼它的查詢速度將快於聚簇索引。
但是由於覆蓋索引的索引項比較多,要佔用比較大的空間。而且update操作會引起索引值改變。所以如果潛在的覆蓋查詢並不常用或不太關鍵，則覆蓋索引的增加反而會降低性能。
四、索引的選擇技術
p_detail是住房公積金管理系統中記錄個人明細的表，有890000行，觀察在不同索引下的查詢運行效果，測試在C/S環境下進行，客戶機是IBM PII350(內存64M),伺服器是DEC Alpha1000A(內存128M),資料庫為SYBASE11.0.3。
1、 select count(*) from p_detail where
op_date>』19990101』 and op_date<』
19991231』 and pri_surplus1>300
2、 select count(*),sum(pri_surplus1) from p_detail
where op_date>』19990101』 and
pay_month between『199908』 and』199912』
不建任何索引查詢1 1分15秒
查詢2 1分7秒
在op_date上建非聚簇索引查詢1 57秒
查詢2 57秒
在op_date上建聚簇索引查詢1 <1秒
查詢2 52秒
在pay_month、op_date、pri_surplus1上建索引查詢1 34秒
查詢2 <1秒
在op_date、pay_month、pri_surplus1上建索引查詢1 <1秒
查詢2 <1秒
從以上查詢效果分析，索引的有無，建立方式的不同將會導致不同的查詢效果，選擇什麼樣的索引基於用戶對數據的查詢條件,這些條件體現於where從句和join表達式中。一般來說建立索引的思路是：
(1)主鍵時常作為where子句的條件，應在表的主鍵列上建立聚簇索引，尤其當經常用它作為連接的時候。
(2)有大量重復值且經常有范圍查詢和排序、分組發生的列，或者非常頻繁地被訪問的列，可考慮建立聚簇索引。
(3)經常同時存取多列，且每列都含有重復值可考慮建立復合索引來覆蓋一個或一組查詢，並把查詢引用最頻繁的列作為前導列，如果可能盡量使關鍵查詢形成覆蓋查詢。
(4)如果知道索引鍵的所有值都是唯一的，那麼確保把索引定義成唯一索引。
(5)在一個經常做插入操作的表上建索引時，使用fillfactor(填充因子)來減少頁分裂，同時提高並發度降低死鎖的發生。如果在只讀表上建索引，則可以把fillfactor置為100。
(6)在選擇索引鍵時，設法選擇那些採用小數據類型的列作為鍵以使每個索引頁能夠容納盡可能多的索引鍵和指針，通過這種方式，可使一個查詢必須遍歷的索引頁面降到最小。此外，盡可能地使用整數為鍵值，因為它能夠提供比任何數據類型都快的訪問速度。
五、索引的維護
上面講到,某些不合適的索引影響到SQL Server的性能,隨著應用系統的運行,數據不斷地發生變化,當數據變化達到某一個程度時將會影響到索引的使用。這時需要用戶自己來維護索引。索引的維護包括：
1、重建索引
隨著數據行的插入、刪除和數據頁的分裂，有些索引頁可能只包含幾頁數據，另外應用在執行大塊I/O的時候，重建非聚簇索引可以降低分片，維護大塊I/O的效率。重建索引實際上是重新組織B-樹空間。在下面情況下需要重建索引：
(1)數據和使用模式大幅度變化。
(2)排序的順序發生改變。
(3)要進行大量插入操作或已經完成。
(4)使用大塊I/O的查詢的磁碟讀次數比預料的要多。
(5)由於大量數據修改，使得數據頁和索引頁沒有充分使用而導致空間的使用超出估算。
(6)dbcc檢查出索引有問題。
當重建聚簇索引時，這張表的所有非聚簇索引將被重建。
2、索引統計信息的更新
當在一個包含數據的表上創建索引的時候，SQL Server會創建分布數據頁來存放有關索引的兩種統計信息：分布表和密度表。優化器利用這個頁來判斷該索引對某個特定查詢是否有用。但這個統計信息並不動態地重新計算。這意味著，當表的數據改變之後，統計信息有可能是過時的，從而影響優化器追求最有工作的目標。因此，在下面情況下應該運行update statistics命令：
(1)數據行的插入和刪除修改了數據的分布。
(2)對用truncate table刪除數據的表上增加數據行。
(3)修改索引列的值。
六、結束語
實踐表明，不恰當的索引不但於事無補，反而會降低系統的執行性能。因為大量的索引在插入、修改和刪除操作時比沒有索引花費更多的系統時間。例如下面情況下建立的索引是不恰當的：
1、在查詢中很少或從不引用的列不會受益於索引，因為索引很少或從來不必搜索基於這些列的行。
2、只有兩個或三個值的列，如男性和女性(是或否)，從不會從索引中得到好處。
另外，鑒於索引加快了查詢速度，但減慢了數據更新速度的特點。可通過在一個段上建表，而在另一個段上建其非聚簇索引，而這兩段分別在單獨的物理設備上來改善操作性能。

『肆』 怎樣進行sql資料庫的優化

1、資料庫空間是個概述，在sqlserver里，使用語句 exec sp_spaceused 'TableName' 這個語句來查。