資料量達百萬以上時,傳統 LIMIT offset, size 分頁效能會隨著 offset 增大而劇烈下降。本文基於 Percona 2009 大會 Yahoo 團隊分享,完整解析高效分頁的原理與實作方案。
EXPLAIN SELECT * FROM `message` ORDER BY `id` DESC LIMIT 10000, 20;
-- type: index, rows: 10020
-- MySQL 必須掃描前 10000 行再丟棄,隨著 offset 增大 I/O 暴增
COUNT(*) 統計總筆數同樣昂貴利用唯一且有序的鍵值(如自增 ID)作為「游標」,以 WHERE id < last_seen_id 替代 OFFSET。
-- 客戶端記住上一頁最後一筆的 id = 100
SELECT * FROM `message`
WHERE `id` < 100
ORDER BY `id` DESC
LIMIT 20;
-- 客戶端記住當前頁第一筆的 id = 98
SELECT * FROM `message`
WHERE `id` > 98
ORDER BY `id` ASC
LIMIT 20;
-- 取得結果後在應用層反轉順序
當排序欄位有重複值,需加入次要鍵(通常是 PK)確保順序確定性:
-- 第一頁
SELECT `thumbs_up`, `id` FROM `message`
ORDER BY `thumbs_up` DESC, `id` DESC
LIMIT 20;
-- 假設最後一筆:thumbs_up=98, id=13
-- 下一頁
SELECT `thumbs_up`, `id` FROM `message`
WHERE `thumbs_up` < 98
OR (`thumbs_up` = 98 AND `id` < 13)
ORDER BY `thumbs_up` DESC, `id` DESC
LIMIT 20;
建立複合索引 (thumbs_up, id),查詢只需掃描索引:
EXPLAIN SELECT `thumbs_up`, `id` FROM `message`
WHERE `thumbs_up` <= 98 AND (`id` < 13 OR `thumbs_up` < 98)
ORDER BY `thumbs_up` DESC, `id` DESC LIMIT 20;
-- Extra: Using where; Using index (Covering Index)
若需 SELECT *,先用覆蓋索引取 PK,再 JOIN 回表:
SELECT m2.*
FROM `message` m1
JOIN `message` m2 ON m1.`id` = m2.`id`
WHERE m1.`thumbs_up` <= 98 AND (m1.`id` < 13 OR m1.`thumbs_up` < 98)
ORDER BY m1.`thumbs_up` DESC, m1.`id` DESC
LIMIT 20;
LIMIT 10000, 20:~600 QPSLIMIT 20:~3,700 QPS(提升 6 倍)游標分頁無法直接跳轉任意頁。解決方案:
游標分頁是大資料量分頁的標準解法,配合覆蓋索引與延遲關聯,能獲得穩定的低延遲體驗。