PostgreSQL 分页, offset, 返回顺序, 扫描方法原理(seqscan, index scan, index only scan, bitmap scan, parallel xx scan),游标-阿里云开发者社区

开发者社区> 德哥> 正文

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

简介: 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 - 分页优化》

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议,奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等,奖品丰富,快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
PostgreSQL 分页, offset, 返回顺序, 扫描方法原理(seqscan, index scan, index only scan, bitmap scan, parallel xx scan),游标
标签 PostgreSQL , 数据离散性 , 扫描性能 , 重复扫 , bitmap index scan , 排序扫描 , 扫描方法 , 顺序 背景 一个这样的问题: 为什么select x from tbl offset x limit x; 两次查询连续的OFFSET,会有重复数据呢? select ctid,* from tbl where ... offset 0 li
1131 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
10080 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,大概有三种登录方式:
2962 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13885 0
通过调整表union all的顺序优化SQL
原文:通过调整表union all的顺序优化SQL   操作系统:Windows XP   数据库版本:SQL Server 2005   今天遇到一个SQL,过滤条件是自动生成的,因此,没法通过调整SQL的谓词达到优化的目的,只能去找SQL中的“大表”。
918 0
T-SQL查询:语句执行顺序
原文:T-SQL查询:语句执行顺序 读书笔记:《Microsoft SQL Server 2008技术内幕:T-SQL查询》   ===============  T-SQL查询的执行顺序 ===============      =============== T-SQL查询的示意图...
711 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载