MySQL LIMIT 分頁查詢優化實戰：從淺分頁到深分頁的高效方案

前言

LIMIT offset, count 是 MySQL 分頁的標準語法，但隨著 offset 增大（深分頁），性能急劇下降。本文深度解析深分頁性能瓶頸原理，提供四種優化方案：延遲關聯、覆蓋索引、游標分頁、ID 範圍過濾，附適用場景對比。

問題現象：深分頁性能雪崩

-- 淺分頁：極快
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 0;

-- 深分頁：極慢（掃描 100 萬行丟棄 999,990 行）
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 1000000;

根因：MySQL 必須掃描 offset + count 行，丟棄前 offset 行，再返回 count 行。offset 越大，掃描越多，IO 與 CPU 消耗線性增長。

方案一：延遲關聯（Delayed Join）— 最通用

-- 原始慢查詢
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 1000000;

-- 優化：先只查主鍵，再回表
SELECT o.* 
FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 10 OFFSET 1000000
) tmp USING (id);

原理：子查詢只掃描主鍵索引（窄、快），外層按主鍵回表僅 10 行。將「掃描寬表 100 萬行」降為「掃描窄索引 100 萬行 + 回表 10 行」。

方案二：覆蓋索引（Covering Index）— 避免回表

-- 建立覆蓋索引（包含所有查詢列）
CREATE INDEX idx_orders_cover ON orders (id, user_id, amount, status, created_at);

-- 查詢只需掃描索引，無需回表
SELECT id, user_id, amount, status, created_at
FROM orders
ORDER BY id LIMIT 10 OFFSET 1000000;

條件：查詢列全部包含在索引中。代價：索引體積大、寫入開銷增大。

方案三：游標分頁 / Keyset Pagination — 高併發首選

-- 第一頁
SELECT * FROM orders WHERE id > 0 ORDER BY id LIMIT 20;

-- 後續頁：用上一頁最大 ID 作為游標
SELECT * FROM orders WHERE id > 1000020 ORDER BY id LIMIT 20;

優點：無 OFFSET，性能恆定、不受頁碼影響；天然防重複/遺漏（數據變更時）。
缺點：無法跳轉任意頁、需前端配合（傳遞游標而非頁碼）。

方案四：ID 範圍過濾（業務允許時）

-- 用戶只看「最近 30 天」訂單，不翻歷史深頁
SELECT * FROM orders 
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY id LIMIT 20 OFFSET 0;

適用：業務邏輯天然限制查詢範圍（如「最近 N 天」、「狀態=進行中」）。

方案對比總結

實戰建議決策樹

1. 能改業務改游標分頁？→ 首選方案三
2. 必須支援任意跳頁？→ 查詢列固定？→ 方案二（覆蓋索引）；查詢列不固定？→ 方案一（延遲關聯）
3. 有天然時間/狀態範圍？→ 方案四疊加上述方案

EXPLAIN 關鍵指標觀察

• rows：預估掃描行數（應遠小於 offset+limit）
• Extra：Using index（覆蓋索引）、Using where、Using temporary; Using filesort（需優化）

總結

深分頁本質是 OFFSET 掃描浪費。游標分頁徹底消除 OFFSET，是架構級解法；延遲關聯、覆蓋索引是存量系統低成本優化手段。根據業務約束選擇：API/無限滾動→游標；後台管理/任意跳頁→延遲關聯/覆蓋索引；有時間範圍→範圍過濾疊加。配合 EXPLAIN 驗證 rows 下降，即可量化優化效果。