使用主键或索引字段进行范围查询可避免深度分页性能问题，如将LIMIT 10000,20改为WHERE id > 10000并利用索引快速定位；结合覆盖索引减少回表操作，创建包含排序和查询字段的联合索引提升效率；采用延迟关联先通过索引获取主键再关联原表，降低扫描开销；书签法基于上一页末尾记录的排序值实现高效翻页，适用于滚动加载场景；对高频访问页面可预加载至缓存减轻数据库压力。实际应用中应根据业务需求选择合适方案——需跳页时用延迟关联，持续加载推荐书签法配合索引优化。
在MySQL中，随着数据量增大，使用LIMIT进行分页查询的性能会显著下降，尤其是深度分页（如LIMIT 10000, 20）时。这是因为MySQL需要扫描并跳过前10000条记录，即使这些数据不会被返回。以下是几种有效的优化方法来提升分页查询效率。
1. 使用主键或索引字段进行范围查询
避免使用OFFSET跳过大量数据，改用上一页最后一条记录的主键值作为起点继续查询。这种方法适用于有序数据分页。
示例：
- 原始写法：SELECT * FROM users ORDER BY id LIMIT 10000, 20;
- 优化写法：SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 20;
前提是id是有序主键，且你知道上一页的最大id值。这样可以利用索引快速定位，避免全表扫描。
2. 覆盖索引减少回表操作
如果查询字段都能被索引覆盖，MySQL无需回表查询主表数据，能大幅提升性能。
建议：
- 创建包含排序字段和查询字段的联合索引。
- 例如：CREATE INDEX idx_created_id ON users(create_time, id);
- 然后查询：SELECT id, name, create_time FROM users WHERE create_time > '2023-01-01' AND id > 1000 ORDER BY create_time, id LIMIT 20;
确保WHERE和ORDER BY中的字段都在索引中，避免额外的IO开销。
3. 延迟关联（Deferred Join）
先通过索引获取主键，再与原表关联获取完整数据，减少大表扫描。
示例：
SELECT u.* FROM users u INNER JOIN (SELECT id FROM users ORDER BY create_time LIMIT 10000, 20) AS tmp ON u.id = tmp.id;子查询只扫描索引获取id，外层再根据id查具体数据，效率更高。
4. 使用书签法（Keyset Pagination）
适合实时性要求高的场景，比如新闻流、日志列表。它依赖上一页最后一个记录的排序值作为“书签”继续下一页。
- 优点：不依赖OFFSET，性能稳定。
- 缺点：不能直接跳转到任意页码。
典型用法：SELECT * FROM users WHERE (create_time, id) > ('2023-05-01 10:00:00', 5000) ORDER BY create_time, id LIMIT 20;
5. 预加载或缓存热门页
对于访问频率高的页面（如前几页），可将结果缓存到Redis等内存数据库中，减少数据库压力。
- 设置合理的缓存过期策略。
- 结合应用层做分页状态管理。
基本上就这些常用手段。关键是要根据业务场景选择合适的方法——如果是后台系统需要跳页，可用延迟关联；如果是前端滚动加载，推荐书签法配合索引优化。
