php - mysql进行update操作速度慢,如何解决

Question

数据库有个items表，现在要update其中的userid字段、
语句是 UPDATE items SET userid = xxx WHERE userid = 0 limit 1;
已经开启事务来优化时间了，但感觉还是太慢了..更新3w条花费3分钟。
早上尝试建立个temp表，将userid->items数组存入，然后写php脚本不停的查询temp表，然后操作。
但是这样执行多个脚本的时候，只有一个能活下来...而且达不到监控的效果.
现在的想法是，将更新操作分成删除和插入..正在尝试
另外问一下，php开发的话，用什么做消息队列比较好..rabbitmq的php版，找不到文档。。。

Answer 1

You can use Replace into instead of update, or you can use insert into...on duplicate key update to update in batches.

In addition, a demo of the Rabbitmq PHP version is attached:
https://github.com/yuansir/ra...

Answer 2

Use redis as a queue. This is generally used. Your sql is probably slow because of the limit. Try to take out the IDs of all the data to be updated and then update it. It will be much better