MySQL 大數據分頁優化實戰：從 OFFSET 掃描到延遲關聯/游標式分頁的效能躍升

MySQL 大數據分頁優化實戰

LIMIT 1000000, 10 是經典效能殺手：MySQL 必須掃描前 100 萬行丟棄，再取 10 行。隨著偏移量增大，響應時間線性增長。本文對比三種分頁模式，給出「延遲關聯」與「游標式 (Keyset Pagination)」兩大優化方案，實現百萬級數據毫秒級翻頁。

問題重現：OFFSET 分頁原理與瓶頸

-- 第 100001 頁，每頁 10 條
SELECT * FROM orders WHERE status=1 ORDER BY id DESC LIMIT 1000000, 10;

執行流程：

1. 按 status=1 過濾 (或全表掃描)。
2. 按 id DESC 排序 (可能 filesort)。
3. 跳過前 1,000,000 行 (CPU 比較、內存拷貝、網路傳輸全參與)。
4. 返回第 1,000,001 ~ 1,000,010 行。

偏移量越大，丟棄工作越多，QPS 急劇下降。

方案一：延遲關聯 / 子查詢回表法 (兼容所有版本)

核心思想：子查詢只走覆蓋索引取主鍵，再回表取完整列，避免大量無用列參與排序與傳輸。

-- 優化前：全列掃描排序
SELECT * FROM orders WHERE status=1 ORDER BY id DESC LIMIT 1000000, 10;

-- 優化後：子查詢只取 id (覆蓋索引 idx_status_id)
SELECT o.*
FROM orders o
JOIN (
    SELECT id
    FROM orders
    WHERE status=1
    ORDER BY id DESC
    LIMIT 1000000, 10
) AS derived USING (id);

效能提升點：

• 子查詢 SELECT id 走 idx_status_id (status, id) 覆蓋索引，無回表、無 filesort (索引已有序)。
• 外層 JOIN ... USING (id) 只回表 10 次 (主鍵精確查找)。
• 典型場景：百萬偏移從 2 秒 → 20 毫秒 (100x)。

方案二：游標式分頁 / Keyset Pagination (極致效能、需業務配合)

核心思想：不使用 OFFSET，改用「上一頁最後一條記錄的排序鍵值」作為下一頁起點。

-- 首頁
SELECT * FROM orders WHERE status=1 ORDER BY id DESC LIMIT 10;

-- 記錄最後一條 id = 999990
-- 下一頁
SELECT * FROM orders WHERE status=1 AND id < 999990 ORDER BY id DESC LIMIT 10;

-- 再下一頁 (假設最後 id = 999980)
SELECT * FROM orders WHERE status=1 AND id < 999980 ORDER BY id DESC LIMIT 10;

優勢：

• 無論翻到第幾頁，皆為 range 索引查找 + LIMIT，時間複雜度 O(log N + K)，極其穩定。
• 無「深分頁性能退化」問題。

適用限制：

• 不支援隨機跳頁 (無法直接跳到第 50 頁)，僅支援「下一頁/上一頁/首頁」。
• 排序鍵必須唯一 (主鍵或唯一索引)，否則需 (sort_col, pk) 組合游標。
• 前端/APP 需維護游標狀態 (last_id、last_sort_val)。

方案三：覆蓋索引 + 條件下推 (ICP) 綜合優化

建立最優索引支撐上述兩種方案：

方案SQL 模式執行時間適用場景


原生 OFFSETLIMIT 1000000, 10~2.5 秒僅極小偏移 (<1000)
延遲關聯子查詢取 id + JOIN 回表~20 毫秒需隨機跳頁、兼容舊版
游標式WHERE id < last_id LIMIT 10~3 毫秒無限滾動、列表流、API 分頁



總數查詢優化 (COUNT(*))
分頁組件常需總記錄數：

精確總數：SELECT COUNT(*) FROM orders WHERE status=1 (走覆蓋索引 idx_status_id 快速掃描)。
估算總數：EXPLAIN SELECT * FROM orders WHERE status=1 觀察 rows 欄位，或 SHOW TABLE STATUS LIKE 'orders' 的 Rows (非事務安全但極快)。
緩存總數：Redis 緩存 count:orders:status=1，異步更新 (定時任務/觸發器)。


避坑指南

ORDER BY 欄位無索引 → 必然 filesort，優化無效。必須建立 (where_col, order_col) 複合索引。
複合排序：ORDER BY status, created_at DESC 游標需雙值 (status, created_at)，SQL 改 WHERE (status, created_at) < (last_status, last_created_at) (元組比較 8.0+ 支援)。
並發寫入導致游標漏讀/重讀：新插入記錄 id > last_id 不影響；但若排序非單調 (如按更新時間)，需業務接受微小不一致或加版本號。
中間件分頁：ProxySQL/ShardingSphere 分頁重寫需驗證邏輯正確性。


總結
MySQL 分頁優化鐵律：**拒絕大偏移 OFFSET**。

• 需隨機跳頁 → **延遲關聯 (子查詢覆蓋索引取主鍵 + JOIN 回表)**。

• 僅順序翻頁/無限滾動 → **游標式分頁 (Keyset Pagination)**，效能極致穩定。

配合覆蓋索引、ICP、總數緩存，構建百萬級數據毫秒級分頁體驗。