[toc]

分页重复数据

背景

最近测试发现APP首页资讯拉取了重复的数据，偶尔还会少数据，和后台的数据对不上，首页获取资讯接口是个按创建时间倒排的分页接口，分页实现方法：limit offset, size order by create_time desc。

现象及产生原因

数据初始化

# 模拟资讯表
CREATE TABLE `content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gmt_create` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
# 数据初始化 
truncate table content;
INSERT INTO `content`(`id`) VALUES (1);
INSERT INTO `content`(`id`) VALUES (2);
INSERT INTO `content`(`id`) VALUES (3);
INSERT INTO `content`(`id`) VALUES (4);
INSERT INTO `content`(`id`) VALUES (5);
INSERT INTO `content`(`id`) VALUES (6);
INSERT INTO `content`(`id`) VALUES (7);
INSERT INTO `content`(`id`) VALUES (8);
INSERT INTO `content`(`id`) VALUES (9);
INSERT INTO `content`(`id`) VALUES (10);

新增操作，导致数据重复

img 查询第一页完后，获取到的数据是6~10，随后新增11和12两条数据，进行查询第二页，获取到的数据是3~7，此时6和7被重复拉取了。

# 第一页
SELECT * FROM content ORDER BY gmt_create DESC limit 0,5;
# 查询结果
| 10 | 2021-07-21 18:04:44.244 |
| 9 |  2021-07-21 18:04:44.214|
| 8 |  2021-07-21 18:04:44.185|
| 7 |  2021-07-21 18:04:44.153|
| 6 |  2021-07-21 18:04:44.114|
# 新增两条数据
INSERT INTO `content`(`id`) VALUES (11);
INSERT INTO `content`(`id`) VALUES (12);
# 第二页
SELECT * FROM content ORDER BY gmt_create DESC limit 5,5;
# 查询结果
|7| 2021-07-21 18:17:40.474
|6| 2021-07-21 18:17:40.456
|5| 2021-07-21 18:17:40.441
|4| 2021-07-21 18:17:40.423
|3| 2021-07-21 18:17:40.399

# 重复数据：6和7

删除操作，导致数据部分丢失

img 查询第一页完后，获取到的数据是6~10，随后删除6这条数据，进行查询第二页，获取到的数据是0~4，此时5既没有在第一页，也没在第二页，丢失了。

# 第一页
SELECT * FROM content ORDER BY gmt_create DESC limit 0,5;
# 查询结果
| 10 | 2021-07-21 18:04:44.244 |
| 9 |  2021-07-21 18:04:44.214|
| 8 |  2021-07-21 18:04:44.185|
| 7 |  2021-07-21 18:04:44.153|
| 6 |  2021-07-21 18:04:44.114|
# 删除id=6的数据
DELETE FROM content WHERE id = 6;
# 第二页
SELECT * FROM content ORDER BY gmt_create DESC limit 5,5;
# 查询结果
|4|    2021-07-21 18:23:27.811
|3|    2021-07-21 18:23:27.797
|2|    2021-07-21 18:23:27.771
|1|    2021-07-21 18:23:27.752
# 丢失数据：5

根本原因

  • 第二页的数据完全依赖第一页的数据，第一页的数据变化影响第二页的数据；主要还是每次分页查询都是从起点分页，分页之前的数据变化都会影响接下来的分页。

解决方案

  • 方案：查询出当前页数据后，记录下本次拉取位置(curPosition)，当页数据和curPosition一并下发下去；下次请求时，需要把上次下发的curPosition传给服务端，服务端从curPosition开始分页。
  • 拉取位置：最好是当前排序字段。
  • 前提条件：所选字段的数据不能重复。

验证

拉取位置选用的是gmt_create字段，防止数据重复时间精确到毫秒。

验证下 新增操作，导致数据重复

# 第一页
SELECT * FROM content ORDER BY gmt_create DESC limit 0,5;
# 查询结果
|10    | 2021-07-21 18:59:53.035
|9    | 2021-07-21 18:59:53.013
|8    | 2021-07-21 18:59:52.995
|7    | 2021-07-21 18:59:52.980
|6    | 2021-07-21 18:59:52.964
# 新增两条数据
INSERT INTO `content`(`id`) VALUES (11);
INSERT INTO `content`(`id`) VALUES (12);
SELECT * FROM content WHERE gmt_create < '2021-07-21 18:59:52.964' ORDER BY gmt_create DESC limit 0,5;
# 查询结果
|5|    2021-07-21 18:59:52.950
|4|    2021-07-21 18:59:52.931
|3|    2021-07-21 18:59:52.907
|2|    2021-07-21 18:59:52.892
|1|    2021-07-21 18:59:52.878

# 查询正常

验证下 删除操作，导致数据部分丢失

# 第一页
SELECT * FROM content ORDER BY gmt_create DESC limit 0,5;
# 查询结果
|10    | 2021-07-21 19:08:05.488
|9    | 2021-07-21 19:08:05.476
|8    | 2021-07-21 19:08:05.461
|7    | 2021-07-21 19:08:05.449
|6    | 2021-07-21 19:08:05.432

# 删除id=6的数据
DELETE FROM content WHERE id = 6;

# 第二页
SELECT * FROM content WHERE gmt_create < '2021-07-21 19:08:05.432' ORDER BY gmt_create DESC limit 0,5;
# 查询结果
|5|    2021-07-21 19:08:05.417
|4|    2021-07-21 19:08:05.403
|3|    2021-07-21 19:08:05.384
|2|    2021-07-21 19:08:05.369
|1|    2021-07-21 19:08:05.355
# 查询正常

深入思考

  1. 选择的拉取位置字段里面数据重复怎么处理？ 新加字段（排序字段值+随机数）
  2. limit 分页查询优化 是不是 有点类似？
  3. 当有多个排序字段时，如何处理？

results matching ""

    No results matching ""