原有业务上有一 SQL 大概是这样
UPDATE
table
SET
sort = sort + 1
WHERE
# 一些固定条件
xxxx = xxxx
AND
sort >= ?
sort 为整形
问题在于现在业务是一组 sort (已升序排列)条件多次调用该 SQL ,由于表中数据有个几万,多次调用就会很慢,问是否有可能使用一条 SQL 完成这批更新
举例: 如原表中数据 sort 值为[0, 1, 2, 6],输入一组 sort 条件为[1, 2],则表中数据 sort 值更新为[0, 3, 4, 8]
1
joooooker21 2021-12-20 10:52:33 +08:00
查出来在代码里循环处理
|
2
MidGap 2021-12-20 10:58:35 +08:00 1
几万还慢。。。
|
3
sockball07 OP #1 @joooooker21 当初我的第一反应也是这样 整个文件下来所有操作全是用 SQL 在 UPDATE 而现在只要求优化这一部分(能优化的话) 似乎起初是因为数据量太大不好一次性查出来再操作(怕内存炸掉)
#2 @MidGap 一组条件就调个百来次... 然后调用的地方还有 10 多组... 够多了吧 |
4
moliliang 2021-12-20 11:16:54 +08:00
可以设置一个很大的间隙,例如 a-b-c 的 position 是 10000 ,20000 ,30000 。
如果要将 c 调到 a-b 之间,那么只要知道 a-b 的位置,然后 c = 10000 + 20000 / 2 不知道是不是你的需求。。 |
5
zxxufo008 2021-12-20 11:24:33 +08:00
感觉最好能优化成只 update 一次就行了.
改成在程序里计算 sort 好了 你举得例子里就是把 sort>=1 的加 2,应该把 sort= sort+1 这里改成传参,个人看法啊 |
6
zlowly 2021-12-20 11:41:33 +08:00
不知道这批 update 的事务性如何?
会不会存在多次 update 其实是改同一条记录,sort 实际上会递增多次,这样你试图只通过一次 update 的实现可能就不符合实际业务。 另外不同数据库对这种批量 update 其实也有不同优化提速方式,例如 oracle 里,你可以用存储过程把需要 update 的记录主键先查询放到数组里,然后再用 for all 批量 update ,也能提高性能。不清楚 mysql 、pg 那些有没类似用法。 |
7
sockball07 OP #4 抱歉有点没看懂 这边实质上就是不停的更新 sort 只是多次执行之后可能会递增多次(像 6 楼说的那样)
#5 如果能在程序里计算也不会来问啦 毕竟程序计算前提就是取出数据 #6 @zlowly 就是会递增多次...试图通过一次 update 也只是尝试 实在不行就没办法了 |
8
feigle 2021-12-20 12:12:52 +08:00 via Android
本来一个 sql 就能处理吧,为啥要多次传参调用同一个 sql ?
|
9
zheng96 2021-12-20 12:17:32 +08:00
|
10
nuanshen 2021-12-20 12:38:35 +08:00
好奇怪的业务,前一次的 update 的结果可能会满足下一次 update 的条件,也就是一条记录可能会被更新 n 次;
但如果输入一组 sort 条件为连续数的话,倒是可以简化成一次 update ; 比如输入[1,2,3],可转换成 update table set sort = sort+3 where sort >= 1; 输入 [3,4,5,6],可转换成 update table set sort = sort+4 where sort >= 3; |
11
sockball07 OP |
12
onhao 2021-12-20 14:39:49 +08:00
@sockball07 楼主 可以把你内容的 sql 包进 一个 [自定义函数]( https://wuhao.pw/archives/277/),select 满足的条件 来执行这个 自定义函数 ,可以参考下。
|
13
RangerWolf 2021-12-20 15:10:00 +08:00
sort 列加一个索引?
|
14
fuchaofather 2021-12-20 15:42:15 +08:00
sort 上有索引吗? 有的话尝试去掉索引或者关闭 chage buffer
|
15
zheng96 2021-12-20 16:03:57 +08:00
|
16
zheng96 2021-12-20 16:05:01 +08:00 1
@sockball07
try again: select t.id,max(t.final) from ( select t.id, t.sort, if(@b != t.id, @a := 0, 0), if(@b != t.id, @b := t.id, 0), if(t.sort + @a >= t.val, @a := @a + 1, 0), t.sort + @a as final from ( select table.id, table.sort, tmp.val from table join (select 1 as val union all select 2) tmp order by table.id, tmp.val) t ) t group by t.id ; |
17
akira 2021-12-20 16:26:11 +08:00
大家对于慢的理解可能不大一样。 能说说现在有多慢么。
按照我的理解的话,几万这个量级,这个 sql 应该是在几百毫秒以内能完成的,不至于需要优化的。 |
18
aliveyang 2021-12-20 17:10:52 +08:00
几万的数据查出来再处理应该可以吧,分批处理也行啊
|
19
privatetan 2021-12-20 17:19:20 +08:00
用存储过程试一试?
|
20
MidGap 2021-12-20 20:31:43 +08:00
@sockball07 我觉得还是内存里搞完?几万条真占不了多少内存。。。
|
21
Fri 2021-12-20 20:41:20 +08:00
试试把 where 条件里的字段加上联合索引
|
22
siweipancc 2021-12-21 09:15:33 +08:00 via iPhone
套娃更新语句……作内存乐观锁更新吧
|
23
sockball07 OP |
24
zheng96 2021-12-21 12:20:53 +08:00
@sockball07 是的有错误,我昨天没发现出来是因为当前连接的 session 的用户变量已经被我赋过值了。
IF(@b != t.id, @a := 0, 0) 这个语句只是为了赋值,返回值没有意义 下面这个 sql 断掉 session 在连也没有问题的: select t.id,max(t.final) from ( select t1.id, t1.sort, if( @b!= t1.id, @a := 0, 0), if( @b!= t1.id, @b := t1.id, 0), if(t1.sort + @a >= t1.val, @a := @a + 1, 0), t1.sort + @a as final from ( select sort_test.id, sort_test.sort, tmp.val from sort_test join (select 1 as val union all select 2) tmp order by sort_test.id, tmp.val) t1,(select @a:=0,@b:=-1) t2 ) t group by t.id ; |