優(yōu)化SQL Server數(shù)據(jù)庫的經驗總結
優(yōu)化SQLServer數(shù)據(jù)庫的經驗總結
下面主要向大家介紹的是正確優(yōu)化SQLServer數(shù)據(jù)庫的經驗總結,其中包括在對其進行優(yōu)化的實際操作中值得大家注意的地方描述,以及對SQL語句進行優(yōu)化的最基本原則,以下就是文章的主要內容描述。優(yōu)化數(shù)據(jù)庫的注意事項:1、關鍵字段建立索引。
2、使用存儲過程,它使SQL變得更加靈活和高效。3、備份數(shù)據(jù)庫和清除垃圾數(shù)據(jù)。
4、SQL語句語法的優(yōu)化。(可以用Sybase的SQLExpert,可惜我沒找到unexpired的序列號)
5、清理刪除日志。
SQL語句優(yōu)化的基本原則:1、使用索引來更快地遍歷表。
缺省情況下建立的索引是非群集索引,但有時它并不是最佳的。在非群集索引下,數(shù)據(jù)在物理上隨機存放在數(shù)據(jù)頁上。合理的索引設計要建立在對各種查詢的分析和預測上。一般來說:
①.有大量重復值、且經常有范圍查詢(between,>,<,>=,<=)和orderby、groupby發(fā)生的列,可考慮建立群集索引
②.經常同時存取多列,且每列都含有重復值可考慮建立組合索引;
③.組合索引要盡量使關鍵查詢形成索引覆蓋,其前導列一定是使用最頻繁的列。2、ISNULL與ISNOTNULL
不能用null作索引,任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下,只要這些列中有一列含有null,該列就會從索引中排除。也就是說如果某列存在空值,即使對該列建索引也不會提高性能。任何在where子句中使用isnull或isnotnull的語句優(yōu)化器是不允許使用索引的。3、IN和EXISTS
EXISTS要遠比IN的效率高。里面關系到fulltablescan和rangescan。幾乎將所有的IN操作符子查詢改寫為使用EXISTS的子查詢。4、在海量查詢時盡量少用格式轉換。5、當在SQLSERVER201*中
如果存儲過程只有一個參數(shù),并且是OUTPUT類型的,必須在調用這個存儲過程的時候給這個參數(shù)一個初始的值,否則會出現(xiàn)調用錯誤。6、ORDERBY和GROPUBY
使用ORDERBY和GROUPBY短語,任何一種索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值,Optimizer將無法優(yōu)化。
7、任何對列的操作都將導致表掃描,它包括SQLServer數(shù)據(jù)庫函數(shù)、計算表達式等等,查詢時要盡可能將操作移至等號右邊。
8、IN、OR子句常會使用工作表,使索引失效。如果不產生大量重復值,可以考慮把子句拆開。拆開的子句中應該包含索引。
9、SETSHOWPLAN_ALL>10、謹慎使用游標在某些必須使用游標的場合,可考慮將符合條件的數(shù)據(jù)行轉入臨時表中,再對臨時表定義游標進行操作,這樣可使性能得到明顯提高。
注釋:所謂的優(yōu)化就是WHERE子句利用了索引,不可優(yōu)化即發(fā)生了表掃描或額外開銷。經驗顯示,SQLServer數(shù)據(jù)庫性能的最大改進得益于邏輯的數(shù)據(jù)庫設計、索引設計和查詢設計方面。反過來說,最大的性能問題常常是由其中這些相同方面中的不足引起的。其實SQL優(yōu)化的實質就是在結果正確的前提下,用優(yōu)化器可以識別的語句,充份利用索引,減少表掃描的I/O次數(shù),盡量避免表搜索的發(fā)生。其實SQL的性能優(yōu)化是一個復雜的過程,上述這些只是在應用層次的一種體現(xiàn),深入研究還會涉及SQLServer數(shù)據(jù)庫層的資源配置、網絡層的流量控制以及操作系統(tǒng)層的總體設計。
擴展閱讀:SQL Server 201* 一千萬條以上記錄分頁數(shù)據(jù)庫優(yōu)化經驗總結
SQLServer201*一千萬條以上記錄分頁數(shù)據(jù)庫優(yōu)化經驗總結
對普通開發(fā)人員來說經常能接觸到上千萬條數(shù)據(jù)優(yōu)化的機會也不是很多,這里還是要感謝公司提供了這樣的一個環(huán)境,而且公司讓我來做優(yōu)化工作。當數(shù)據(jù)庫中的記錄不超過10萬條時,很難分辨出開發(fā)人員的水平有多高,當數(shù)據(jù)庫中的記錄條數(shù)超過1000萬條后,還是蠻能考驗開發(fā)人員的綜合技術能力。當然不是每個公司都能請得起專業(yè)的DBA,話又說過來專業(yè)的DBA也未必能來我們公司長期工作,這就不只是薪資待遇問題了還會涉及到人家的長期發(fā)展規(guī)劃了,當然我也不是專業(yè)的DBA,本著能把問題解決好就是好貓的理念。
我們先看圖,數(shù)據(jù)庫中的記錄數(shù)如下:記錄數(shù)為10581490條同時還需要從另外一個表讀取7萬多條數(shù)據(jù)。
頁面運行效果如下:這是查看某個單位的數(shù)據(jù),每頁顯示16條、記錄數(shù)1087292條、分頁數(shù)為67956頁。
遇到的難題如下:
1:當客戶用了幾年后數(shù)據(jù)變得很龐大分頁速度緩慢得要命幾乎到了無法忍受的程度。
2:分頁到最后一頁時往往速度很慢會有死機現(xiàn)象出現(xiàn),特別是記錄條數(shù)很多時死機現(xiàn)象比較多。
那再講講,解決問題的方法步驟:
1:首先優(yōu)化數(shù)據(jù)庫、因為程序也很復雜一時也看不過來也不敢亂改,先從數(shù)據(jù)庫字段類型優(yōu)化開始入手會好很多。
先把數(shù)據(jù)庫里的datetime都修改為smalldatetime,數(shù)據(jù)庫變小了幾百M很有成就感,最起碼磁盤的讀取壓力減少不少吧。由于數(shù)據(jù)庫數(shù)據(jù)有上千萬條,無法用管理工具修改結構,只能用新建查詢執(zhí)行SQL命令才可以。
會有如下超時現(xiàn)象會發(fā)生。
那我們只能用執(zhí)行查詢的方式對表結構進行調整了,每次執(zhí)行一個SQL指令大概需要10分鐘時間才能順利執(zhí)行好,數(shù)據(jù)量實在是太大了。
2:接著再優(yōu)化,數(shù)據(jù)庫索引,原先的索引很亂可以理解為是亂來的所以我全部干掉重新進行了組織。把多余的索引先通通干掉,然后重新建立索引,因為記錄數(shù)太龐大了,有多余的索引會使數(shù)據(jù)庫變大很龐大,給他先減輕減輕體重。
把主鍵設置為倒序的、非聚集的,這樣的好處是可以把最新的數(shù)據(jù)排序在最前面。
把主要查詢的條件設置為索引,GroupBy的放第一個位置然后設置為聚集索引,這樣的好處時查詢時會快很多很多,普通所以沒這個效率高,數(shù)據(jù)實在是太龐大了,超過了1000萬條數(shù)據(jù)后,對比一下還是很明顯的,都能感覺得到。
完成以上2個步驟后分頁速度快了很多最起碼沒死機現(xiàn)象了,還有一點遺憾是當數(shù)據(jù)量大時最后一頁的分頁速度還是有些慢,有些難以忍受的感覺,但是最起碼不會死機了。
3:接著重點優(yōu)化,數(shù)據(jù)庫分頁的存儲過程,最后一頁難以忍受的問題先解決一下。分頁是用了SELECTTOPN的反轉的方式,我把最后一頁到底獲取多少條記錄準確數(shù)字計算出來,適當?shù)男薷牧艘幌伦詈笠豁撀盟廊セ顏淼膯栴},得到了適當?shù)沫h(huán)節(jié),雖然沒能徹底解決也速度明顯快了一些,由于寫的這個分頁程序也有些復雜,我也不敢亂動,就把問題解決好就完事大吉的目的了,不去惹更多的麻煩了。
4:對比一下數(shù)據(jù)庫結構優(yōu)化后的前后如下圖索引優(yōu)化前索引占用空間2706.109M
索引優(yōu)化后索引占用空間520.805M
我想就這么一個1000w條記錄的表光索引就優(yōu)化了2200M空間,就單單這個也提高不少性能了。
5:接著重點優(yōu)化,程序代碼部分了,其實代碼優(yōu)化是在索引優(yōu)化之前的,因為先讀懂了代碼、讀懂了業(yè)務邏輯才好優(yōu)化索引,這邊文章寫著寫著順序有些顛倒了,大家心里有數(shù)就可以了,我還是按照我的思路繼續(xù)寫吧。
在上圖的企業(yè)編號、企業(yè)名稱等,在程序里都進行了LIKE處理,當數(shù)據(jù)庫記錄超過1000萬條時,對字符進行Like操作,那真是會要命的,畢竟那么多數(shù)據(jù)都進行一次匹配,雖然電腦的運算速度很快,但是上千萬條記錄,這么被計算過一下,能快到哪里去?改進方法:
A:輸入企業(yè)編號、企業(yè)名稱修改為模糊查詢,能明確定位一個藥店的名稱。
B:若已經獲得企業(yè)編號了,不再匹配企業(yè)名稱,而且企業(yè)編號用=來判斷,并把企業(yè)編號進行索引。
海量數(shù)據(jù)庫分頁優(yōu)化總結:
折騰了接近1周左右,終于把這個1千多萬條記錄的數(shù)據(jù)表給優(yōu)化好了,難題也解決好了雖然不太科學也不專業(yè)也缺少理論依據(jù)、試驗數(shù)據(jù)、圖表對比、性能調試工具等等,但是還好把問題都解決好了,老鼠抓到了就是好貓咪了哈哈。數(shù)據(jù)庫進行了徹底的翻天覆地的優(yōu)化、程序代碼也進行了徹底的翻天覆地的優(yōu)化后,分頁速度飛快了。每頁顯示16條、記錄數(shù)1087292條、分頁數(shù)為67956頁,每頁分頁速度都完全在3秒內,最后一頁也不會死機了,也蠻快的足夠可以忍受了。
等有空時,再把最后一頁分頁速度慢的問題再深入解決一下,先不去惹麻煩了稍微休息一下再說。
優(yōu)化的每個動作需要10分鐘左右才會執(zhí)行好,若做錯一次基本上就代表半個小時白忙乎了,還需要刪除掉,再重新執(zhí)行修正過的SQL語句,所以一天下來優(yōu)化的成果并不會非常明顯、需要幾天時間才能優(yōu)化好。
將權限管理、工作流管理做到我能力的極致,一個人只能做好那么很少的幾件事情。
友情提示:本文中關于《優(yōu)化SQL Server數(shù)據(jù)庫的經驗總結》給出的范例僅供您參考拓展思維使用,優(yōu)化SQL Server數(shù)據(jù)庫的經驗總結:該篇文章建議您自主創(chuàng)作。
來源:網絡整理 免責聲明:本文僅限學習分享,如產生版權問題,請聯(lián)系我們及時刪除。