推迟 MySQL 中的频繁更新
我经常更新用户表，该表只是设置用户的最后一次查看时间，我想知道是否有一种简单的方法可以推迟它们并在短暂的超时（5 分钟左右）后将它们分组到单个查询中。这将大大减少对我的用户数据库的查询。
I have frequent updates to a user table that simply sets the last seen time of a user, and I was wondering whether there is a simple way to defer them and group them into a single query after a short timeout (5 minutes or so). This would reduce queries on my user database quite a lot.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您执行
UPDATE LOW_PRIORITY 表...，您将确保它仅在不执行任何其他操作时执行您的更新。除此之外，我认为 MySQL 内部没有太多选择。另外，它现在是否会引起问题，或者您只是在优化一些不是问题的东西？就我个人而言，如果我要像这样批量更新，我只需将所有 ID 插入 memcached 中，并使用 cronjob 每 5 分钟更新一次。
If you do a
UPDATE LOW_PRIORITY table ...you will make sure it will only execute your update when it's not doing anything else. Besides that I don't think there are much options inside MySQL.Also, is it causing problems now or are you simply optimizing something that isn't a problem? Personally, if I would batch updates like these I would simply insert all the IDs in memcached and use a cronjob to update every 5 minutes.
沃尔夫的建议应该可以解决问题。还可以创建第二个不带任何索引的表，并将所有数据插入到该表中。它甚至可以是内存表。然后，您定期执行 INSERT INTO table1 SELECT * FROM TABLE2 ON DUPLICATE KEY UPDATE ... 以传输到主表。
Wolph's suggestion should do the trick. Also possible is to create a second table without any indices on it and insert all your data into that table. It can even be an in memory table. Then you an do a periodic INSERT INTO table1 SELECT * FROM TABLE2 ON DUPLICATE KEY UPDATE ... to transfer to the main table.