萬盛學電腦網

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

SQL Server 三大分頁寫法效能對比:NOT IN、MAX(ID)、ROW_NUMBER()

SQL Server 2005/2008/2012+ 三種經典分頁寫法,從語法到效能特徵完整對比,並附 Oracle 對照。

寫法 1:NOT IN + TOP (最直觀、效能最差)

DECLARE @pagesize INT = 5, @currentpage INT = 2;
SELECT TOP @pagesize id, email, qq, wechat, phone, phone1
FROM contact
WHERE id NOT IN (
  SELECT TOP (@pagesize * (@currentpage - 1)) id
  FROM contact ORDER BY id ASC
)
ORDER BY id ASC;
  • 缺點:子查詢每頁重掃、NOT IN 含 NULL 會導致空結果、不支援複合 PK。

寫法 2:MAX(ID) + 子查詢 (需連續 ID、唯一遞增)

SELECT TOP 5 *
FROM AISINO_BD_TelephoneRecord
WHERE ID > (
  SELECT MAX(ID)
  FROM (SELECT TOP (5 * (2 - 1)) ID FROM AISINO_BD_TelephoneRecord ORDER BY ID ASC) t
)
ORDER BY ID ASC;
  • 缺點:ID 有刪除/間隙會漏資料、不支援非唯一排序欄位。

寫法 3:ROW_NUMBER() 標準分頁 (SQL Server 2005+ 推薦)

SELECT *
FROM (
  SELECT TOP (5 * (1 - 1) + 5)
    ROW_NUMBER() OVER (ORDER BY Id ASC) AS rn, *
  FROM AISINO_BD_TelephoneRecord
) t
WHERE rn > 5 * (1 - 1);
  • 優點:支援任意排序、複合排序、無間隙問題、計畫穩定。
  • SQL Server 2012+ 可簡化為 OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY。

Oracle 對照 (原生 ROWNUM)

SELECT *
FROM (
  SELECT a.*, ROWNUM rn
  FROM (SELECT * FROM aos_rms_user) a
  WHERE ROWNUM <= 5 * (1 - 1) + 5
)
WHERE rn >= 5 * (1 - 1);

效能實測建議

場景推薦寫法
小表/低頻/簡單 PK寫法 1/2 皆可
大表/高頻/複雜排序寫法 3 (ROW_NUMBER) 或 OFFSET/FETCH
深分頁 (頁碼 > 1000)游標分頁 / Keyset Pagination (WHERE id > last_id ORDER BY id LIMIT N)

現代專案優先使用 OFFSET/FETCH (2012+) 或 ROW_NUMBER(),棄用 NOT IN 與 MAX(ID) 寫法。

copyright © 萬盛學電腦網 all rights reserved