在SQL的分页查询场景中，LIMIT和OFFSET是最常用的语法组合，很多开发者默认认为只要加上这两个关键字就能高效获取分页数据，却忽略了它们在不同使用场景下的性能差异。尤其是当OFFSET的数值不断增大时，查询的实际成本会远超预期，甚至成为数据库的性能瓶颈。
LIMIT和OFFSET的基本工作原理
LIMIT用于限制查询结果返回的最大行数，OFFSET用于跳过结果集中指定数量的行。数据库执行包含这两个关键字的查询时，会先按照查询条件筛选所有符合要求的行，然后跳过OFFSET指定的行数，最后返回接下来的LIMIT行数据。
我们可以通过一个简单的查询示例来理解基本用法，假设有一张用户表user，需要查询第3页的数据，每页显示10条：
-- 每页10条，查询第3页，跳过前20条，返回接下来10条 SELECT id, username, create_time FROM user ORDER BY create_time DESC LIMIT 10 OFFSET 20;
大偏移量下的性能成本分析
当OFFSET的数值较小时，比如查询前几页的数据，LIMIT和OFFSET的组合性能表现尚可，因为需要跳过的行数很少。但当OFFSET数值增大到几千、几万甚至更多时，性能问题就会凸显，核心原因有两个：
- 数据库需要先扫描并排序所有符合查询条件的行，即使最终只需要返回很少的一部分，跳过的行依然会被处理，这部分扫描和排序的成本会随着OFFSET增大线性增长。
- 如果查询没有合适的索引支撑排序字段，数据库可能需要进行全表扫描和临时排序，大偏移量下的额外开销会进一步放大。
我们可以通过执行计划来直观看到不同OFFSET下的成本差异，以下是两个查询的执行计划对比：
| OFFSET值 | 扫描行数 | 执行时间（毫秒） |
|---|---|---|
| 10 | 20 | 2 |
| 10000 | 10010 | 120 |
| 100000 | 100010 | 1500 |
对比其他分页方案的成本
基于游标的分页方案
基于游标的分页不使用OFFSET，而是通过上一页最后一条记录的唯一标识来定位下一页的起始位置，比如使用自增ID或者带索引的时间字段：
-- 假设上一页最后一条记录的id是100，查询下一页10条数据 SELECT id, username, create_time FROM user WHERE id > 100 ORDER BY id ASC LIMIT 10;
这种方案的成本不会随着页数的增加而上升，因为数据库可以直接通过索引定位到起始位置，不需要扫描和跳过前面的所有行，性能远优于大偏移量的LIMIT+OFFSET方案。
延迟关联优化方案
如果必须使用OFFSET分页，可以通过延迟关联的方式减少扫描的行数，先通过索引获取需要的主键ID，再关联原表获取完整数据：
-- 先查主键，再关联原表，减少回表开销
SELECT u.id, u.username, u.create_time
FROM user u
INNER JOIN (
SELECT id
FROM user
ORDER BY create_time DESC
LIMIT 10 OFFSET 10000
) tmp ON u.id = tmp.id;
这种方式虽然还是需要处理OFFSET跳过的行，但因为子查询只扫描索引上的主键，不需要回表取所有字段，能在一定程度上降低大偏移量下的性能开销。
使用建议
在实际开发中，我们可以根据场景选择合适的方案：
- 如果只需要查询前几页的数据，且数据量不大，使用LIMIT+OFFSET是简单可行的方案。
- 如果需要查询很深的页数，优先选择基于游标的分页方案，避免大偏移量带来的性能问题。
- 如果必须使用OFFSET，确保排序字段有合适的索引，尽量使用延迟关联的方式减少扫描开销。
- 避免让用户输入过大的OFFSET值，比如限制最大查询页数，防止恶意请求导致数据库压力过大。
需要注意的是，不同的数据库对LIMIT和OFFSET的实现细节可能有差异，比如MySQL、PostgreSQL、SQL Server的语法和支持特性不同，实际使用时需要结合对应数据库的文档优化。
理解LIMIT和OFFSET的真实成本，能帮助我们在开发分页功能时做出更合理的选择，避免因为不当使用导致的性能问题，保障数据库的稳定运行。