Oracle、DB2、MySQL 分頁查詢語法大對比：ROWNUM vs LIMIT vs ROW_NUMBER() 最佳實踐

三大主流數據庫分頁查詢語法深度對比與性能優化指南

分頁查詢是應用開發高頻需求，Oracle、DB2、MySQL 在語法與優化機制上存在顯著差異。本文系統對比三種核心方案（分析函數/ROWNUM/LIMIT），提供跨平台兼容寫法與執行計劃優化技巧。

一、核心方案對比表

二、Oracle 三大分頁寫法性能實測

測試表：`TAB (TNAME, TABTYPE)`，100萬行，`TNAME` 有索引

1. 分析函數 `ROW_NUMBER()` — 最推薦（10g+）

SELECT tname, tabtype FROM (
  SELECT tname, tabtype, ROW_NUMBER() OVER (ORDER BY tname) rn FROM tab
) WHERE rn BETWEEN 100 AND 150;

優勢：

• 語義清晰，支援複雜排序/分組（`PARTITION BY`）
• 優化器可下推 predicate，產生 `WINDOW SORT PUSHED RANK`
• 12c+ 可改寫為 `OFFSET 99 ROWS FETCH NEXT 50 ROWS ONLY`

2. `ROWNUM` 兩層嵌套 — 經典兼容寫法（8i+）

SELECT tname, tabtype FROM (
  SELECT tname, tabtype, ROWNUM rn FROM tab WHERE ROWNUM <= 150
) WHERE rn >= 100;

關鍵限制：

• 內層 `WHERE ROWNUM <= 150` 必須在 `ORDER BY` 之前
• 若需排序，必須在子查詢中完成：

SELECT * FROM (
  SELECT tname, tabtype, ROWNUM rn FROM (
    SELECT tname, tabtype FROM tab ORDER BY tname
  ) WHERE ROWNUM <= 150
) WHERE rn >= 100;

性能陷阱：大偏移量時仍需掃描前 N 行，`OFFSET 1000000` 極慢。

3. `FETCH FIRST` — 12c+ 標準語法

SELECT tname, tabtype FROM tab ORDER BY tname
OFFSET 99 ROWS FETCH NEXT 50 ROWS ONLY;

內部轉換為 `ROW_NUMBER()`，性能等效，語法最標準。

三、進階場景：去重分頁與並列排名

測試數據：`MYNUMBER (ID, NAME)`，ID 有重複值

場景 1：取前 10 個不同 ID 的所有行

-- 方案 A：DISTINCT + ROWNUM
SELECT id, name FROM mynumber WHERE id IN (
  SELECT id FROM (SELECT DISTINCT id FROM mynumber) WHERE ROWNUM <= 10
);

-- 方案 B：DENSE_RANK() — 推薦
SELECT * FROM (
  SELECT id, name, DENSE_RANK() OVER (ORDER BY id) dr FROM mynumber
) WHERE dr <= 10;

場景 2：取前 10 行，含並列排名（第 10 名並列全取）

-- RANK() 允許跳號，DENSE_RANK() 不跳號
SELECT * FROM (
  SELECT id, name, RANK() OVER (ORDER BY id) rk FROM mynumber
) WHERE rk <= 10;

場景 3：純前 10 行（含重複 ID）

-- 簡單 ROWNUM
SELECT id, name FROM mynumber WHERE ROWNUM <= 10;

-- ROW_NUMBER() — 確定性排序
SELECT * FROM (
  SELECT id, name, ROW_NUMBER() OVER (ORDER BY id) rn FROM mynumber
) WHERE rn <= 10;

四、跨平台兼容寫法策略

策略 1：ORM/框架層抽象（推薦）

• Hibernate：`setFirstResult`/`setMaxResults` 自動生成方言 SQL
• MyBatis：`RowBounds` + 方言攔截器
• Spring Data：`Pageable` 統一介面

策略 2：SQL 模板雙維護

  SELECT * FROM (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY ${sort}) rn FROM ${table} t
    
  ) WHERE rn BETWEEN #{start} AND #{end}




  SELECT * FROM ${table}
  
  ORDER BY ${sort}
  LIMIT #{offset}, #{pageSize}

五、大偏移量優化方案（百萬級 OFFSET）

1. 游標分頁：記住上一頁最大主鍵，`WHERE id > :last_id ORDER BY id LIMIT n`
2. 延遲關聯：先分頁查主鍵，再 `JOIN` 回表取明細
3. 物化視圖/緩存：熱點分頁預計算存 Redis

六、執行計劃關鍵操作識別

七、總結建議

• 新專案：優先 `ROW_NUMBER()` / `OFFSET FETCH`（標準、可移植、優化器友好）
• 舊版 Oracle：`ROWNUM` 兩層嵌套，注意排序順序
• 大偏移量：拒絕 OFFSET，改用游標/延遲關聯
• 去重/並列：`DENSE_RANK`/`RANK` 替代 `DISTINCT + ROWNUM`