sql - 尽管有缓存，搜索查询的性能还是被共同好友限制了 98%

Question

所以在我的社交网站上，类似于 facebook，我的搜索速度被这一部分限制了 98%。我想根据搜索用户拥有的共同 friend 的数量对结果进行排名，所有结果(我们可以假设他们是用户)

我的 friend 表有 3 列 -

• user_id(发送请求的人)
• friend_id(收到请求的人)
• pending( bool 值表示请求是否被接受)

user_id 和 friend_id 都是引用 users.id 的外键

查找用户的 friend_ids 很简单，看起来像这样

def friends
  Friend.where(
    '(user_id = :id OR friend_id = :id) AND pending = false',
     id: self.id
  ).pluck(:user_id, :friend_id)
   .flatten
   .uniq
   .reject { |id| id == self.id }
end

因此，在获得与搜索查询匹配的结果后，将结果按共同好友排名，需要执行以下步骤-

• 获取所有搜索用户的 friend 的 user_ids - Set(A)。上面提到的 friend 方法就是这样做的
• 遍历 Set(A) 中的每个 id -
  • 获取|id|的所有好友的user_ids - 设置 (B)。同样，通过 friend 的方法完成
  • 求集合A和集合B的交集长度
• 对所有结果按交集长度降序排列

这里最昂贵的操作显然是获取数百个用户的 friend_ids。所以我缓存了所有用户的 friend_ids 以加快速度。性能上的差异是惊人的，但我很好奇它是否可以进一步改进。

我想知道是否有一种方法可以在单个查询中获取所有所需用户的 friend_ids，这种方法非常有效。有点像 -

SELECT user_id, [array of friend_ids of the user with id = user_id]
FROM friends
....

谁能帮我写一个快速的 SQL 或 ActiveRecord 查询？

这样我就可以将所有搜索结果的 user_id 及其对应的 friend_id 存储在哈希或其他一些快速数据结构中，然后执行相同的排名操作(我在上面提到过)。因为我不会为成千上万的用户和他们的 friend_ids 访问缓存，所以我认为它会显着加快这个过程

Answer 1

如果您希望您的站点增长到大量用户，那么在 RAM 中缓存您的 friends 表不是一个可行的方法，但我确信它对少量用户非常有用。

以尽可能少的调用从数据库中获得最多的工作对您有利。发出大量查询是低效的，因为每个查询的开销相对较大。此外，数据库是为您要执行的任务类型而构建的。我认为您在 Ruby 方面做的工作太多了，您应该让数据库做它最擅长的工作。

你没有提供很多细节，所以我决定从定义一个最小模型数据库开始:

create table users (
  user_id int not null primary key,
  nick varchar(32)
  );

create table friends (
  user_id int not null,
  friend_id int not null,
  pending bool,
  primary key (user_id, friend_id),
  foreign key (user_id) references users(user_id),
  foreign key (friend_id) references users(user_id),
  check (user_id < friend_id)
  );

friends 上的check 约束避免了同一对用户以两种顺序列在表中，当然 PK 防止同一对用户被多次注册次序相同。 PK 还自动具有与之关联的唯一索引。

因为我假设“是……的 friend ”关系应该是逻辑对称的，所以定义一个呈现这种对称性的 View 很方便:

create view friends_symmetric (user_id, friend_id) as (
  select user_id, friend_id from friends where not pending
  union all
  select friend_id, user_id from friends where not pending
  );

(如果友谊不是对称的，那么您可以删除检查约束和 View ，并使用表friends 代替friends_symmetric以下内容。)

作为你想要对其结果进行排名的模型查询，那么，我采取这个:

select * from users where nick like 'Sat%';

目标是按每次命中与 User1(代表其运行查询的用户)共有的 friend 数的降序返回结果行。你可以这样做:

(更新:修改了这个查询以过滤掉重复的结果)

select *
from (
    select
      u.*,
      count(mutual.shared_friend_id) over (partition by u.user_id) as num_shared,
      row_number() over (partition by u.user_id) as copy_num
    from 
      users u
      left join (
          select
            f1.friend_id as shared_friend_id,
            f2.friend_id as friend_id
          from friends_symmetric f1
            join friends_symmetric f2
              on f1.friend_id = f2.user_id
          where f1.user_id = ?
            and f2.friend_id != f1.user_id
        ) mutual
        on u.user_id = mutual.friend_id
    where u.nick like 'Sat%'
  ) all_rows
where copy_num = 1
order by num_shared desc

其中 ? 是包含 User1 ID 的参数的占位符。

---

编辑添加:

我用窗口函数而不是聚合查询来构造这个查询，因为这样的结构对于查询规划器来说更容易优化。然而，内联 View “相互”可以改为构造为聚合查询，该查询计算搜索用户与每个共享至少一个 friend 的用户拥有的共享 friend 的数量，并且这将允许避免一级内联 View .如果所提供查询的性能不足或变得不足，则值得测试该变体。

---

还有其他方法可以解决在数据库中执行排序的问题，其中一些可能表现更好，并且可能有一些方法可以通过调整数据库(添加索引或约束、修改表定义)来提高每种方法的性能，计算数据库统计信息，...)。

我无法预测该查询是否会优于您现在正在执行的查询，但我向您保证它的扩展性更好，并且更易于维护。