请教个 mysql 更新问题

[brader]

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

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

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

[rqxiao]

一次锁住这 1 万条数据

[nekolr]

缩小锁定的范围，拆开更新？

[lmshl]

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]

分批更新的过程中如果有其它查询数据的请求进来，可能会查到中间状态的数据，需要考虑下会不会对系统有影响

[brader]

@lmshl 好的，谢谢前辈，感觉这个解决方案适合我，想问下你平时<batch-size>一般设置多大？ 100 ？ 1000 ？

[cnoder]

可以不 wherein ，直接 limit ，一样的,UPDATE table SET onsale=0 WHERE uid=116980 and onsale !=0 limit 100

[brader]

@7911364440 是的，我就是查询到线上前面比较多数据的几个用户，每人有大概 100 万条数据要更新，我就是担心一次更新 100 万条，会把数据库搞死，想小批量更新

[dongtingyue]

innodb 是锁行否则是锁表。每次更新少点

[fmumu]

先查出来主键,用主键去更新

[googol2chen]

@cnoder wherein 是为了用主键索引，加快查询速度。

[lmshl]

@cnoder 谢谢，学到了，原来 mysql 还支持 update/delete 的时候加 limit 。pg 不支持这个，我也没想到这个

[brader]

@lmshl
@cnoder 我查找了一些资料，我认为先查主键出来再更新，和使用二级索引更新的时候加 limit ，这两个分批更新的方案，在本质上还是有所不同的，我分析原因如下：
这两个方案，比如一次更新 100 条，可能花费的更新时间差不多，锁定时间差不多，但是我认为他们锁定的数据范围有所不同，使用主键更新，明确了哪些主键，有更明确的行锁范围，使用二级索引+limit ，锁的行数范围会更广。

你们觉得呢

[lmshl]

@brader 没考虑到那么细过，不过如果是我们能想到的优化方案，说不定执行引擎也想到了，优化后效果可能是一样的