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()*