MySQL更新总计表，包括计数和光标，不带锁定值表

Question

我有兴趣能够使用Values表中的计数或总和增量更新Totals表中的行，而不会阻止插入Values。

这个想法是收集 id 大于总检查点的所有值，然后更新总计数和检查点。这将创建一个快照以Totals.以下是表格：

Table: Totals
Create Table: CREATE TABLE `Totals` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`count` int(11) NOT NULL DEFAULT '0',
`values_checkpoint` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Table: Values
Create Table: CREATE TABLE `Values` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`total_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `total_id_id` (`total_id`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

组合快照和值的查询可能如下所示：

SELECT t.count + count(v.total_id),max(v.id) 
FROM `Totals` t
JOIN `Values` v ON v.total_id = t.id AND v.id > t.values_checkpoint
WHERE t.id = 1;

更新Totals行时出现问题。

将其直接转换为更新将不起作用，因为MySQL不允许更新中的聚合方法。

加入子查询将不起作用，因为它无法关联，我需要 WHERE 子句来id > t.values_checkpoint.

分别设置t.count和t.values_checkpoint将不起作用，因为它会阻止插入ValuesREPEATABLE READ，并且它们在READ COMMITTED中不一致

我能弄清楚如何做到这一点的唯一方法是启动一个事务并SELECT ... FOR UPDATE总行以获取可以在 Join 子查询中使用的values_checkpoint。这并不理想，因为我需要在打开事务的情况下保持锁定挂起。

有没有办法用单个更新语句和单个 SELECT onValues来更新t.count和t.values_checkpoint？

Answer 1

与其从你"离开"的地方一直到最后，不如限制它不超过 1000 行。 这将使干扰保持在更短的时间内。 (如果需要，使用少于 1000 个。 预先计算(在事务之外)一个"好"的停止点也可能是件好事。

SELECT @stop := LEAST(t.values_checkpoint + 1000, MAX(v.id))
FROM ...

然后继续执行查询;大概IODKU就足够了(并且始终执行更新端)：

INSERT INTO Totals (id, count, values_checkpoint)
SELECT t.count + count(v.total_id), max(v.id) 
FROM `Totals` t
JOIN `Values` v ON v.total_id = t.id
AND v.id > t.values_checkpoint
AND v.id <= @stop
WHERE t.id = 1
ON DUPLICATE KEY UPDATE
count = VALUES(count),
values_checkpoint = VALUES(values_checkpoint);

然后足够频繁地进行更新，这样您就不会落后 1000 人。