文档章节

postgresql-分页重复数据探索

o
 osc_fmg49rzg
发布于 2019/03/20 11:08
字数 2504
阅读 51
收藏 0

精选30+云产品,助力企业轻松上云!>>>



# postgresql-分页重复数据探索

## 问题背景

许多开发和测试人员都可能遇到过列表的数据翻下一页的时候显示了上一页的数据,也就是翻页会有重复的数据。

### 如何处理?

这个问题出现的原因是因为选择的排序字段有重复,常见的处理办法就是排序的时候加上唯一字段,这样在分页的过程中数据就不会重复了。
关于这个问题文档也有解释并非是一个bug。而是排序时需要选择唯一字段来做排序,不然返回的结果不确定

### 排序返回数据重复的根本原因是什么呢?

经常优化sql的同学可能会发现,执行计划里面会有Sort Method这个关键字,而这个关键字就是排序选择的方法。abase的排序分为三种

```sql
quicksort                        快速排序    
top-N heapsort  Memory            堆排序
external merge  Disk            归并排序
```

### 推测

分页重复的问题和执行计划选择排序算法的稳定性有关。

### 简单介绍下这三种排序算法的场景:

在有索引的情况下:排序可以直接走索引。
在没有索引的情况下:当表的数据量较小的时候选择快速排序(排序所需必须内存小于work_mem),
当排序有limit,且耗费的内存不超过work_mem时选择堆排序,
当work_mem不够时选择归并排序。

### 验证推测

1.创建表,初始化数据

```sql
abase=# create table t_sort(n_int int,c_id varchar(300));
CREATE TABLE
abase=# insert into t_sort(n_int,c_id) select   100,generate_series(1,9);
INSERT 0 9
abase=# insert into t_sort(n_int,c_id) select   200,generate_series(1,9);
INSERT 0 9
abase=# insert into t_sort(n_int,c_id) select   300,generate_series(1,9);
INSERT 0 9
abase=# insert into t_sort(n_int,c_id) select   400,generate_series(1,9);
INSERT 0 9
abase=# insert into t_sort(n_int,c_id) select   500,generate_series(1,9);
INSERT 0 9
abase=# insert into t_sort(n_int,c_id) select   600,generate_series(1,9);
INSERT 0 9
```

### 三种排序

```sql
--快速排序 quicksort
abase=# explain  analyze select ctid,n_int,c_id from t_sort order by n_int asc;
                                               QUERY PLAN                                                
------------------------------------------------------------
 Sort  (cost=3.09..3.23 rows=54 width=12) (actual time=0.058..0.061 rows=54 loops=1)
   Sort Key: n_int
   Sort Method: quicksort  Memory: 27kB
   ->  Seq Scan on t_sort  (cost=0.00..1.54 rows=54 width=12) (actual time=0.021..0.032 rows=54 loops=1)
 Planning time: 0.161 ms
 Execution time: 0.104 ms
(6 rows)
--堆排序  top-N heapsort
abase=# explain  analyze select ctid,n_int,c_id from t_sort order by n_int asc limit 10;
                                                  QUERY PLAN                                                 
 
------------------------------------------------------------
 Limit  (cost=2.71..2.73 rows=10 width=12) (actual time=0.066..0.068 rows=10 loops=1)
   ->  Sort  (cost=2.71..2.84 rows=54 width=12) (actual time=0.065..0.066 rows=10 loops=1)
         Sort Key: n_int
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on t_sort  (cost=0.00..1.54 rows=54 width=12) (actual time=0.022..0.031 rows=54 loops=1
)
 Planning time: 0.215 ms
 Execution time: 0.124 ms
(7 rows)
--归并排序  external sort  Disk
--插入大量值为a的数据
abase=# insert into t_sort(n_int,c_id) select   generate_series(1000,2000),'a';
INSERT 0 1001
abase=# set work_mem = '64kB';
SET
abase=# explain  analyze select ctid,n_int,c_id from t_sort order by n_int asc;
                                                 QUERY PLAN                                                 
-------------------------------------------------------------
 Sort  (cost=18.60..19.28 rows=270 width=12) (actual time=1.235..1.386 rows=1055 loops=1)
   Sort Key: n_int
   Sort Method: external sort  Disk: 32kB
   ->  Seq Scan on t_sort  (cost=0.00..7.70 rows=270 width=12) (actual time=0.030..0.247 rows=1055 loops=1)
 Planning time: 0.198 ms
 Execution time: 1.663 ms
(6 rows)
```

### 快速排序

```sql
--快速排序
abase=# explain  analyze select ctid,n_int,c_id from t_sort order by n_int asc;
                                               QUERY PLAN                                                
------------------------------------------------------------
 Sort  (cost=3.09..3.23 rows=54 width=12) (actual time=0.058..0.061 rows=54 loops=1)
   Sort Key: n_int
   Sort Method: quicksort  Memory: 27kB
   ->  Seq Scan on t_sort  (cost=0.00..1.54 rows=54 width=12) (actual time=0.021..0.032 rows=54 loops=1)
 Planning time: 0.161 ms
 Execution time: 0.104 ms
(6 rows)   

--获取前20条数据
    abase=#  select ctid,n_int,c_id from t_sort order by n_int asc  limit 20;
      ctid  | n_int | c_id
    --------+-------+------
     (0,7)  |   100 | 7
     (0,2)  |   100 | 2
     (0,4)  |   100 | 4
     (0,8)  |   100 | 8
     (0,3)  |   100 | 3
     (0,6)  |   100 | 6
     (0,5)  |   100 | 5
     (0,9)  |   100 | 9
     (0,1)  |   100 | 1
     (0,14) |   200 | 5
     (0,13) |   200 | 4
     (0,12) |   200 | 3
     (0,10) |   200 | 1
     (0,15) |   200 | 6
     (0,16) |   200 | 7
     (0,17) |   200 | 8
     (0,11) |   200 | 2
     (0,18) |   200 | 9
     (0,20) |   300 | 2
     (0,19) |   300 | 1
    (20 rows)
    --分页获取前10条数据
    abase=# select ctid,n_int,c_id from t_sort order by n_int asc limit 10 offset 0;
      ctid  | n_int | c_id
    --------+-------+------
     (0,1)  |   100 | 1
     (0,3)  |   100 | 3
     (0,4)  |   100 | 4
     (0,2)  |   100 | 2
     (0,6)  |   100 | 6
     (0,7)  |   100 | 7
     (0,8)  |   100 | 8
     (0,9)  |   100 | 9
     (0,5)  |   100 | 5
     (0,10) |   200 | 1
    (10 rows)
    --分页从第10条开始获取10条
    abase=#  select ctid,n_int,c_id from t_sort order by n_int asc  limit 10 offset 10;
      ctid  | n_int | c_id
    --------+-------+------
     (0,13) |   200 | 4
     (0,12) |   200 | 3
     (0,10) |   200 | 1
     (0,15) |   200 | 6
     (0,16) |   200 | 7
     (0,17) |   200 | 8
     (0,11) |   200 | 2
     (0,18) |   200 | 9
     (0,20) |   300 | 2
     (0,19) |   300 | 1
    (10 rows)
```

limit 10 offset 0,limit 10 offset 10,连续取两页数据

此处可以看到limit 10 offset 10结果中,第三条数据重复了第一页的最后一条: (0,10) |   200 | 1

并且n_int = 200 and c_id = 5这条数据被“遗漏”了。

### 堆排序

```sql
abase=# select count(*) from t_sort;
 count
-------
  1055
(1 row)
--设置work_mem 4MB
abase=# show work_mem ;
 work_mem
----------
 4MB
(1 row)

--top-N heapsort
abase=# explain analyze select * from ( select ctid,n_int,c_id from test order by n_int asc limit 1001 offset 0) td limit 10;
                                                        QUERY PLAN                                           
              
-------------------------------------------------------------------------------------------------------------
 Limit  (cost=2061.33..2061.45 rows=10 width=13) (actual time=15.247..15.251 rows=10 loops=1)
   ->  Limit  (cost=2061.33..2063.83 rows=1001 width=13) (actual time=15.245..15.247 rows=10 loops=1)
         ->  Sort  (cost=2061.33..2135.72 rows=29757 width=13) (actual time=15.244..15.245 rows=10 loops=1)
               Sort Key: test.n_int
               Sort Method: top-N heapsort  Memory: 95kB
               ->  Seq Scan on test  (cost=0.00..429.57 rows=29757 width=13) (actual time=0.042..7.627 rows=2
9757 loops=1)
 Planning time: 0.376 ms
 Execution time: 15.415 ms
(8 rows)

--获取limit 1001 offset 0,然后取10前10条数据
abase=#  select * from ( select ctid,n_int,c_id from test order by n_int asc limit 1001 offset 0) td limit 10;
   ctid   | n_int | c_id
----------+-------+------
 (0,6)    |   100 | 6
 (0,2)    |   100 | 2
 (0,5)    |   100 | 5
 (87,195) |   100 | 888
 (0,3)    |   100 | 3
 (0,1)    |   100 | 1
 (0,8)    |   100 | 8
 (0,55)   |   100 | 888
 (44,12)  |   100 | 888
 (0,9)    |   100 | 9
(10 rows)

---获取limit 1001 offset 1,然后取10前10条数据
abase=#  select * from ( select ctid,n_int,c_id from test order by n_int asc limit 1001 offset 1) td limit 10;
   ctid   | n_int | c_id
----------+-------+------
 (44,12)  |   100 | 888
 (0,8)    |   100 | 8
 (0,1)    |   100 | 1
 (0,5)    |   100 | 5
 (0,9)    |   100 | 9
 (87,195) |   100 | 888
 (0,7)    |   100 | 7
 (0,6)    |   100 | 6
 (0,3)    |   100 | 3
 (0,4)    |   100 | 4
(10 rows)

---获取limit 1001 offset 2,然后取10前10条数据
abase=# select * from ( select ctid,n_int,c_id from test order by n_int asc limit 1001 offset 2) td limit 10;
   ctid   | n_int | c_id
----------+-------+------
 (0,5)    |   100 | 5
 (0,55)   |   100 | 888
 (0,1)    |   100 | 1
 (0,9)    |   100 | 9
 (0,2)    |   100 | 2
 (0,3)    |   100 | 3
 (44,12)  |   100 | 888
 (0,7)    |   100 | 7
 (87,195) |   100 | 888
 (0,4)    |   100 | 4
(10 rows)

堆排序使用内存: Sort Method: top-N heapsort  Memory: 95kB
```

当offset从0变成1后,以及变成2后,会发现查询出的10条数据不是有顺序的。

### 归并排序

```sql
--将work_mem设置为64kb让其走归并排序。
abase=# set work_mem ='64kB';
SET
abase=# show work_mem;
 work_mem
----------
 64kB
(1 row)

-- external merge  Disk
abase=# explain analyze  select * from ( select ctid,n_int,c_id from test order by n_int asc limit 1001 offset 0) td limit 10;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2061.33..2061.45 rows=10 width=13) (actual time=27.912..27.916 rows=10 loops=1)
   ->  Limit  (cost=2061.33..2063.83 rows=1001 width=13) (actual time=27.910..27.913 rows=10 loops=1)
         ->  Sort  (cost=2061.33..2135.72 rows=29757 width=13) (actual time=27.909..27.911 rows=10 loops=1)
               Sort Key: test.n_int
               Sort Method: external merge  Disk: 784kB
               ->  Seq Scan on test  (cost=0.00..429.57 rows=29757 width=13) (actual time=0.024..6.730 rows=29757 loops=1)
 Planning time: 0.218 ms
 Execution time: 28.358 ms
(8 rows)

--同堆排序一样,获取limit 1001 offset 0,然后取10前10条数据
abase=# select * from ( select ctid,n_int,c_id from test order by n_int asc limit 1001 offset 0) td limit 10;
  ctid  | n_int | c_id
--------+-------+------
 (0,1)  |   100 | 1
 (0,2)  |   100 | 2
 (0,4)  |   100 | 4
 (0,8)  |   100 | 8
 (0,9)  |   100 | 9
 (0,5)  |   100 | 5
 (0,3)  |   100 | 3
 (0,6)  |   100 | 6
 (0,55) |   100 | 888
 (0,7)  |   100 | 7
(10 rows)

--同堆排序一样,获取limit 1001 offset 1,然后取10前10条数据
abase=# select * from ( select ctid,n_int,c_id from test order by n_int asc limit 1001 offset 1) td limit 10;
   ctid   | n_int | c_id
----------+-------+------
 (0,2)    |   100 | 2
 (0,4)    |   100 | 4
 (0,8)    |   100 | 8
 (0,9)    |   100 | 9
 (0,5)    |   100 | 5
 (0,3)    |   100 | 3
 (0,6)    |   100 | 6
 (0,55)   |   100 | 888
 (0,7)    |   100 | 7
 (87,195) |   100 | 888
(10 rows)

--同堆排序一样,获取limit 1001 offset 2,然后取10前10条数据
abase=# select * from ( select ctid,n_int,c_id from test order by n_int asc limit 1001 offset 2) td limit 10;
   ctid   | n_int | c_id
----------+-------+------
 (0,4)    |   100 | 4
 (0,8)    |   100 | 8
 (0,9)    |   100 | 9
 (0,5)    |   100 | 5
 (0,3)    |   100 | 3
 (0,6)    |   100 | 6
 (0,55)   |   100 | 888
 (0,7)    |   100 | 7
 (87,195) |   100 | 888
 (44,12)  |   100 | 888
(10 rows)

```

减小work_mem使用归并排序的时候,offset从0变成1后以及变成2后,任然有序。

### 还有一种情况,那就是在查询前面几页的时候会有重复,但是越往后面翻就不会重复了,现在也可以解释清楚。

```sql
如果每页10条数据,当offse较小的时候使用的内存较少。当offse不断增大,所耗费的内存也就越多。
--设置work_mem =64kb
abase=# show work_mem;
 work_mem
----------
 64kB
(1 row)
--查询limit 10 offset 10
abase=# explain analyze  select * from ( select ctid,n_int,c_id from test order by n_int asc limit 10 offset 10) td limit 10;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1221.42..1221.54 rows=10 width=13) (actual time=12.881..12.884 rows=10 loops=1)
   ->  Limit  (cost=1221.42..1221.44 rows=10 width=13) (actual time=12.879..12.881 rows=10 loops=1)
         ->  Sort  (cost=1221.39..1295.79 rows=29757 width=13) (actual time=12.877..12.879 rows=20 loops=1)
               Sort Key: test.n_int
               Sort Method: top-N heapsort  Memory: 25kB
               ->  Seq Scan on test  (cost=0.00..429.57 rows=29757 width=13) (actual time=0.058..6.363 rows=29757 loops=1)
 Planning time: 0.230 ms
 Execution time: 12.976 ms
(8 rows)

--查询limit 10 offset 1000
abase=# explain analyze  select * from ( select ctid,n_int,c_id from test order by n_int asc limit 10 offset 1000) td limit 10;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2065.75..2065.88 rows=10 width=13) (actual time=27.188..27.192 rows=10 loops=1)
   ->  Limit  (cost=2065.75..2065.78 rows=10 width=13) (actual time=27.186..27.188 rows=10 loops=1)
         ->  Sort  (cost=2063.25..2137.64 rows=29757 width=13) (actual time=26.940..27.138 rows=1010 loops=1)
               Sort Key: test.n_int
               Sort Method: external merge  Disk: 784kB
               ->  Seq Scan on test  (cost=0.00..429.57 rows=29757 width=13) (actual time=0.026..6.374 rows=29757 loops=1)
 Planning time: 0.207 ms
 Execution time: 27.718 ms
(8 rows)

可以看到当offset从10增加到1000的时候,使用的内存增加,排序的方法从堆排序变成了归并排序。而归并排序为稳定排序,所以后面的分页不会再有后一页出现前一页数据的情况。
```

## 结语

1.关于分页重复数据的问题主要是排序字段不唯一并且执行计划走了快速排序和堆排序导致。

2.当排序有重复字段,但是如果查询是归并排序,便不会存在有重复数据的问题。

3.当用重复字段排序,前面的页重复,随着offset的增大导致work_mem不足以后使用归并排序,就不存在重复的数据了。

4.排序和算法的稳定性有关,当执行计划选择不同的排序算法时,返回的结果不一样。

5.处理重复数据的常见手段就是,排序的时候选择唯一字段来排序。

[参考资料](https://stackoverflow.com/questions/13580826/postgresql-repeating-rows-from-limit-offset)

[参考资料](https://www.postgresql.org/docs/8.3/queries-limit.html)

o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
Django组件--分页器(有用)

一、分页器对象 from django.core.paginator import Paginator,EmptyPage booklist = Book.objects.all() #假设有100条数据 #分页器 每页展示条数:8条 paginator = Paginator(booklist, 8)......

osc_yz2rojzt
2019/04/19
3
0
postgresql行转列

问:怎么分页&&按条件&&按顺序&&姓名不重复查出数据? 答:其实就是行转列,那么,postgresql怎么进行转列呢,百度了下,大概有三种写法 写法1 group by + sum + case when select name,sum(...

osc_iqtexsjp
2018/08/25
9
0
MySQL与PostgreSQL的大表处理性能比较

硬件: Intel E8400 3.0GHz / 4G DDR2 / 1T 7200R HD 操作系统: Windows 7 64bit MySQL: v5.5.8, 64bit, Server模式配置(Windows下的GUI界面配置工具里的选项,所举典型应用场景为将MySQL安装...

cnblogs
2011/04/23
1.1W
5
PostgreSQL数据库中的表和数据(Tables & Data)

照顾好你的数据,数据库也会照顾你。保持数据库的整洁,查询起来也会更快,应用也会少些错误。半夜被叫醒解决数据问题并不酷。接下来,就和章郎虫博主一起来了解postgresql的表和数据吧。 一、...

章郎虫
2014/02/25
418
0
Ceph中PG和PGP的区别

http://www.zphj1987.com/2016/10/19/Ceph%E4%B8%ADPG%E5%92%8CPGP%E7%9A%84%E5%8C%BA%E5%88%AB/ 一、前言 首先来一段英文关于PG和PGP区别的解释: PG = Placement Group PGP = Placement G......

osc_0jbt10ev
2019/08/22
0
0

没有更多内容

加载失败,请刷新页面

加载更多

聚焦餐饮行业,研究院昨发布数据显示

谈话,聚焦餐饮行业,研究院昨发布数据显示,今年上半年,全国餐饮行业招聘需求增长46.18%,平均月薪6387元.随着餐饮行业的快速发展,"如何留人"也成为餐饮企业的思考题. 记者了解到,中国饭店协会...

点击fojewio
30分钟前
20
0
3·15晚会曝光上海氪信、招财旺旺SDK包泄露隐私 后台上传交易验证码敏感信息

来源 | 央视 7月16日,央视3·15晚会曝光国美易卡、美的空调遥控器、姨妈日历、银码头等50多款软件中内嵌的SDK包读取、上传用户隐私问题。上海氪信信息技术有限公司、北京招财旺旺信息技术有...

镭射财经
40分钟前
14
0
名称=''的无效表单控件不可聚焦 - An invalid form control with name='' is not focusable

问题: I have an acute problem on my website. 我的网站上有一个严重的问题。 In Google Chrome some customers are not able to proceed to my payment page. 在Google Chrome浏览器中,某......

技术盛宴
41分钟前
14
0
Hacker News 简讯 2020-07-17

更新时间: 2020-07-17 00:00 D 2.093.0 - (dlang.org) D 2.093.0 得分:32 | 评论:2 Let’s avoid talk of ‘chemical imbalance’: it’s people in distress - (psyche.co) 让我们避免谈论“......

FalconChen
46分钟前
80
0
【LeetCode】 59 在排序数组中查找元素的第一个和最后一个位置

题目: 解题思路: 二分法 https://leetcode-cn.com/problems/find-first-and-last-position-of-element-in-sorted-array/solution/zai-pai-xu-shu-zu-zhong-cha-zhao-yuan-su-de-di-yi-/ 代......

JaneRoad
昨天
17
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部