SQL Server中TOP子句可能導致的問題以及解決辦法
簡介
在SQL Server中，針對復雜查詢使用TOP子句可能會出現對性能的影響，這種影響可能是好的影響，也可能是壞的影響，針對不同的情況有不同的可能性。
關系數據庫中SQL語句只是一個抽象的概念，不包含任何邏輯。很多元數據都會影響執行計劃的生成，SQL語句本身并不作為生成執行計劃所參考的元數據（提示除外），但TOP關鍵字卻是直接影響執行計劃的一個關鍵字，因此在某些情況下使用TOP會導致性能受到影響，下面我們來看集中不同的情況。
單表情況
對于單表查詢（這里的所說的單表指的是不包含視圖、表值函數的物理單表）來說，存在TOP基本不會對性能產生影響，如果在SQL Server中加入了TOP，那么TOP本身可以看作是一個查詢提示，意味著告訴優化器“返回結果只有N行”。我們看一個簡單的例子，如圖1所示：
圖1.指定TOP關鍵字的單表執行計劃
由圖1執行計劃對比可以看出，對于有索引支撐的單表查詢來說，使用TOP子句往往可以提升性能，此時TOP N的行數的N則提示查詢優化器該查詢返回N行，而不是使用統計信息中的數據分布，此時TOP N對于查詢優化器來說是合理的。
但有些時候Grant Memory（每次執行計劃生成時會預估所需的內存，如果預估內存小于執行內存，則會spill to tempdb，對性能產生非常大的影響，由于每一個版本預估內存的公式變化極大，因此不在此詳細解釋了）不準會產生非常高的性能影響。在開始談這點，之前，我們先談兩個操作符：
Sort
Sort操作符是非常通用的排序操作符，在執行計劃中可能會出現在多個地方，比如Merge Join之前，由于Order By導致的等。該算法非常通用，可以對非常大的結果集進行排序，該操作符是阻塞式（意味著排序結束之前數據無法流動到下一個操作符），并且需要大量內存和CPU資源。該操作符還有一個問題是當Grant Memory不足時，需要TempDB輔助完成排序，因此有極大的性能開銷。
Top N Sort
TOP N Sort是適應小場景，專門針對少量查詢的排序算法。對于只選擇幾條數據來說，對于整個結果集進行排序成本過于高昂，因此TOP N的算法是首先取第一條數據，與其他數據進行對比，看是否最大（或最小），再取第二條數據對比，依次類推，直到找到前N條數據。該算法如果行數較小，則相比SORT操作符性能提升明顯，但如果N值過大，則由于下述原因該算法不合適：
1.該算法不支持spill to tempdb，導致無法承載太大的結果集。
2.該算法需要遍歷N次，如果N過大，則成本過高。
對于SQL Server來說，這個N是否過大的閾值是100。下面我們來看一個例子，測試數據和代碼如代碼清單1所示。
CREATE TABLE TestTop
(id INT,sortkey INT,SOMEvalue CHAR(1000))
DECLARE @i INT =1
WHILE @i<300000
BEGIN
INSERT INTO TestTop VALUES(@i,@i,'a')
SET @i=@i+1
END
CREATE CLUSTERED INDEX PK_id ON TestTop(id)
--test 1
SELECT TOP(100) * FROM TestTop
ORDER BY sortkey
--test 2
SELECT TOP(101) * FROM TestTop
ORDER BY sortkey
代碼清單1.測試數據與測試代碼
第一個測試為TOP 100，正好使用TOP N Sort的算法，第二個測試為TOP 101，只能使用普通Sort的算法，如圖2所示。
圖2.TOP 101的SORT需要更多內存，從而導致內存授予不足spill to tempdb
我們再來看執行時間，由于spill to tempdb的存在，那么執行時間如圖3所示。
圖3.相差非常大的執行時間
從圖3可以看出，執行時間相差非常大。
因此對于TOP的使用來說，盡量使用TOP 100以內的數值。
多表情況
由于TOP語句帶有對優化器基數估計的提示功能，因此多表查詢時在極端情況下可能導致行數低估從而影響性能。
比如下面如圖4的示例查詢
圖4.使用TOP 1的表接連查詢
在這種情況下，由于TOP1的存在使得查詢優化器使用1作為估計行數，與實際的行數差異巨大，因此對于這種情況，使用TOP反而可能導致成本更高（雖然我們看到圖4中估計的是0%對比100%，但實際差異巨大），成本如圖5所示。
圖5.使用TOP反而導致性能下降
對于上面這種情況來說，我們通常可以有下面集中解決辦法：
1.使用提示，由于我們知道這是由于實際行數遠大于估計行數導致，因此我們可以嘗試使用hash join,forcescan等提示。
2.增加where條件，使得返回行數具有更高的選擇性。
3.不使用TOP1，而使用TOP 10以上的數字，讓估計行數變大，比如圖5中的查詢我們由TOP1 變為TOP10，那么執行計劃則變為如圖6所示。
圖6.TOP 10的執行計劃
這是由于當行數少時，LOOP JOIN可以更快返回有限的行數，相當于對表加了FAST N提示，但行數增多時，優化器更傾向使用MERGE或者HASH完成操作，在上面返回行極多（選擇性低）的極端情況下，會擁有更好的性能，結果如圖7所示。
圖7.特殊情況下TOP10相比TOP1有更好性能。
因此結合單表的例子，推薦使用TOP關鍵字時，數字在10到100之間。
小結
本文介紹了TOP關鍵字在單表和多表條件下可能對執行計劃產生的影響，進而影響了查詢計劃。TOP影響執行計劃主要是下面兩個方面：
- 內存授予
- 估計行數
因此在特殊情況下調優TOP語句時，可以根據實際情況考慮本文的建議。