MySQL单表过亿后的分页优化：延迟关联与覆盖索引

发布时间：2026-09-23 04:31 更新时间：2026-09-23 04:31 阅读量：0

表数据涨到几千万甚至过亿之后，很多站长会发现一个现象：后台列表页第一页秒开，越往后翻越慢，翻到几十万页时接口直接超时。这类问题通常不是数据库整体性能下降，而是深分页写法本身的问题。本文以「MySQL 单表数据量过亿后的分页查询优化」为线索，讲清 limit 偏移量大时慢在哪里，并给出延迟关联、覆盖索引两种可落地的改写方式，最后用 EXPLAIN 验证是否生效。

深分页为什么越翻越慢

先看最常见的一种写法，假设订单表 orders 有主键 id，业务按创建时间倒序翻页：

SELECT id, user_id, amount, created_at
FROM orders
ORDER BY id DESC
LIMIT 9000000, 20;

这条语句的语义是「扫描并丢弃前 900 万行，再返回接下来 20 行」。MySQL 在 InnoDB 上通常会走主键索引反向扫描，把符合条件的前 900 万零 20 条记录逐行读出来，前 900 万条读完后被丢弃。也就是说，偏移量越大，被白白读取和丢弃的行就越多，耗时基本随 offset 线性增长。如果 SELECT 的字段较多、包含大字段，回表次数同样会放大开销。

很多人第一反应是「加个索引就好了」，但普通二级索引对 LIMIT offset 这种「先取后丢」的行为帮助有限，真正要解决的是减少被丢弃行的读取成本。下面两种改写针对的正是这一点。

延迟关联：先拿主键再回表

延迟关联（deferred join）的思路是：先用覆盖索引只查主键，把 offset 对应的那批主键定位出来，再用主键去关联原表取完整字段。因为第一步只涉及索引列，不需要回表，代价比逐行读整行小得多。以按 id 倒序分页为例，可以改写成：

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

内层子查询只在主键索引上扫描并丢弃偏移行，返回 20 个 id；外层再用这 20 个 id 做等值回表。相比原写法，被丢弃阶段的读取对象从「整行」变成了「索引条目」，IO 和内存压力都会明显下降。需要注意的是，如果排序字段不是主键，而是 created_at 这类时间列，子查询里就要按该列排序，并且要有一个以该列开头的联合索引，否则内层依然会走文件排序。

另一个常见变体是「记住上一页最后一条记录」，用 WHERE 条件替代大 offset，也就是常说的游标分页。它不依赖 offset，翻页速度恒定，但只适合「上一页/下一页」式导航，无法直接跳到第 N 页。对后台需要跳页的场景，延迟关联更通用。

覆盖索引：让排序与过滤都走索引

覆盖索引指的是查询需要的列全部包含在索引里，MySQL 不必回表。对分页优化来说，它的价值有两点：一是让排序在索引上有序完成，避免 Using filesort；二是配合延迟关联时，内层子查询只扫描索引即可。假设业务实际按用户维度筛选再翻页：

SELECT id, user_id, amount, created_at
FROM orders
WHERE user_id = 12345
ORDER BY id DESC
LIMIT 50000, 20;

可以建立这样的联合索引，让过滤和排序同时命中：

ALTER TABLE orders ADD INDEX idx_user_id_id (user_id, id);

索引列顺序有讲究：等值过滤列 user_id 放前面，排序列 id 放后面，这样 InnoDB 在 user_id 相同的区间内天然按 id 有序，排序不额外花钱。如果把两个列的顺序写反，过滤效率可能下降。建索引前建议先用 EXPLAIN 看当前执行计划，确认瓶颈到底在排序还是回表，再决定索引怎么加，避免盲目堆索引拖慢写入。

需要提醒的是，联合索引的列数、字段类型要和查询条件完全一致，隐式类型转换会导致索引失效，这一点可以配合慢查询日志一起观察。字段类型和字符集以实际建表语句为准。

用 EXPLAIN 验证改写是否生效

改完之后不能只看「感觉快了」，要用 EXPLAIN 或 EXPLAIN FORMAT=JSON 核对执行计划。重点看几个字段：type 是否从 ALL 变为 range/ref，key 是否命中预期索引，Extra 里有没有 Using filesort、Using temporary。下面这条命令用来对比改写前后的计划：

EXPLAIN SELECT o.id, o.user_id, o.amount, o.created_at
FROM orders o
INNER JOIN (
  SELECT id FROM orders ORDER BY id DESC LIMIT 9000000, 20
) AS t ON o.id = t.id;

如果内层子查询出现 Using index 且没有 filesort，说明覆盖索引生效；外层出现 ref 或 eq_ref 说明回表走了主键等值查找。反之，若仍出现 Using filesort，优先检查排序字段是否在索引前缀里。执行计划的具体输出随 MySQL 版本和统计信息变化，建议以实际库上的结果为准，必要时用 ANALYZE TABLE 更新统计信息后再看。

还有一个容易被忽略的点：数据量过亿后，统计信息的准确度会影响优化器选路。可以定期关注 innodb_stats_persistent 相关设置与表统计信息更新时间，避免优化器误判。是否调整这些参数，以官方文档和实际压测结果为准。

小结与下一步

深分页慢的根因是「扫描并丢弃大量行」，延迟关联把丢弃阶段的开销从整行降到索引条目，覆盖索引让排序和过滤都留在索引层，两者配合通常能显著改善大表翻页体验。落地时建议按这个顺序推进：先用慢查询日志定位具体语句，再 EXPLAIN 确认瓶颈，然后改写 SQL 并补上匹配的联合索引，最后在预发或低峰期做一次压测对比。

如果业务允许，把「跳页」改成游标式翻页是收益更稳定的方案；如果后台必须支持随机跳页，可以给列表页设置一个合理的最大翻页深度，超出后引导用户用筛选条件缩小范围，而不是硬翻到百万页。索引不是越多越好，每次新增都要评估写入放大与磁盘占用，具体以实际业务读写比例为参考。

继续阅读