SQL分页查询有哪些优化策略与实践方法

在业务系统中，分页查询是获取列表数据的常用方式，但当表数据量达到百万甚至千万级别时，传统的分页写法会导致查询耗时急剧增加，影响系统响应速度。分页查询的性能问题主要出现在大偏移量的场景下，偏移量越大，数据库需要扫描的无效数据越多，查询效率越低。

传统分页查询的问题

大多数开发者最初接触的分页查询写法是使用LIMIT offset, size的语法，比如要获取第1001到1010条数据，会写成如下语句：

SELECT id, name, age FROM user ORDER BY id LIMIT 1000, 10;

这条语句的逻辑是数据库先扫描前1010条数据，然后丢弃前1000条，返回剩下的10条。当offset值很大时，比如offset为100000，数据库需要扫描100010条数据，大量无效扫描会消耗大量IO和CPU资源，导致查询性能下降。

基于索引的优化策略

利用覆盖索引减少回表

如果分页查询的字段都包含在索引中，数据库可以直接从索引中获取数据，不需要回表查询聚簇索引，能显著提升查询速度。比如user表的id是主键，查询id和name字段时，可以给name字段建立索引，或者直接使用主键索引：

-- 利用主键索引覆盖查询，避免回表
SELECT id FROM user ORDER BY id LIMIT 100000, 10;

基于游标的分页方式

避免使用大偏移量的LIMIT语法，改用基于上一页最后一条记录的ID进行查询，这种方式只需要扫描少量数据。比如上一页最后一条数据的id是100000，那么下一页的查询可以写成：

SELECT id, name, age FROM user WHERE id > 100000 ORDER BY id LIMIT 10;

这种方式的前提是排序字段是唯一的，比如主键id，并且排序方向固定。如果排序字段有重复值，需要结合其他唯一字段来保证分页的准确性。

不同数据库的分页语法优化

MySQL的分页优化

MySQL除了使用上述的游标分页方式，还可以利用子查询先获取主键id，再关联查询完整数据，减少回表次数：

SELECT u.id, u.name, u.age 
FROM user u 
INNER JOIN (
    SELECT id FROM user ORDER BY id LIMIT 100000, 10
) tmp ON u.id = tmp.id;

PostgreSQL的分页优化

PostgreSQL支持FETCH FIRST N ROWS ONLY的语法，结合OFFSET使用时，同样可以用游标方式优化：

-- 传统写法
SELECT id, name, age FROM user ORDER BY id OFFSET 100000 LIMIT 10;
-- 优化写法
SELECT id, name, age FROM user WHERE id > 100000 ORDER BY id LIMIT 10;

Oracle的分页优化

Oracle常用ROWNUM实现分页，大偏移量时可以结合子查询和索引优化：

-- 传统写法
SELECT * FROM (
    SELECT t.*, ROWNUM rn FROM (
        SELECT id, name, age FROM user ORDER BY id
    ) t WHERE ROWNUM <= 100010
) WHERE rn > 100000;
-- 优化写法，基于id游标
SELECT id, name, age FROM user WHERE id > 100000 ORDER BY id FETCH FIRST 10 ROWS ONLY;

分页查询的额外优化建议

• 尽量保证分页查询的排序字段有索引，避免全表排序。如果排序字段是多个，需要建立联合索引，索引的顺序要和ORDER BY的顺序一致。
• 避免SELECT * 的写法，只查询需要的字段，减少数据传输和数据库扫描的开销。
• 如果业务允许，可以限制最大分页深度，比如只允许查询前100页，避免用户查询过大的偏移量数据。
• 对于实时性要求不高的分页场景，可以考虑使用缓存存储前几页的数据，减少数据库的查询压力。

实践案例对比

假设user表有1000万条数据，id是主键自增，测试两种分页方式的耗时：

从测试结果可以看出，游标分页的方式性能提升非常明显，适合大数量下的分页场景。

SQL分页查询查询优化索引优化修改时间：2026-06-22 23:36:35