TiDB V4.0中如何高速批量更新数据,目前2000条数据需要20秒

【 TiDB 使用环境】生产环境

【概述】 场景 + 问题概述
TiDB V4.0中通过update命令更新数据,一个事务中有2000多条更新,需要耗时20s,如何优化

==========
客户的生产环境TiDB数据库通过大事务来批量更新数据。一个事务中有2000条更新语句,耗时20s左右。客户说执行时间太慢,以前在其他数据库上更新5000条数据花费的时间是3s左右。我尝试执行了其中的一条更新,耗时11ms。他这个语句没法合并,只能一条一条的执行(因为条件不一样,也没有共通性)不知道有什么其他的优化方案嘛

更新的事务如下:

Begin;
update tb1 set c1=,c2=,c3=, … where id=** and c1>* and …;
update tb1 set c1=,c2=,c3=, … where id=** and c1>* and …;
update tb1 set c1=,c2=,c3=, … where id=** and c1>* and …;
update tb1 set c1=,c2=,c3=, … where id=** and c1>* and …;

update tb1 set c1=,c2=,c3=, … where id=** and c1>* and …;
update tb1 set c1=,c2=,c3=, … where id=** and c1>* and …;
update tb1 set c1=,c2=,c3=, … where id=** and c1>* and …;
END;

1 个赞

where的条件列是否可以加唯一索引。加上的话会快点

3 个赞

多谢回复,目前where条件对应的列已经有索引。目前执行其中的一条update只需要10毫秒左右,因为执行2000多条相当于执行了2000次的update,所有导致总的时间长。就是不知道有没有什么办法可以并行执行2000行的更新。

1 个赞

我看它是多列过滤条件,用复合索引,效果会更好。放到一个事务里这么多条SQL,不太合理。

谢谢回复,已经有复合索引了。目前是放在一个事务中去处理的,我建议开发通过多线程来并行,每个事务中放5-10个更新。但是开发的人说只能放在一个事务中,无语呀。不知道是否还有其他方案。

可以参考一下,做一下事务的调整。
https://docs.pingcap.com/zh/tidb/v5.2/sql-faq#一个事务中的语句数量最大是多少

谢谢回复,所有更新放到一个事务中它是顺序执行的。现在就想着有什么其他的版本可以快速更新5000条。

1 个赞

UPDATE tb1, ( select id , c1 , c2,c3 from tb1) target
SET tbl1.c1= ,c2= ,c3= …
where tb1.id = target.id
and target.c1>* and …

把待更新的数据存为一个临时表,然后这样批量更新?

这样测试过? 效果怎么样呀?

我的每一条更新语句的条件不一致,所以不能用一条语句将所有的更新完成,只能一条一条更新

如果每一条更新语句中,c1,c2,c3列的值更新都相同的情况下,可以考虑用如下的语句:
update tb1 set tb.c1=,c2=,c3=
where (tb1的主键字段列表) in (
select tb1的主键字段列表 from
tb1 where (id=** and c1>* and …) or()/这里就是把各条语句的条件通过or连接起来/

如果你用多线程,复杂度就会提升,你要考虑一个线程执行失败后,整个事务该如何处理?有啥补偿方案?否则很容易就造成数据不一致。

最后开发还是进行了事务拆分,将批弄小一点就可以。

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