萬盛學電腦網

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

SQL Server 分頁查詢四大經典寫法演進與效能比較

分頁是應用系統最常見需求。SQL Server 歷經多版本演進,從 NOT IN 子查詢到 OFFSET/FETCH,效能與可讀性大幅提升。

寫法一:NOT IN 子查詢 (SQL 2000 通用,效能最差)

SELECT TOP @PageSize * 
FROM Articles 
WHERE Id NOT IN (
    SELECT TOP @Offset Id FROM Articles ORDER BY Id DESC
)
ORDER BY Id DESC;
  • @Offset = (@PageIndex - 1) * @PageSize
  • 缺點:子查詢每次執行、NOT IN 反模式、大偏移量極慢

寫法二:MAX/MIN 邊界值法 (SQL 2000/2005,中等效能)

SELECT TOP @PageSize * 
FROM Articles 
WHERE Id < (
    SELECT MIN(Id) FROM (
        SELECT TOP @Offset Id FROM Articles ORDER BY Id DESC
    ) AS Tmp
)
ORDER BY Id DESC;
  • 利用索引範圍掃描,避免 NOT IN
  • 需主鍵/索引為遞增/遞減且無洞

寫法三:游標分頁 (SQL 2000/2005,程式化控制)

CREATE PROCEDURE GetPagedData
    @PageSize INT, @PageIndex INT, @TotalPages INT OUTPUT
AS
BEGIN
    DECLARE @Total INT, @Start INT, @End INT;
    DECLARE cur SCROLL CURSOR FOR SELECT * FROM Articles ORDER BY Id;
    
    OPEN cur;
    SET @Total = @@CURSOR_ROWS;
    
    IF @Total > 0
    BEGIN
        SET @TotalPages = CASE WHEN @Total % @PageSize = 0 
                               THEN @Total / @PageSize 
                               ELSE @Total / @PageSize + 1 END;
        
        SET @Start = (@PageIndex - 1) * @PageSize + 1;
        FETCH ABSOLUTE @Start FROM cur;
        
        -- 逐列 FETCH @PageSize 次...
    END
    CLOSE cur; DEALLOCATE cur;
END;
  • 伺服器端游標,網路傳輸少
  • 游標開銷大,並發差,不推薦高併發場景

寫法四:ROW_NUMBER() 視窗函數 (SQL 2005+,標準寫法)

WITH Paged AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Id DESC) AS RowNum
    FROM Articles
)
SELECT * FROM Paged 
WHERE RowNum BETWEEN @Offset + 1 AND @Offset + @PageSize;
  • 單次掃描、單次排序、標準 SQL
  • 大偏移量仍需掃描前 N 列

寫法五:OFFSET / FETCH (SQL 2012+,現代標準)

SELECT * FROM Articles
ORDER BY Id DESC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
  • 語法最簡潔、最符合 ANSI SQL 標準
  • 執行計畫與 ROW_NUMBER 相近
  • 推薦新專案首選

寫法六:Keyset Pagination (游標式分頁,極大資料集最佳)

-- 第一頁
SELECT TOP 20 * FROM Articles ORDER BY Id DESC;

-- 後續頁 (記住上一頁最後一筆 Id = @LastId)
SELECT TOP 20 * FROM Articles 
WHERE Id < @LastId 
ORDER BY Id DESC;
  • 無偏移量掃描,索引直接定位,效能恆定 O(PageSize)
  • 不支援跳頁、總頁數需另查
  • 適合無限捲動、API 分頁

效能對比 (百萬列測試,第 50000 頁,每頁 20 筆)

寫法邏輯讀取CPU (ms)耗時 (ms)適用場景
NOT IN極高>10000>10000極小表/教學
MAX/MIN高~500~800舊版本/有序鍵
游標中~300~500低併發/程式控制
ROW_NUMBER中高~200~300通用標準
OFFSET/FETCH中高~200~300SQL 2012+ 標準
Keyset極低~10~20巨量資料/無限捲動

選擇建議

  1. SQL 2012+ 一般分頁 → OFFSET/FETCH
  2. SQL 2005-2008R2 → ROW_NUMBER() CTE
  3. 超大表/高併發/無限捲動 → Keyset Pagination
  4. 需總筆數/總頁數 → 另行 COUNT(*) 或視窗函數 COUNT(*) OVER()

分頁無銀彈,理解資料分佈、存取模式、業務需求,選對策略事半功倍。

copyright © 萬盛學電腦網 all rights reserved