Postgresql 之分页技术（三）

瀚高PG实验室

于 2018-04-04 00:09:11 发布

阅读量2.6k

点赞数

分类专栏： PostgreSQL之日常维护文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/pg_hgdb/article/details/79811217

版权

PostgreSQL之日常维护专栏收录该内容

197 篇文章 41 订阅

订阅专栏

作者：瀚高PG实验室（Highgo PG Lab）- 波罗
游标分页
如何使用游标分页？
– 开启一个事务
BEGIN;
– 定义游标
DECLARE medley_cur CURSOR FOR SELECT * FROM medley;
– 获取10条记录
FETCH 10 FROM medley_cur;
– …
– 继续获取10条记录
FETCH 10 FROM medley_cur;
–最后结束事务
COMMIT;

测试：游标分页
创建一个测试表，并插入十万条记录：
postgres=# create table test(id int,name varchar);
CREATE TABLE
postgres=# insert into test values(generate_series(1,100000),‘aaa’);
INSERT 0 100000

在一个事务中，使用游标进行分页：
postgres=# begin;
BEGIN
postgres=# declare mycur cursor for select * from test;
DECLARE CURSOR
postgres=# fetch 5 from mycur;
id | name
----±-----
1 | aaa
2 | aaa
3 | aaa
4 | aaa
(5 rows)

postgres=# fetch 5 from mycur;
id | name
----±-----

5 | aaa
6 | aaa
7 | aaa
8 | aaa
9 | aaa
10 | aaa
(5 rows)

postgres=# commit;
COMMIT

postgres=#

游标分页的原理：首先要把相关数据加载到内存中，然后再进行提取数据。

使用游标分页，两个必须：
大数据量的时候，必须过滤掉不需要的数据，否则将会消耗大量的系统资源。
游标使用完毕后，必须关闭，否则系统资源将被耗尽。

已创建完成的游标，不会随着数据的更新而更新。
所以，在相关数据更新之后，已经创建的 cursor 的数据就陈旧了。

测试：游标分页的弊端
Session1：启动一个事务，使用游标进行分页
highgo=# create table test(id int,name varchar);
CREATE TABLE
highgo=# insert into test values (generate_series(1,1000),‘aaa’);
INSERT 0 1000
highgo=# begin;
BEGIN
highgo=# declare mycur cursor for select * from test;
DECLARE CURSOR
highgo=# fetch 5 from mycur;
id | name
----±-----
1 | aaa
2 | aaa
3 | aaa
4 | aaa
5 | aaa
(5 行记录)
highgo=#