业务 SQL 优化问题

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

This topic created in 1629 days ago, the information mentioned may be changed or developed.

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

24 replies • 2021-12-21 12:20:53 +08:00

joooooker21

Dec 20, 2021

查出来在代码里循环处理

MidGap

Dec 20, 2021

几万还慢。。。

sockball07

Dec 20, 2021

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

moliliang

Dec 20, 2021

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

zxxufo008

Dec 20, 2021

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

zlowly

Dec 20, 2021

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

sockball07

Dec 20, 2021

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

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

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

feigle

Dec 20, 2021 via Android

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

zheng96

Dec 20, 2021

随手写的，性能不保证，可以试试
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

Dec 20, 2021

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

Dec 20, 2021

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

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

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

onhao

Dec 20, 2021

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

RangerWolf

Dec 20, 2021

sort 列加一个索引？

fuchaofather

Dec 20, 2021

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

zheng96

Dec 20, 2021

@sockball07

zheng96

Dec 20, 2021

@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

Dec 20, 2021

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

aliveyang

Dec 20, 2021

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

privatetan

Dec 20, 2021

用存储过程试一试？

MidGap

Dec 20, 2021

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

Fri

Dec 20, 2021

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

siweipancc

Dec 21, 2021 via iPhone

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

sockball07

Dec 21, 2021

#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

Dec 21, 2021

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