V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
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
sockball07
V2EX  ›  MySQL

业务 SQL 优化问题

  •  
  •   sockball07 · 159 天前 · 2804 次点击
    这是一个创建于 159 天前的主题,其中的信息可能已经有所发展或是发生改变。

    原有业务上有一 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 条回复    2021-12-21 12:20:53 +08:00
    joooooker21
        1
    joooooker21  
       159 天前
    查出来在代码里循环处理
    MidGap
        2
    MidGap  
       159 天前   ❤️ 1
    几万还慢。。。
    sockball07
        3
    sockball07  
    OP
       159 天前
    #1 @joooooker21 当初我的第一反应也是这样 整个文件下来所有操作全是用 SQL 在 UPDATE 而现在只要求优化这一部分(能优化的话) 似乎起初是因为数据量太大不好一次性查出来再操作(怕内存炸掉)
    #2 @MidGap 一组条件就调个百来次... 然后调用的地方还有 10 多组... 够多了吧
    moliliang
        4
    moliliang  
       159 天前
    可以设置一个很大的间隙,例如 a-b-c 的 position 是 10000 ,20000 ,30000 。
    如果要将 c 调到 a-b 之间,那么只要知道 a-b 的位置,然后 c = 10000 + 20000 / 2
    不知道是不是你的需求。。
    zxxufo008
        5
    zxxufo008  
       159 天前
    感觉最好能优化成只 update 一次就行了.
    改成在程序里计算 sort 好了
    你举得例子里就是把 sort>=1 的加 2,应该把 sort= sort+1 这里改成传参,个人看法啊
    zlowly
        6
    zlowly  
       159 天前
    不知道这批 update 的事务性如何?
    会不会存在多次 update 其实是改同一条记录,sort 实际上会递增多次,这样你试图只通过一次 update 的实现可能就不符合实际业务。
    另外不同数据库对这种批量 update 其实也有不同优化提速方式,例如 oracle 里,你可以用存储过程把需要 update 的记录主键先查询放到数组里,然后再用 for all 批量 update ,也能提高性能。不清楚 mysql 、pg 那些有没类似用法。
    sockball07
        7
    sockball07  
    OP
       159 天前
    #4 抱歉有点没看懂 这边实质上就是不停的更新 sort 只是多次执行之后可能会递增多次(像 6 楼说的那样)

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

    #6 @zlowly 就是会递增多次...试图通过一次 update 也只是尝试 实在不行就没办法了
    feigle
        8
    feigle  
       159 天前 via Android
    本来一个 sql 就能处理吧,为啥要多次传参调用同一个 sql ?
    zheng96
        9
    zheng96  
       159 天前
    随手写的,性能不保证,可以试试
    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
        10
    nuanshen  
       159 天前
    好奇怪的业务,前一次的 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
        11
    sockball07  
    OP
       159 天前
    #8 @feigle 10 楼解释了 因为前一次 update 可能会满足下一次的条件...

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

    #10 @nuanshen 前人的成果😅 也不知道是怎么变成这样的 开始叫我优化的时候没仔细看想着这还不简单 然而还隐藏了个下次递增... 能不能保证为连续数 这个得去验证一下 谢谢
    onhao
        12
    onhao  
       159 天前
    @sockball07 楼主 可以把你内容的 sql 包进 一个 [自定义函数]( https://wuhao.pw/archives/277/),select 满足的条件 来执行这个 自定义函数 ,可以参考下。
    RangerWolf
        13
    RangerWolf  
       159 天前
    sort 列加一个索引?
    fuchaofather
        14
    fuchaofather  
       159 天前
    sort 上有索引吗? 有的话尝试去掉索引或者关闭 chage buffer
    zheng96
        15
    zheng96  
       159 天前
    zheng96
        16
    zheng96  
       159 天前   ❤️ 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 ;
    akira
        17
    akira  
       159 天前
    大家对于慢的理解可能不大一样。 能说说现在有多慢么。
    按照我的理解的话,几万这个量级,这个 sql 应该是在几百毫秒以内能完成的,不至于需要优化的。
    aliveyang
        18
    aliveyang  
       159 天前
    几万的数据查出来再处理应该可以吧,分批处理也行啊
    privatetan
        19
    privatetan  
       159 天前
    用存储过程试一试?
    MidGap
        20
    MidGap  
       158 天前
    @sockball07 我觉得还是内存里搞完?几万条真占不了多少内存。。。
    Fri
        21
    Fri  
       158 天前
    试试把 where 条件里的字段加上联合索引
    siweipancc
        22
    siweipancc  
       158 天前 via iPhone
    套娃更新语句……作内存乐观锁更新吧
    sockball07
        23
    sockball07  
    OP
       158 天前
    #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
        24
    zheng96  
       158 天前
    @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 ;
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2508 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 20ms · UTC 10:45 · PVG 18:45 · LAX 03:45 · JFK 06:45
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.