面试考察点
- 能否解释大 OFFSET 仍需扫描并丢弃前置记录。
- 是否会用覆盖索引延迟关联或游标分页。
- 是否理解稳定排序和翻页一致性。
核心答案
LIMIT offset, size的 offset 很大时，MySQL 通常仍要扫描 offset + size 条记录并丢弃前面部分；若排序或筛选不能由覆盖索引完成，还会产生大量回表。连续翻页优先使用基于稳定排序键的游标分页。
例如按 (created_at, id) 倒序，下一页带上上一页最后一条的两个值，用小于条件继续查询，复杂度不再随页码线性增长。
常见方案
覆盖索引加延迟关联可以先在窄索引上选出目标主键，再回表获取少量完整行。只支持跳到有限页时可限制最大页数；离线导出应按主键或时间区间分批，而不是不断增加 OFFSET。
OFFSET 的真实成本
SELECT id, order_no, created_at
FROM orders
WHERE tenant_id = 1001
ORDER BY created_at DESC, id DESC
LIMIT 1000000, 20;
即使最终只返回 20 行，引擎仍要沿满足条件的索引扫描约 1000020 个候选，跳过前 100 万行。若查询列不在索引中，扫描到的候选还可能逐行回表；若 ORDER BY 无法利用索引顺序，还会出现大范围排序或临时表。成本随页码线性增加，缓存命中时可能只是慢，缓存未命中或并发高时会直接挤占 I/O。
EXPLAIN ANALYZE 中应重点比较实际扫描行数、回表次数、排序时间和返回行数。不要只看 SQL 文本里 LIMIT 很小就假定开销很小。
游标分页的完整写法
-- 第一页
SELECT id, order_no, created_at
FROM orders
WHERE tenant_id = :tenantId
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- 下一页，cursor 是上一页末条的 (created_at, id)
SELECT id, order_no, created_at
FROM orders
WHERE tenant_id = :tenantId
AND (created_at < :lastCreatedAt
OR (created_at = :lastCreatedAt AND id < :lastId))
ORDER BY created_at DESC, id DESC
LIMIT 20;
索引应与条件和排序一致，例如 (tenant_id, created_at DESC, id DESC)。游标不是简单的 id 字符串，而是“完整排序位置”；服务端可以把排序字段、查询条件版本和签名编码成不透明 token，防止客户端伪造或把 A 查询的游标用于 B 查询。
延迟关联什么时候有效
当列表页必须返回大字段而索引无法覆盖时，可先在窄索引中定位主键，再关联完整行：
SELECT o.*
FROM orders o
JOIN (
SELECT id
FROM orders FORCE INDEX (idx_tenant_time_id)
WHERE tenant_id = :tenantId
ORDER BY created_at DESC, id DESC
LIMIT :offset, :size
) page ON page.id = o.id
ORDER BY o.created_at DESC, o.id DESC;
它并没有消除 offset 扫描，但把“扫描一百万行完整宽记录”缩小为“扫描一百万个索引条目后回表 20 行”。必须用真实执行计划验证，盲目 FORCE INDEX 可能在不同参数下退化。
翻页一致性如何定义
用户翻页期间数据会新增、更新、删除。普通游标分页提供的是沿当前排序继续读取，不保证整套结果是某个时刻的快照；新插入且排在游标之前的数据通常不会出现在后页，这往往符合无限滚动预期。
需要报表级稳定结果时，可固定查询上界（例如 created_at <= requestStartedAt）、使用版本号或在可承受的事务快照中导出。强快照会占用更多资源，不能默认给所有在线列表使用。
COUNT(*) 与任意跳页
产品若要求“跳到第 5000 页”和精确总数，数据库无法同时免费提供。精确 count 可能扫描大范围索引；可使用近似数、异步统计、按时间分段、限制最大页数，或者把检索需求交给更适合的搜索系统。技术方案必须和产品交互能力一起讨论。
线上治理清单
- 为页面尺寸设置最大值，拒绝无限 size。
- 对 offset 设置可接受上限，并提供“按条件继续加载”的替代体验。
- 把导出、全量扫描移到异步任务，使用 seek 分批和限速。
- 监控深分页 SQL 指纹、扫描行数、临时表和慢查询占比。
- 索引变更前评估写放大、磁盘空间和在线 DDL 风险。
一致性设计
排序必须稳定且唯一，时间相同要追加主键。游标还应绑定查询条件和排序方向；数据实时变化时，需要接受弱一致翻页，或通过快照版本、时间上界固定结果集。
常见误区
只把 LIMIT 100000, 20 改成子查询不一定更快，关键是子查询能否覆盖索引、减少扫描列和回表。游标分页也不擅长任意跳转到第 N 页，这是产品能力上的取舍。
高频追问与参考回答
追问：为什么只用 id 作为游标可能不正确？
如果业务按时间或其他字段排序，id 顺序未必等同展示顺序；游标必须包含完整排序键，才能避免遗漏或重复。
追问：游标分页可以向上一页吗？
可以保存当前页首条的排序键，用相反比较符和反向排序查询前一批，再在应用层反转结果；实现比下一页复杂，要明确 token 中的方向和边界。
追问：深分页能靠分区表自动解决吗？
只有 WHERE 条件能有效裁剪到少量分区时才有帮助。单个分区内部的大 offset 仍要扫描，分区不替代正确索引和游标设计。
追问：为什么 SELECT * 更容易让深分页变慢？
宽行增加回表、页读取、网络传输和应用对象创建；列表接口只选必要列更容易实现覆盖索引，也减少总链路成本。
总结
深分页优化本质是减少无效扫描和回表：连续浏览用游标，特定场景用覆盖索引延迟关联，并明确一致性语义。
机制全景图
下面把「MySQL 深分页为什么慢？如何优化？」从输入到结果压缩成一条可复述的主链路。面试时先用图建立全局坐标，再进入局部实现，能避免只背零散结论。
flowchart LR
A["接收页码与排序"]
A --> B["索引扫描跳过 offset"]
B --> C["逐行回表或过滤"]
C --> D["取 limit 条记录"]
D --> E["返回下一页定位信息"]
完整链路：从输入到结果
沿着「接收页码与排序 → 索引扫描跳过 offset → 逐行回表或过滤 → 取 limit 条记录 → 返回下一页定位信息」观察输入、状态与输出，下面每个阶段都对应一个可以在源码、日志或系统表中验证的位置。
1. 接收页码与排序
LIMIT offset,limit 需要找到并丢弃前 offset 行，页码越深工作量线性增长。
2. 索引扫描跳过 offset
若排序索引不覆盖返回列，跳过的每行仍可能回表，I/O 放大更严重。
3. 逐行回表或过滤
WHERE 过滤不能完全在索引完成时，执行器还需读取记录再判断。
4. 取 limit 条记录
真正返回的行数很少，但 rows examined 和临时排序空间可能巨大。
5. 返回下一页定位信息
游标分页把上一页最后排序键作为下一页起点，扫描量接近 limit，并能提供更稳定翻页语义。
源码与实现定位
| 入口 | 阅读重点 |
|---|---|
| handler_read_next | 顺序扫描行计数 |
| EXPLAIN ANALYZE | offset 丢弃行的真实工作量 |
源码或系统表应按上表顺序追踪：先确认入口实际走到哪条路径，再用运行时数据验证，而不是仅凭类名或配置推测。
参数配置与可复现实验
SELECT id, created_at FROM orders
WHERE (created_at,id) < (?,?)
ORDER BY created_at DESC,id DESC LIMIT 100;
对 offset 0/10万/100万与游标分页画延迟曲线，并在并发插入下核对重复遗漏。
验证步骤与预期结果
1. 固定输入和基线
先在没有故障注入的环境执行上述配置，固定数据规模、并发度、运行时版本和预热时间。以「扫描/返回」为主基线，记录值应满足「游标接近 1」；同时保存 rows examined/returned、回表次数，使后续变化能够回到同一时间轴比较。
2. 从实现入口确认路径
在「handler_read_next」确认请求确实进入「顺序扫描行计数」对应的实现，再沿「EXPLAIN ANALYZE」观察「offset 丢弃行的真实工作量」。如果入口路径都未命中，就不应继续调整下游参数，而应先检查调用条件、版本或路由是否与假设一致。
3. 注入本文特有的失败模式
优先复现「只用非唯一时间字段做游标导致遗漏」，并把单一变量逐级放大，直到「扫描/返回」越过「offset 线性上涨」。随后再分别验证「深分页加缓存仍计算昂贵」和「COUNT(*) 与数据页一起拖慢接口」，三类故障分开执行，避免多个变量同时变化而无法归因。
4. 执行止损和根因修复
第一轮只应用「使用复合唯一游标」，确认它能控制影响范围；第二轮应用「全量导出改异步检查点」，验证核心链路恢复；最后落实「限制 max page/offset」，消除同类问题再次出现的条件。每一步都保留变更前后数据，不用“感觉变快了”替代测量。
5. 通过退出条件
实验只有同时满足三项才算通过：「扫描/返回」回到「游标接近 1」、「单页 P99」回到「各页稳定」、「重复遗漏」回到「稳定游标为 0」，并且业务结果差异为零。若性能恢复但结果不一致，仍应视为失败；若指标恢复后很快再次越线，则说明只完成了临时止损，没有消除根因。
量化基线
| 指标 | 样例基线/口径 | 风险线 | 结论 |
|---|---|---|---|
| 扫描/返回 | 游标接近 1 | offset 线性上涨 | 改游标 |
| 单页 P99 | 各页稳定 | 随页码增长 | 深分页 |
| 重复遗漏 | 稳定游标为 0 | 出现非零 | 排序不唯一 |
这些数值是实验口径或示例告警线，不是可复制到所有系统的固定答案；上线阈值应由本系统稳态、峰值和故障演练共同确定。
事故复盘：后台导出任务拖慢线上查询
导出按页码每次 offset 增加 1000，到后期单页扫描数百万行。改为按 (created_at,id) 复合游标推进，并记录检查点后，单批成本稳定且可断点续跑。
| 失败模式 | 首要证据 | 第一处置动作 |
|---|---|---|
| 只用非唯一时间字段做游标导致遗漏 | rows examined/returned | 使用复合唯一游标 |
| 深分页加缓存仍计算昂贵 | 回表次数 | 全量导出改异步检查点 |
| COUNT(*) 与数据页一起拖慢接口 | 页码与延迟曲线 | 限制 max page/offset |
发布与回滚检查点
- 发布前：确认「handler_read_next」对应实现和上述配置在目标版本仍然有效，并保存「扫描/返回」基线。
- 灰度中：同时观察 rows examined/returned、回表次数、页码与延迟曲线；任一指标越过表中风险线，就停止继续扩量。
- 回滚时：先执行「使用复合唯一游标」控制影响，再回退代码或参数；涉及持久状态时必须额外核对结果差异。
- 发布后：至少覆盖一个完整峰值周期，确认「只用非唯一时间字段做游标导致遗漏」没有再次出现，才关闭变更观察窗口。
方案对比与选型
| 方案 | 更适合的场景 | 主要收益 | 代价与边界 |
|---|---|---|---|
| offset 分页 | 浅页、允许随机跳页 | 接口简单、总数展示自然 | 深页线性变慢且并发变更会漂移 |
| Keyset 游标 | 顺序翻页与无限滚动 | 性能稳定、重复遗漏少 | 不能自然随机跳页 |
| 异步导出 | 全量或超大结果集 | 隔离在线请求、可断点 | 结果延迟且需任务治理 |
选型至少带上 数据规模、选择性、读写比、事务长度和峰值并发，并用上面的量化基线验证；未知数据应明确为待测假设。
设计边界与工程取舍
游标排序必须稳定且唯一，通常使用业务排序字段加主键；过滤条件和排序方向也要编码进游标，防止跨查询误用。
工程落地遵循：正确性由约束和事务兜底，性能优化必须用执行计划与测量验证。回答时直接引用「handler_read_next」、配置实验和事故数据，比复述固定模板更有说服力。