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

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

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

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

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

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

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

在上一篇文章《個人ASP.NET程式效能最佳化心得(1)：資料庫篇》裡，不少園友對我其中的一些觀點提出了一些質疑，這裡我認真查閱了一些資料，更正了一些存在錯誤的地方，另外對一些存在爭議的地方加上更詳細的說明，並會對一些將於效能方面的問題以執行個體資料表現出來。

表結構：

News(NewsId,NewsTitle,Content,CateId,CateTitle,PostUserId,PostUserName,AddTime,DateNum,Hits,CommentNum)//Hits：點擊數；CommentNum：評論次數Cate(CateId,CateTitle)

只有主鍵，未建外鍵，暫時未建其他索引；其中News表插入了100萬條測試資料，Cate共有三條資料。

1、對於外鍵及相關鍵是否比嚴格的範式型效率高：

這兩張表是顯著的胖瘦表查詢，這種情況也比較常見，假設使用嚴格的範式型表結構，也就是News表不存在CateTitle，這裡進行串連查詢：

select n.*,c.* from news n inner join Cate c on c.CateId=n.CateId and c.CateId=1

執行計畫如下：

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

另一種情況查詢語句如下：

select * from News where CateId=1

執行計畫如下：

從執行計畫裡可以看到第一種情況在兩次聚焦索引尋找後再進行一次嵌套迴圈將結果合并，而第二種情況只有一次聚焦索引掃描，因此會在一定程式上減少效能的消耗，是SQL Server Profiler的對比：

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

這裡Reads大致相同，由於語句又進行了一次合并去處，會對CPU有一定的效能消耗。

結論：將外鍵及相關鍵合并到主表上會在這種簡單查詢中提升一定的效能，但是它卻是靠資料冗餘來達到提升效能的目的，而實際上由於查詢條件是在叢集索引上進行的，因此如果是資料量不大的情況可以不必考慮這種情況。

2、DateTime類型問題

上一篇文章我提到了一個觀點，DateTime比Int效能要高，這是很武斷的結論，而且那個例子裡我僅是以排序來去說明。事實上兩者類型都是BigInt類型來儲存在資料庫中的，只不過DateTime佔用8個位元組，Int佔用4個位元組，在這種簡單的排序中效能基本沒有任何差別，DateTime具有強大的時間運算函數，Int類型當然達不到這些功能，這時候使用DateTime是必須的，但如果是類似ORDER BY AddTime DESC這種情況，假設添加時間預設值是GETDATE()，那麼這個排序與主鍵排序應該是一致的，這時建議使用ORDER BY NewsID DESC來進行排序。

SQL語句如下：

select top 5000 * from News order by addtime descselect top 5000 * from News order by NewsId desc

在SQL Server Profiler中執行結果如下：

因此結論是：DateTime類型還是繼續保留使用吧，如果儲存的實際上是SmallDateTime建議還是使用smalldatetime來儲存資料。

3、SELECT TOP 1問題

搞清楚叢集索引就一定會明白這個問題，主鍵一定是叢集索引，在叢集索引上進行查詢效能其實影響不大，分別是帶TOP 1和不帶TOP 1對查詢條件在主鍵上的分析：

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

而在複雜查詢條件下我們就需要更多的參數進行查詢，這個時候條件列往往假設在沒有索引的情況下，就會進行全表掃描。這個時候效能就會受到影響，不使用TOP 1情況下：

select * from News where NewsId=500000 and PostUserName='walkingp'

執行計畫如下：

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

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

使用TOP 1進行條件約束情況下：

select top 1 * from News where NewsId=500000 and PostUserName='walkingp'

可以看到在查詢到結果後符合TOP數目即返回了結果，這樣就節省了全表掃描的時間：

詳情對比如：

因此結論是對於查詢不全部在（聚集）索引上的查詢，如果僅是需要返回某幾條，建議採用TOP進行約束，這種效能上的差異在巢狀查詢IN等會體現得更加明顯。

4、Hits、UpdateTime欄位是否應該從表中分開

這類欄位屬於主表中更新最為頻繁的欄位，頻繁對一張大資料量進行更新資料，顯示會造成效能下降，因此在資料量較大時建議將這類資料分離到另一張表中，並對該表中邏輯外鍵列建立索引以提升效能。

新表結構如下：

News(NewsId,NewsTitle,Content,CateId,CateTitle,PostUserId,PostUserName,AddTime,DateNum,CommentNum)//Hits：點擊數；CommentNum：評論次數Hits(NewsId,Hits)Cate(CateId,CateTitle)

5、外鍵問題

外鍵問題影響效能是不言的事實，我們這裡也是主要以效能為最主要考察點，當然具體情況其實更要以具體情況來考慮，外鍵是維護資料完整性重要的一個手段，在某些應用場合下資料的完整性可能要比效能更加的重要，這種情況下建議還是要建立外鍵。這種效能上的消耗相對於業務上的重要性要小得多，另外也可以通過其他最佳化方式來進行效能的最佳化。

對於互連網應用，資料增長極其快速，另外設計不合理、編碼不嚴謹等方面都會造成運行中不可預料的問題（除了CSDN上那位老濕，誰敢保證0 bug？），相對來講，使用外鍵的成本要高很多。當然對於企業網站這種小型系統，建議還是使用外鍵，最起碼可以規範自己的編碼規範。

6、Scan都是會進行全表掃描嗎？

在物理上，SQL Server使用三種方法來組織其分區中的資料：1、用B樹儲存有叢集索引的表資料頁；2、使用堆來儲存沒有儲存群組織的表；3、非叢集索引使用與叢集索引相類似的B樹來儲存索引結構。針對這三種不同結構，SQL Server使用的資料檢索方法也會不一樣：

這其實已經是一個相當深奧的知識點了，我本人現在也是一知半形，來自《Microsoft SQL Server企業級平台管理實踐》一書，結論如下：SCAN並非都會進行全表掃描；在某些情況下，Scan並非比Seek效能差。想要完全搞明白它，需要更清楚SQL Server核心的一些查詢機制。

但是值得注意的是，一般情況下我們的查詢以簡單查詢為主，這種情況下要盡量避免Scan，尤其是類似多重巢狀查詢這種複雜的情境。

7、見識索引的強大

索引的概念相信不用我來講了，下面以執行個體來說明索引的強大作用。

首先是在不建立索引的情況下對CommentNum(評論次數)進行倒序排序：

select top 5000 * from News order by CommentNum desc

然後對CommentNum建立索引：

重新執行SQL語句：

可以看出，在沒有建立索引前，時間主要花費在排序上，而建立索引後時間就基本上全部都在尋找了。兩者對比如：

針對查詢中可能出現的複雜條件可對其進行分析適當建立索引，一定會讓效能大大提升。

最後，最重要的一點是,在實際的資料庫應用中，應當靈活運用，不能拿理論去死套，在存在疑惑的地方可以自己去測試一下，多去查閱一些相對權威的資料，這樣技術就在不斷的慢慢進步了。

針對本文中有任何問題的，歡迎回複討論。

參考資料：

《Microsoft SQL Server企業級平台管理實踐》

本文同時發在我的個人首頁：http://www.walkingp.com/?p=1136