MySQL 大數據量分頁效能優化：避免深分頁掃描的實戰方案

MySQL 大數據量分頁效能優化：避免深分頁掃描的實戰方案

傳統 LIMIT offset, rows 在大偏移量時需掃描大量資料再丟棄，效能急劇下降。本文提供基於 ROW_NUMBER() 窗口函數的優化方案（適用 MySQL 8.0+）。

優化邏輯

1. 先按排序欄位賦予每行連續序號 dataLevel
2. 直接查詢目標序號範圍，避免掃描前 N 行

完整測試代碼

-- 建立測試環境
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 筆（對應 LIMIT 1,3）
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,   -- 起始頁碼（0-based）
    @maximumRows INT,     -- 每頁筆數
    @sort VARCHAR(50)     -- 排序欄位
)
AS
BEGIN
    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);
END;

MySQL 5.7 及以下替代方案

• 延遲關聯： SELECT * FROM t INNER JOIN (SELECT id FROM t ORDER BY create_time LIMIT 10000, 10) AS tmp USING(id);
• 書籤式分頁： SELECT * FROM t WHERE create_time < last_seen_time ORDER BY create_time DESC LIMIT 10;（需前端配合傳遞上一頁最後時間）
• 覆蓋索引： 確保排序欄位+主鍵組成覆蓋索引，減少回表

核心原則： 深分頁必須避免 LIMIT 大偏移量, 小行數。MySQL 8.0+ 首選窗口函數，舊版用延遲關聯或書籤式分頁。配合覆蓋索引效果最佳。