postgresql分页数据重复问题的深入理解
问题背景
许多开发和测试人员都可能遇到过列表的数据翻下一页的时候显示了上一页的数据，也就是翻页会有重复的数据。
如何处理？
这个问题出现的原因是因为选择的排序字段有重复，常见的处理办法就是排序的时候加上唯一字段，这样在分页的过程中数据就不会重复了。 关于这个问题文档也有解释并非是一个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;
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对我们的支持。
相关推荐
-
PHP实现从PostgreSQL数据库检索数据分页显示及根据条件查找数据示例
本文实例讲述了PHP实现从PostgreSQL数据库检索数据分页显示及根据条件查找数据.分享给大家供大家参考,具体如下: 主要功能是从postgreSql查询数据,并检索,由于自己刚开始接触,所以难点在于多条数据同时筛选并分页显示出来,写下自己的代码与大家共享. <html> <head> <script type="text/javascript"> /** * 分页函数 * pno--页数 * psize--每页显示记录数 * 分页部分是从真实数
-
postgresql分页数据重复问题的深入理解
问题背景 许多开发和测试人员都可能遇到过列表的数据翻下一页的时候显示了上一页的数据,也就是翻页会有重复的数据. 如何处理? 这个问题出现的原因是因为选择的排序字段有重复,常见的处理办法就是排序的时候加上唯一字段,这样在分页的过程中数据就不会重复了. 关于这个问题文档也有解释并非是一个bug.而是排序时需要选择唯一字段来做排序,不然返回的结果不确定 排序返回数据重复的根本原因是什么呢? 经常优化sql的同学可能会发现,执行计划里面会有Sort Method这个关键字,而这个关键字就是排序选择的方法
-
MySql分页时使用limit+order by会出现数据重复问题解决
目录 摘要 问题描述 分析问题 解决问题 摘要 能把复杂的知识讲的简单很重要 在学习的过程中我们看过很多资料.视频.文档等,因为现在资料视频都较多所以往往一个知识点会有多种多样的视频形式讲解.除了推广营销以外,确实有很多人的视频讲解非常优秀,例如李永乐老师的短视频课,可以在一个黑板上把那么复杂的知识,讲解的那么容易理解,那么透彻.而我们学习编程的人也是,不只是要学会把知识点讲明白,也要写明白. 问题描述 在 MySQL 中我们通常会采用 limit 来进行翻页查询,比如 limit(0,10)
-
SQL Server 2012使用Offset/Fetch Next实现分页数据查询
在Sql Server 2012之前,实现分页主要是使用ROW_NUMBER(),在SQL Server2012,可以使用Offset ...Rows Fetch Next ... Rows only的方式去实现分页数据查询. select [column1] ,[column2] ... ,[columnN] from [tableName] order by [columnM] offset (pageIndex-1)*pageSize rows fetch next pageSize r
-
在ASP.NET 2.0中操作数据之二十六：排序自定义分页数据
导言 和默认翻页方式相比,自定义分页能提高几个数量级的效率.当我们的需要对大量数据分页的时候就需要考虑自定义分页,然而实现自定义分页相比默认分页需要做更多工作.对于排序自定义分页数据也是这样,在本教程中我们就会扩展前面的例子来实现自定义分页数据的排序. 注意:既然本教程是基于前一个的,因此我们需要把前面教程示例页面EfficientPaging.aspx的<asp:Content>元素中的代码复制到本教程SortParameter.aspx示例页面中.关于如何进行这样的复制操作请参看为删除数据
-
thinkphp中多表查询中防止数据重复的sql语句(必看)
下面先来看看例子: table id name 1 a 2 b 3 c 4 c 5 b 库结构大概这样,这只是一个简单的例子,实际情况会复杂得多. select *, count(distinct name) from table group by name 结果: id name count(distinct name) 1 a 1 2 b 1 3 c 1 最后一 项是多余的,不用管就行了 tp2.0手册 搜索连贯操作 可看到相关的资料 SELECT cat_id, COUNT(*) AS
-
Codeigniter框架实现获取分页数据和总条数的方法
本文实例讲述了Codeigniter框架实现获取分页数据和总条数的方法.分享给大家供大家参考.具体实现方法如下: 一般在数据分页的时候需要获取当前页的数据和总条数,一般人是在model中封装两个函数分别获取当前页的数据和数据总条数,业务逻辑类似,感觉有点冗余,可以封装在一起 复制代码 代码如下: /** * 获取分页数据及总条数 * @param string @tablename 表名 * @param mixed $where 条件 * @param int $limit 每页条数 * @p
-
BootStrap+Mybatis框架下实现表单提交数据重复验证
效果: jsp页面: <form class="form-horizontal lui-tj-bd" id="dbc_code_add_form" method="post"> <div class="row"> <div class="col-xs-12"> <!-- PAGE CONTENT BEGINS --> <div class="t
-
Element实现表格分页数据选择+全选所有完善批量操作
后台管理系统中的列表页面,一般都会有对列表数据进行批量操作的功能,例如:批量删除.批量删除等. 之前项目中只是简单的用到Element框架中常规的属性.事件.在一次机缘巧合下,了解到一个公司内部的框架是基于Element框架内部实现了一些插件功能,对于表格这一块完善了很多功能,当时没有把握住机会去看源码是怎么实现的,现在有点小后悔呢,嘤嘤嘤~~~~没关系,自己慢慢一点一点实现. 实现的功能有: 分页数据选择 全选所有数据(不是element框架自带的全选本页哦!) 1.分页数据选择 一开始以为不
-
MySQL Limit性能优化及分页数据性能优化详解
MySQL Limit可以分段查询数据库数据,主要应用在分页上.虽然现在写的网站数据都是千条级别,一些小的的优化起的作用不大,但是开发就要做到极致,追求完美性能.下面记录一些limit性能优化方法. Limit语法: SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT子句可以被用于强制 SELECT 语句返回指定的记录数.LIMIT接受一个或两个数字参数.参数必须是一个整数常量. 如果给定两个参数,第一个参数指定
-
Spring mvc防止数据重复提交的方法
本文实例为大家分享了Spring mvc如何防止数据重复提交,供大家参考,具体内容如下 方法分析: 这里使用的防止数据重复提交的方法是使用token,给所有的url加一个拦截器,在拦截器里面用java的UUID生成一个随机的UUID并把这个UUID放到session里面,然后在浏览器做数据提交的时候将此UUID提交到服务器.服务器在接收到此UUID后,检查一下该UUID是否已经被提交,如果已经被提交,则不让逻辑继续执行下去. 源码实现: 注解Token代码: @Target(ElementTyp