一句话总结
深分页（LIMIT 1000000, 10）慢的根源：MySQL 必须扫描并丢弃前 100 万行，才能拿到 10 条数据，offset 越大越慢。四种解法按推荐度排序：游标分页（WHERE id > lastId LIMIT N，走主键索引，但只能顺序翻页）、覆盖索引 + 延迟关联（子查询只查 ID 再回表）、子查询定位起始 ID、换 ES / HBase。业务上首选用筛选、搜索、时间范围替代无底洞式的深翻页。
初级理解
什么是深分页
# 深分页：offset 很大的分页查询
SELECT * FROM user ORDER BY id LIMIT 10, 10; # 第2页，毫秒级
SELECT * FROM user ORDER BY id LIMIT 10000, 10; # 第1001页，开始卡
SELECT * FROM user ORDER BY id LIMIT 1000000, 10; # 第10万页，秒级甚至更慢
# 规律：offset 与耗时近似线性增长，表越大曲线越陡
# 分页翻到几十页之后，用户体验和数据库压力同时恶化
慢在哪：扫描 + 丢弃 + 回表
# MySQL 执行 LIMIT 1000000, 10 的三笔开销：
# 1. 扫描：按索引顺序读前 1000010 条记录
# 2. 丢弃：扔掉前 1000000 条，只留下最后 10 条的主键
# 3. 回表：10 个主键回聚簇索引取完整行（SELECT * 字段多时更贵）
# 关键认知：代价不在"取 10 行"，而在"扫 100 万行"
# 所以优化的核心是削减扫描量，而不是优化那 10 行
offset 与耗时参考
| offset | 扫描行数 | 量级参考（百万级表） | 处理建议 |
|---|---|---|---|
| 1,000 | 1,010 | 毫秒级 | 无需优化 |
| 100,000 | 100,010 | 几十到几百毫秒 | 关注，可优化 |
| 1,000,000 | 1,000,010 | 秒级 | 必须优化 |
| 10,000,000+ | 千万级 | 十秒级甚至超时 | 换方案（游标/ES） |
上表是数量级参考，实际耗时还与字段宽度、缓冲池命中率、ORDER BY 是否走索引有关。
中级深入
方案一：游标分页（首选）
# 思路：记住上一页最后一条的 ID，下一页从它之后继续查
SELECT * FROM user WHERE id > #{lastId} ORDER BY id LIMIT 10;
# 为什么快：id 是主键，WHERE id > ? 直接定位索引位置
# 每页只扫描 10 行，翻多深耗时都稳定，与 offset 彻底无关
# 限制：只能"下一页"，不能跳页（没有 offset 就没有随机访问）
# 适用：无限滚动、Feed 流、时间线、导出流水
方案二：覆盖索引 + 延迟关联
# 思路：把"扫 100 万行"限制在覆盖索引内，回表只发生在最后 10 行
SELECT u.* FROM user u
INNER JOIN (
SELECT id FROM user ORDER BY id LIMIT 1000000, 10
) t ON u.id = t.id;
# 拆解：
# 1. 子查询只查 id：走覆盖索引、不回表，百万行也只是个索引区间扫描
# 2. 外层 JOIN 只对 10 个 id 回表取完整数据
# 相比直接 SELECT * ... LIMIT 1000000,10，回表次数从百万降到 10
# 适用：必须支持跳页的后台管理列表
方案三：子查询定位起始 ID
# 思路：先廉价地定位起始主键，再按主键范围查数据
SELECT * FROM user
WHERE id >= (SELECT id FROM user ORDER BY id LIMIT 1000000, 1)
ORDER BY id LIMIT 10;
# 子查询同样走覆盖索引；拿到起始 id 后外层是主键范围查询
# 与延迟关联效果接近，写法更简单
# 依赖优化器对子查询的处理，推荐度略低于延迟关联
四种方案对比
| 方案 | 能否跳页 | 深分页性能 | 复杂度 | 适用场景 |
|---|---|---|---|---|
| 游标分页 | 否（仅顺序） | 极佳 | 低 | Feed、无限滚动 |
| 延迟关联 | 是 | 好 | 中 | 后台列表跳页 |
| 子查询定位 | 是 | 好 | 低 | 后台列表跳页 |
| ES / HBase | 是 | 极佳 | 高 | 搜索、超大规模 |
高级进阶
count(*) 也要治
# 分页通常附带 SELECT count(*)，大表上 count 同样是全表/全索引扫描
# 1. 近似总数：用 EXPLAIN 的 rows 估算，或维护一张计数表定时刷
# 2. 缓存总数：列表页只显示"约 X 条"，允许分钟级延迟
# 3. 只要"有没有下一页"：查 size+1 条，返回 hasMore 布尔值，不渲染总页数
# 4. 深分页场景直接去掉总数展示，比任何 count 优化都彻底
覆盖索引怎么选
# 延迟关联成立的前提：子查询走覆盖索引
# ORDER BY id LIMIT 1000000,10 → 主键索引本身就是覆盖索引，无需额外设计
# 若 ORDER BY create_time：建 (create_time, id) 联合索引，避免回表与文件排序
# 反例：ORDER BY status（区分度低）且无索引 → 文件排序，延迟关联也救不了
# 经验：先 EXPLAIN 确认 Extra 里出现 Using index，再上延迟关联
PageHelper 的深分页注意事项
# 1. PageHelper 只会拼 LIMIT，不会自动帮你做延迟关联
# 深分页要么手写 SQL，要么用 SqlParser 定制分页逻辑
# 2. startPage 只对紧跟的第一个查询生效，别在中间夹别的查询
# 3. reasonable=true：pageNum 超过总页数时查最后一页，避免无效深页
# 4. 限制 pageSize 上限（如 100），防止单页 limit=10000 直接拖库
# 5. 一对多嵌套结果映射分页不准：先分页查主表 ID，再批量关联查详情
换引擎与业务替代
# ES：search_after / scroll API 就是为深分页设计的，适合搜索场景
# HBase：按 rowkey 范围扫描，天然支持"从某条继续"
# TiDB 等分布式数据库：深分页性能优于单机 MySQL，但成本高
# 业务层替代（往往比技术优化更有效）：
# 1. 加筛选条件（时间范围、状态、关键词），把结果集压小
# 2. 换排序维度：按热度/相关性排序，用户很少翻到深处
# 3. 产品形态改游标：无限滚动替代页码，从根上消灭深分页
实战场景
# 场景1：游标分页（无限滚动）
-- Mapper 接口
List<User> selectByCursor(@Param("lastId") Long lastId, @Param("size") int size);
-- Mapper XML
SELECT * FROM user
WHERE id > #{lastId}
ORDER BY id
LIMIT #{size}
-- Controller：第一页 lastId 传 0
@GetMapping("/users/feed")
public List<User> feed(@RequestParam(required = false) Long lastId,
@RequestParam(defaultValue = "20") int size) {
return userMapper.selectByCursor(lastId == null ? 0L : lastId, size);
}
-- 前端：用返回列表最后一条的 id 作为下一次请求的 lastId
# 场景2：延迟关联（后台列表跳页）
-- Mapper XML：内层只查 id，外层回表
SELECT u.* FROM user u
INNER JOIN (
SELECT id FROM user
WHERE status = #{status}
ORDER BY create_time DESC, id DESC
LIMIT #{offset}, #{size}
) t ON u.id = t.id
-- 配合 (status, create_time, id) 联合索引，子查询走覆盖索引
-- 深页耗时从秒级回落到毫秒级
# 场景3：不要总数的分页接口
-- 查 size+1 条判断有没有下一页
List<User> list = mapper.selectPage(offset, size + 1);
boolean hasMore = list.size() > size;
if (hasMore) list.remove(list.size() - 1);
return Map.of("list", list, "hasMore", hasMore);
-- 响应体里没有 total，深页接口也稳定在毫秒级
# 场景4：PageHelper 防护配置
# application.yml
pagehelper:
reasonable: true # pageNum 越界时返回最后一页，避免无效深页
page-size-zero: true # pageSize=0 查全部（谨慎开启）
support-methods-arguments: true
params: pageNum=pageNum;pageSize=pageSize
# 代码里再兜底：pageSize 超过 100 直接拒绝，防止拖库
面试模拟
面试官：LIMIT 1000000, 10 为什么慢？
你：MySQL 要按顺序扫描前 1000010 行、丢弃前 100 万行，最后只回表 10 行。代价在扫描量而不在结果集，所以 offset 越大越慢，且和表数据量正相关。
面试官：深分页怎么优化？
你：按场景选：① 顺序翻页用游标分页，WHERE id > lastId LIMIT N，走主键索引，性能与页深度无关；② 必须跳页用覆盖索引 + 延迟关联，子查询只查 ID 再回表，把回表从百万次降到 10 次；③ 也可以用子查询定位起始 ID；④ 搜索类场景上 ES 的 search_after。再往上一层，用筛选条件、时间范围、无限滚动从业务上减少深分页。
面试官：游标分页不能跳页，产品要求跳页怎么办？
你：跳页场景用延迟关联或子查询定位，保证任意页性能；同时把 count(*) 换成 hasMore（查 size+1 条）或近似总数，避免大表 count 成为新瓶颈。如果产品其实只需要"加载更多"，就推动改游标形态，从根上消灭问题。