关于sql：用于标记的数据库设计

Database Design for Tagging

您如何设计数据库来支持以下标记功能：

项目可以具有大量标签
搜索带有给定标签集的所有项目必须快速(这些项目必须具有ALL标记，因此这是AND搜索，而不是OR搜索)
创建/写入项目可能较慢，无法快速查找/阅读

理想情况下，应使用单个SQL语句查找(至少)标记有n个给定标记集的所有项目。由于要搜索的标签数量以及任何项目上的标签数量都是未知的并且可能很高，因此使用JOIN是不切实际的。

有任何想法吗？

到目前为止，感谢您提供所有答案。

但是，如果我没记错的话，给出的答案将显示如何对标签进行"或"搜索。 (选择具有n个标签中的一个或多个标签的所有项目)。我正在寻找有效的AND搜索。 (选择所有带有n个标记的项目-可能还有更多。)

这是一篇有关标记数据库模式的好文章：

http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/

以及性能测试：

http://howto.philippkeller.com/2005/06/19/Tagsystems-performance-tests/

请注意，那里的结论非常特定于MySQL，MySQL的结论(至少在撰写本文时是在2005年)具有很差的全文索引特性。

关于ANDing：听起来您正在寻找"关系除法"操作。本文以简洁但可理解的方式介绍了关系划分。

关于性能：基于位图的方法听起来似乎很适合这种情况。但是，我不认为"手动"实现位图索引是一个好主意，就像digiguru所建议的：每当添加新标签时，听起来情况就很复杂(？)，但是某些DBMS(包括Oracle)提供了位图索引，这可能会以某种方式之所以有用，是因为内置的索引系统消除了索引维护的潜在复杂性；另外，提供位图索引的DBMS在执行查询计划时应该能够适当考虑它们。

我只是想强调一下@Jeff Atwood链接到的文章(http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/)非常详尽(它讨论了3种不同架构的优点方法)，并为AND查询提供了一个很好的解决方案，该查询通常会比到目前为止所提到的要好(即，每个术语都没有使用相关的子查询)。在评论中也有很多好东西。

ps-每个人都在这里谈论的方法在本文中称为" Toxi"解决方案。

我不认为直接解决方案有问题：项目表，标签表，"标签"交叉表

交叉表上的指标应该足够优化。选择适当的项目将是

1
2
3

SELECT * FROM items WHERE id IN
(SELECT DISTINCT item_id FROM item_tag WHERE
tag_id = tag1 OR tag_id = tag2 OR ...)

与标记将是

1
2
3
4

SELECT * FROM items WHERE
EXISTS (SELECT 1 FROM item_tag WHERE id = item_id AND tag_id = tag1)
AND EXISTS (SELECT 1 FROM item_tag WHERE id = item_id AND tag_id = tag2)
AND ...

诚然，对于大量的比较标签而言，效率不高。如果要在内存中维护标签计数，则可以使查询以不经常使用的标签开始，因此AND序列的计算速度更快。根据要匹配的标签的预期数量以及与它们中的任何一个匹配的期望，这可能是一个好的解决方案，如果要匹配20个标签，并且期望某个随机项目将匹配其中的15个，那么这仍然很繁重在数据库上。

您可能想尝试使用非限制性的数据库解决方案(例如Java Content Repository实现(例如Apache Jackrabbit))，并使用基于Apache Lucene之类的基础之上构建的搜索引擎。

与本地解决方案相比，具有适当缓存机制的此解决方案可能会产生更好的性能。

但是，我真的不认为在中小型应用程序中，您将需要比以前的文章中提到的规范化数据库更复杂的实现。

编辑：澄清一下，在搜索引擎中使用类似JCR的解决方案似乎更有吸引力。从长远来看，这将大大简化您的程序。

最简单的方法是创建标签表。
Target_Type-如果要标记多个表
Target-被标记的记录的键
Tag-标签的文本

查询数据将类似于：

1
2
3

SELECT DISTINCT target FROM tags
WHERE tag IN ([your list OF tags TO SEARCH FOR here])
AND target_type = [the TABLE you're searching]

更新
根据您对AND条件的要求，上面的查询将变成这样

1
2
3
4
5
6
7
8

SELECT target
FROM (
SELECT target, COUNT(*) cnt
FROM tags
WHERE tag IN ([your list OF tags TO SEARCH FOR here])
AND target_type = [the TABLE you're searching]
)
where cnt = [number of tags being searched]

我第二个@Zizzencs建议您可能想要的不是完全以(R)DB为中心的内容

我以某种方式相信使用简单的nvarchar字段通过适当的缓存/索引存储该标签可能会产生更快的结果。但这就是我。

我已经实现了使用3个表表示之前的多对多关系(项目标签ItemTags)的标记系统，但是我想您将在很多地方处理标签，我可以告诉您3个表必须始终被同时操纵/查询肯定会使您的代码更复杂。

您可能要考虑增加的复杂性是否值得。

如果您使用的是数组类型，则可以预先汇总所需的数据。在单独的线程中查看此答案：

数组类型的用途是什么？

上述答案的一种变体是采用标签ID，对其进行排序，组合为^分隔的字符串并对其进行哈希处理。
然后，只需将哈希与该项目相关联即可。标签的每种组合都会产生一个新密钥。要执行AND搜索，只需使用给定的标签ID重新创建哈希并进行搜索。
更改项目上的标签将导致重新创建哈希。具有相同标签集的项目共享相同的哈希键。

用其他人的话来解释一下：技巧不在模式中，而在查询中。

实体/标签/标签的幼稚模式是正确的方法。但是，正如您所看到的，目前尚不清楚如何对带有许多标签的AND查询执行操作。

优化该查询的最佳方法将取决于平台，因此，我建议您使用RDBS重新标记您的问题，并将标题更改为"在标记数据库上执行AND查询的最佳方法"之类的内容。

我对MS SQL有一些建议，但如果您使用的不是平台，请尽量避免。

我想做的是有许多代表原始数据的表，因此在这种情况下

1
2
3