MySQL 深分頁優化與條件分頁：把 OFFSET 換成“游標”，再用覆蓋索引抄近路

這不是“玄學調優”，而是可復制的方案。本文用可復現的 DDL/造數腳本，演示為什么 OFFSET 越大越慢，如何用 條件游標（Keyset Pagination） 替換它，并配上 覆蓋索引。還會教你看 EXPLAIN/EXPLAIN ANALYZE 與慢日志，拿到優化前后的硬指標。

文章目錄

• MySQL 深分頁優化與條件分頁：把 OFFSET 換成“游標”，再用覆蓋索引抄近路
• • @[toc]
  • 0. TL;DR（先給答案）
  • 1. 可復現環境（DDL/造數）
  • 2. 為什么深分頁用 OFFSET 會慢？
  • 3. 條件游標（Keyset Pagination）：用邊界替代偏移
  • • 3.1 基本寫法（全站時間線）
    • 3.2 用戶頁/篩選頁
    • 3.3 穩定性與并發插入
  • 4. 覆蓋索引：一頁信息“在索引里就夠了”
  • 5. 聯表場景的分頁套路
  • • 5.1 先定位 id，再回表取詳情
    • 5.2 標簽/多條件
  • 6. Explain 前后對比：怎么看才算“快了”
  • 7. 開慢日志 & 抓“優化前/后”的證據
  • 8. API 接口如何落地（游標憑證）
  • 9. 邊界與常見坑
  • 10. 一頁就抄的“覆蓋索引清單”
  • 11. 演示 SQL（復制即可跑）
  • 12. 收尾

0. TL;DR（先給答案）

• 深分頁不要 OFFSET：LIMIT 20 OFFSET 100000 會讓 InnoDB 掃描并丟棄前 10 萬行。
• 用 條件游標：按穩定排序鍵（如 created_at, id）記住“上一頁最后一條”的邊界，下一頁用
  WHERE (created_at,id) < (?,?) ORDER BY created_at DESC, id DESC LIMIT 20。
• 覆蓋索引：如果一頁只展示 id/created_at/total，就建立 (created_at DESC, id DESC, total) 組合索引，查詢即走 Index Only Scan，無需回表。
• 監控與驗證：EXPLAIN ANALYZE 看“rows examined/loops/時間”，開啟慢日志看是否還在爆。

---

1. 可復現環境（DDL/造數）

直接在 MySQL 8.0+ 執行；數據量不大也能看出差距，想更明顯把 N_ORDERS 調大。

CREATE DATABASE IF NOT EXISTS demo;
USE demo;DROP TABLE IF EXISTS orders;
CREATE TABLE orders (id BIGINT PRIMARY KEY AUTO_INCREMENT,user_id BIGINT NOT NULL,status ENUM('CREATED','PAID','CANCELLED') NOT NULL,total_cents INT NOT NULL,created_at DATETIME NOT NULL,KEY idx_ctime_id (created_at DESC, id DESC),                 -- 全局時間倒序翻頁KEY idx_user_ctime_id (user_id, created_at DESC, id DESC),   -- 用戶維度翻頁KEY idx_status_ctime (status, created_at DESC)               -- 常見過濾
) ENGINE=InnoDB;-- 造 20 萬行（遞歸 CTE）
WITH RECURSIVE seq AS (SELECT 1 AS nUNION ALLSELECT n+1 FROM seq WHERE n < 200000
)
INSERT INTO orders (user_id, status, total_cents, created_at)
SELECT1 + FLOOR(RAND()*5000)  AS user_id,ELT(1+FLOOR(RAND()*