萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 分頁查詢效能大比拚:TOP 子查詢 vs ROW_NUMBER() 窗口函數

兩種分頁寫法

SQL Server 2000/2005 前:TOP + NOT IN

SELECT TOP 10 * 
FROM TCCLine.dbo.CLine_CommonImage 
WHERE id NOT IN (
    SELECT TOP 40 id 
    FROM TCCLine.dbo.CLine_CommonImage 
    ORDER BY id
)
ORDER BY id;
-- 40 = 10 * (5-1) 第 5 頁、每頁 10 筆

SQL Server 2005+:ROW_NUMBER() 窗口函數

SELECT TOP 10 numComImg.*
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY id ASC) AS rownumber, *
    FROM (SELECT * FROM TCCLine.dbo.CLine_CommonImage) AS comImg
) AS numComImg
WHERE rownumber > 40;

執行計劃對比

  • TOP + NOT IN: 兩次 Clustered Index Scan,查詢成本 72%
  • ROW_NUMBER(): 單次掃描 + 窗口運算,查詢成本 28%

執行時間統計

SET STATISTICS TIME ON;

ROW_NUMBER() 版本

CPU 時間:15 ms,經過時間:12 ms

TOP 子查詢版本

CPU 時間:31 ms,經過時間:28 ms

深度原理分析

維度TOP + NOT INROW_NUMBER()
掃描次數2 次(外層 + 子查詢)1 次(流式處理)
中間結果集子查詢產生臨時結果集流式計算行號,無額外結果集
索引利用子查詢可能無法最佳化可充分利用 ORDER BY 索引順序
記憶體壓力較大較小
並行度受限支援並行窗口運算

進階:OFFSET/FETCH (SQL 2012+)

SELECT * FROM TCCLine.dbo.CLine_CommonImage
ORDER BY id
OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY;

語法更簡潔,執行計劃與 ROW_NUMBER() 幾乎相同,推薦新專案採用。

結語

ROW_NUMBER() 窗口函數分頁在邏輯清晰、效能優越、可維護性上全面優於傳統 TOP + NOT IN 模式。SQL Server 2012 以上請直接使用 OFFSET/FETCH 標準語法。分頁查詢永遠要配合 ORDER BY 確定順序,並建立支援排序的索引以獲得最佳效能。

copyright © 萬盛學電腦網 all rights reserved