為什麼 OFFSET … FETCH 與舊式 ROW_NUMBER 方案之間存在執行計劃差異?
SQL Server 2012 中引入的新
OFFSET ... FETCH
模型提供了簡單和快速的分頁。考慮到這兩種形式在語義上相同且非常常見,為什麼會有任何差異?人們會假設優化器可以辨識兩者並(微不足道地)最大限度地優化它們。
這是一個非常簡單的案例,
OFFSET ... FETCH
根據成本估算,它的速度快了約 2 倍。SELECT * INTO #objects FROM sys.objects SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r FROM #objects ) x WHERE r >= 30 AND r < (30 + 10) ORDER BY object_id SELECT * FROM #objects ORDER BY object_id OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY
可以通過創建 CI 或添加過濾器來改變此測試案例,
object_id
但不可能消除所有計劃差異。OFFSET ... FETCH
總是更快,因為它在執行時做的工作更少。
問題中的範例並不完全產生相同的結果(該
OFFSET
範例有一個錯誤)。下面更新的表單修復了該問題,刪除了ROW_NUMBER
案例的額外排序,並使用變數使解決方案更通用:DECLARE @PageSize bigint = 10, @PageNumber integer = 3; WITH Numbered AS ( SELECT TOP ((@PageNumber + 1) * @PageSize) o.*, rn = ROW_NUMBER() OVER ( ORDER BY o.[object_id]) FROM #objects AS o ORDER BY o.[object_id] ) SELECT x.name, x.[object_id], x.principal_id, x.[schema_id], x.parent_object_id, x.[type], x.type_desc, x.create_date, x.modify_date, x.is_ms_shipped, x.is_published, x.is_schema_published FROM Numbered AS x WHERE x.rn >= @PageNumber * @PageSize AND x.rn < ((@PageNumber + 1) * @PageSize) ORDER BY x.[object_id]; SELECT o.name, o.[object_id], o.principal_id, o.[schema_id], o.parent_object_id, o.[type], o.type_desc, o.create_date, o.modify_date, o.is_ms_shipped, o.is_published, o.is_schema_published FROM #objects AS o ORDER BY o.[object_id] OFFSET @PageNumber * @PageSize - 1 ROWS FETCH NEXT @PageSize ROWS ONLY;
該
ROW_NUMBER
計劃的估計成本為0.0197935:該
OFFSET
計劃的估計成本為0.0196955:這節省了0.000098 個估計成本單位(儘管
OFFSET
如果您想為每一行返回一個行號,該計劃將需要額外的運算符)。OFFSET
一般來說,該計劃仍然會稍微便宜一些,但請記住,估計成本正是如此——仍然需要進行實際測試。兩個計劃中的大部分成本是輸入集的全部類型的成本,因此有用的索引將使兩種解決方案都受益。在使用常量字面值的情況下(例如
OFFSET 30
在原始範例中),優化器可以使用 TopN 排序而不是後跟 Top 的完整排序。當 TopN 排序所需的行是常量字面量且 <= 100( 和 的總和OFFSET
)FETCH
時,執行引擎可以使用不同的排序算法,該算法的執行速度比廣義 TopN 排序更快。這三種情況總體上具有不同的性能特徵。至於為什麼優化器不自動轉換
ROW_NUMBER
語法模式來使用OFFSET
,有很多原因:
- 編寫一個匹配所有現有用途的轉換幾乎是不可能的
- 自動轉換一些分頁查詢而不是其他的可能會令人困惑
- 不能保證該
OFFSET
計劃在所有情況下都更好上述第三點的一個範例發生在分頁集非常寬的情況下。與使用或掃描索引相比,使用非聚集索引查找所需的鍵並手動查找聚集索引會更有效。如果分頁應用程序需要知道總共有多少行或頁面,還有其他問題需要考慮。這裡有另一個關於“key seek”和“offset”方法的相對優點的很好的討論。
OFFSET``ROW_NUMBER
OFFSET
總的來說,如果合適的話,在徹底測試之後,人們做出明智的決定來改變他們的分頁查詢使用可能會更好。