分页查询出现数据重复或丢失,你遇到过吗?

数据库 其他数据库
如果是 app 端的查询,不建议设计多字段排序,因为在多字段排序的环境下,服务端在进行多条件的过滤查询时,可能会把有效的数据给过滤掉,如果无法避开,尽量将多个排序字段合并到一个排序字段上,保证数据的查询符合预期。

​一、问题背景

最近部分销售人员反馈在 APP 上查询自己名下客户订单数据时,当往下拉取数据的时候,列表上经常出现重复的订单数据,经过排查,后端代码是通过如下方式来实现数据的分页查询的。

limit offset, size order by create_time desc

经过细致的分析,这种排序方式,在 app 端分页查询的时候,确实存在问题。

详细的分析过程如下!

二、原因分析

首先我们初始化一张表,用于模拟订单表查询。

CREATE TABLE `tb_order` (
`order_id` bigint(11) unsigned NOT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

然后初始化 5 条数据进去,方便数据分析

INSERT INTO `tb_order` (`order_id`, `create_time`) VALUES (1, '2023-03-03 12:00:01');
INSERT INTO `tb_order` (`order_id`, `create_time`) VALUES (2, '2023-03-03 12:00:02');
INSERT INTO `tb_order` (`order_id`, `create_time`) VALUES (3, '2023-03-03 12:00:03');
INSERT INTO `tb_order` (`order_id`, `create_time`) VALUES (4, '2023-03-03 12:00:04');
INSERT INTO `tb_order` (`order_id`, `create_time`) VALUES (5, '2023-03-03 12:00:05');

假设我们每次只查询 2 条数据,并且按照时间倒序来查询,结果如下:

-- 发起第一页查询
select * from tb_order order by create_time desc limit 0,2;
-- 第一页查询结果
|order_id | create_time |
|5 | 2023-03-03 12:00:05|
|4 | 2023-03-03 12:00:04|

-- 发起第二页查询
select * from tb_order order by create_time desc limit 2,2;
-- 第二页查询结果
|order_id | create_time |
|3 | 2023-03-03 12:00:03|
|2 | 2023-03-03 12:00:02|

当订单数据没有发生变动的时候,这种查询方式是不会造成出现重复的数据问题。

但是当订单数据发生了变动,比如在查询的时候,突然新增了订单数据,此时的查询结果就完全不一样了。

还是以上面为例,假设在第一次查询的时候,突然新增了一条数据,看看结果如何。

-- 发起第一页查询
select * from tb_order order by create_time desc limit 0,2;
-- 第一页查询结果
|order_id | create_time |
|5 | 2023-03-03 12:00:05|
|4 | 2023-03-03 12:00:04|

-- 新增一条订单数据
INSERT INTO `tb_order` (`order_id`, `create_time`) VALUES (6, '2023-03-03 12:00:06');

-- 发起第二页查询
select * from tb_order order by create_time desc limit 2,2;
-- 第二页查询结果
|order_id | create_time |
|4 | 2023-03-03 12:00:04|
|3 | 2023-03-03 12:00:03|

可以很明显的发现,订单ID=4的数据,出现在页面上两次,正常情况下只有一次!

图片

上面说到的是新增一条数据,假设删除某条数据,看看结果如何。

-- 发起第一页查询
select * from tb_order order by create_time desc limit 0,2;
-- 第一页查询结果
|order_id | create_time |
|5 | 2023-03-03 12:00:05|
|4 | 2023-03-03 12:00:04|

-- 删除一条订单数据
delete from tb_order where order_id = 4;

-- 发起第二页查询
select * from tb_order order by create_time desc limit 2,2;
-- 第二页查询结果
|order_id | create_time |
|2 | 2023-03-03 12:00:02|
|1 | 2023-03-03 12:00:01|

可以很明显的发现,删除订单ID=4的数据之后,页面查询结果直接到订单ID=2了,直接跳过订单ID=3了,也就是说订单ID=3的数据展示,丢失了!

图片

总结下来,结论如下!

  • 当新增某条数据之后,通过常规的分页查询,列表会出现数据重复的现象;
  • 当删除某条数据之后,通过常规的分页查询,列表会出现数据丢失的现象;

那怎么解决以上的问题呢?办法如下!

三、解决方案

针对上面所说的分页查询方式,我们需要做一些调整,调整办法如下:

  • 第一步:当查询出当页的数据之后,记录下本次拉取的最后一条数据的排序字段值;当发起下一页数据查询的时候,带上这个参数,服务端通过这个参数做过滤条件
  • 第二步:排序字段值不能出现重复

以上面的新增为例,详细的实践过程如下:

-- 发起第一页查询
select * from tb_order order by create_time desc limit 0,2;
-- 第一页查询结果
|order_id | create_time |
|5 | 2023-03-03 12:00:05|
|4 | 2023-03-03 12:00:04|

-- 新增一条订单数据
INSERT INTO `tb_order` (`order_id`, `create_time`) VALUES (6, '2023-03-03 12:00:06');

-- 发起第二页查询,带上第一页查询的最后一条数据的排序字段值
select * from tb_order where create_time < '2023-03-03 12:00:04' order by create_time desc limit 0,2;
-- 第二页查询结果
|order_id | create_time |
|3 | 2023-03-03 12:00:03|
|2 | 2023-03-03 12:00:02|

此时的查询结果正常,符合预期效果!

同样的,以上面的删除为例,详细的实践过程如下:

-- 发起第一页查询
select * from tb_order order by create_time desc limit 0,2;
-- 第一页查询结果
|order_id | create_time |
|5 | 2023-03-03 12:00:05|
|4 | 2023-03-03 12:00:04|

-- 删除一条订单数据
delete from tb_order where order_id = 4;

-- 发起第二页查询
select * from tb_order where create_time < '2023-03-03 12:00:04' order by create_time desc limit 0,2;
-- 第二页查询结果
|order_id | create_time |
|3 | 2023-03-03 12:00:03|
|2 | 2023-03-03 12:00:02|

查询结果与预期一致,正常!

四、深入思考

  • 选择的排序字段值出现了重复,怎么办?

在上面我们提到了,排序字段值不能出现重复的要求,但是现实的情况是,如果以订单的创建时间来排序,当同一秒多次下单的时候大概率会出现重复,这个时候只能在订单表里面新增一个排序字段,设置全局唯一索引,内容是以时间为基础来生成,比如雪花算法,或者自己写一个基于时间全局自增的算法,确保全局唯一,最重要的是值的长度必须固定,订单主键 ID 的生成规则推荐采用此方式,利用主键 ID 来排序效率查询会非常高!

  • 当出现多个排序字段时,如何处理?

如果是 app 端的查询,不建议设计多字段排序,因为在多字段排序的环境下,服务端在进行多条件的过滤查询时,可能会把有效的数据给过滤掉,如果无法避开,尽量将多个排序字段合并到一个排序字段上,保证数据的查询符合预期。

五、小结

本文主要围绕 app 端分页查询出现数据重复或丢失的问题,进行一次复盘总结,如果有描述不对的地方,欢迎网友留言指出!

六、参考

1、知乎 - HQGDD - 分页出现数据重复或丢失的问题,一文搞定!

责任编辑:武晓燕 来源: 鸭血粉丝Tang
相关推荐

2020-11-08 14:38:35

JavaScript代码开发

2009-07-23 15:07:32

2011-04-26 09:22:05

SQLite

2020-10-12 09:49:14

C++ 开发代码

2021-12-26 14:32:11

缓存数据库数据

2018-04-25 10:57:00

AIX报错vios

2021-08-29 18:36:17

MySQL技术面试题

2020-09-24 10:49:09

iOSiPadOSBug

2019-09-15 22:36:26

数据库查询日志检索

2021-12-30 09:32:04

缓存数据库数据

2022-10-20 18:00:59

OCP模型参数

2019-10-28 14:07:29

研发管理技术

2021-04-04 22:31:26

白帽子厂商漏洞

2017-07-14 09:29:45

AndroidWebview

2018-03-26 09:39:06

大数据IT互联网

2021-02-19 11:01:46

异步竞态接口异步

2020-04-26 14:40:19

戴尔

2021-05-27 09:27:35

开发技能缓存

2017-09-07 15:23:21

神经网络数据网络

2020-10-14 10:41:24

安全漏洞数据
点赞
收藏

51CTO技术栈公众号