我应该如何使用Rails索引和查询连接表？

I have a ruby on Rails 4 app, using devise and with a User model and a Deal model.

我在Rails 4应用程序上有一个ruby,使用设计,用户模型和交易模型。

I am creating a user_deals table for has_many/has_many relationship between User and Deal.

我正在为User和Deal之间的has_many / has_many关系创建一个user_deals表。

Here is the migration

这是迁移

class CreateUserDeals < ActiveRecord::Migration

  def change
    create_table :user_deals do |t|
        t.belongs_to :user
      t.belongs_to :deal
      t.integer         :nb_views

      t.timestamps
    end
  end
end

When a user load a Deal (for example Deal id= 4), I use a method called show

当用户加载Deal(例如Deal id = 4)时,我使用名为show的方法

controllers/deal.rb
#for the view of the Deal page
def show
end

In the view of this Deal id=4 page, I need to display the nb of views of the Devise's current_user inside the Deal page the user is currently on.

在此Deal id = 4页面的视图中,我需要在用户当前所在的交易页面内显示Devise的current_user的nb个视图。

deal/show.html

here is the nb of views of user: <% current_user.#{deal_id}.nb_views%>

Lets' say I have 10M+ user_deals lines, I wanted to know if I should use an index

让我们说我有10M + user_deals行,我想知道我是否应该使用索引

add_index :user_deals, :user_id
add_index :user_deals, :deal_id

or maybe

add_index(:deals, [:user_id, deal_id])

Indeed in other situations I would have said Yes, but here I don't know how Rails works behind the scenes. It feels as if Rails is aware of what to do without me needing to speed up the process,...as if when Rails loads this view that there is no SQL query (such as 'find the nb of views WHERe user_id= x and deal_id= Y')....because I'm using just for the current_user who is logged-in (via devise's current_user) and for deal_id Rails knows it as we are on the very page of this deal (show page) so I just pass it as a parameter.

确实在其他情况下我会说是,但在这里我不知道Rails如何在幕后工作。感觉好像Rails在没有我需要加速进程的情况下知道该做什么,......好像当Rails加载这个视图时没有SQL查询(例如'找到视图的nb,而用户_id = x和deal_id = Y')....因为我只使用登录的current_user(通过devise的current_user)和for deal_id Rails知道它,因为我们在这笔交易的页面上(显示页面)所以我只需将其作为参数传递。

So do I need an index to speed it up or not?

那么我需要一个索引来加快速度吗?

3 个解决方案

#1

Your question on indexes is a good one. Rails does generate SQL* to do its magic so the normal rules for optimising databases apply.

你对索引的问题很好。 Rails确实生成SQL *以实现其魔力,因此适用于优化数据库的常规规则。

The magic of devise only extends to the current_user. It fetches their details with a SQL query which is efficient because the user table created by devise has helpful indexes on it by default. But these aren't the indexes you'll need.

设计的魔力只延伸到current_user。它使用SQL查询获取其详细信息,这是有效的,因为默认情况下由devise创建的用户表具有有用的索引。但这些不是您需要的索引。

Firstly, there's a neater more idiomatic way to do what you're after

首先,有一种更为惯用的方式来做你想要的事情

class CreateUserDeals < ActiveRecord::Migration
  def change
    create_join_table :users, :deals do |t|
      t.integer :nb_views
      t.index [:user_id, :deal_id]
      t.index [:deal_id, :user_id]
      t.timestamps
    end
  end
end

You'll notice that migration included two indexes. If you never expect to create a view of all users for a given deal then you won't need the second of those indexes. However, as @chiptuned says indexing each foreign key is nearly always the right call. An index on an integer costs few write resources but pays out big savings on read. It's a very low cost default defensive position to take.

您会注意到迁移包括两个索引。如果您从未期望为给定的交易创建所有用户的视图,那么您将不需要第二个索引。但是,正如@chiptuned所说,索引每个外键几乎总是正确的。整数的索引会花费很少的写入资源,但会大大节省读取费用。这是一个非常低成本的默认防守阵地。

You'll have a better time and things will feel clearer if you put your data fetching logic in the controller. Also, you're showing a deal so it will feel right to make that rather than current_user the center of your data fetch.

如果将数据提取逻辑放在控制器中,您将获得更好的时间,事情会更清晰。此外,您正在展示一笔交易,因此您可以将其作为数据提取的中心而不是current_user。

You can actually do this query without using the through association because you can do it without touching the users table. (You'll likely want that through association for other circumstances though.) Just has_many :user_deals will do the job for this.

实际上,您可以在不使用直通关联的情况下执行此查询,因为您可以在不触及users表的情况下执(你可能希望通过关联来解决其他情况。)只需要has_many:user_deals就可以完成这项任务。

To best take advantage of the database engine and do this in one query your controller can look like this:

为了最好地利用数据库引擎并在一个查询中执行此操作,您的控制器可能如下所示:

def show
  @deal = Deal.includes(:user_deals)
              .joins(:user_deals)
              .where("User_deals.user_id = ?", current_user.id)
              .find(params["deal_id"])
end

Then in your view...

那么在你看来......

I can get info about the deal: <%= @deal.description %>

And thanks to the `includes` I can get user nb_views without a separate SQL query: 
<%= @deal.user_deals.nb_views %>

* If you want to see what SQL rails is magically generating just put .to_sql on the end. e.g. sql_string = current_user.deals.all.to_sql

*如果你想看看什么SQL rails神奇地生成只是把.to_sql放在最后。例如sql_string = current_user.deals.all.to_sql

#2

Yes, you should use an index to speed up the querying of the user_deals records. Definitely at least on user_id, but probably both [:user_id, :deal_id] as you stated.

是的,您应该使用索引来加速查询user_deals记录。绝对至少在user_id上,但可能正如你所说的那样[:user_id,:deal_id]。

As for why you don't see a SQL query...

至于为什么你没有看到SQL查询......

First off, your code in the view appears to be incorrect. Assuming you have set up a has_many :deals, through: :user_deals association on your User class, it should be something like:

首先,视图中的代码似乎不正确。假设您已经在User类上设置了has_many:deals,通过:: user_deals关联,它应该是这样的:

here is the nb of views of user: <%= current_user.deals.find(deal_id).nb_views %>

这是用户的nb个视图:<%= current_user.deals.find(deal_id).nb_views%>

If you see the right number showing up for nb_views, then a query should be made when the view is rendered unless current_user.deals is already being loaded earlier in the processing or you've got some kind of caching going on.

如果您看到正确的数字显示nb_views,那么在呈现视图时应该进行查询,除非在处理的早期已经加载了current_user.deals,或者您正在进行某种缓存。

If Rails is "aware", there is some kind of reason behind it which you should figure out. Expected base Rails behavior is to have a SQL query issued there.

如果Rails“意识到”,那么你应该弄清楚它背后的某种原因。预期的基本Rails行为是在那里发布一个SQL查询。

#3

Is a cleaner way of indexing other tables not:

是一种更简洁的索引其他表的方法:

class CreateUserDeals < ActiveRecord::Migration

   def change
      create_table :user_deals do |t|
         t.references :user
         t.references :deal
         t.integer :nb_views

         t.timestamps
      end
   end
end

#1