MySQL深分页优化最佳实践：延迟关联、keyset与产品层改造指南

大家好，我是数据库小学妹👋 我踩过的坑，你别再踩。

上个月运营同学给我发了张截图。她想找最早的一批订单，在订单列表上点了「末页」，页面转了二十多秒才出来。她问我是不是数据库坏了。我第一反应是索引没建对。打开表结构一看，created_at 上有索引，该有的都有。

我把那条 SQL 单独拎出来跑了一遍。确实慢。但慢的地方不在索引。

先把这条 SQL 摆出来

-- 订单列表最后一页，每页 10 条
SELECT id, order_no, user_id, amount, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 9999990, 10;

表里 1000 万行，一共 100 万页。这个查询要跑二十多秒。翻到第一页的时候，结构一模一样的 SQL，是毫秒级的。

我以前一直以为，写了 LIMIT 数据库就不会多干活。这个理解只对了一半。LIMIT 能让它提前结束，但没法让它跳过去。MySQL 没有直接跳到第 999 万行的能力。它得从索引第一行开始，一行行往下数。数到第 999 万零一行，才开始取你要的那 10 条。

所以真正被读的行数是 999 万零 10。前面那 999 万行读完就被丢了，纯粹白读。

还有个更隐蔽的坑。你去翻 EXPLAIN，rows 那一列未必能反映真实扫描量。LIMIT 会影响优化器的估算，它觉得反正只要 10 行，代价不高。我那时候就是被这一列骗了，以为计划没问题。想看清真实读取量，得看 Handler 变量。

-- 执行前后各看一次，差值才是真实读取量
SHOW SESSION STATUS LIKE 'Handler_read_next';

解法一：覆盖索引加延迟关联

深分页最贵的地方，其实不是数那 999 万行，是数的时候还要回表。索引上只有 created_at 和主键 id，你要的 order_no、amount 都不在里面。每数一行，就得拿着 id 回主键索引捞一次完整数据。999 万次随机读，慢就慢在这儿。

SELECT o.id, o.order_no, o.user_id, o.amount, o.created_at
FROM orders o
INNER JOIN (
    SELECT id FROM orders
    ORDER BY created_at DESC
    LIMIT 9999990, 10
) AS t ON o.id = t.id
ORDER BY o.created_at DESC;

子查询里只要 id，而 InnoDB 的二级索引叶子节点上本来就存着主键。所以这 999 万行的扫描全程都在索引里完成，一次回表都不用。等到最后只剩 10 条了，才回表取完整字段。这条改完，二十多秒降到七八秒。

这招省掉的是回表，不是扫描。999 万条索引项该读还是得读。所以它能把"没法用"救成"能用"，但它救不成"快"。这个区别很重要，很多文章不说清楚，照着抄完发现还是慢，就以为是自己的问题。

解法二：keyset，直接换掉分页方式

-- 上一页最后一条的 created_at 和 id，由前端传回来
SELECT id, order_no, user_id, amount, created_at
FROM orders
WHERE (created_at, id) < ('2026-08-01 10:00:00', 9999990)
ORDER BY created_at DESC, id DESC
LIMIT 10;

这招是真的快，因为它不做扫描。B+树本身是有序的，给它一个位置，它能二分定位下去，然后顺着链表读 10 行就走。读取量从 999 万变成了 10，耗时也回到了毫秒级。

代价是它只能一页一页地翻。用户想直接跳到第 500 页，做不到。这是它唯一的限制，也是我最后选它的原因，后面说。

要让它生效，索引得建对。(created_at, id) 这样的复合索引，顺序要跟排序方向对得上。

解法三到五：还有三条路

第三条路是把总数和翻页解耦。列表顶上那句"共 XX 条"最坑。COUNT(*) 在 1000 万行的表上本身就是个大开销。深分页的时候，你也根本用不上精确总数。再加上"最多翻 100 页"的限制，很多问题直接消失。

第四条路是产品层的筛选。给列表加上时间范围、订单状态这些筛选项。用户就从"翻到末页"变成了"筛出 8 月 1 日之后的订单"。翻页深度直接掉到个位数。这条改动不写一行 SQL，效果却最好。

第五条路是把列表要展示的字段直接做进索引。这样查询全程在索引里完成，一次回表都没有。

-- 宽索引：列表要的字段全放进去
ALTER TABLE orders ADD INDEX idx_list
(created_at, id, order_no, user_id, amount);

这招很暴力，代价也实在。索引宽度翻了十几倍，占空间，写入还得同步维护它。只有读远多于写的列表才划得来。我们后来没在订单表上用，挪到了另一张配置表上。

避坑清单

ORDER BY 的字段上没有索引，前面这些招式全都白搭。子查询本身就要排序，那就得走 filesort，临时文件一落盘，延迟关联也救不了。动手前先用 EXPLAIN 确认排序字段走的是索引。

用时间做游标，必须带上一个唯一列当第二排序键。我踩过这个坑。一开始只用 created_at 做游标，同一秒里进来两条订单，翻页的时候少了一条，怎么查都查不出来。后来把 id 加进去才对齐。这个坑很隐蔽，因为它在数据少的时候根本不出现。

别让前端还能一键翻到末页。这既是性能问题，也是在纵容一个没人用的功能。我们后来加了页数上限，还顺手把返回的精确总数改成了"约 XX 条"，省掉一次全表计数。

写在最后

这五条路试完，我最大的收获不是学会了哪一招，是搞清楚了一个分水岭：用户到底需不需要跳页。

需要跳页，就在延迟关联外面加一刀，把最大页数限制住。不需要跳页，就直接上 keyset，把读取量从千万级压到个位数。而绝大多数场景里，能一键跳到末页是产品设计出来的，不是用户真的想看。真去翻日志，你会发现点过末页的只有那一次，而且是为了给我截图。运营真正想要的是"最早的订单"，这件事本来该有一个时间筛选器。

所以顺序上，我会先问产品一句，这个列表用户真的会翻到底吗。问了以后再决定是改 SQL 还是改产品。先动 SQL，常常是把力气花在了错的地方。

你们系统的列表分页，现在能翻到多深？评论区聊聊。

我是数据库小学妹，帮你少走弯路少踩坑，咱们下篇见👋