安科网

postgresql分页数据重复问题的深入理解

84096424

84096424

2019-04-05

关注 关注

问题背景

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

如何处理？

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

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

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

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

推测

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

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

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

验证推测

1.创建表，初始化数据

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

三种排序

--快速排序 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)

快速排序

--快速排序
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这条数据被“遗漏”了。

堆排序

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条数据不是有顺序的。

归并排序

--将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后，任然有序。

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

如果每页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的时候，使用的内存增加，排序的方法从堆排序变成了归并排序。而归并排序为稳定排序，所以后面的分页不会再有后一页出现前一页数据的情况。

参考资料:PostgreSQL - repeating rows from LIMIT OFFSET

参考资料: LIMIT and OFFSET

结语

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

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

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

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

5.处理重复数据的常见手段就是，排序的时候可以在排序字段d_larq(立案日期)后面加上c_bh(唯一字段)来排序。

order by d_larq,c_bh;

总结

分页 排序算法 sql排序 sort postgresql

84096424

84096424

0 关注 0 粉丝 0 动态

关注 关注

相关推荐

sql中order by 待排序的字段值相同时，发生分页出现重复数据的问题

问题发生：当‘金额差‘值大量为0时（相同值）时，进行排序。这时候对数据的排序可能变得随机，即一会儿这条数据在前面，一会儿这条数据在后面了 ，这时就会出现分页的数据出现重复。例如：表一查询150-200条的数据，表二查询150-201的数据。

dreamhua 2020-05-10

ThinkPHP5分页paginate代码实例解析

$list = db('user')->paginate(10);$list = db('user')->paginate(10,false,['query'=>array('id' => $id)]);$this->assi

K先生 2020-11-10

THINKPHP5分页数据对象处理过程解析

在用到THINKPHP5的分页的时候，我们可以发现获取的数据是对象，如果我们要对数据进行循环增加数据就实现不了。V5.0.9版本开始 支持分页类后数据直接each遍历处理，方便修改分页后的数据，而不是只能通过模型的获取器来补充字段。

wushengyong 2020-10-28

MySQL 千万级数据量如何快速分页

后端开发中为了防止一次性加载太多数据导致内存、磁盘IO都开销过大，经常需要分页展示，这个时候就需要用到MySQL的LIMIT关键字。但你以为LIMIT分页就万事大吉了么，Too young,too simple啊，LIMIT在数据量大的时候极可能造成的一个

西瓜皮儿的皮儿 2020-09-07

Elasticsearch聚合后分页深入详解

不支持，看看Elasticsearch员工如何解读。这个问题，2014年在github上有很长的讨论。究其为什么ES不支持聚合后分页？可概括如下： 1）性能角度——聚合分页会在大量的记录中产生性能问题。2）正确性角度——聚合的文档计数不准确。所以奇怪的事情

molong0 2020-08-03

在Redis中进行分页排序查询【转】

Redis是一个高效的内存数据库，它支持包括String、List、Set、SortedSet和Hash等数据类型的存储，在Redis中通常根据数据的key查询其value值，Redis没有条件查询，在面对一些需要分页或排序的场景时，Redis就不太好不处

jokewinl 2020-07-18

算法的时间空间转换

算法的好坏有两个指标：需要的内存空间，代码运行的时间。程序的设计要不就是时间换空间，要不就是用空间去换时间。操作系统会将辅助存储器中的数据分区成固定大小的区块，称为“页”。当不需要时，将分页由主存移到辅助存储器；当需要时，再将数据取回，加载主存中。相对于分

田有朋 2020-07-05

nodejs mongoose 分页查询

router.post(‘/getArticleList‘, function ( req, res) {. message:‘请求成功‘,page和limit是由前端传过来的，通过req.body来获取，因为是要给前端传所有的数据条数，所以就需要将所有数

80530895 2020-07-05

mybatis 分页

　　　　jdbc驱动中有 Fetch Size 参数，表示每次最多从数据库中查询多少条数据，如果超过了这个数，实际上jdbc自动去再执行了查询。　　　　如果数据很多，使用RowBounds查询所有数据会有内存溢出风险。List<User> li

javamagicsun 2020-06-21

elasticsearch使用踩坑

es 在数据量很大的情况下如何提高查询效率啊？我们往 es 里写的数据，实际上都写到磁盘文件例如了，查询的时候，操作系统会将磁盘文件里的数据自动缓存到 filesystem cache 里面去。es 的搜索引擎严重依赖于底层的 filesystem cac

liangwenrong 2020-06-17

Elasticsearch聚合后分页

文章里面说了，es本身不支持分页，因为性能和正确性得不到保障，作者提供了一个方法，属于笨方法，就是把聚合后的数据全存入内存中，例如放List中，然后自己根据分页参数，使用List中的方法subList来截取数据。这样做的弊端显而易见，如果聚合后的数据过大，

molong0 2020-06-16

Mybatis 分页插件PageHelper 遇坑

在项目中使用了mybatis的插件PageHelper，在分页查询时总数据量有2400条记录，分页参数为offset 和length，现在想要查询所有数据，即循环查询，循环跳出条件为查询数据为空，length=500，在执行时发现循环不会跳出，陷入死循环，

xiuyangsong 2020-06-03

报表连 MongoDB，数据量大报表慢，怎么做分页？

MongoDB 没有关系数据库之类的分页机制，要实现分页查询得硬编码，可以通过实现 Pageable 接口来自定义分页类，具体网上有很多介绍。简单一点的方式是使用支持 MongoDB 分批取数的报表工具，这样就可以解决大数据量查询的问题。这里用了两个异步线

jaylong 2020-06-02

django中的分页

#首先导入分页的包。#在view.py文件中导入分页包，导入models表，用cate商品分类表距离。#先获取到所有要分页的数据。paginotor = Paginator #第一个参数为要分页的数据，第二个参数为一页获取多少条数据。paged = P

EdShao 2020-05-29

关于数据分页

抛开数据库和ES不说，我们平时常用的List和Map，其实也是可以分页的，通过对于List和Map的分页，我们可以去理解分页的原理。所以在分页的时候，需要确定可以分多少页，更深一个层次就是意味着，能不能完整的分页。如果可以，那么就是数据数量/页面大小个页面

晨曦之星 2020-05-29

Mybatis 使用Spring boot AOP +自定义注解+PageHelper实现分页

最近项目又用到了Mybaits。在Mybatis中分页是个比较头疼的事，因为需要我们每次都写重复的sql。好在我们有PageHelper这样的分页工具，它可以拦截你的sql，从而进行分页操作。首先我们引入maven依赖。在service或者persista

smalllove 2020-05-09

Mybatis面试题

MyBatis中使用#和$书写占位符有什么区别？#将传入的数据都当成一个字符串，会对传入的数据自动加上引号；$将传入的数据直接显示生成在SQL中。解释一下MyBatis中命名空间的作用。Dao 接口里的方法，是不能重载的，因为是全限名+方法名的保存和寻找策

liqiancao 2020-05-08

MyBatis使用pagehelper进行分页

　　通过MyBatis的插件原理，在mapper配置文件将pagehelper注册为MyBatis的插件，从而进行分页。//通过userService获取user的信息，其sql语句为"select * from user" 但因pag

Dullonjiang 2020-05-06

【Mybatis】10 实现分页 &amp; 分页插件

参数的数据类型选型问题，到底怎么注入参数才算合适？SQL不需要参数注入，而是通过RowBounds对象实现，这跟我的Limit参数类不是一个道理吗？暂时不明白第二个参数的意义是？

GechangLiu 2020-05-04

8种MySQL分页方法总结

MySQL的分页似乎一直是个问题，有什么优化方法吗？网上看到网上推荐了一些分页方法，但似乎不太可行，你能点评一下吗？---语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N。Limit限制的是从结果集的M位置处

achiverhai 2020-04-29
84096424

84096424

W3CSchool教程
HTML 教程
CSS 教程
Bootstrap 教程
Javascript 教程
jQuery 教程
后端教程
C 教程
Java 教程
PHP 教程
Python 教程
Go 教程
移动开发
Android 教程
Swift 教程
Kotlin 教程
jQuery Mobile 教程
ionic 教程
关于我们
新闻动态
联系方式
招聘英才
安科实验室
帮助与反馈

安科网(Ancii)，中国第一极客网

Copyright © 2013 - 2019 Ancii.com

京ICP备18063983号 京公网安备11010802014868号