個人ASP.NET程式效能最佳化心得(1)：資料庫篇

個人ASP.NET程式效能最佳化心得系列：

個人ASP.NET程式效能最佳化心得(1)：資料庫篇

個人ASP.NET程式效能最佳化心得(1)：資料庫篇（外一篇）

個人ASP.NET程式效能最佳化心得(2)：ASP.NET代碼最佳化

個人ASP.NET程式效能最佳化心得(3)：前端效能最佳化

------------------------------------------------------------------------------

前言

相信園子裡有不少程式員同學都是在做著xx管理系統這樣的中小型項目，這種項目往往是一種工作量的代碼，程式員同學就將青春耗費在這樣的項目中，不斷改變需求，不斷地加班趕工，於是就開始懷疑這個行業，對developer充滿厭惡，想學新東西，可是周圍同事的水平都是差不多；想買書學平時加班根本沒有自己的時間。這種狀況相信大多數情況都在我們身邊發生，我之前就是處於這種狀態，使用的是asp.net語言，不過很難界定所做的項目是網站還是軟體，因為它很複雜，開發週期和傳統軟體開發沒有什麼區別，但它確實是部署在IIS上可以通過瀏覽器訪問。或者又是專門給企業做網站的程式員，一套程式核心不變，只是每個網站換個殼，新聞系統、留言系統、下載系統等等。……為什麼我要說這些呢？因為在前面我要說的是，這些並不是真正的互連網公司，這種公司往往追求利益最大化、最快化、最直接化，簽單給錢，整個流程程式員的作用幾乎可以忽略不計，因為隨便招幾個畢業生帶幾一兩個月就可以繼續把項目做下去。

而在互連網公司裡，尤其是中大型網站，效能絕對是躍居非常重要的位置，試想一下日IP過百萬上千萬，並發成千上萬的網站，如果首頁每節省1k的流量，那麼一天下來就為企業節省相當可觀的支出；頁面載入每減少1秒的時間，就會減少可觀的使用者流失。我之前是在上段說講到的小公司做項目，現在在一家算是中型互連網公司裡，日PV幾十萬的網站，前端、代碼、資料庫設計的影響對網站的影響是如此之重大，是我之前從沒有親身體會到的。在這近一年的時間裡，總結了一些關於asp.net效能最佳化的一些經驗與大家分享，由於個人水平的限制，難免有一些不準確、不完善的地方，歡迎大家拍磚o(∩_∩)o 

這一系列文章我計劃花三個大的方向來講解一些效能方面的東西，包括資料庫效能最佳化、asp.net程式最佳化和前端最佳化，這裡我將資料庫效能最佳化放在前面，代表了它的重要性。我個人認為影響一個網站效能從程式上來說最主要就是這三個方面，從這三個方面逐一進行最佳化，將對網站效能的提升會有較大的協助。

資料庫效能最佳化

一、欄位的建立

1、減少跨表查詢

需求確定後往往就開始建立資料庫，那麼建立資料庫，對資料庫的操作離不開增刪改查這些最基本的操作，其中查詢應該是頻繁的操作，提升查詢操作的一個基本的原則是盡量減少跨表查詢，也就是JOIN、UNION和子查詢等，這種情況往往最為常見，往往是A表中其中一個欄位是B表的外鍵，查詢時往往需要同時將A表中的資料全部查詢出來，同時再把匹配A表外鍵的欄位查詢出來，這樣就會大大增加查詢的成本。這裡我們以文章系統為例，一般表結構如下：

News(NewsId,NewsTitle,Content,CateId,PostUserId,Hits,AddTime)--文章NewsCate(CateId,CateTitle)--文章分類

這樣的表結構肯定就需要使用兩次查詢或者串連查詢等方法來取得兩張表的資料。如果統計一個分類下的文章數量，還需要SELECT COUNT(*)來進行News表的全表掃描來完成，這樣就會在效能上受到嚴重影響。

對於這種本身表結構設計欠合理的情況，最佳化SQL語句基本作用不大，改進方法是將表中需要跨表查詢的地方減少到最小，可以將表結構建立如下：

News(NewsId,NewsTitle,Content,CateId,CateName,PostUserId,PostUserName,Hits,AddTime)NewsCate(CateId,CateTitle,NewsNum)

這裡進行了兩個地方的改進，一是將CateName同時併入到News表中，這樣可以避免跨表串連查詢帶來的效能損耗(PostUserName與此類似)；二是將News表中的總記錄數存入NewsCate表中的NewsNum欄位中，可以避免SELECT COUNT(*)帶來的效能損耗（在大資料量的情況下效果是非常明顯的）。由此產生的冗餘欄位帶來的效能影響與之前的效能影響相比較，可以很明顯的對比出來。這種最佳化可以稱為以空間換時間。

2、排序問題

另外一點是針對AddTime欄位，一般情況下以它排序的情況較多，這種DateTime類型欄位在排序時會進行計算，它的排序比Int類型要慢得多，因此還可以考慮新增加一個DateNum(int)欄位來儲存日期，比如AddTime為2011-05-27，那麼插入到DateNum可以是20110527這個數字，這樣在排序時可以通過ORDER BY DateNum DESC來減少排序的時間。當然如果你的AddTime預設是getdate()，並且排序只有一個按時間排序的話，可以ORDER BY NewsID DESC來完成。

對於排序，資料庫在查詢出滿足WHERE所有條件的資料後，然後再進行排序，因此如果沒有必要，不要使用複雜的排序，可以根據實際情況考慮是否添加OrderNum來減少相關的排序；對於的確需要複雜的排序我們第二點會講到索引問題來解決。

3、需要外鍵嗎？

如果你的資料庫學得不錯的話，一定記得資料庫範式，滿足三級範式才是標準的資料庫設計，在實際情況中，絕不可完全照書本來。對於是否需要外鍵爭論一直較多，我的理解是外鍵是一個約束，它在避免程式插入異常資料會有一定的協助，異常的資料會導致程式需要異常處理的地方增加，隨之代碼增加，程式穩定性降低。但在實際的開發中，外鍵會導致偵錯工具的複雜，並且會在一定程式上降低SQL執行的效率，因為資料在插入前引擎會對資料的合法性進行校正，這樣在一定程式上也會降低資料庫的效能，另外對於外鍵資料在刪除情況下查詢主表資料可能會發生不可預料的異常。在網站中我的個人建議是不用外鍵，但為了避免出現DBNull的情況，“是否為空白”這個選項在必要時要選擇不允許為空白。

二、索引的建立

在項目開始前要確定實際項目中可能哪些會頻繁進行查詢——一般情況下實際情況是程式員和DBA是同一個人，因此假設你已經知道了這些會頻繁查詢的地方。以一個文章系統為例來說，可能會有時間、分類和關鍵字這幾種查詢比較頻繁。那麼必要時要建立索引，一般情況需要對其各自建立索引，比如實際的表結構如下：

News(NewsId,NewsTitle,Content,CateId,CateName,PostUserId,PostUserName,Hits,AddTime,CommentNum)

假設以分類和時間排序來進行查詢十分頻繁，那麼需要各自建立CateId和AddTime索引；如果這個查詢屬於複雜查詢，如果SQL語句如下：

SELECT * FROM News WHERE CateId=1 ORDER BY AddTime DESC,CommentNum DESC

那麼可以建立多個欄位的複合索引，這裡可以將CateId,AddTime建立複合索引，如有必要可以將CommentNum也包括在內。

對於搜尋如果以關鍵字查詢較為頻繁，建議在查詢欄位上建立全文索引，全文索引是SQL Server內建的搜尋演算法來進行的查詢規則，效能比LIKE就好很多。比如查詢標題：

SELECT TOP 10 * FROM News WHERE CONTAINS(News，‘walkingp’)

*注意SQL 2000及以後的版本才有全文索引功能。

三、查詢的最佳化

查詢是絕大多數SQL語句最佳化的用武之地，不同的SQL語句可能會讓查詢時間有著很大的區別；查詢最佳化最核心的內容就是減少scan，盡量做到seek；scan代表全表掃描，seek代表定位到某一行。使用COUNT、NOT、!=、IN、LIKE等都會引起全表掃描，如果這張表資料足夠多，那麼效能影響是非常大的。更好的方法是避免這種全表掃描，使用最準確的條件限制來縮小資料庫掃描的範圍，減少SQL執行的時間。

以上表為例，ASP.NET程式最常使用的DAL功能是根據某一編號取資料然後儲存到對象中。

SELECT * FROM News WHERE NewsId=@NewsId

這樣即使在查詢到需要的資料後，它仍會執行剩餘資料的查詢進行全表掃描，這樣就浪費了大量的資源和程式時間。可以使用TOP 1來進行條件限制：

SELECT TOP 1 * FROM News WHERE NewsId=@NewsId

假設有一百萬條資料，實際中NewsId=1，那麼我們就節約了查詢999999條資料的時間。

一般情況下News表會很大，而NewsCate會很少，對於這種對比非常懸殊的兩表，如果進行串連查詢，將資料小的NewsCate放到JOIN後面，這樣可以提升查詢效能。

關於查詢最佳化，相關的資料非常豐富，大家可以自己去搜尋一下，對於爭執比較多的類似IN和EXISTS等問題，可以在實際資料庫中測試其效能，然後決定選用哪一種。

四、SQL Profiler的使用

SQL Profiler是最容易被忽視的工具，而這個工具是資料庫效能最佳化一個非常強大的工具，它與Management Studio在SQL Server安裝時都會綁定在一起，選擇建立跟蹤，然後在跟蹤屬性中選擇相應的事件列，一般選擇CPU、Reads、Writes、Duration、StartTime、EndTime即可，它們對應了在物理上SQL語句對CPU的佔用、對硬碟讀寫的次數和起止時間，通過它可以很直觀地看出影響SQL效能的地方。

個人ASP.NET程式效能最佳化心得(1)：資料庫篇

比如我這裡測試是對一百萬條資料SELECT *和SELECT NewsId的效能測試，可以較直觀地看出SELECT * 在CPU損耗、和硬碟讀取上會大很多。因此在實際項目中建議“吃多少，拿多少”。

個人ASP.NET程式效能最佳化心得(1)：資料庫篇

以上就是關於在資料庫效能最佳化的部分，期待大家有更好的討論一起來分享，下一篇文章會講解ASP.NET代碼的最佳化。

這篇文章同時發表在我的個人部落格上 http://www.walkingp.com/

（感謝大家的指點和討論，晚上的時候我會將會更新一下這篇文章的版本，以免誤導新手。）