首页 文章

在多对多关系中进行复杂搜索的最快SQL表达式？

提问于
浏览 次
3

在product_tag表中，列是

id，product_id，tag_id

如果我想搜索tag1或tag2 OR tag3的产品，直接的方法是：

SELECT DISTINCT productId FROM product_tags WHERE tagId IN (2,4);

如果我想搜索tag1 AND tag2 AND tag3的产品，直接的方法是：

SELECT productId FROM product_tag WHERE tag_id IN (tag1, tag2, tag3) GROUP BY productId HAVING COUNT(*) = 3

但问题是我是否想要搜索具有复杂标签关系的产品，例如：

产品是（tag1 OR tag2 OR tag3）AND（tag 4 OR tag5 OR tag 6）AND（tag 7 OR tag8 OR tag9）

What is the SQL expression with best performance? (and preferably elegant).

编辑：
最重要的性能提升是添加索引，如评论中推荐的Remus .

sql performance many-to-many

5 回答

  • 1

    性能不会那么好但你可以做一个嵌套查询

    SELECT 
ProductID FROM
Products 
WHERE tag_id IN (tag1, tag2, tag3)
AND ProductID IN (
SELECT 
ProductID FROM
Products 
WHERE tag_id IN (tag4, tag5, tag6)
)
AND ProductID IN (
SELECT 
ProductID FROM
Products 
WHERE tag_id IN (tag7, tag8, tag9)
)
    回复于
  • 0

    您实际上无法使用基于集合的语言（如SQL）直接执行此操作 .

    除非您没有（productId，tagId）的重复项，否则您的简单“AND”版本也将无效 .

    对于复杂的关系，有必要将查询分成几个子查询 . 首先打破所有“AND”条款：

    WHERE tag_id IN (tag1, tag2, tag3)
WHERE tag_id IN (tag4, tag5, tag6)
WHERE tag_id IN (tag7, tag8, tag9)

    然后对查询结果进行交叉检查 .

    如果这些子查询中的任何一个不是简单的OR列表，而是在更复杂的逻辑结构中包含AND，则需要进一步递归地分解这些子查询 .

    换句话说，您可以沿“AND”子句递归地分解逻辑树，然后在每个树级别执行查询结果的INTERSECT .

    这样做可能比生成一次性返回结果的巨大SQL要快得多 - 因为每个简单的OR列表都可以利用tag_id上的索引 .

    回复于
  • 0

    联盟所有3组 . 他们是3选，但他们真的很简单 .

    回复于
  • 1

    我注意到Select values that meet different conditions on different rows?

    怎么样

    SELECT DISTINCT t1.productId FROM product_tags t1
JOIN product_tags t2 ON t1.productId=t2.productId AND t2.tagId IN (tag4,tag5,tag6)
JOIN product_tags t3 ON t1.productId=t3.productId AND t3.tagId IN (tag7, tag8, tag9)
AND t1.tagId IN (tag1,tag2,tag3)

    如果以某种方式删除 DISTINCT 会更好 .

    回复于
  • 0

    标签数量是否已预先知道？如果它不会随着时间的推移而增长，我会将tag_id更改为bitset .

    WITH T AS 
 (SELECT product_id, bit_or((1<<tag_id)::bigint) tagset 
  FROM product_tag GROUP BY product_id) 
SELECT product_id 
WHERE (tagset & 7)>0 AND (tagset & 56)>0 AND (tagset & 448)>0;

    我在这里使用了Postgres，其中＆被称为按位AND; bit_or是一个聚合函数（假设在product_tag表中没有允许重复，SUM在这里也可以正常工作） . 掩码中的幻数只是两个幂的bit_ors . 双冒号是Postgres演员 . 这里的一切都应该在其他地方略有不同的名称下提供 . 但PG也有不确定大小的位串，并且可以为大量标签实现与位串相同的逻辑 .

    顺便说一句，匹配所有标签的情况只是 (tagset & mask)=mask .

    这实际上是为什么你的指数运作如此之快;它们可能正在合并到这种类型的测试中 .

    回复于

相关问题