Rails：如何最大限度地减少数据库命中率？急切加载不适用

this question is maybe a little specific, but I think it's interesting from a general pov also.

这个问题可能有点具体,但我认为一般的pov也很有趣。

In a Rails App users can subscribe to other users. When I show a list of users I have to check, if the current user has subscribed to the users in the list. If he has subscribed, I show the unsubscribe button and the other way around.

在Rails App中,用户可以订阅其他用户。当我显示我必须检查的用户列表时,如果当前用户已订阅列表中的用户。如果他订阅了,我会显示取消订阅按钮,反之亦然。

Because the whole thing depends on the current user I can't use eager loading. So when I show 20 users in the list, I generate 20 additional hits on the DB, which appears to me to be bad practice.

因为整个事情取决于当前用户我不能使用急切加载。因此,当我在列表中显示20个用户时,我会在数据库上生成20个额外的点击,这在我看来是不好的做法。

I'm thinking about a good way to solve this problem. The best solution I came up with so far is to load the ids of the users the current_user has subscribed to in the session during the login once and then just check every user.id against the ids in the session. But maybe this could lead to other issues when the user has subscribed to a lot of people. Also I'm not sure if it's the best way to load all subscriptions even though the user might never look at the user list during this session.

我正在考虑解决这个问题的好方法。到目前为止,我提出的最佳解决方案是在登录期间加载current_user在会话中订阅的用户的ID,然后只检查每个user.id与会话中的ID。但是,当用户订阅了很多人时,这可能会导致其他问题。此外,我不确定这是否是加载所有订阅的最佳方式,即使用户可能永远不会在此会话期间查看用户列表。

The next best thing which came to my mind was to do the same thing, but not during login but instead when a user-list is loaded.

我想到的最好的事情是做同样的事情,但不是在登录期间,而是在加载用户列表时。

What do you think?

你怎么看?

2 个解决方案

#1

You should definitely start working on a cache system. There are at least 3 ways you can follow. You can also combine them to get more efficiency.

你一定要开始使用缓存系统。您可以遵循至少3种方式。您还可以将它们组合起来以获得更高的效率。

Database caching

Create a relationship table to hold the ID relationships between the user and the subscribers so that you don't need to calculate them on the fly.

创建一个关系表来保存用户和订户之间的ID关系,这样您就不需要动态计算它们。

Model Caching

Expensive queries can be cached.

可以缓存昂贵的查询。

def find_subscribers
  Rails.cache.fetch("find_subscribers_#{current_user}") do
    # run the query
  end
end

View Caching

You can also cache view fragments to prevent expensive elaborations.

您还可以缓存视图片段以防止昂贵的细节。

You might want to start with:

您可能想要从:

Rockstar Memcaching
Scaling Rails Videos

缩放Rails视频

EDIT:

You query can be optimized.

您可以优化查询。

ActiveRecord::Base.connection.execute("SELECT count(*) as c FROM subscribers_users WHERE user_id = #{other_user.id} AND subscriber_id = #{self.id}")

can become

counters = SubscribersUser.count(:conditions => { :subscriber_id => self.id }, :group => "user_id")

The query will return a Hash where the key is the user_id and the value the result of count. Then you can iterate the hash instead of running a query for any record in the view.

查询将返回一个Hash,其中键是user_id,值是count的结果。然后,您可以迭代哈希,而不是为视图中的任何记录运行查询。

#2

There's no rule against reloading the current user if it allows you to use eager loading:

如果允许您使用预先加载,则没有规则可以重新加载当前用户:

user = User.find(current_user.id, :include => :subscribers)

user = User.find(current_user.id,:include =>:subscriber)

Provided this is a simple has_many relation this is only 2 SQL statements.

如果这是一个简单的has_many关系,那么这只是2个SQL语句。

Now you can iterate over user.subscribers without producing another DB hit.

现在,您可以迭代user.subscribers而不会产生另一个数据库命中。

#1