mysql - 如何从 BEFORE UPDATE 触发器内的同一个表中检索计数

Question

我有一个大表(3.6M)，其中包含每天插入的 5K 到 8K 条记录(数据集)，这些是“请求”的每日快照。我想添加在以前的数据集中看到唯一引用的次数。这里的关键字段是 ID、DataSetID、RequestReference。

BEGIN
SET NEW.calcRefPrevSeen=(SELECT COUNT(ID) AS CNT from db.tblrequests WHERE DataSetID<NEW.DataSetID and RequestReference=NEW.RequestReference);
END

我有一个带有 {DataSetID, RequestReference} 的索引。

然而事实证明，这效率太低，并且由于记录数量太多而花费太长时间。有没有更好的方法在触发器内更新此值？

Answer 1

我建议将此数据保存在单独的表中，而不是每次都进行非常昂贵的count()操作。在表上创建插入后和删除后触发器。在此触发器中，您只需执行

/*pseudo code...*/
INSERT INTO my_count_table VALUES(...) ON DUPLICATE KEY UPDATE my_counter = my_counter + 1;
/*or - 1 of course for the delete trigger*/

在manual中阅读有关在重复 key 更新时插入...的更多信息.