业务 SQL 优化问题

[sockball07]

原有业务上有一 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]

[joooooker21]

查出来在代码里循环处理

[MidGap]

几万还慢。。。

[sockball07]

#1 @joooooker21 当初我的第一反应也是这样 整个文件下来所有操作全是用 SQL 在 UPDATE 而现在只要求优化这一部分（能优化的话） 似乎起初是因为数据量太大不好一次性查出来再操作（怕内存炸掉）
#2 @MidGap 一组条件就调个百来次... 然后调用的地方还有 10 多组... 够多了吧

[moliliang]

可以设置一个很大的间隙，例如 a-b-c 的 position 是 10000 ，20000 ，30000 。
如果要将 c 调到 a-b 之间，那么只要知道 a-b 的位置，然后 c = 10000 + 20000 / 2
不知道是不是你的需求。。

[zxxufo008]

感觉最好能优化成只 update 一次就行了.
改成在程序里计算 sort 好了
你举得例子里就是把 sort>=1 的加 2,应该把 sort= sort+1 这里改成传参,个人看法啊

[zlowly]

不知道这批 update 的事务性如何？
会不会存在多次 update 其实是改同一条记录，sort 实际上会递增多次，这样你试图只通过一次 update 的实现可能就不符合实际业务。
另外不同数据库对这种批量 update 其实也有不同优化提速方式，例如 oracle 里，你可以用存储过程把需要 update 的记录主键先查询放到数组里，然后再用 for all 批量 update ，也能提高性能。不清楚 mysql 、pg 那些有没类似用法。

[sockball07]

#4 抱歉有点没看懂 这边实质上就是不停的更新 sort 只是多次执行之后可能会递增多次（像 6 楼说的那样）

#5 如果能在程序里计算也不会来问啦 毕竟程序计算前提就是取出数据

#6 @zlowly 就是会递增多次...试图通过一次 update 也只是尝试 实在不行就没办法了

[feigle]

本来一个 sql 就能处理吧，为啥要多次传参调用同一个 sql ？

[zheng96]

随手写的，性能不保证，可以试试
UPDATE
table, (select id,count(1) as cnt from (
select * from table WHERE xxxx = xxxx and sort > ? (1)
union all
select * from table WHERE xxxx = xxxx and sort > ? (2)
...
) t group by id) t2
SET
sort = sort + cnt
WHERE
table.id = t2.id

[nuanshen]

好奇怪的业务，前一次的 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;

[sockball07]

#8 @feigle 10 楼解释了 因为前一次 update 可能会满足下一次的条件...

#9 @zheng96 应该不对 第一次大于 1 的 更新以后是满足下一组条件的...

#10 @nuanshen 前人的成果😅 也不知道是怎么变成这样的 开始叫我优化的时候没仔细看想着这还不简单 然而还隐藏了个下次递增... 能不能保证为连续数 这个得去验证一下 谢谢

[onhao]

@sockball07 楼主 可以把你内容的 sql 包进 一个 [自定义函数]( https://wuhao.pw/archives/277/)，select 满足的条件 来执行这个 自定义函数 ,可以参考下。

[RangerWolf]

sort 列加一个索引？

[fuchaofather]

sort 上有索引吗? 有的话尝试去掉索引或者关闭 chage buffer

[zheng96]

@sockball07

[zheng96]

@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 ;

[akira]

大家对于慢的理解可能不大一样。 能说说现在有多慢么。
按照我的理解的话，几万这个量级，这个 sql 应该是在几百毫秒以内能完成的，不至于需要优化的。

[aliveyang]

几万的数据查出来再处理应该可以吧，分批处理也行啊

[privatetan]

用存储过程试一试？

[MidGap]

@sockball07 我觉得还是内存里搞完？几万条真占不了多少内存。。。

[Fri]

试试把 where 条件里的字段加上联合索引

[siweipancc]

套娃更新语句……作内存乐观锁更新吧

[sockball07]

#16 @zheng96 和我优化另外 2 个的思路很像（一个也是 UNION ALL 固定数据，一个也是使用多个临时变量）像是一个结合体 应该是可行的 （还是很像一种循环

要指出的一个错误是 你的 @b 在没有初始化的情况下始终为 NULL 而 NULL 不能与任何类型作比较 于是执行 IF(@b != t.id, @a := 0, 0)的时候永远会是 FALSE 的逻辑

另外要提醒的是 IF(@b != t.id, @a := 0, 0) 为 TRUE 的逻辑中是一个赋值表达式 最终返回为 NULL

[zheng96]

@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 ;