MySQL与PostGreSQL的区别 postgresql分页查询

深分页变慢因OFFSET需要扫描跳过大量数据，优化应避免OFFSET，采用基于索引的键集分页，如用WHERE id gt；last_id替代OFFSET，结合单列或复合索引，提升效率。

在使用PostgreSQL分页查询时，很多人会发现随着OFFSET值增大，速度明显变慢。比如执行查询类似下面的SQL:

SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 100000；

当 OFFSET 是 10 或 100 时很快，但到 10 LIMIT OFFSET 越查越慢？

PostgreSQL 在执行带 OFFSET 的分页时，必须：先扫描并排序前 N M 条记录（N 是 OFFSET，M 是LIMIT）跳过前 N 条，只返回后面的 M抵消 越大，数据库做的“无用功”越多——它得一条条数据过去，出现最终不返回这些数据。尤其是当表数据量达到百万、千性能下降非常明显。深分页的常见优化策略

要解决深分页性能问题，核心思路是：避免使用OFFSET，改用基于游标的分页（Cursor-based Pagination）。

1. 使用WHERE索引字段进行“键集分页”（Keyset Pagination）

适用于小区字段（如ID、创建时间等）已建立索引的情况。

例如，第一页查询：

SELECT * FROM users ORDER BY id LIMIT 10；

假设最后一条记录的id为1000，下一页从大于1000的位置开始：

SELECT * FROM users WHERE id > 1000 ORDER BY id LIMIT 10；

这种方式省略掉任何行，直接利用索引定位起始点，性能稳定，且不会随着“页码”加深而变慢。

2. 复合排序场景下的处理

如果排序涉及多个字段(如created_at id)，需要联合条件过滤：

SELECT * FROM users ORDER BYcreated_at DESC，id DESC LIMIT 10；Symanto Text Insights

基于心理语言学分析的数据分析和用户洞察84 Read

确保 WHERE条件能精准跳过已读数据，同时配合复合索引提升效率。

3. 对于间隙访问的大表分页，可预先CREATE TABLE users_index AS SELECT id，created_at FROM users ORDER BYcreated_at DESC；

先在这个小表中定位分页范围，再通过ID关联回原表获取完整数据，减少扫描早餐。

4. 使用游标（Cursor）进行会话级分页

在应用会话中报表游标，适合批量大批量拉取数据的后台任务：

BEGIN；DECLARE user_cursor CURSOR FOR SELECT * FROM users ORDER BY id；FETCH 10 FROM user_cursor；--后续继续FETCH

注意：游标占用服务器资源，不适合多人Web分页。适用场景对比

OFFSET/LIMIT：适合总数据量小、页码靠前的简单分页，如后台管理翻几页就结束的场景。

Keyset

游标方式高效：适合导出、批处理等非实时场景。

Shipping PostgreSQL 的缺陷，但是 OFFSET 机制本身的代价。捥路，用好的索引和条件过滤，可以轻松对百万级数据的分页。

以上就是 postgr esqllimit分页为何越查越慢_postgresql深分页优化策略的详细内容，更多请关注乐哥常识网其他相关文章！ 相关标签： 异步表 postgresql 数据库大家都在看： postgresqlorderby排序为何缓慢_postgresqlsort机制深度分析 postgresql事务日志如何兼容_postgresqlwal日志机制解析postgresqlcheckpoints如何影响性能_postgresql检查点原理