傳統 LIMIT offset, rows(MySQL)或 TOP(SQL Server)在 Offset 極大時效能急劇下降,因資料庫需掃描並捨棄前 N 筆記錄。
-- 假設主鍵為 id,需查第 10000 頁、每頁 20 筆
SELECT t.*
FROM `large_table` t
INNER JOIN (
SELECT id FROM `large_table` ORDER BY id LIMIT 200000, 20
) tmp USING (id);
原理:子查詢僅掃描索引(覆蓋索引)定位主鍵,再回表取完整欄位,避免大量回表開銷。
-- 建立測試環境
CREATE DATABASE TestForPaging;
GO
USE TestForPaging;
GO
CREATE TABLE SomeData (
id INT PRIMARY KEY,
name VARCHAR(30) NULL,
description TEXT
);
GO
-- 插入測試資料...
-- 核心分頁存儲過程
CREATE PROCEDURE GetDataPaged
@startRowIndex INT,
@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
CASE WHEN @sort = 'id' THEN id END DESC,
CASE WHEN @sort = 'name' THEN name END
) AS dataLevel
FROM SomeData
) AS datawithlevel
WHERE dataLevel > (@startRowIndex * @maximumRows)
AND dataLevel <= ((@startRowIndex + 1) * @maximumRows);
END
GO
注意:原文範例為 SQL Server T-SQL 語法(ROW_NUMBER() OVER、GO 批次分隔符),MySQL 8.0+ 才支援窗口函數,舊版需用變數模擬或採用上述延遲關聯法。
SELECT *,僅取必要欄位。WHERE id > last_seen_id ORDER BY id LIMIT 20),效能穩定不隨頁碼遞減。