6
业务 SQL 优化问题
source link: https://www.v2ex.com/t/823263
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
原有业务上有一 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]
11 条回复 • 2021-12-20 13:48:55 +08:00
1
sockball07 2 小时 41 分钟前
#1 @joooooker21 当初我的第一反应也是这样 整个文件下来所有操作全是用 SQL 在 UPDATE 而现在只要求优化这一部分（能优化的话） 似乎起初是因为数据量太大不好一次性查出来再操作（怕内存炸掉）
#2 @MidGap 一组条件就调个百来次... 然后调用的地方还有 10 多组... 够多了吧
#2 @MidGap 一组条件就调个百来次... 然后调用的地方还有 10 多组... 够多了吧
moliliang 2 小时 36 分钟前
可以设置一个很大的间隙，例如 a-b-c 的 position 是 10000 ，20000 ，30000 。
如果要将 c 调到 a-b 之间，那么只要知道 a-b 的位置，然后 c = 10000 + 20000 / 2
不知道是不是你的需求。。
如果要将 c 调到 a-b 之间，那么只要知道 a-b 的位置，然后 c = 10000 + 20000 / 2
不知道是不是你的需求。。
zxxufo008 2 小时 28 分钟前
感觉最好能优化成只 update 一次就行了.
改成在程序里计算 sort 好了
你举得例子里就是把 sort>=1 的加 2,应该把 sort= sort+1 这里改成传参,个人看法啊
改成在程序里计算 sort 好了
你举得例子里就是把 sort>=1 的加 2,应该把 sort= sort+1 这里改成传参,个人看法啊
zlowly 2 小时 11 分钟前
不知道这批 update 的事务性如何？
会不会存在多次 update 其实是改同一条记录，sort 实际上会递增多次，这样你试图只通过一次 update 的实现可能就不符合实际业务。
另外不同数据库对这种批量 update 其实也有不同优化提速方式，例如 oracle 里，你可以用存储过程把需要 update 的记录主键先查询放到数组里，然后再用 for all 批量 update ，也能提高性能。不清楚 mysql 、pg 那些有没类似用法。
会不会存在多次 update 其实是改同一条记录，sort 实际上会递增多次，这样你试图只通过一次 update 的实现可能就不符合实际业务。
另外不同数据库对这种批量 update 其实也有不同优化提速方式，例如 oracle 里，你可以用存储过程把需要 update 的记录主键先查询放到数组里，然后再用 for all 批量 update ，也能提高性能。不清楚 mysql 、pg 那些有没类似用法。
sockball07 1 小时 43 分钟前
#4 抱歉有点没看懂 这边实质上就是不停的更新 sort 只是多次执行之后可能会递增多次（像 6 楼说的那样）
#5 如果能在程序里计算也不会来问啦 毕竟程序计算前提就是取出数据
#6 @zlowly 就是会递增多次...试图通过一次 update 也只是尝试 实在不行就没办法了
#5 如果能在程序里计算也不会来问啦 毕竟程序计算前提就是取出数据
#6 @zlowly 就是会递增多次...试图通过一次 update 也只是尝试 实在不行就没办法了
zheng96 1 小时 35 分钟前
nuanshen 1 小时 14 分钟前
好奇怪的业务，前一次的 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;
但如果输入一组 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 4 分钟前
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK