在表格中计算实例数

Question

我有一个标记系统，用于标记博客文章等内容。标记存储在一个表中，只包含标记名称和主键。然后我有另一个表格，其中包含正在使用这些标记的对象。

它可能看起来像这样:

_________________________________
| tags                          |
--------------------------------|
| id  | name                    |
|-------------------------------|
|  1  | Scuba diving            |
|  2  | Dancing                 |
---------------------------------

_________________________________
| tag_objects                   |
--------------------------------|
| id  | tag  | object           |
|-------------------------------|
|  1  |  2   | 13               |
|  2  |  2   | 18               |
|  3  |  1   | 24               |
---------------------------------

现在，我需要完成的是向标签表添加一列，称为"occurrences"或其他名称。 对于标签表中的每个标签，出现次数应设置为该标签在tag_objects中使用的次数。

因此，基本上像这样（显然是伪代码）：

foreach(tags):
    UPDATE tags
       SET occurrences = (SELECT COUNT(id) 
                            FROM tag_objects 
                           WHERE tag = tags.id);

当人们在未来创建新的帖子和内容时，我将只需设置一个触发器来更新计数，但是我已经有几千行需要先进行计数。我不知道怎么做，所以任何帮助都将不胜感激。

- Tommy Brunn

Answer 1

最简单的方法，不需要任何额外的表格，是：

首先添加额外的字段：

mysql> alter table tags add occurs int default 0;

然后只需使用出现次数更新这个新字段。

mysql> update tags left join (select tag, count(id) as cnt from tag_objects group by tag) as subq on tags.id=subq.tag set occurs=coalesce(subq.cnt,0);

请注意使用左连接以确保计算所有标签，即使是未使用的标签也要计算。coalesce函数将NULL转换为0。

- Eljakim

Answer 2

你做得很好，你的查询应该可以工作。

但是，这将导致可怕的性能。我建议你重新创建一个表：

CREATE TABLE newTags AS
SELECT t.id, t.name, COUNT(*) AS occurrences
FROM tags t
    INNER JOIN tag_objects to
        ON to.tag = tags.id
GROUP BY t.id, t.name

这将非常快。

- Cyril Gandon

Answer 3

通常情况下，我不会希望在数据库上存储计算出的值 - 这很混乱，容易失步，并且违反了规范化的原则。然而，如果您确实需要具有计数的数据库实体，而不是动态计算，我会创建一个视图（http://dev.mysql.com/doc/refman/5.0/en/create-view.html），该视图存储预先计算好的值，使用Scorpio提供的SQL。

CREATE view tag_occurences AS
SELECT t.id, t.name, 
COUNT(*) AS occurrences
FROM tags t
    INNER JOIN tag_objects to
        ON to.tag = tags.id
GROUP BY t.id, t.name

- Neville Kuyt

---

MySQL不支持物化视图，因此它不会存储任何内容。每次都需要运行查询。 - Bill Karwin

它不需要被实现 - 它只是将查询塞入视图中，而不是每次运行它。但是，像你一样，我会直接运行查询，而不是通过视图。 - Neville Kuyt

你误解了。在MySQL中，通过视图查询与直接运行查询完全相同。它不会预先计算任何值。使用视图在优化、执行或表I/O方面都没有节省。 - Bill Karwin

是的，我知道 - 但我不认为性能是这个问题的首要考虑因素。事实上，他根本没有提到性能作为一个关注点。我认为这种方法可能比将值写入持久存储性能更好 - 触发器（如果他使用了被接受答案中的SQL）会随着数据增长而变得非常缓慢，因为它不限制更新到当前记录。如果表被很好地索引，并且记录数量不太多，则纯粹的“每次有人访问视图时运行查询”策略应该可以正常工作... - Neville Kuyt

Answer 4

除非你真的需要对数据进行反规范化，否则最好不要这样做。依赖索引列通常非常快速。我是干净和规范化数据的忠实粉丝 ;-)

- Jean-Francois

---

这也让我很痛心，但这个数据库已经非常不堪重负了，而且它经历过比这更恶劣的环境。我们的流量也相当大，因此需要任何减少每个页面加载时数据库所需工作量的方法。 - Tommy Brunn

啊！我明白了...;-)在之前的工作中，我曾经遇到过这种情况。我们花了几个月的时间进行优化，并且取得了50%左右的效果。我们最终通过更换硬件和升级（这是一种Oracle数据库）来真正解决了问题。祝好运！ - Jean-Francois

Answer 5

我认为如果您在表tag_objects的插入/删除触发器中增加和减少occurrences的值，您将获得更好的性能。

- Gedrox

---

当然，我将来会这样做。但是，我正在一个相当老的数据库上实现它，该数据库已经有几千行了。我只打算运行一次，然后添加触发器进行更新。 - Tommy Brunn

这并不一定是正确的。这完全取决于更新/插入/删除的数量！如果这个数字很大，触发器实际上可能会锁定标签表太长时间，导致事情变得非常缓慢！ - Eljakim

在标准情况下，标签数据被读取的次数比被更改的次数多。如果不是这样，那么标签的使用就值得怀疑了。 - Gedrox

Answer 6

您的伪代码将按照编写时的确切方式工作（无需使用foreach循环）。至少在Oracle中是这样的，我认为MySQL也允许您将相关子查询用作值。

- Sodved

Answer 7

对于插入新行，您可以使用以下查询： INSERT INTO tags VALUES(x,y,z,1) ON DUPLICATE KEY UPDATE occurrences = occurrences+1; 我没有检查语法，但大致是这样。

- AndSoYouCode