萬盛學電腦網

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

SQL Server 分頁查詢五種寫法效能大比拼 (含 2005+ 新語法)

測試環境

  • 資料表 `tb_TestTable`:200 萬列,主鍵 `ID` (Identity)、`userName`、`userPWD`、`userEmail`。
  • 分頁參數:`@pageIndex` (0 基底)、`@pageSize` (每頁筆數)。
  • 測試指標:單次執行耗時 (毫秒)。

寫法一:NOT IN 子查詢 (經典、效能最差)

SELECT TOP (@pageSize) *
FROM tb_TestTable
WHERE ID NOT IN (SELECT TOP (@pageSize * @pageIndex) ID FROM tb_TestTable ORDER BY ID)
ORDER BY ID;

缺點:`NOT IN` 含 `NULL` 語意陷阱、子查詢重複掃描、無法利用索引 Seek、隨頁碼增大線性惡化。

寫法二:MAX(ID) 子查詢 (略優於 NOT IN)

SELECT TOP (@pageSize) *
FROM tb_TestTable
WHERE ID > (SELECT MAX(ID) FROM (SELECT TOP (@pageSize * @pageIndex) ID FROM tb_TestTable ORDER BY ID) AS T)
ORDER BY ID;

缺點:仍需掃描前 N 列取 MAX,深分頁效能差。

寫法三:游標/變數迴圈累積最大 ID (舊版常用、效能尚可)

DECLARE @count INT = 0, @ID INT = 0;
SELECT @count = @count + 1, @ID = CASE WHEN @count <= @pageSize * @pageIndex THEN ID ELSE @ID END
FROM tb_TestTable ORDER BY ID;
SELECT TOP (@pageSize) * FROM tb_TestTable WHERE ID > @ID ORDER BY ID;

優點**:單次掃描、無子查詢嵌套。
缺點**:T-SQL 迴圈逐列處理極慢 (RBAR),200 萬列需數秒,不適合高並發。

寫法四:ROW_NUMBER() 視窗函數 (SQL 2005+ 標準、效能最佳)

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
    FROM tb_TestTable
) AS T
WHERE RowNum > @pageSize * @pageIndex
  AND RowNum <= @pageSize * (@pageIndex + 1)
ORDER BY ID;

優點**:單次掃描、最佳化器可推導 `TOP` + `ORDER BY` 推播、支援並行計畫、語法標準、可維護性高。
測試結果**:各頁碼穩定 **< 50ms** (含首頁、尾頁)。

寫法五:CTE + ROW_NUMBER() / CEILING 分組 (邏輯清晰)

WITH CTE AS (
    SELECT *,
           CEILING(ROW_NUMBER() OVER (ORDER BY ID) * 1.0 / @pageSize) AS PageNum
    FROM tb_TestTable
)
SELECT * FROM CTE WHERE PageNum = @pageIndex + 1;  -- 1 基底頁碼

本質同寫法四,`CEILING` 計算頁碼直觀,效能相近。

效能比較總結 (200 萬列測試)

寫法首頁中間頁 (1000)尾頁評價
NOT IN120ms3,800ms7,500ms❌ 淘汰
MAX(ID)90ms2,500ms5,200ms❌ 淘汰
變數迴圈1,200ms1,200ms1,200ms⚠️ 固定高成本,RBAR
ROW_NUMBER()15ms18ms22ms✅ 首選
CTE + CEILING16ms19ms23ms✅ 推薦

現代最佳實務 (SQL Server 2012+)

-- OFFSET-FETCH 標準語法 (ANSI SQL:2008)
SELECT * FROM tb_TestTable
ORDER BY ID
OFFSET @pageSize * @pageIndex ROWS
FETCH NEXT @pageSize ROWS ONLY;
  • 語法簡潔、最佳化器原生支援、無需包裹子查詢/CTE。
  • **分頁穩定性**:`ORDER BY` 必須**唯一** (含 PK),否則同鍵值跨頁可能重複/遺漏。建議 `ORDER BY ID` (PK)。

索引建議

  • 分頁欄位 (`ORDER BY` 列) **必須有索引** (最好是叢集索引或覆蓋索引)。
  • 避免 `SELECT *`,僅選需要欄位,利用 **覆蓋索引** (`INCLUDE`) 避免 Key Lookup。
  • 深分頁 (百萬頁) 仍有掃描成本,考慮 **Keyset Pagination** (游標式:`WHERE ID > @lastSeenId ORDER BY ID LIMIT @pageSize`),前端傳遞上一頁最大 ID。
copyright © 萬盛學電腦網 all rights reserved