请教个 mysql 更新问题

› 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 1462 days ago, the information mentioned may be changed or developed.

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

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

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

uid

MySQL

语句

更新

14 replies • 2024-06-11 15:32:08 +08:00

rqxiao

Sep 29, 2022

一次锁住这 1 万条数据

nekolr

Sep 29, 2022

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

lmshl

Sep 29, 2022

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

Sep 29, 2022

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

brader

Sep 29, 2022

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

cnoder

Sep 29, 2022

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

brader

Sep 29, 2022

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

dongtingyue

Sep 29, 2022

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

fmumu

Sep 30, 2022

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

googol2chen

Sep 30, 2022

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

lmshl

Sep 30, 2022

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

brader

Sep 30, 2022

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

你们觉得呢

lmshl

Sep 30, 2022

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

hoko1814

Jun 11, 2024

学习了