如何有效地使用标记查询项

Question

我的模式如下所示：

items ( id, title)
tags (id, name )
items_tags ( item_id, tag_id )

我希望高效地获得一个项目列表，每个项目都有自己的标记集。大概是使用对数据库的一个查询。高效是指查询应该使用尽可能少的服务器和数据库资源(如CPU和)，在尽可能快的时间内返回其结果。据推测，条目和标签的数量超过数百万，并行查询的数量也很高。高负荷和所有东西。所以，就像：

// Get all items with tags
'Item-1' has 'Tag-1', 'Tag-2'
'Item-2' has 'Tag-3', 'Tag-5'
...

我使用的是PostgreSQL 10，所以实际上有两个问题：

1. 如何使用高效的 SQL查询从这样的模式检索这样的数据？
2. 也许可以重新设计数据的模式，以便更有效地处理这种情况？也许我应该使用数组，HStore，JSONB？

Answer 1

您的模式对于多到多的关系是好的。

您只需要添加主键和外键约束。

要查询所需的数据，只需根据三个表的自然连接条件加入它们。

要获得聚合列表，请使用聚合函数(如string_agg和group items.title )。