Oracle、MySQL、SQL Server 三大數據庫分頁查詢語法對比與效能分析

核心語法速查表

MySQL：最簡單直觀

-- 前 10 行
SELECT * FROM T WHERE ... LIMIT 10;
-- 第 2 頁（每頁 10 行）
SELECT * FROM T WHERE ... LIMIT 10, 10;  -- offset=10, rows=10
-- 或標準寫法
SELECT * FROM T WHERE ... LIMIT 10 OFFSET 10;

注意：深分頁（如 LIMIT 1000000, 10）需掃描並丟棄前 100 萬行，效能線性下降。優化：覆蓋索引 + 延遲關聯或 Keyset 分頁（WHERE Id > @LastId LIMIT 10）。

Oracle：兩代方案並存

方案一：經典 ROWNUM 三層嵌套（11g 及以前主流）

SELECT * FROM (
    SELECT A.*, ROWNUM RN FROM (
        SELECT * FROM Table_Name ORDER BY Sort_Col
    ) A WHERE ROWNUM <= 40
) WHERE RN >= 21;  -- 第 21-40 行

• 內層排序 → 中層截斷最大行號 → 外層過濾最小行號。
• WHERE ROWNUM <= 40 可提前終止排序，關鍵優化點。

方案二：標準 OFFSET/FETCH（12c+）

SELECT * FROM Table_Name
ORDER BY Sort_Col
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;

執行計劃等價於方案一，語法標準、可移植。

SQL Server：從 ROW_NUMBER 到 OFFSET/FETCH

方案一：TOP + NOT IN（舊版、效能差）

SELECT TOP 10 * FROM TestTable
WHERE Id NOT IN (SELECT TOP 20 Id FROM TestTable ORDER BY Id)
ORDER BY Id;

子查詢隨頁碼增長掃描量線性增大，深分頁極慢。

方案二：TOP + MAX(Id) 子查詢（較好）

SELECT TOP 10 * FROM TestTable
WHERE Id > (SELECT MAX(Id) FROM (SELECT TOP 20 Id FROM TestTable ORDER BY Id) AS T)
ORDER BY Id;

利用索引求 MAX，避免 NOT IN 全掃描，效能顯著優於方案一。

方案三：游標存儲過程（通用但最慢）

使用 sp_cursoropen/fetch/close，適配任意查詢但開銷極大，僅作兜底。

方案四：ROW_NUMBER() 窗口函數（2005+ 標準）

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Id) AS Rn, *
    FROM TestTable
) T WHERE Rn BETWEEN 21 AND 30;

方案五：OFFSET/FETCH（2012+ 推薦）

SELECT * FROM TestTable
ORDER BY Id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

效能對比總結（百萬級測試）

通用最佳實踐：Keyset 分頁（游標分頁）

-- 首頁
SELECT * FROM T ORDER BY Id LIMIT 20;

-- 後續頁（記住上一頁最大 Id）
SELECT * FROM T WHERE Id > @LastMaxId ORDER BY Id LIMIT 20;

• 無 OFFSET 掃描，索引直接定位，恆定 O(log N)。
• 要求排序列唯一且不變（主鍵最佳），不支援隨機跳頁。
• 適合無限滾動、API 分頁、異步加載場景。

選型建議

1. 新項目、現代版本 → OFFSET/FETCH（標準 SQL:2008）。
2. 舊版本維護 → MySQL LIMIT、Oracle ROWNUM、SQL Server ROW_NUMBER()。
3. 高併發、深分頁、無限滾動 → Keyset 分頁。
4. 報表需隨機跳頁 → OFFSET/FETCH + 覆蓋索引，接受深分頁代價。