MySQL 大數據分頁效能優化：ROW_NUMBER() 窗口函數避免深分頁掃描

傳統 LIMIT offset, size 深分頁 (大 offset) 需掃描大量行再丟棄，效能極差。利用窗口函數 ROW_NUMBER() 先標記序號再篩選，僅讀取顯示資料。

測試環境建立

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 name, description,
  ROW_NUMBER() OVER (ORDER BY id DESC) AS dataLevel
FROM SomeData;

指定層級範圍查詢 (分頁)

SELECT dataLevel, name, description FROM (
  SELECT name, description,
    ROW_NUMBER() OVER (ORDER BY id DESC) AS dataLevel
  FROM SomeData
) AS datawithlevel
WHERE dataLevel BETWEEN 2 AND 4;

封裝為預存程序

CREATE PROCEDURE GetDataPaged
  @startRowIndex INT,
  @maximumRows INT,
  @sort VARCHAR(50)
AS
  IF LEN(@sort) = 0 SET @sort = 'id';
  SELECT dataLevel, name, description FROM (
    SELECT name, description,
      ROW_NUMBER() OVER (ORDER BY @sort DESC) AS dataLevel
    FROM SomeData
  ) AS datawithlevel
  WHERE dataLevel > (@startRowIndex * 10)
    AND dataLevel <= (@startRowIndex * 10 + @maximumRows);

注意：此語法為 SQL Server (T-SQL)。MySQL 8.0+ 同樣支援 ROW_NUMBER() OVER ()，參數化排序需動態 SQL 或應用層組裝。

MySQL 8.0 實作對應

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY id DESC) AS rn
  FROM SomeData
) t WHERE rn > (page-1)*pageSize AND rn <= page*pageSize;

配合覆蓋索引 (id, name, description) 可實現純索引掃描，極大提升百萬級資料分頁效能。