请教个 mysql 更新问题

2022-09-29 17:27:25 +08:00
 brader

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

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

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

1880 次点击
所在节点    MySQL
14 条回复
rqxiao
2022-09-29 17:29:30 +08:00
一次锁住这 1 万条数据
nekolr
2022-09-29 17:31:21 +08:00
缩小锁定的范围,拆开更新?
lmshl
2022-09-29 17:53:55 +08:00
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
2022-09-29 18:06:29 +08:00
分批更新的过程中如果有其它查询数据的请求进来,可能会查到中间状态的数据,需要考虑下会不会对系统有影响
brader
2022-09-29 18:10:31 +08:00
@lmshl 好的,谢谢前辈,感觉这个解决方案适合我,想问下你平时<batch-size>一般设置多大? 100 ? 1000 ?
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
2022-09-29 18:14:37 +08:00
@7911364440 是的,我就是查询到线上前面比较多数据的几个用户,每人有大概 100 万条数据要更新,我就是担心一次更新 100 万条,会把数据库搞死,想小批量更新
dongtingyue
2022-09-29 18:29:21 +08:00
innodb 是锁行否则是锁表。每次更新少点
fmumu
2022-09-30 10:39:34 +08:00
先查出来主键,用主键去更新
googol2chen
2022-09-30 11:00:25 +08:00
@cnoder wherein 是为了用主键索引,加快查询速度。
lmshl
2022-09-30 11:55:46 +08:00
@cnoder 谢谢,学到了,原来 mysql 还支持 update/delete 的时候加 limit 。pg 不支持这个,我也没想到这个
brader
2022-09-30 12:01:13 +08:00
@lmshl
@cnoder 我查找了一些资料,我认为先查主键出来再更新,和使用二级索引更新的时候加 limit ,这两个分批更新的方案,在本质上还是有所不同的,我分析原因如下:
这两个方案,比如一次更新 100 条,可能花费的更新时间差不多,锁定时间差不多,但是我认为他们锁定的数据范围有所不同,使用主键更新,明确了哪些主键,有更明确的行锁范围,使用二级索引+limit ,锁的行数范围会更广。

你们觉得呢
lmshl
2022-09-30 12:04:39 +08:00
@brader 没考虑到那么细过,不过如果是我们能想到的优化方案,说不定执行引擎也想到了,优化后效果可能是一样的
hoko1814
150 天前
学习了

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/883870

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX