PostgreSQL 分页, offset, 返回顺序, 扫描方法原理（seqscan, index scan, index only scan, bitmap scan, parallel xx scan），游标

• 背景
• 扫描方法
• • 1、全表扫描, seqscan
  • 2、并发全表扫描, concurrently seqscan
  • 3、索引扫描, index scan
  • 4、索引ONLY扫描, index only scan
  • 5、位图扫描, bitmap scan
  • 6、并行扫描, parallel xx scan
  • 7、hash join
  • 8、nest loop join
  • 9、merge join
  • 扫描方法决定了数据返回顺序
  • 1、如果是全表扫描，
  • 2、如果是索引扫描，
• 保证绝对的连续
• • 1、使用rr隔离级别（repeatable read），并且按PK（唯一值字段、字段组合）排序，OFFSET
  • 2、使用游标
• 参考

简介： PostgreSQL 分页, offset, 返回顺序, 扫描方法原理(seqscan, index scan, index only scan, bitmap scan, parallel xx scan)，游标

背景

一个这样的问题：

为什么 select x from tbl offset x limit x;

两次查询连续的OFFSET，会有重复数据呢？

select ctid,* from tbl where ... offset 0 limit 10;  
select ctid,* from tbl where ... offset 10 limit 10;  

为什么多数时候offset会推荐用order by？

不使用ORDER BY的话，返回顺序到底和什么有关？

答案是：

数据库的扫描方法。

数据库扫描方法，具体的原理可以到如下文档中找到PDF，PDF内有详细的扫描方法图文介绍。

《阿里云 PostgreSQL 产品生态；案例、开发管理实践、原理、学习资料、视频；PG天天象上沙龙记录 - 珍藏级》

扫描方法

1、全表扫描, seqscan

从第一个数据块开始扫描，返回复合条件的记录。

2、并发全表扫描, concurrently seqscan

如果有多个会话，对同一张表进行全表扫描时，后发起的会话会与前面正在扫描的会话进行BLOCK对齐步调，也就是说，后面发起的会话，可能是从表的中间开始扫的，扫描到末尾再转回去，避免多会话同时对一个表全表扫描时的IO浪费。

例如会话1已经扫到了第99个数据块，会话2刚发起这个表的全表扫描，则会从第99个数据块开始扫描，扫完在到第一个数据块扫，一直扫到第98个数据块。

3、索引扫描, index scan

按索引顺序扫描，并回表。

4、索引ONLY扫描, index only scan

按索引顺序扫描，根据VM文件的BIT位判断是否需要回表扫描。

5、位图扫描, bitmap scan

按索引取得的BLOCKID排序，然后根据BLOCKID顺序回表扫描，然后再根据条件过滤掉不符合条件的记录。

这种扫描方法，主要解决了离散数据(索引字段的逻辑顺序与记录的实际存储顺序非常离散的情况)，需要大量离散回表扫描的情况。

6、并行扫描, parallel xx scan

并行的全表、索引、索引ONLY、位图扫。首先会FORK出若干个WORKER，每个WORKER负责一部分数据块，一起扫描，WORKER的结果（FILTER后的）发给下一个GATER WORKER节点。

7、hash join

哈希JOIN，

8、nest loop join

嵌套循环

9、merge join

合并JOIN（排序JOIN）。

更多扫描方法，请参考PG代码。

扫描方法决定了数据返回顺序

根据上面的这些扫描方法，我们可以知道一条QUERY下去，数据的返回顺序是怎么样的。

select * from tbl where xxx offset 10 limit 100;  

1、如果是全表扫描，

那么返回顺序就是数据的物理存放顺序，然后偏移10条有效记录，取下100条有效记录。

2、如果是索引扫描，

则是依据索引的顺序进行扫描，然后偏移10条有效记录，取下100条有效记录。

不再赘述。

保证绝对的连续

如何保证第一次请求，第二次请求，第三次请求，。。。每一次偏移（offset）固定值，返回的结果是完全有序，无空洞的。

1、使用rr隔离级别（repeatable read），并且按PK（唯一值字段、字段组合）排序，OFFSET

使用rr级别，保证一个事务中的每次发起的SQL读请求是绝对视角一致的。

使用唯一字段或字段组合排序，可以保证每次的结果排序是绝对一致的。加速每次偏移的数据一样，所以可以保证数据返回是绝对连续的。

select * from tbl where xx order by a,b offset x limit xx;  

2、使用游标

使用游标，可以保证视角一致，数据绝对一致。

postgres=# \h declare  
Command:     DECLARE  
Description: define a cursor  
Syntax:  
DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]  
    CURSOR [ { WITH | WITHOUT } HOLD ] FOR query  

begin;  
declare a cursor for select * from tbl where xx;  
fetch x from a;  
... 每一次请求，游标向前移动    
end;  

参考

《PostgreSQL 数据离散性 与 索引扫描性能(btree & bitmap index scan)》

《PostgreSQL 11 preview - 分页内核层优化 - 索引扫描offset优化(使用vm文件skip heap scan)》

《PostgreSQL 范围过滤 + 其他字段排序OFFSET LIMIT(多字段区间过滤)的优化与加速》

《PostgreSQL Oracle 兼容性之 - TZ_OFFSET》

《PostgreSQL 索引扫描offset内核优化 - case》

《PostgreSQL 数据访问 offset 的质变 case》

《论count与offset使用不当的罪名 和 分页的优化》

《PostgreSQL offset 原理，及使用注意事项》

《妙用explain Plan Rows快速估算行 - 分页数估算》

《分页优化 - order by limit x offset y performance tuning》

《分页优化, add max_tag column speedup Query in max match enviroment》

《PostgreSQL’s Cursor USAGE with SQL MODE - 分页优化》

.
.
.
…
来源 https://developer.aliyun.com/article/772133