SQL Server中TOP子句可能導致的問題以及解決辦法

標籤：

原文: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 @[email protected]+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所示。

圖5.使用TOP反而導致效能下降

 

    根本原因是由於估計行數只有1行，大部分情況下這一行

    對於上面這種情況來說，我們通常可以有下面集中解決辦法：

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語句時，可以根據實際情況考慮本文的建議。

SQL Server中TOP子句可能導致的問題以及解決辦法