mysql - MySQL Update带有计数和游标的总计表，没有锁定值表

我感兴趣的是能够用Totals表中的计数或总和来增量更新Values表中的行，而不会阻止对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不允许在更新中使用聚合方法。

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

分别设置t.count和t.values_checkpoint无效，因为它可能阻止在Values中插入REPEATABLE READ，并且在READ COMMITTED中不一致

我能弄清楚如何完成这项工作的唯一方法是启动一个事务，然后在SELECT ... FOR UPDATE总行中获取可以在Join子查询中使用的values_checkpoint。这不是理想的，因为我需要在事务打开时挂锁。

是否可以通过单个更新语句和t.count上的单个SELECT来更新t.values_checkpoint和Values？

最佳答案

而不是从一开始就“停下来”一直走到最后，而要限制它的行数不要超过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。

totals

mysql - MySQL Update带有计数和游标的总计表，没有锁定值表