SQL分页查询是几乎所有业务系统都会用到的功能，无论是列表展示还是数据导出，都需要对查询结果进行分页处理。随着业务数据量从几万增长到几百万甚至上千万，原本运行正常的分页查询会逐渐出现响应延迟，严重时还会导致数据库连接池占满，影响整个系统的稳定性。
传统分页查询的性能问题
很多开发者习惯使用LIMIT offset, size的方式实现分页，这种方式在数据量小的时候没有明显问题，但当offset值很大时，性能会急剧下降。因为数据库需要先扫描到offset指定的位置，再取后面的size条数据，offset越大，扫描的无用数据越多。
比如下面这个常见的分页查询语句，查询用户表的第10001到10020条数据：
-- 传统分页查询，offset为10000时性能较差 SELECT id, username, email, create_time FROM user ORDER BY id ASC LIMIT 10000, 20;
当user表有1000万条数据时，这条语句需要先扫描前10000条记录，再返回后面的20条，大量IO资源被浪费在无用的数据扫描上。
分页查询优化方案
1. 基于索引覆盖的游标分页
如果分页查询的排序字段是唯一索引，可以使用游标分页的方式，避免大offset扫描。核心思路是记录上一页最后一条数据的排序字段值，下次查询直接从该值之后开始取数据。
以上面的用户表查询为例，优化后的语句如下：
-- 游标分页优化，假设上一页最后一条数据的id是10000 SELECT id, username, email, create_time FROM user WHERE id > 10000 ORDER BY id ASC LIMIT 20;
这种方式利用了id索引的有序性，直接定位到id大于10000的位置，不需要扫描前面的10000条数据，查询效率基本不受数据量增长的影响。
2. 延迟关联优化
如果排序字段不是唯一索引，或者查询需要关联多张表，游标分页可能不适用，这时候可以使用延迟关联的方式优化。先通过索引查询出需要的主键id，再关联原表获取其他字段，减少回表次数。
比如需要按用户创建时间分页查询，且创建时间不是唯一索引：
-- 延迟关联优化分页查询
SELECT u.id, u.username, u.email, u.create_time
FROM user u
INNER JOIN (
-- 子查询先通过索引获取分页对应的主键id
SELECT id
FROM user
ORDER BY create_time ASC
LIMIT 10000, 20
) tmp ON u.id = tmp.id
ORDER BY u.create_time ASC;
子查询中只查询id字段，利用create_time的索引完成排序和分页，减少不必要的数据读取，再关联原表获取完整数据，性能会比直接查询提升很多。
3. 业务层限制最大分页深度
除了SQL层面的优化，还可以在业务层做限制，比如不允许用户查询超过1000页的数据，或者提供搜索功能替代深度分页。因为绝大多数用户只会查看前几页的数据，深度分页的需求本身就不合理。
真实案例解析
某电商系统的订单列表分页功能，原本使用LIMIT offset, 20的方式实现，订单表数据量达到500万条后，查询第500页以后的数据响应时间超过3秒，用户反馈强烈。
优化前查询语句：
-- 优化前的订单分页查询 SELECT order_id, user_id, order_amount, order_status, create_time FROM order_info WHERE user_id = 123 ORDER BY create_time DESC LIMIT 10000, 20;
分析发现order_info表的user_id和create_time有联合索引idx_user_create(user_id, create_time)，于是采用游标分页优化，前端每次请求携带上一页最后一条订单的create_time和order_id，后端查询时直接基于这两个值过滤：
-- 优化后的订单分页查询，假设上一页最后一条数据create_time是2024-05-01 12:00:00，order_id是100000 SELECT order_id, user_id, order_amount, order_status, create_time FROM order_info WHERE user_id = 123 AND (create_time < '2024-05-01 12:00:00' OR (create_time = '2024-05-01 12:00:00' AND order_id < 100000)) ORDER BY create_time DESC, order_id DESC LIMIT 20;
优化后，即使查询第1000页的数据，响应时间也稳定在100毫秒以内，性能提升了30倍以上。同时业务层限制了最大只能查询前200页的数据，进一步避免了极端深度分页的场景。
不同数据库的分页优化差异
不同数据库的分页语法有差异，优化方式也需要适配对应的数据库特性：
| 数据库类型 | 原生分页语法 | 优化建议 |
|---|---|---|
| MySQL | LIMIT offset, size | 优先使用游标分页，其次延迟关联，避免大offset |
| PostgreSQL | LIMIT size OFFSET offset | 可以使用WHERE条件基于索引字段过滤替代offset，或者利用游标 |
| Oracle | ROWNUM 或者 FETCH FIRST size ROWS ONLY OFFSET offset ROWS | 使用ROWUM分页时结合索引过滤，避免大offset |
分页优化总结
SQL分页查询优化的核心思路是减少不必要的数据扫描，优先利用索引的有序性定位数据，避免大offset带来的性能损耗。实际优化时需要结合业务场景、数据量、索引情况选择合适的方案，同时配合业务层的限制，才能从根本上解决分页查询的性能问题。在复杂查询场景中，多分析执行计划，找到性能瓶颈点，才能写出高效的SQL语句。