MySQL 大表分页怎样避免深分页扫描

我在给订单列表加“跳到很后面一页”时，最先看到的不是返回结果变少，而是查询开始为前面的页付费：LIMIT 200000, 20 仍然要找到并跳过前 200000 行。MySQL 官方手册也提醒，LIMIT 配合无法由索引满足的排序时，可能要先选出并排序大量匹配行。

直接做法是把页码分页改成游标分页（keyset pagination）：用 created_at 和唯一的 id 组成稳定排序，把上一页最后一条记录作为游标，下一页用范围条件继续读，并让联合索引匹配过滤和排序。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/limit-optimization.html

大表分页的关键不是把 offset 写得更聪明，而是让数据库从“第几页”改为“从哪个已知位置继续读取”。如果业务必须跳到任意页，就保留传统分页；如果主要是下一页、加载更多或按时间浏览，优先考虑 keyset pagination。

消息是什么：深分页慢在要丢弃前面的行

传统写法通常是下面这样：

-- 这是页码分页：offset 越大，数据库需要处理的前置记录越多
SELECT id, created_at, total_amount
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 200000, 20;

即使最终只返回 20 行，offset 也描述了需要越过多少行。若排序键与过滤条件没有合适的索引，MySQL 还可能进入额外排序；若查询需要回表，前面被丢弃的候选行也会增加读取成本。这里的“慢”不是固定由某一个参数造成，而是扫描、排序、回表和丢弃叠加后的结果。

适用场景：先确认你真的不需要任意页跳转

我会先看接口的交互方式。消息流、订单时间线、审计记录和后台“加载更多”通常只需要拿到当前页之后的下一页，这类场景非常适合游标分页。客户端保存一个不透明游标，服务端从游标恢复排序键即可。

但报表用户可能会输入页码 50 并要求精确跳转，或者产品必须显示总页数。这时 keyset pagination 不能直接替代所有能力：可以把列表浏览改成游标方式，同时把统计总数、筛选和跳页设计成独立接口，不要为了保留页码而让主列表一直承受深 offset。

快速试用：用稳定排序键定位下一页

先把排序写完整。时间戳可能重复，所以只按 created_at 排序不够；加入唯一的 id 后，每一行都能在排序中得到稳定位置。

-- 第一页只负责建立游标，排序顺序必须和后续查询完全一致
SELECT id, created_at, total_amount
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;

假设这一页最后一行是 created_at = '2026-10-22 18:30:00'、id = 98120，下一页使用“更早的时间，或同一时间但 id 更小”的条件：

-- 游标由上一页最后一行产生；DESC 排序对应小于比较
SELECT id, created_at, total_amount
FROM orders
WHERE user_id = 42
  AND (
    created_at 

这条语句表达的是“从上一页末尾之后继续取”，而不是“先找到第 200001 行再丢掉前面所有行”。游标可以在接口层编码成 Base64 或签名字符串，但编码只是传输形式，服务端仍应校验字段格式、用户筛选条件和排序版本。

和旧方案对比：索引必须同时照顾过滤和顺序

查询改成范围条件后，还要让索引路径与它匹配。一个常见起点是把等值过滤列放在前面，再放联合排序列：

-- 索引顺序服务于 user_id 过滤，再服务于时间和主键排序
CREATE INDEX idx_orders_user_created_id
ON orders (user_id, created_at, id);

实际是否使用这个索引仍由优化器结合数据分布和查询列决定，不能只看索引名字。可以用 EXPLAIN 观察访问类型、候选行数和是否出现额外排序；不要把一次执行计划当成所有数据规模下的保证。

两种方案不是互斥的。我的经验是：搜索结果页保留浅页的 offset，持续向后浏览的主列表使用游标；不要为了“接口统一”让所有入口都采用同一套分页方式。

采用风险：倒序、重复时间和筛选变化要先定规则

倒序要反过来比较。上面的示例是 DESC，因此下一页用小于号；若改为 ASC，就要使用大于号。不要只改 ORDER BY 而忘记范围条件。

游标必须和查询条件绑定。用户切换 user_id、状态、时间范围或排序方向后，旧游标就不应继续使用。服务端可以把筛选摘要、排序版本和最后一行键一起签名，条件不一致时返回重新开始。

新增数据会改变“页码”的含义。keyset 的优势是继续从确定边界读取，不承诺传统页码在实时数据下永远不变。如果业务需要导出一份稳定快照，应增加明确的时间上界或快照标识，而不是把游标分页伪装成静态页码。

末页用数量判断。请求 size + 1 行，返回前 size 行并根据是否多出一行设置 has_more。这样不需要先执行昂贵的总数统计，也能告诉客户端是否继续加载。

-- 多取一行只用于判断是否还有下一页，应用层返回时丢弃这一行
SELECT id, created_at, total_amount
FROM orders
WHERE user_id = 42
  AND (created_at, id) 

行构造比较能让 SQL 更短，但团队应结合目标 MySQL 版本、索引和执行计划确认表达方式；如果需要把边界写得更直白，可以继续使用前面的 OR 条件。关键不在语法长短，而在排序键、比较方向和联合索引三者一致。

我的结论：先改列表语义，再改 SQL

避免 MySQL 深分页扫描的落点有三件事：把排序变成“时间 + 唯一键”的稳定顺序，为过滤和排序建立匹配索引，用上一页末尾值替代大 offset。改造前后都应针对真实数据量查看 EXPLAIN，并分别观察浅页、后页、筛选变化和并发写入下的行为。

如果产品强依赖“跳到第 N 页”，keyset 不是万能替换；如果产品主要是下一页和加载更多，它通常更贴近用户真正的浏览方式。先确认这个边界，再决定是否迁移，往往比单纯给原查询加一个索引更可靠。

深分页是不是一定要改成游标分页？

不一定。页数浅、数据量小或必须任意页跳转时，传统分页更直接；只有后页成本明显且交互允许“从当前位置继续”时，游标分页才更合适。

只给 created_at 建索引可以吗？

如果 created_at 重复，排序边界可能不稳定。通常应加入唯一键作为第二排序列，并让索引顺序与过滤列、排序列共同匹配。

为什么改了 SQL 还是出现 filesort？

可能是过滤条件、排序顺序、表达式或数据分布使优化器选择了其他路径。用 EXPLAIN 分别看 key、rows 和 Extra，再确认索引是否真的覆盖了查询形状。