order by + limit分页查询结果数据重复不符合预期

allens · 2024 年6 月 5 日 02:55

【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】5.7.25-TiDB-v7.5.0,8.0.11-TiDB-v7.5.0
【复现路径】做过哪些操作出现的问题
创建测试表及写入数据
CREATE TABLE test(idbigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',create_timedatetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '记录创建时间戳',update_timedatetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '记录更新时间戳',task_idbigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '任务id',user_idbigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'C端用户id',statustinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '任务状态0:未知,1:待推送,2:推送完成,3:推送失败,4:已取消',reason varchar(64) NOT NULL DEFAULT '' COMMENT '失败原因', PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */, KEY idx_task (task_id), KEY idx_status (status), UNIQUE KEY uk_task_user (task_id, user_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin AUTO_INCREMENT = 635227 COMMENT = ‘发送任务详情表’

INSERT INTO imt_coupon_test_db.test (id,create_time,update_time,task_id,user_id,status,reason) VALUES
(593431,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:02.950’,71,1,2,‘’),
(593432,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.030’,71,2452000,2,‘’),
(593433,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.165’,71,3178014,2,‘’),
(593434,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.274’,71,488021,2,‘’),
(593435,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.313’,71,2628001,2,‘’),
(593436,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.356’,71,2452002,2,‘’),
(593437,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.402’,71,2452003,2,‘’),
(593438,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.444’,71,2452004,2,‘’),
(593439,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.483’,71,2452005,2,‘’),
(593440,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.526’,71,2452006,2,‘’);
INSERT INTO imt_coupon_test_db.test (id,create_time,update_time,task_id,user_id,status,reason) VALUES
(593441,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.565’,71,3178015,2,‘’),
(593442,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.607’,71,2452001,2,‘’),
(593443,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.647’,71,3178016,2,‘’),
(593444,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.741’,71,3178018,2,‘’),
(593445,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.782’,71,3238000,2,‘’),
(593446,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.823’,71,3238001,2,‘’),
(593447,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.867’,71,3240003,2,‘’),
(593448,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.923’,71,3240004,2,‘’),
(593449,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.968’,71,108026,2,‘’),
(593450,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.990’,71,2,3,‘库存不足’);
【遇到的问题：问题现象及影响】
分页查询出现数据重复，
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】
github issue：order by + limit分页查询结果数据重复不符合预期 · Issue #53818 · pingcap/tidb · GitHub

小龙虾爱大龙虾 · 2024 年6 月 5 日 06:06

SELECT * FROM test WHERE (task_id = 71) ORDER BY create_time DESC LIMIT 0,2

SELECT * FROM test WHERE (task_id = 71) ORDER BY create_time DESC LIMIT 2,2

看起来是没有问题的，返回的数据符合 SQL 逻辑，因为 create_time 是一样的，加上 TiDB 底层算子实现上是并行的，所以导致重复，Mysql 不会重复也是底层实现不一样。
建议您的 SQL 修改为 SELECT * FROM test WHERE (task_id = 71) ORDER BY create_time DESC,id LIMIT 0,2；这样就不会重复了

zhihui · 2024 年6 月 5 日 06:49

小龙虾的回复不错，在order by的时候加上唯一键一起排序就可以了

allens · 2024 年6 月 5 日 07:10

这个我们也试了确实是可以达到效果，主要疑问的是分页为何会重复，底层并行按说也应该在各个节点上处理好分页
应该是算子下推，查询到结果，返回聚合的问题，
另外，通过order by task_id又能正确返回预期结果，看起来应该是真的不同类型有不同处理逻辑？

allens · 2024 年6 月 5 日 07:45

了解，这个我们也试了，但这个重复感觉不应该有，另外，如果分布式id不是顺序自增的话，是不是也会存在问题

Defined2014 · 2024 年6 月 5 日 08:35

这个不是重复，每次 SQL 之间执行的结果是独立的

FutureDB · 2024 年6 月 5 日 15:05

这是因为你的create_time有重复，所以在只按create_time排序，在分布式数据库中结果会不稳定；
以你里面的例子，假设create_time倒序排列，前4条的create_time都是一样的，但是id都是不一样，你可以试下不分页，就单纯地按create_time倒序查这四条数据出来，多查几次，你可能会发现每次结果并不是一模一样的。这在分布式数据库中是符合预期的。

FutureDB · 2024 年6 月 5 日 15:14

这种现象的复现最容易在大表中，且create_time相同的数据又刚好分布在多个不同的TiKV节点上

WinterLiu · 2024 年6 月 6 日 01:43

这么说很有道理

帅的掉渣 · 2024 年6 月 6 日 02:03

sql分发给tikv执行，然后跟进返回的结果集在root重排序。返回顺序不一样，根据重复值排序确实会有这个问题。

帅的掉渣 · 2024 年6 月 6 日 02:05

唯一键，肯定不存在这个问题。

数据库真NB · 2024 年6 月 6 日 02:56

数据结果不一致大概率不是数据库的问题，生成时间和limit不能独立区分数据结果，造成排序不固定的可能比较大，假如实锤这个不一致，那就是bug了，报送TIDB研发吧

yiduoyunQ · 2024 年6 月 6 日 03:05

create_time 不要一样再试一次
当前 create_time 是一样的，返回任何顺序都符合 SQL 语义

小龙虾爱大龙虾 · 2024 年6 月 6 日 05:17

分布式数据库底层算子的执行方式你要了解下，一条 SQL 如果没有加 order by ，多次执行结果顺序可能不一样的，加了order by ,order by 字段有重复值，那多次查询结果集顺序也有可能不一样，你那个 limit 0,2; limit 2,2; 就是多次查询

zhaokede · 2024 年6 月 6 日 06:12

排序的条件要唯一

林夕一指 · 2024 年6 月 6 日 07:16

分页查询 | PingCAP 文档中心
直接用官方的分页查询

小于同学 · 2024 年6 月 6 日 08:26

两次查询根据一个重复的字段排序的结果可能会不一样

allens · 2024 年6 月 7 日 09:24

感谢各位大佬的回复，受益不少
目前方案已通过order by create_time,id desc 解决，并符合预期
查看了TiKV的代码，这个注释似乎也能解释此场景

另外，在OceanBase、StarRocks、MySQL NDB上都做了验证，分页一样出现数据重复。
不过从业务角度，特别是传统单实例场景迁移过来，需要一定接受和改造成本

Kongdom · 2024 年6 月 8 日 14:53

这么看在写sql的时候，还是要开发规范，分页查询时按唯一键排序

system · 2024 年8 月 7 日 14:53

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。