PostgreSQL - limit offset SQL优化

标签：PostgreSQL 分页 SQL xx limit OFFSET LIMIT 万行

这个案例来自项目组最近一直在做性能优化的一个案列，我们项目每周都有通过Kibana (EFLK) 导出性能周报，最近一周出现一个分页查询的API出现了slow call (响应大于1秒)，我们对代码和SQL进行了review,Code部分这里省略掉，讲下SQL的部分，下面是SQL

select t.id, t.xx from xx_table t where xxx order by id limit 10000 offset 1000000;

OFFSET 和 LIMIT 有什么问题？

OFFSET 和 LIMIT 对于数据量少的项目来说是没有问题的。但是，当数据库里的数据量超过服务器内存能够存储的能力，并且需要对所有数据进行分页，问题就会出现。为了实现分页，每次收到分页请求时，数据库都需要进行低效的全表扫描。

什么是全表扫描？全表扫描 (又称顺序扫描) 就是在数据库中进行逐行扫描，顺序读取表中的每一行记录，然后检查各个列是否符合查询条件。这种扫描是已知最慢的，因为需要进行大量的磁盘 I/O，而且从磁盘到内存的传输开销也很大。

这意味着如果你有 5000万的数据，OFFSET 是100万，那么它需要获取所有这些记录 (包括那么多根本不需要的数据)，将它们放入内存，然后获取 LIMIT 指定的 1万 条结果。也就是说为了获取一页的数据：2000万行中的100万行到101万行。
需要先获取 100 万行。这么做是多么低效？数据越多，情况就越糟。

下面是guithub上别人对 10 万行数据进行的 PoC

https://github.com/IvoPereira/Efficient-Pagination-SQL-PoC?ref=hackernoon.com

现在你应该知道这背后都发生了什么：OFFSET 越高，查询时间就越长。

下面是优化后的SQL

select t.id, t.xx from xx_table t where xxx and id>= xx limit 10000;

这是一种基于指针的分页。你要在本地保存上一次接收到的主键 (通常是一个 ID) 和 LIMIT，而不是 OFFSET 和 LIMIT，那么每一次的查询可能都与此类似。因为通过显式告知数据库最新行，数据库就确切地知道从哪里开始搜索（基于有效的索引），而不需要考虑目标范围之外的记录。如果我们的表没有主键，比如是具有多对多关系的表，那么就使用传统的 OFFSET/LIMIT 方式，只是这样做存在潜在的慢查询问题。所以在需要分页的表中使用自动递增的主键，即使只是为了分页。

 

在线SQL测试：https://www.db-fiddle.com

标签：PostgreSQL,分页,SQL,xx,limit,OFFSET,LIMIT,万行
From： https://www.cnblogs.com/hlkawa/p/17624297.html