时间戳

首页 > 解决方案 > 优化查找好友的查询

sql - 优化查找好友的查询

问题描述

我有一张代表成对人之间关系的表格。

user_relating | user_related | relation_type 
--------------------------------------------
     1        |       2      |      1      --> means user 1 follows user 2

我需要找到特定用户 X 的所有朋友。友谊意味着两个人互相关注。因此，如果用户 A 关注用户 B、C。并且用户 B、C 关注 A，则 B、C 是 A 的朋友。

我写了这个查询：

SELECT users.* -- user's followers
FROM users
JOIN user_relations rel
ON users.id = rel.user_relating AND user_related = 2

INTERSECT

SELECT users.* -- who the user follows
FROM users
JOIN user_relations rel
ON users.id = rel.user_related AND user_relating = 2;

但我认为这是低效的。有没有更优化的方法来完成这项工作？

我试过做这样的事情：

SELECT DISTINCT f.*
FROM users f
JOIN user_relations u1 
    on f.id = u1.user_related
JOIN user_relations u2 
    on f.id = u2.user_relating

WHERE u1.user_related = 2 
   or u2.user_related = 2;

通过 EXPLAIN ANALYZE 判断它似乎更有效（虽然我只有一个非常小的表，比如 10 行，所以我不确定这是一个很好的衡量标准）。

但这里的问题是它也会返回有问题的用户。意思是，如果我想要 User B 的朋友，那么这个查询会返回 User B 和他所有的朋友。我可以以某种方式从查询结果中排除用户 B 吗？

而且，如前所述，我很高兴收到一些关于执行此类查询的最优化和最有效方法的想法。

标签： sqlpostgresql

解决方案

可能最有效的方法是：

select ur.*
from user_relations ur
where ur.user_relating < ur.user_related and
      ur.relation_type = 1 and
      exists (select 1
              from user_relations ur2
              where ur2.user_relating = ur.user_related and
                    ur2.user_related = ur.user_relating and
                    ur2.relation_type = 1
             );

并且为了性能，您需要在user_relations(user_relating, user_related, relationship_type).

也就是说，这与您的版本类似join，但不需要删除重复项。

如果您有许多不同的关系类型，那么该列上的索引也会有所帮助。

编辑：

如果您有特定用户并想要他们的朋友：

select (case when user_relating = 2 then user_related else user_relating end) as user_friend
from user_relations ur
where ur.user_relating < ur.user_related and
      ur.relation_type = 1 and
      exists (select 1
              from user_relations ur2
              where ur2.user_relating = ur.user_related and
                    ur2.user_related = ur.user_relating and
                    ur2.relation_type = 1
             ) and
      2 in (ur.user_relating, user_related)

推荐阅读