使用主键或索引字段进行范围查询可显著提升mysql大数据量分页性能，避免大偏移LIMIT导致的全表扫描；通过覆盖索引+延迟关联减少回表次数，结合复合索引优化排序效率；书签法利用上一页末尾记录值作为下一页起点，实现高效滚动分页。

MySQL在进行大数据量分页时，LIMIT m, n 随着偏移量m增大，查询速度会明显变慢。这是因为MySQL需要扫描前m+n行，再舍弃前m行，只返回n条结果。当m非常大时，比如 LIMIT 100000, 20，效率急剧下降。以下是几种有效的优化方案。

1. 使用主键或索引字段进行范围查询替代LIMIT偏移

避免使用大偏移的LIMIT，改用WHERE条件基于主键或有索引的排序字段进行“滚动分页”或“游标分页”。

例如，原SQL：

select * FROM user ORDER BY id LIMIT 100000, 20;

优化为：

SELECT * FROM user WHERE id > 100000 ORDER BY id LIMIT 20;

前提是id是有序主键。每次查询记录最后一条的id，作为下一页的起点。这种方式避免了全表扫描前10万条数据。

2. 覆盖索引 + 延迟关联（Deferred Join）

如果查询字段较多，但排序字段有索引，可以先通过索引获取主键，再关联原表获取完整数据。

示例：

SELECT u.* FROM user u INNER JOIN ( SELECT id FROM user ORDER BY create_time LIMIT 100000, 20 ) AS tmp ON u.id = tmp.id;

这样子查询只走索引（create_time + id），速度快，外层再回表取数据，大幅减少排序和扫描成本。

3. 建立合适的复合索引

确保ORDER BY字段上有索引，尤其是多字段排序时，建立复合索引。

SpeakingPass-打造你的专属雅思口语语料

使用chatGPT帮你快速备考雅思口语，提升分数

25

查看详情

比如按时间排序分页：

CREATE INDEX idx_create_time ON user(create_time DESC, id);

复合索引包含排序字段和主键，可支持覆盖索引优化，提升子查询性能。

4. 使用书签法（Bookmarking）实现高效翻页

适用于不允许跳页的场景，如“下一页”按钮。保存上一页最后一个记录的排序值（如时间、ID），作为下一页的查询起点。

第一次查询：

SELECT * FROM user ORDER BY create_time DESC, id DESC LIMIT 20;

拿到最后一条记录的create_time和id，假设是 ‘2023-01-01 10:00:00’ 和 50000。

下一页查询：

SELECT * FROM user WHERE (create_time