如何实现标签系统

Question

如何实现标签系统

algorithmsystemtagging

97

我在想如何实现像SO上使用的标签系统，我考虑过以下方案，但无法想出一个好的可扩展解决方案。

我考虑采用基本的3个表结构：一个tags表、一个articles表和一个tag_to_articles表。

这是解决此问题的最佳方案吗？还有其他替代方案吗？使用这种方法，随着时间的推移，表格将变得非常大，在搜索方面效率也不太高。另一方面，查询执行速度并不是非常重要。

- Saif Bechan

https://dev59.com/zqvka4cB1Zd3GeqPrE9x - Kanagavelu Sugumar

7个回答

8

你的三个表方案没有问题。

另一个选择是限制可以应用于文章的标签数量（例如SO中的5个），并将这些标签直接添加到您的文章表中。

规范化数据库既有优点也有缺点，就像将事物硬编码到一个表中一样，也有优点和缺点。

并不意味着你不能两者都做。重复信息违反关系型数据库范例，但如果目标是性能，你可能需要打破这些范例。

- John

将标签直接放入文章表中肯定是一种选择，尽管这种方法有一些缺点。如果您将5个标签存储在逗号分隔的字段中，例如（tag1,2,3,4），那么这将是一种简单的方法。问题是搜索是否会更快。例如，有人想查看所有带有tag1的内容，您必须遍历整个文章表。这虽然比遍历tag_to_article表要少，但是tag_to_article表更加精简。另一件事是您必须每次在php中进行解析，我不知道这需要多长时间。 - Saif Bechan

如果你同时使用标签和单独的表格，那么这将为基于文章和基于标签的搜索都提供性能。权衡是维护重复信息的负担。此外，通过限制标签数量，可以将每个标签放入自己的列中。只需选择* from articles where XXXXX并执行即可，无需展开。 - John

6

您提出的三个表实现可以用于标记。

然而，Stack Overflow使用不同的实现。他们将标记存储到帖子表中的varchar列中，并使用全文索引来获取与标记匹配的帖子。例如posts.tags = "algorithm system tagging best-practices"。我确信Jeff在某个地方提到过这一点，但我忘记了在哪里。

- Juha Syrjälä

4

这似乎非常低效。标签顺序怎么办？或者相关的标签呢？（例如，“process”与“algorithm”类似或类似的标签） - Richard Duerr

3

我能想到的解决标签和文章之间多对多关系的最佳方法，如果不是唯一可行的方法，那么也是最好的。所以我的投票是“是的，它仍然是最好的”。不过，我对任何其他替代方案也很感兴趣。

- David Thomas

我同意。这些Tags和TagMap表的记录大小很小，如果正确索引，不应该会显著降低性能。限制每个项目的标签数量也可能是一个好主意。 - PanJanek

2

如果您的数据库支持可索引数组（例如PostgreSQL），我建议完全去规范化的解决方案 - 将标签作为字符串数组存储在同一张表上。如果不支持，则使用一个辅助表来映射对象和标签是最好的解决方案。如果需要存储标签的额外信息，可以使用单独的标签表，但没有必要为每个标签查找引入第二个连接。

- Nick Johnson

PostgreSQL仅支持整数数组上的索引：http://www.postgresql.org/docs/current/static/intarray.html - Mike Chamberlain

1

现在它也支持文本：https://www.postgresql.org/docs/9.6/static/arrays.html - luckydonald

2

我想建议优化MySQLicious以获得更好的性能。在此之前，让我们先看看Toxi（3张表）方案的缺点：

如果你有数百万个问题，并且每个问题有5个标签，那么tagmap表中就会有500万条记录。因此，首先我们必须基于标签搜索过滤出1万个tagmap条目，然后再过滤出这1万个条目中匹配的问题。因此，在过滤时，如果文章ID是简单数字，则没有问题，但如果它是UUID（32个字符），则需要进行更大的比较，尽管已经建立了索引。

我的解决方案是：

每当创建一个新标签时，将counter++（十进制），并将该计数器转换为base64。现在每个标签名称都将具有base64 ID，并将此ID与名称一起传递给UI。这样，在我们的系统中创建4095个标签之前，您将拥有最多两个字符的ID。现在将这些多个标签连接成每个问题表的标签列。添加分隔符并对其进行排序。

因此，表看起来像这样

在查询时，应该使用id而不是真实标签名称进行查询。由于它是排序的，因此在标签上的and条件将更有效率（LIKE '%|a|%|c|%|f|%）。

请注意，单个空格分隔符是不够的，我们需要双重分隔符来区分像sql和mysql这样的标签，因为LIKE "%sql%"也会返回mysql的结果。应该是LIKE "%|sql|%" 我知道搜索是非索引的，但仍然可能已经对与文章相关的其他列进行了索引，如作者/日期时间，否则将导致完整表扫描。

最后，通过这种解决方案，在连接条件上不需要内部连接，其中百万条记录必须与500万条记录进行比较。

- Kanagavelu Sugumar

团队，请在评论中提供您对此解决方案的缺点的意见。 - Kanagavelu Sugumar

@Nick Dandoulakis 请帮我提供您对上述解决方案是否可行的评论？ - Kanagavelu Sugumar

@Juha Syrjälä 上述方案可以吗？ - Kanagavelu Sugumar

这不是很实际吧？在现实世界中，我们需要通过标签名称而不是标签ID进行查询。 - cusX

0

CREATE TABLE Tags (
    tag VARHAR(...) NOT NULL,
    bid INT ... NOT NULL,
    PRIMARY KEY(tag, bid),
    INDEX(bid, tag)
)

注意：

这种方法比 TOXI 更好，因为它不需要经过额外的多对多表，这使得优化变得困难。
当然，我的方法可能会稍微有些臃肿（比 TOXI），因为它包含了冗余标签，但是这只占整个数据库的一小部分，而性能提升可能是显著的。
它具有高度可扩展性。
它不需要使用代理 AUTO_INCREMENT PK。因此，它比 Scuttle 更好。
MySQLicious 很糟糕，因为它不能使用索引（LIKE 与前导通配符；在子字符串上产生错误的匹配）
对于 MySQL，请确保使用 ENGINE=InnoDB 以获得“聚集”效果。

相关讨论（针对 MySQL）：
多对多映射表优化
 有序列表

- Rick James

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Nick Dandoulakis · Accepted Answer

我相信您会发现这篇博客文章很有趣：标签：数据库模式问题是：您希望拥有一个数据库模式，其中可以使用尽可能多的标签来标记书签（或博客文章或任何东西）。稍后，您想运行查询以将书签约束为标记的并集或交集。您还希望从搜索结果中排除（例如：减去）某些标记。

“MySQLicious”解决方案

在此解决方案中，模式只有一个表，它是非规范化的。这种类型称为“MySQLicious解决方案”，因为MySQLicious将del.icio.us数据导入具有此结构的表中。

enter image description here

交集（AND）

查询“search+webservice+semweb”：

SELECT *
FROM `delicious`
WHERE tags LIKE "%search%"
AND tags LIKE "%webservice%"
AND tags LIKE "%semweb%"

联合查询（或者）针对“search|webservice|semweb”的查询：

SELECT *
FROM `delicious`
WHERE tags LIKE "%search%"
OR tags LIKE "%webservice%"
OR tags LIKE "%semweb%"

针对“search+webservice-semweb”的Minus查询

SELECT *
FROM `delicious`
WHERE tags LIKE "%search%"
AND tags LIKE "%webservice%"
AND tags NOT LIKE "%semweb%"

“Scuttle”解决方案

Scuttle将其数据组织在两个表中。那张名为“scCategories”的表是“标签”表，它有一个指向“书签”表的外键。

enter image description here

交集查询 (AND) 查询“bookmark+webservice+semweb”：

SELECT b.*
FROM scBookmarks b, scCategories c
WHERE c.bId = b.bId
AND (c.category IN ('bookmark', 'webservice', 'semweb'))
GROUP BY b.bId
HAVING COUNT( b.bId )=3

首先，搜索所有书签标记组合，其中标记为“bookmark”，“webservice”或“semweb”（c.category IN（'bookmark'，'webservice'，'semweb'）），然后仅考虑已搜索到所有三个标记的书签（HAVING COUNT（b.bId）= 3）。

联合（OR）查询“bookmark | webservice | semweb”： 只需省略HAVING子句即可使用联合：

SELECT b.*
FROM scBookmarks b, scCategories c
WHERE c.bId = b.bId
AND (c.category IN ('bookmark', 'webservice', 'semweb'))
GROUP BY b.bId

减法（排除）针对“bookmark+webservice-semweb”进行查询，即：书签 AND Web服务 AND NOT 语义Web。

SELECT b. *
FROM scBookmarks b, scCategories c
WHERE b.bId = c.bId
AND (c.category IN ('bookmark', 'webservice'))
AND b.bId NOT
IN (SELECT b.bId FROM scBookmarks b, scCategories c WHERE b.bId = c.bId AND c.category = 'semweb')
GROUP BY b.bId
HAVING COUNT( b.bId ) =2

略去HAVING COUNT会导致查询“bookmark | webservice-semweb”。

“Toxi”解决方案

Toxi提出了一种三表结构。通过“tagmap”表，书签和标签是n对m相关的。每个标签可以与不同的书签一起使用，反之亦然。这个数据库架构也被WordPress使用。查询与“scuttle”解决方案几乎相同。

enter image description here

交集（AND）

查询“bookmark+webservice+semweb”

SELECT b.*
FROM tagmap bt, bookmark b, tag t
WHERE bt.tag_id = t.tag_id
AND (t.name IN ('bookmark', 'webservice', 'semweb'))
AND b.id = bt.bookmark_id
GROUP BY b.id
HAVING COUNT( b.id )=3

联合（或）查询“bookmark|webservice|semweb”

SELECT b.*
FROM tagmap bt, bookmark b, tag t
WHERE bt.tag_id = t.tag_id
AND (t.name IN ('bookmark', 'webservice', 'semweb'))
AND b.id = bt.bookmark_id
GROUP BY b.id

减法（排除）针对“bookmark+webservice-semweb”进行查询，即：书签 AND Web服务 AND NOT 语义Web。

SELECT b. *
FROM bookmark b, tagmap bt, tag t
WHERE b.id = bt.bookmark_id
AND bt.tag_id = t.tag_id
AND (t.name IN ('Programming', 'Algorithms'))
AND b.id NOT IN (SELECT b.id FROM bookmark b, tagmap bt, tag t WHERE b.id = bt.bookmark_id AND bt.tag_id = t.tag_id AND t.name = 'Python')
GROUP BY b.id
HAVING COUNT( b.id ) =2

略过HAVING COUNT会导致查询“bookmark|webservice-semweb”。