在MySQL中不锁定整个表的情况下更新表

Question

运行此更新语句将锁定表中的每一行5秒钟。

UPDATE `audio_clips` SET activity = activity * 0.95;

有没有一种批处理的方式（在mysql内部），或者执行语句而不锁定？

这是一个用于显示站点当前热门内容（例如Reddit，Hacker News等）的字段。每当播放音频剪辑时，活动就会增加一次。定期地，我们会“衰减”每个剪辑的活动量。我不介意进行原子更新，只要每一行都被衰减即可。

- skattyadz

---

表的类型是MyISAM还是InnoDB？你为什么要更新所有记录，还是这只是一个打字错误？你发布的SQL语句中缺少WHERE子句。 - Michael J.V.

InnoDB。我正在更新每个记录，因为我将按列排序。我需要热门项目上升列表，不再流行的项目下降列表。 - skattyadz

Answer 1

我会采用不同的方法。

是否可以在播放剪辑时设置时间戳，然后在碰撞时计算自那个时间戳以来的衰减？在读取统计数据时，应该将活动量减去自上一个时间戳以来的衰减。

这样，您仍然只需要每个播放剪辑进行一次更新。

具体来说：

UPDATE `audio_clips` SET `lastview`=UNIX_TIMESTAMP(),
                   `activity`=1+`activity`*POW(0.9,(UNIX_TIMESTAMP()-`lastview`)/3600)
               WHERE `clipid`=$clipid

对于每小时10%的下降和每次查看增加1个的情况。

查看当前统计数据：

SELECT *,`activity`*POW(0.9,(UNIX_TIMESTAMP()-`lastview`)/3600) AS `current_activity`
       FROM `audio_clips`

- mvds

---

我喜欢这种方法，但如果我计划按列排序，我仍然需要对非活动剪辑进行批量衰减，不是吗？ - skattyadz

不，您可以按任何表达式排序，包括计算出的当前活动。 - mvds

嗯，它可能需要积极的缓存，因为它无法使用索引。如果我告诉你我们正在讨论大约35万行，这会有所不同吗？ - skattyadz

嗯，是的。知道了这一点，我肯定不会进行整个表格的更新;-)你需要测试一下你的缓存假设。但是我猜你可能只需要最活跃的N个，如果是这样，你可以按activity进行排序，取最活跃的2*N个，然后对结果进行后处理，以获取真正的前N个。 - mvds

Answer 2

最终，我最终选择分批处理。虽然它可能不是完全可扩展的，但每1000个记录的批处理需要约35毫秒。整个过程每小时仅需10秒。

已经足够好了。

- skattyadz