萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 百萬級分頁優化:從 LIMIT 偏移到游標分頁的效能躍升實戰

資料量達百萬以上時,傳統 LIMIT offset, size 分頁效能會隨著 offset 增大而劇烈下降。本文基於 Percona 2009 大會 Yahoo 團隊分享,完整解析高效分頁的原理與實作方案。

為什麼 OFFSET 分頁會變慢?

EXPLAIN SELECT * FROM `message` ORDER BY `id` DESC LIMIT 10000, 20;
-- type: index, rows: 10020
-- MySQL 必須掃描前 10000 行再丟棄,隨著 offset 增大 I/O 暴增
  • 大偏移導致大量無用資料讀入記憶體
  • 資料超過記憶體時觸發磁碟 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;

效能對比(5000 萬筆測試)

  • LIMIT 10000, 20:~600 QPS
  • 游標分頁 LIMIT 20:~3,700 QPS(提升 6 倍)

總筆數統計替代方案

  1. 不顯示總數,改用「下一頁」按鈕
  2. 快取總數,定期非同步更新
  3. 顯示「Top 1000+」等模糊數字
  4. 觸發器維護計數表

SEO 與深層頁面存取

游標分頁無法直接跳轉任意頁。解決方案:

  • 預讀額外幾頁資料,產生前後幾頁連結
  • 小範圍 OFFSET(如 < 100)配合游標,兼顧跳頁與效能

游標分頁是大資料量分頁的標準解法,配合覆蓋索引與延遲關聯,能獲得穩定的低延遲體驗。

copyright © 萬盛學電腦網 all rights reserved