MySQL 大數據量分頁查詢優化：ROW_NUMBER() 方案實測

問題背景

傳統 LIMIT offset, rows 在大偏移量時效能急劇下降，因 MySQL 需掃描並丟棄前 offset 行。優化思路：先定位主鍵 ID 範圍，再回表查試完整欄位。

優化方案：使用 ROW_NUMBER() 窗口函數

適用於 MySQL 8.0+（支援窗口函數）。測試腳本：

-- 建立測試庫表
CREATE DATABASE TestForPaging;
USE TestForPaging;

CREATE TABLE SomeData (
  id INT PRIMARY KEY,
  name VARCHAR(30),
  description TEXT
);

-- 插入樣本資料
INSERT INTO SomeData VALUES
(1,'num1','第1條'), (2,'num2','第2條'), (3,'num3','第3條'),
(4,'num4','第4條'), (5,'num5','第5條');

-- 總筆數
SELECT COUNT(*) FROM SomeData;

-- 為每行生成排序後的行號
SELECT name, description, ROW_NUMBER() OVER (ORDER BY id DESC) AS dataLevel
FROM SomeData;

-- 查詢指定頁面（如第 2-4 條）
SELECT dataLevel, name, description FROM (
  SELECT name, description, ROW_NUMBER() OVER (ORDER BY id DESC) AS dataLevel
  FROM SomeData
) AS t
WHERE dataLevel BETWEEN 2 AND 4;

-- 封裝為存儲過程
CREATE PROCEDURE GetDataPaged (
  @startRowIndex INT,
  @maximumRows INT,
  @sort VARCHAR(100)
)
BEGIN
  IF LENGTH(@sort) = 0 THEN SET @sort = 'id'; END IF;
  
  SELECT dataLevel, name, description FROM (
    SELECT name, description, ROW_NUMBER() OVER (ORDER BY @sort DESC) AS dataLevel
    FROM SomeData
  ) AS t
  WHERE dataLevel > (@startRowIndex * 10)
    AND dataLevel <= (@startRowIndex * 10 + @maximumRows);
END;

核心原理

1. ROW_NUMBER() OVER (ORDER BY ...) 為結果集生成連續行號
2. 外層查詢直接按行號範圍篩選，避免大偏移量掃描
3. 若需顯示完整欄位，可先查主鍵 ID 範圍，再 JOIN 回表

MySQL 5.7 及以下替代方案

-- 方案 1：子查詢鎖定 ID 範圍
SELECT * FROM SomeData
WHERE id >= (SELECT id FROM SomeData ORDER BY id LIMIT 100000, 1)
ORDER BY id LIMIT 20;

-- 方案 2：使用變數模擬 ROW_NUMBER()
SET @rownum = 0;
SELECT * FROM (
  SELECT @rownum := @rownum + 1 AS rownum, t.*
  FROM SomeData t
  ORDER BY id
) AS t2
WHERE rownum BETWEEN 100001 AND 100020;

效能對比（百萬級測試）

最佳實踐建議

• MySQL 8.0+ 首選 ROW_NUMBER() 窗口函數，語法清晰且效能最佳
• 舊版本用「子查詢鎖定 ID + 回表」方案，需確保主鍵連續遞增
• 避免在大偏移量場景直接使用 LIMIT offset, rows
• 配合覆蓋索引（Covering Index）進一步減少回表開銷

掌握分頁優化技巧，能讓高併發、大數據量的列表查詢保持毫秒級響應。