優(yōu)化SQL Server數(shù)據(jù)庫(kù)的經(jīng)驗(yàn)總結(jié)

優(yōu)化SQLServer數(shù)據(jù)庫(kù)的經(jīng)驗(yàn)總結(jié)

下面主要向大家介紹的是正確優(yōu)化SQLServer數(shù)據(jù)庫(kù)的經(jīng)驗(yàn)總結(jié)，其中包括在對(duì)其進(jìn)行優(yōu)化的實(shí)際操作中值得大家注意的地方描述，以及對(duì)SQL語(yǔ)句進(jìn)行優(yōu)化的最基本原則，以下就是文章的主要內(nèi)容描述。優(yōu)化數(shù)據(jù)庫(kù)的注意事項(xiàng)：1、關(guān)鍵字段建立索引。

2、使用存儲(chǔ)過程，它使SQL變得更加靈活和高效。3、備份數(shù)據(jù)庫(kù)和清除垃圾數(shù)據(jù)。

4、SQL語(yǔ)句語(yǔ)法的優(yōu)化。(可以用Sybase的SQLExpert，可惜我沒找到unexpired的序列號(hào))

5、清理刪除日志。

SQL語(yǔ)句優(yōu)化的基本原則：1、使用索引來更快地遍歷表。

缺省情況下建立的索引是非群集索引，但有時(shí)它并不是最佳的。在非群集索引下，數(shù)據(jù)在物理上隨機(jī)存放在數(shù)據(jù)頁(yè)上。合理的索引設(shè)計(jì)要建立在對(duì)各種查詢的分析和預(yù)測(cè)上。一般來說：

①.有大量重復(fù)值、且經(jīng)常有范圍查詢(between,>,<，>=,<=)和orderby、groupby發(fā)生的列，可考慮建立群集索引

②.經(jīng)常同時(shí)存取多列，且每列都含有重復(fù)值可考慮建立組合索引;

③.組合索引要盡量使關(guān)鍵查詢形成索引覆蓋，其前導(dǎo)列一定是使用最頻繁的列。2、ISNULL與ISNOTNULL

不能用null作索引，任何包含null值的列都將不會(huì)被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會(huì)從索引中排除。也就是說如果某列存在空值，即使對(duì)該列建索引也不會(huì)提高性能。任何在where子句中使用isnull或isnotnull的語(yǔ)句優(yōu)化器是不允許使用索引的。3、IN和EXISTS

EXISTS要遠(yuǎn)比IN的效率高。里面關(guān)系到fulltablescan和rangescan。幾乎將所有的IN操作符子查詢改寫為使用EXISTS的子查詢。4、在海量查詢時(shí)盡量少用格式轉(zhuǎn)換。5、當(dāng)在SQLSERVER201*中

如果存儲(chǔ)過程只有一個(gè)參數(shù)，并且是OUTPUT類型的，必須在調(diào)用這個(gè)存儲(chǔ)過程的時(shí)候給這個(gè)參數(shù)一個(gè)初始的值，否則會(huì)出現(xiàn)調(diào)用錯(cuò)誤。6、ORDERBY和GROPUBY

使用ORDERBY和GROUPBY短語(yǔ)，任何一種索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值，Optimizer將無法優(yōu)化。

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

8、IN、OR子句常會(huì)使用工作表，使索引失效。如果不產(chǎn)生大量重復(fù)值，可以考慮把子句拆開。拆開的子句中應(yīng)該包含索引。

9、SETSHOWPLAN_ALL>10、謹(jǐn)慎使用游標(biāo)在某些必須使用游標(biāo)的場(chǎng)合，可考慮將符合條件的數(shù)據(jù)行轉(zhuǎn)入臨時(shí)表中，再對(duì)臨時(shí)表定義游標(biāo)進(jìn)行操作，這樣可使性能得到明顯提高。

注釋：所謂的優(yōu)化就是WHERE子句利用了索引，不可優(yōu)化即發(fā)生了表掃描或額外開銷。經(jīng)驗(yàn)顯示，SQLServer數(shù)據(jù)庫(kù)性能的最大改進(jìn)得益于邏輯的數(shù)據(jù)庫(kù)設(shè)計(jì)、索引設(shè)計(jì)和查詢?cè)O(shè)計(jì)方面。反過來說，最大的性能問題常常是由其中這些相同方面中的不足引起的。其實(shí)SQL優(yōu)化的實(shí)質(zhì)就是在結(jié)果正確的前提下，用優(yōu)化器可以識(shí)別的語(yǔ)句，充份利用索引，減少表掃描的I/O次數(shù)，盡量避免表搜索的發(fā)生。其實(shí)SQL的性能優(yōu)化是一個(gè)復(fù)雜的過程，上述這些只是在應(yīng)用層次的一種體現(xiàn)，深入研究還會(huì)涉及SQLServer數(shù)據(jù)庫(kù)層的資源配置、網(wǎng)絡(luò)層的流量控制以及操作系統(tǒng)層的總體設(shè)計(jì)。

擴(kuò)展閱讀：SQL Server 201* 一千萬條以上記錄分頁(yè)數(shù)據(jù)庫(kù)優(yōu)化經(jīng)驗(yàn)總結(jié)

SQLServer201*一千萬條以上記錄分頁(yè)數(shù)據(jù)庫(kù)優(yōu)化經(jīng)驗(yàn)總結(jié)

對(duì)普通開發(fā)人員來說經(jīng)常能接觸到上千萬條數(shù)據(jù)優(yōu)化的機(jī)會(huì)也不是很多，這里還是要感謝公司提供了這樣的一個(gè)環(huán)境，而且公司讓我來做優(yōu)化工作。當(dāng)數(shù)據(jù)庫(kù)中的記錄不超過10萬條時(shí)，很難分辨出開發(fā)人員的水平有多高，當(dāng)數(shù)據(jù)庫(kù)中的記錄條數(shù)超過1000萬條后，還是蠻能考驗(yàn)開發(fā)人員的綜合技術(shù)能力。當(dāng)然不是每個(gè)公司都能請(qǐng)得起專業(yè)的DBA，話又說過來專業(yè)的DBA也未必能來我們公司長(zhǎng)期工作，這就不只是薪資待遇問題了還會(huì)涉及到人家的長(zhǎng)期發(fā)展規(guī)劃了，當(dāng)然我也不是專業(yè)的DBA，本著能把問題解決好就是好貓的理念。

我們先看圖，數(shù)據(jù)庫(kù)中的記錄數(shù)如下：記錄數(shù)為10581490條同時(shí)還需要從另外一個(gè)表讀取7萬多條數(shù)據(jù)。

頁(yè)面運(yùn)行效果如下：這是查看某個(gè)單位的數(shù)據(jù)，每頁(yè)顯示16條、記錄數(shù)1087292條、分頁(yè)數(shù)為67956頁(yè)。

遇到的難題如下：

1：當(dāng)客戶用了幾年后數(shù)據(jù)變得很龐大分頁(yè)速度緩慢得要命幾乎到了無法忍受的程度。

2：分頁(yè)到最后一頁(yè)時(shí)往往速度很慢會(huì)有死機(jī)現(xiàn)象出現(xiàn)，特別是記錄條數(shù)很多時(shí)死機(jī)現(xiàn)象比較多。

那再講講，解決問題的方法步驟：

1：首先優(yōu)化數(shù)據(jù)庫(kù)、因?yàn)槌绦蛞埠軓?fù)雜一時(shí)也看不過來也不敢亂改，先從數(shù)據(jù)庫(kù)字段類型優(yōu)化開始入手會(huì)好很多。

先把數(shù)據(jù)庫(kù)里的datetime都修改為smalldatetime，數(shù)據(jù)庫(kù)變小了幾百M(fèi)很有成就感，最起碼磁盤的讀取壓力減少不少吧。由于數(shù)據(jù)庫(kù)數(shù)據(jù)有上千萬條，無法用管理工具修改結(jié)構(gòu)，只能用新建查詢執(zhí)行SQL命令才可以。

會(huì)有如下超時(shí)現(xiàn)象會(huì)發(fā)生。

那我們只能用執(zhí)行查詢的方式對(duì)表結(jié)構(gòu)進(jìn)行調(diào)整了，每次執(zhí)行一個(gè)SQL指令大概需要10分鐘時(shí)間才能順利執(zhí)行好，數(shù)據(jù)量實(shí)在是太大了。

2：接著再優(yōu)化，數(shù)據(jù)庫(kù)索引，原先的索引很亂可以理解為是亂來的所以我全部干掉重新進(jìn)行了組織。把多余的索引先通通干掉，然后重新建立索引，因?yàn)橛涗洈?shù)太龐大了，有多余的索引會(huì)使數(shù)據(jù)庫(kù)變大很龐大，給他先減輕減輕體重。

把主鍵設(shè)置為倒序的、非聚集的，這樣的好處是可以把最新的數(shù)據(jù)排序在最前面。

把主要查詢的條件設(shè)置為索引，GroupBy的放第一個(gè)位置然后設(shè)置為聚集索引，這樣的好處時(shí)查詢時(shí)會(huì)快很多很多，普通所以沒這個(gè)效率高，數(shù)據(jù)實(shí)在是太龐大了，超過了1000萬條數(shù)據(jù)后，對(duì)比一下還是很明顯的，都能感覺得到。

完成以上2個(gè)步驟后分頁(yè)速度快了很多最起碼沒死機(jī)現(xiàn)象了，還有一點(diǎn)遺憾是當(dāng)數(shù)據(jù)量大時(shí)最后一頁(yè)的分頁(yè)速度還是有些慢，有些難以忍受的感覺，但是最起碼不會(huì)死機(jī)了。

3：接著重點(diǎn)優(yōu)化，數(shù)據(jù)庫(kù)分頁(yè)的存儲(chǔ)過程，最后一頁(yè)難以忍受的問題先解決一下。分頁(yè)是用了SELECTTOPN的反轉(zhuǎn)的方式，我把最后一頁(yè)到底獲取多少條記錄準(zhǔn)確數(shù)字計(jì)算出來，適當(dāng)?shù)男薷牧艘幌伦詈笠豁?yè)慢得死去活來的問題，得到了適當(dāng)?shù)沫h(huán)節(jié)，雖然沒能徹底解決也速度明顯快了一些，由于寫的這個(gè)分頁(yè)程序也有些復(fù)雜，我也不敢亂動(dòng)，就把問題解決好就完事大吉的目的了，不去惹更多的麻煩了。

4：對(duì)比一下數(shù)據(jù)庫(kù)結(jié)構(gòu)優(yōu)化后的前后如下圖索引優(yōu)化前索引占用空間2706.109M

索引優(yōu)化后索引占用空間520.805M

我想就這么一個(gè)1000w條記錄的表光索引就優(yōu)化了2200M空間，就單單這個(gè)也提高不少性能了。

5：接著重點(diǎn)優(yōu)化，程序代碼部分了，其實(shí)代碼優(yōu)化是在索引優(yōu)化之前的，因?yàn)橄茸x懂了代碼、讀懂了業(yè)務(wù)邏輯才好優(yōu)化索引，這邊文章寫著寫著順序有些顛倒了，大家心里有數(shù)就可以了，我還是按照我的思路繼續(xù)寫吧。

在上圖的企業(yè)編號(hào)、企業(yè)名稱等，在程序里都進(jìn)行了LIKE處理，當(dāng)數(shù)據(jù)庫(kù)記錄超過1000萬條時(shí)，對(duì)字符進(jìn)行Like操作，那真是會(huì)要命的，畢竟那么多數(shù)據(jù)都進(jìn)行一次匹配，雖然電腦的運(yùn)算速度很快，但是上千萬條記錄，這么被計(jì)算過一下，能快到哪里去��？改進(jìn)方法：

A:輸入企業(yè)編號(hào)、企業(yè)名稱修改為模糊查詢，能明確定位一個(gè)藥店的名稱。

B:若已經(jīng)獲得企業(yè)編號(hào)了，不再匹配企業(yè)名稱，而且企業(yè)編號(hào)用=來判斷，并把企業(yè)編號(hào)進(jìn)行索引。

海量數(shù)據(jù)庫(kù)分頁(yè)優(yōu)化總結(jié)：

折騰了接近1周左右，終于把這個(gè)1千多萬條記錄的數(shù)據(jù)表給優(yōu)化好了，難題也解決好了雖然不太科學(xué)也不專業(yè)也缺少理論依據(jù)、試驗(yàn)數(shù)據(jù)、圖表對(duì)比、性能調(diào)試工具等等，但是還好把問題都解決好了，老鼠抓到了就是好貓咪了哈哈。數(shù)據(jù)庫(kù)進(jìn)行了徹底的翻天覆地的優(yōu)化、程序代碼也進(jìn)行了徹底的翻天覆地的優(yōu)化后，分頁(yè)速度飛快了。每頁(yè)顯示16條、記錄數(shù)1087292條、分頁(yè)數(shù)為67956頁(yè)，每頁(yè)分頁(yè)速度都完全在3秒內(nèi)，最后一頁(yè)也不會(huì)死機(jī)了，也蠻快的足夠可以忍受了。

等有空時(shí)，再把最后一頁(yè)分頁(yè)速度慢的問題再深入解決一下，先不去惹麻煩了稍微休息一下再說。

優(yōu)化的每個(gè)動(dòng)作需要10分鐘左右才會(huì)執(zhí)行好，若做錯(cuò)一次基本上就代表半個(gè)小時(shí)白忙乎了，還需要?jiǎng)h除掉，再重新執(zhí)行修正過的SQL語(yǔ)句，所以一天下來優(yōu)化的成果并不會(huì)非常明顯、需要幾天時(shí)間才能優(yōu)化好。

將權(quán)限管理、工作流管理做到我能力的極致，一個(gè)人只能做好那么很少的幾件事情。

友情提示：本文中關(guān)于《優(yōu)化SQL Server數(shù)據(jù)庫(kù)的經(jīng)驗(yàn)總結(jié)》給出的范例僅供您參考拓展思維使用，優(yōu)化SQL Server數(shù)據(jù)庫(kù)的經(jīng)驗(yàn)總結(jié)：該篇文章建議您自主創(chuàng)作。

來源：網(wǎng)絡(luò)整理 免責(zé)聲明：本文僅限學(xué)習(xí)分享，如產(chǎn)生版權(quán)問題，請(qǐng)聯(lián)系我們及時(shí)刪除。

《優(yōu)化SQL Server數(shù)據(jù)庫(kù)的經(jīng)驗(yàn)總結(jié)》由互聯(lián)網(wǎng)用戶整理提供,轉(zhuǎn)載分享請(qǐng)保留原作者信息,謝謝!
鏈接地址：http://www.weilaioem.com/gongwen/672274.html

• 上一篇：SQL- VB實(shí)訓(xùn)總結(jié)
• 下一篇：SQL數(shù)據(jù)庫(kù)知識(shí)總結(jié)