SQL Server中TOP子句可能導致的問題以及解決辦法 - CareySon－IT工程師數位筆記本

文章出處

簡介

在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%，但實際差異巨大），更高的原因不僅僅是優化器估計為1，因為Loop Join只要發現1條就可以立刻結束，但上面例子中由于過濾條件選擇性過低，導致找到第一條數據的隨機查找過多（loop join內表循環是隨機IO），成本如圖5所示。