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
brader
V2EX  ›  MySQL

请教个 mysql 更新问题

  •  
  •   brader · 2022-09-29 17:27:25 +08:00 · 1894 次点击
    这是一个创建于 786 天前的主题,其中的信息可能已经有所发展或是发生改变。

    有这样一条语句 UPDATE table SET onsale=0 WHERE uid=116980

    uid 是有索引的,假如 uid=116980 的数据一共有 1 万条,执行这条更新语句大约需要 20s ,想问下,更新期间,mysql 是会一次锁住这 1 万条数据,还是每次短时间锁一条啊?

    如果他会一次锁住 1 万条的话,我要怎么避免这种长时间的锁定

    14 条回复    2024-06-11 15:32:08 +08:00
    rqxiao
        1
    rqxiao  
       2022-09-29 17:29:30 +08:00
    一次锁住这 1 万条数据
    nekolr
        2
    nekolr  
       2022-09-29 17:31:21 +08:00
    缩小锁定的范围,拆开更新?
    lmshl
        3
    lmshl  
       2022-09-29 17:53:55 +08:00   ❤️ 4
    1. 锁 1 万毋庸置疑
    2. UPDATE table SET onsale=0 WHERE pk IN (SELECT pk FROM table where uid=116980 AND onsale <> 0 LIMIT <batch-size>) 重复执行几次,直至 effect rows = 0
    我经常这么干,如果你这条查询走索引或者数据量不大的话就无所谓,数据量大且没索引的时候可以考虑先取到程序里再分批更新。
    7911364440
        4
    7911364440  
       2022-09-29 18:06:29 +08:00
    分批更新的过程中如果有其它查询数据的请求进来,可能会查到中间状态的数据,需要考虑下会不会对系统有影响
    brader
        5
    brader  
    OP
       2022-09-29 18:10:31 +08:00
    @lmshl 好的,谢谢前辈,感觉这个解决方案适合我,想问下你平时<batch-size>一般设置多大? 100 ? 1000 ?
    cnoder
        6
    cnoder  
       2022-09-29 18:13:41 +08:00
    可以不 wherein ,直接 limit ,一样的,UPDATE table SET onsale=0 WHERE uid=116980 and onsale !=0 limit 100
    brader
        7
    brader  
    OP
       2022-09-29 18:14:37 +08:00
    @7911364440 是的,我就是查询到线上前面比较多数据的几个用户,每人有大概 100 万条数据要更新,我就是担心一次更新 100 万条,会把数据库搞死,想小批量更新
    dongtingyue
        8
    dongtingyue  
       2022-09-29 18:29:21 +08:00
    innodb 是锁行否则是锁表。每次更新少点
    fmumu
        9
    fmumu  
       2022-09-30 10:39:34 +08:00
    先查出来主键,用主键去更新
    googol2chen
        10
    googol2chen  
       2022-09-30 11:00:25 +08:00
    @cnoder wherein 是为了用主键索引,加快查询速度。
    lmshl
        11
    lmshl  
       2022-09-30 11:55:46 +08:00
    @cnoder 谢谢,学到了,原来 mysql 还支持 update/delete 的时候加 limit 。pg 不支持这个,我也没想到这个
    brader
        12
    brader  
    OP
       2022-09-30 12:01:13 +08:00
    @lmshl
    @cnoder 我查找了一些资料,我认为先查主键出来再更新,和使用二级索引更新的时候加 limit ,这两个分批更新的方案,在本质上还是有所不同的,我分析原因如下:
    这两个方案,比如一次更新 100 条,可能花费的更新时间差不多,锁定时间差不多,但是我认为他们锁定的数据范围有所不同,使用主键更新,明确了哪些主键,有更明确的行锁范围,使用二级索引+limit ,锁的行数范围会更广。

    你们觉得呢
    lmshl
        13
    lmshl  
       2022-09-30 12:04:39 +08:00
    @brader 没考虑到那么细过,不过如果是我们能想到的优化方案,说不定执行引擎也想到了,优化后效果可能是一样的
    hoko1814
        14
    hoko1814  
       165 天前
    学习了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2870 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 14:10 · PVG 22:10 · LAX 06:10 · JFK 09:10
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.