postgresql分页数据重复问题的深入理解
问题背景
许多开发和测试人员都可能遇到过列表的数据翻下一页的时候显示了上一页的数据，也就是翻页会有重复的数据。
如何处理？
这个问题出现的原因是因为选择的排序字段有重复，常见的处理办法就是排序的时候加上唯一字段，这样在分页的过程中数据就不会重复了。关于这个问题文档也有解释并非是一个bug。而是排序时需要选择唯一字段来做排序，不然返回的结果不确定
排序返回数据重复的根本原因是什么呢？
经常优化sql的同学可能会发现，执行计划里面会有SortMethod这个关键字，而这个关键字就是排序选择的方法。abase的排序分为三种
quicksort 快速排序
top-Nheapsort Memory 堆排序
externalmerge Disk 归并排序
推测
分页重复的问题和执行计划选择排序算法的稳定性有关。
简单介绍下这三种排序算法的场景：
在有索引的情况下：排序可以直接走索引。在没有索引的情况下：当表的数据量较小的时候选择快速排序（排序所需必须内存小于work_mem），当排序有limit，且耗费的内存不超过work_mem时选择堆排序，当work_mem不够时选择归并排序。
验证推测
1.创建表，初始化数据
abase=#createtablet_sort(n_intint,c_idvarchar(300)); CREATETABLE abase=#insertintot_sort(n_int,c_id)select100,generate_series(1,9); INSERT09 abase=#insertintot_sort(n_int,c_id)select200,generate_series(1,9); INSERT09 abase=#insertintot_sort(n_int,c_id)select300,generate_series(1,9); INSERT09 abase=#insertintot_sort(n_int,c_id)select400,generate_series(1,9); INSERT09 abase=#insertintot_sort(n_int,c_id)select500,generate_series(1,9); INSERT09 abase=#insertintot_sort(n_int,c_id)select600,generate_series(1,9); INSERT09
三种排序
--快速排序quicksort abase=#explainanalyzeselectctid,n_int,c_idfromt_sortorderbyn_intasc; QUERYPLAN ------------------------------------------------------------ Sort(cost=3.09..3.23rows=54width=12)(actualtime=0.058..0.061rows=54loops=1) SortKey:n_int SortMethod:quicksortMemory:27kB ->SeqScanont_sort(cost=0.00..1.54rows=54width=12)(actualtime=0.021..0.032rows=54loops=1) Planningtime:0.161ms Executiontime:0.104ms (6rows) --堆排序top-Nheapsort abase=#explainanalyzeselectctid,n_int,c_idfromt_sortorderbyn_intasclimit10; QUERYPLAN ------------------------------------------------------------ Limit(cost=2.71..2.73rows=10width=12)(actualtime=0.066..0.068rows=10loops=1) ->Sort(cost=2.71..2.84rows=54width=12)(actualtime=0.065..0.066rows=10loops=1) SortKey:n_int SortMethod:top-NheapsortMemory:25kB ->SeqScanont_sort(cost=0.00..1.54rows=54width=12)(actualtime=0.022..0.031rows=54loops=1 ) Planningtime:0.215ms Executiontime:0.124ms (7rows) --归并排序externalsortDisk --插入大量值为a的数据 abase=#insertintot_sort(n_int,c_id)selectgenerate_series(1000,2000),'a'; INSERT01001 abase=#setwork_mem='64kB'; SET abase=#explainanalyzeselectctid,n_int,c_idfromt_sortorderbyn_intasc; QUERYPLAN ------------------------------------------------------------- Sort(cost=18.60..19.28rows=270width=12)(actualtime=1.235..1.386rows=1055loops=1) SortKey:n_int SortMethod:externalsortDisk:32kB ->SeqScanont_sort(cost=0.00..7.70rows=270width=12)(actualtime=0.030..0.247rows=1055loops=1) Planningtime:0.198ms Executiontime:1.663ms (6rows)
快速排序
--快速排序 abase=#explainanalyzeselectctid,n_int,c_idfromt_sortorderbyn_intasc; QUERYPLAN ------------------------------------------------------------ Sort(cost=3.09..3.23rows=54width=12)(actualtime=0.058..0.061rows=54loops=1) SortKey:n_int SortMethod:quicksortMemory:27kB ->SeqScanont_sort(cost=0.00..1.54rows=54width=12)(actualtime=0.021..0.032rows=54loops=1) Planningtime:0.161ms Executiontime:0.104ms (6rows) --获取前20条数据 abase=#selectctid,n_int,c_idfromt_sortorderbyn_intasclimit20; 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 (20rows)--分页获取前10条数据 abase=#selectctid,n_int,c_idfromt_sortorderbyn_intasclimit10offset0; 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 (10rows) --分页从第10条开始获取10条 abase=#selectctid,n_int,c_idfromt_sortorderbyn_intasclimit10offset10; 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 (10rows) limit10offset0,limit10offset10，连续取两页数据
此处可以看到limit10offset10结果中，第三条数据重复了第一页的最后一条：(0,10)|200|1
并且n_int=200andc_id=5这条数据被“遗漏”了。
堆排序
abase=#selectcount(*)fromt_sort; count ------- 1055 (1row) --设置work_mem4MB abase=#showwork_mem; work_mem ---------- 4MB (1row) --top-Nheapsort abase=#explainanalyzeselect*from(selectctid,n_int,c_idfromtestorderbyn_intasclimit1001offset0)tdlimit10; QUERYPLAN ------------------------------------------------------------------------------------------------------------- Limit(cost=2061.33..2061.45rows=10width=13)(actualtime=15.247..15.251rows=10loops=1) ->Limit(cost=2061.33..2063.83rows=1001width=13)(actualtime=15.245..15.247rows=10loops=1) ->Sort(cost=2061.33..2135.72rows=29757width=13)(actualtime=15.244..15.245rows=10loops=1) SortKey:test.n_int SortMethod:top-NheapsortMemory:95kB ->SeqScanontest(cost=0.00..429.57rows=29757width=13)(actualtime=0.042..7.627rows=2 9757loops=1) Planningtime:0.376ms Executiontime:15.415ms (8rows) --获取limit1001offset0，然后取10前10条数据 abase=#select*from(selectctid,n_int,c_idfromtestorderbyn_intasclimit1001offset0)tdlimit10; 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 (10rows) ---获取limit1001offset1，然后取10前10条数据 abase=#select*from(selectctid,n_int,c_idfromtestorderbyn_intasclimit1001offset1)tdlimit10; 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 (10rows) ---获取limit1001offset2，然后取10前10条数据 abase=#select*from(selectctid,n_int,c_idfromtestorderbyn_intasclimit1001offset2)tdlimit10; 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 (10rows)
堆排序使用内存：SortMethod:top-Nheapsort Memory:95kB
当offset从0变成1后，以及变成2后，会发现查询出的10条数据不是有顺序的。
归并排序
--将work_mem设置为64kb让其走归并排序。 abase=#setwork_mem='64kB'; SET abase=#showwork_mem; work_mem ---------- 64kB (1row) --externalmergeDisk abase=#explainanalyzeselect*from(selectctid,n_int,c_idfromtestorderbyn_intasclimit1001offset0)tdlimit10; QUERYPLAN --------------------------------------------------------------------------------------------------------------------------- Limit(cost=2061.33..2061.45rows=10width=13)(actualtime=27.912..27.916rows=10loops=1) ->Limit(cost=2061.33..2063.83rows=1001width=13)(actualtime=27.910..27.913rows=10loops=1) ->Sort(cost=2061.33..2135.72rows=29757width=13)(actualtime=27.909..27.911rows=10loops=1) SortKey:test.n_int SortMethod:externalmergeDisk:784kB ->SeqScanontest(cost=0.00..429.57rows=29757width=13)(actualtime=0.024..6.730rows=29757loops=1) Planningtime:0.218ms Executiontime:28.358ms (8rows) --同堆排序一样，获取limit1001offset0，然后取10前10条数据 abase=#select*from(selectctid,n_int,c_idfromtestorderbyn_intasclimit1001offset0)tdlimit10; 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 (10rows) --同堆排序一样，获取limit1001offset1，然后取10前10条数据 abase=#select*from(selectctid,n_int,c_idfromtestorderbyn_intasclimit1001offset1)tdlimit10; 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 (10rows) --同堆排序一样，获取limit1001offset2，然后取10前10条数据 abase=#select*from(selectctid,n_int,c_idfromtestorderbyn_intasclimit1001offset2)tdlimit10; 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 (10rows)
减小work_mem使用归并排序的时候，offset从0变成1后以及变成2后，任然有序。
还有一种情况，那就是在查询前面几页的时候会有重复，但是越往后面翻就不会重复了，现在也可以解释清楚。
如果每页10条数据，当offse较小的时候使用的内存较少。当offse不断增大，所耗费的内存也就越多。
--设置work_mem=64kb abase=#showwork_mem; work_mem ---------- 64kB (1row) --查询limit10offset10 abase=#explainanalyzeselect*from(selectctid,n_int,c_idfromtestorderbyn_intasclimit10offset10)tdlimit10; QUERYPLAN --------------------------------------------------------------------------------------------------------------------------- Limit(cost=1221.42..1221.54rows=10width=13)(actualtime=12.881..12.884rows=10loops=1) ->Limit(cost=1221.42..1221.44rows=10width=13)(actualtime=12.879..12.881rows=10loops=1) ->Sort(cost=1221.39..1295.79rows=29757width=13)(actualtime=12.877..12.879rows=20loops=1) SortKey:test.n_int SortMethod:top-NheapsortMemory:25kB ->SeqScanontest(cost=0.00..429.57rows=29757width=13)(actualtime=0.058..6.363rows=29757loops=1) Planningtime:0.230ms Executiontime:12.976ms (8rows) --查询limit10offset1000 abase=#explainanalyzeselect*from(selectctid,n_int,c_idfromtestorderbyn_intasclimit10offset1000)tdlimit10; QUERYPLAN --------------------------------------------------------------------------------------------------------------------------- Limit(cost=2065.75..2065.88rows=10width=13)(actualtime=27.188..27.192rows=10loops=1) ->Limit(cost=2065.75..2065.78rows=10width=13)(actualtime=27.186..27.188rows=10loops=1) ->Sort(cost=2063.25..2137.64rows=29757width=13)(actualtime=26.940..27.138rows=1010loops=1) SortKey:test.n_int SortMethod:externalmergeDisk:784kB ->SeqScanontest(cost=0.00..429.57rows=29757width=13)(actualtime=0.026..6.374rows=29757loops=1) Planningtime:0.207ms Executiontime:27.718ms (8rows)
可以看到当offset从10增加到1000的时候，使用的内存增加，排序的方法从堆排序变成了归并排序。而归并排序为稳定排序，所以后面的分页不会再有后一页出现前一页数据的情况。
参考资料:PostgreSQL-repeatingrowsfromLIMITOFFSET
参考资料:LIMITandOFFSET
结语
1.关于分页重复数据的问题主要是排序字段不唯一并且执行计划走了快速排序和堆排序导致。
2.当排序有重复字段，但是如果查询是归并排序，便不会存在有重复数据的问题。
3.当用重复字段排序，前面的页重复，随着offset的增大导致work_mem不足以后使用归并排序，就不存在重复的数据了。
4.排序和算法的稳定性有关，当执行计划选择不同的排序算法时，返回的结果不一样。
5.处理重复数据的常见手段就是，排序的时候可以在排序字段d_larq(立案日期)后面加上c_bh(唯一字段)来排序。
orderbyd_larq,c_bh;
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。