mysql - 如何优化每个用户的自定义搜索结果

Question

假设我们有以下场景，2 个实体；用户、图像。
用户可以喜欢图像，也可以互相关注。 (所以我们有 2 个关系表，user_likes 和 follow where who likes what，以及 who follow who 被保留)

因此，我们(由用户代表)想要执行搜索来获取我们的 friend 喜欢的名为“cat.jpg”的图像。

在 sql 中类似的东西看起来像

SElECT DISTINCT(images.id) 
FROM images 
JOIN likes ON likes.image_id = images.id 
JOIN 
  (SELECT follow.following_id 
   FROM follow 
   WHERE follow.follower_id = MY_ID) as following 
 ON following.following_id = likes.user_id 
WHERE images.name = "cat.jpg"
ORDER BY images.date DESC
LIMIT 0, 20

上面的查询将返回我们关注的用户喜欢的图像的 20 个最新唯一 ID，这些图像的名称为“cat.jpg”。

我的问题是...如何优化这个过程？

我想到的第一个想法是缓存，但是如果另一个用户搜索“cat.jpg”，他/她将得到不同的结果(因为他/她将关注一组不同的用户)。因此，在这种特定场景中缓存似乎成本高昂，因为可能存在大量可能的搜索关键字和大量用户关注用户组合。这是一个可行的解决方案吗？如果该用户不再搜索“cat.jpg”，那么缓存响应只会浪费内存。

一般来说，我见过有人建议使用 Redis 甚至 Memcached 来存储每个用户的更新列表或社交提要条目，但在搜索场景中，这样的事情似乎不够。不？

非常感谢任何建议、提示或讨论类似问题和方法的资源链接!

Answer 1

这是您的查询(使用表别名进行简化):

SElECT DISTINCT i.id
FROM images i JOIN
     likes l
     ON l.image_id = i.id JOIN 
     (SELECT f.following_id 
      FROM follow f
      WHERE f.follower_id = MY_ID
     ) as f 
    ON f.following_id = l.user_id 
WHERE i.name = 'cat.jpg'
ORDER BY i.date DESC
LIMIT 0, 20;

怎样才能让它运行得更快？好吧，首先，不需要子查询:

SElECT DISTINCT i.id
FROM images i JOIN
     likes l
     ON l.image_id = i.id JOIN 
     follow f
     ON f.following_id = l.user_id and
        f.follower_id = MY_ID
WHERE i.name = 'cat.jpg'
ORDER BY i.date DESC
LIMIT 0, 20;

其次，以下索引可能有助于提高性能:

images(name, date);
likes(image_id, user_id);
follow(user_id, follower_id);