我为什么把“分页查询”从“能查出来就行”改成了“上线前必审”

刚工作那两年，我对分页的理解就是 LIMIT offset, size。前端要第几页就传 pageNum，后端算一下 offset = (pageNum - 1) * pageSize，拼进 SQL 里执行。接口能返回数据，前端能翻页，万事大吉。

直到运营找我说“后台订单列表翻到第 80 页就特别慢，要七八秒才出来”，我才第一次认真看了一眼那个分页 SQL 的执行计划——type: ALL，rows: 2,300,000。全表扫描，偏移量 1,600,000，数据库把前两百万行都扫了一遍才丢掉，只为了取后面 20 条。

那次我花了一下午把深分页改成游标分页，响应时间从 8 秒降到 120ms。但真正让我后怕的不是慢，是另一个场景——

一、深分页不只是慢，还会把数据库拖死

我们的用户端有个"我的订单"列表，早期也是 LIMIT offset, size。有一天一个用户写了个脚本，疯狂翻页，每次把 pageNum 传得很大。offset 到了百万级，单条 SQL 执行 5 秒以上，连接池被占满，其他正常查询全排队。DBA 在监控里看到慢查询数飙升，直接把这条 SQL kill 了，但新的请求还在涌进来。

根因就是那行 LIMIT 1000000, 20——MySQL 要先找到第 1000000 行的位置，一行一行数过去，数完再取 20 条返回。offset 越大，数据库干的无效功越多。

解法：游标分页（Cursor-based Pagination）

放弃 offset，改用"上一页最后一条记录的 ID"作为游标：

-- ❌ 深分页：越翻越慢
SELECT * FROM orders WHERE user_id = ? 
ORDER BY created_at DESC 
LIMIT 100000, 20;

-- ✅ 游标分页：稳定快速
SELECT * FROM orders WHERE user_id = ? 
AND created_at < ? 
ORDER BY created_at DESC 
LIMIT 20;

前提是排序字段要有索引，且游标值来自上一页最后一条记录。这样数据库直接走索引定位，不用数前面那些行。响应时间跟翻第几页无关，永远是常数级。

代价是前端不能跳页（只能"上一页/下一页"），但移动端和大多数列表场景本来就用不到跳页。

二、COUNT(*) 的坑，我踩了两次

分页接口一般要返回 total（总条数），前端用来算总页数。我第一版直接 SELECT COUNT(*) FROM table WHERE ...，在 200 万数据上跑，花了 1.2 秒。

第一次踩坑后我加了索引，好了一点，但还是 300-400ms。后来发现，很多列表页根本不需要精确总数——"共约 10,000 条"就够了。改成 SQL_CALC_FOUND_ROWS 或者用估算值：

// 方案1：只 COUNT 少量数据，超过阈值就返回估算
Long total = mapper.countByCondition(query);
if (total > 10000) {
    total = estimateFromStatsTable(); // 从统计表取预计算值
}

// 方案2：不返回总数，只返回"是否有下一页"
boolean hasMore = result.size() > pageSize;

如果你一定要精确总数，至少加个缓存，5 分钟过期。用户翻页时不需要每次都重新 COUNT。

三、pageSize 不设上限，等于给黑客留了后门

早期我的分页接口 pageSize 直接信任前端传的值。有天安全扫描报了个问题：有人传 pageSize=10000，一次性拉走一万条数据。如果接口没做权限校验，这就是数据泄露。

后来我统一在分页参数校验里加了上限：

public Result list(QueryReq req) {
    int pageSize = req.getPageSize();
    if (pageSize <= 0 || pageSize > 100) {
        pageSize = 20; // 强制限制在 1-100
    }
    // ...
}

网关层也加了全局限制，超过 200 的直接拒绝。

四、多表 JOIN 的分页，是个大坑

有一段时间我们的商品搜索列表要关联 5 张表：商品表、库存表、价格表、标签表、评价表。分页 SQL 大概长这样：

SELECT g.*, i.stock, p.price, AVG(r.score) 
FROM goods g 
LEFT JOIN inventory i ON g.id = i.goods_id
LEFT JOIN price p ON g.id = p.goods_id
LEFT JOIN review r ON g.id = r.goods_id
WHERE g.status = 1
ORDER BY g.created_at DESC
LIMIT 10000, 20;

执行时间 12 秒。问题是 JOIN 之后数据膨胀，MySQL 要先算出完整的 JOIN 结果集，再排序，再跳过前面 10000 行。

解法：先在主表上分页，再查关联数据

-- 第一步：只查主表，快速定位 ID
SELECT id FROM goods 
WHERE status = 1 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;

-- 第二步：用 ID 批量查关联数据
SELECT * FROM inventory WHERE goods_id IN (...);
SELECT * FROM price WHERE goods_id IN (...);
SELECT * FROM review WHERE goods_id IN (...);

主表有索引，第一步走索引很快。拿到 20 个 ID 后，关联查询用 IN 批量查，每个子查询都能走索引。总耗时从 12 秒降到 200ms。

五、一个容易忽略的点：分页结果的一致性

用户在翻页时，如果数据在变化（新增、删除、状态变更），会出现：

• 漏数据：第 1 页查完，新数据插到前面，第 2 页就从原来的第 21 条开始，中间插的那条被跳过了
• 重复数据：第 1 页查完，某条数据被删了，后面的记录往前移，第 2 页第一条和第 1 页最后一条重复

这不是 Bug，是 OFFSET 分页的天然缺陷。游标分页能缓解这个问题，但也不是万能——如果游标字段本身变了（比如 updated_at 被更新），那条记录可能从当前页消失或跳到前面。

对一致性要求极高的场景（比如财务对账），我会用快照分页：查询时记录当前最大 ID 或时间戳，后续翻页都基于这个快照，不感知新数据。

六、一个心态的转变

以前我觉得分页是"最基础的 CRUD"，现在觉得是最容易出性能事故的隐形炸弹。它不显眼，不复杂，每个后端都写过，但几乎每个人都在深分页上栽过跟头。offset 越大越慢、COUNT 拖性能、JOIN 让问题翻倍、不设上限会被刷数据——每一个坑都藏在"能跑就行"的代码里。

如果你现在的项目里还有 LIMIT #{offset}, #{size} 这种写法，今晚就查一下：有没有人传过大的 pageNum？有没有 JOIN 多表的？total 的 COUNT 花了多久？不用一步到位全改游标分页，但至少给 offset 加个上限（比如最多 10000），超过就返回错误。每堵一个口子，你的数据库就少一分被拖垮的风险。毕竟，分页接口是访问量最大的接口之一，它出问题，整个系统都会抖。