mysql - 分离度查询

Question

我有一张成员(member)间关系表。模式是 member_id、friend_id、is_active。我想建立一个 friend 的 friend 的成员联系列表。我不太确定如何处理查询，更不用说以半优化的方式了。

上表的工作方式是 member_id 和 friend_id 在另一个表上基本相同。在我的系统中，这些 id 通常被称为 member_id，除了这个表。例如，假设我的 member_id 是 21。我的号码可以作为 member_id 或 friend_id 在无限数量的其他行上，它要么基于最初发起实际友谊请求的人，要么我不想要冗余数据在哪里我会欺骗行来基本上做同样的事情。

我想要一个查询，我不仅可以确定学位级别(想想 LinkedIn)，而且还可以确定一个人可能有多少共同 friend ，这些 friend 正在显示(想想 Facebook)。这里的 x 因子是我前面提到的 is_active 列。此列可以是 0 或 1。它是一个简单的 tinyint 列，充当开/关开关。任何具有 1 的 friend 连接都将是活跃的友谊，而 0 则处于待定状态。我需要将此查询基于我的活跃 friend 和他们的活跃 friend 等等。我 friend 的活跃 friend 都不是我的活跃 friend 。

我如何构建这样的查询(即使我无法显示分离级别并且只能获得相互计数)？现在，我能想到一些东西，但它涉及一个又一个查询，一些嵌套在循环中，是的，我无法想象随着时间的推移，这对我的服务器的整体性能或健康状况有什么好处。

Answer 1

以下是如何使用 JOIN 使用广度优先、最短路径搜索来执行搜索。这个算法没有任何魔力，因为我们使用 MySQL 来寻找答案，并且我们没有结合任何使用任何启发式或优化的奇特搜索算法。

我的“ friend ”表具有单向关系，因此在存储“1 到 2”和“2 到 1”的意义上我们确实有重复项。我也排除了 is_active 因为实现将是显而易见的:

这是数据:

member_id   friend_id
1           2
1           3
1           4
2           1
2           3
2           5
2           6
3           2
3           1
4           1
5           2
6           2
6           7
7           6
7           8
8           7

我们选择了成员 1，我们要问的是 1 位 friend 和 7 位 friend ， friend 的 friend 等等？计数为 0 表示否，计数为 1 表示是。

SELECT COUNT(*)
FROM friends f1
WHERE f1.member_id = 1
  AND f1.friend_id = 7

如果不是，那么他们是 friend 的 friend 吗？

SELECT COUNT(*)
FROM friends f1
JOIN friends f2
  ON f2.member_id = f1.friend_id
WHERE f1.member_id = 1
  AND f2.friend_id = 7

如果不是，那么 friend 的 friend 的 friend ？

SELECT COUNT(*)
FROM friends f1
JOIN friends f2
  ON f2.member_id = f1.friend_id
JOIN friends f3
  ON f3.member_id = f2.friend_id
WHERE f1.member_id = 1
  AND f3.friend_id = 7

等等……

第三个查询将查找路径“1 到 2”、“2 到 6”和“6 到 7”，返回计数 1。

每个查询都变得更加昂贵(由于连接数量更多)，因此您可能希望在某个时候限制搜索。一件很酷的事情是，这种搜索从两端向中间进行，这是为最短路径搜索建议的一种简单优化。

以下是为成员 1 查找那些共同好友推荐的方法:

SELECT f2.friend_id
FROM friends f1
JOIN friends f2
  ON f2.member_id = f1.friend_id
LEFT JOIN friends f3
  ON f3.member_id = f1.member_id
  AND f3.friend_id = f2.friend_id
WHERE f1.member_id = 1
  AND f2.friend_id <> f1.member_id // Not ourself
  AND f3.friend_id IS NULL // Not already a friend