如何正确索引 Rails 连接表迁移上的字段? [英] How to properly index fields on a Rails join table migration?

问题描述

Rails 4 引入了一个用于生成连接表迁移的功能:

Rails 4 introduced a feature for generating join table migrations:

bin/rails generate migration CreateTeamsUsersJoinTable team user

这会产生以下文件:

class CreateTeamsUsersJoinTable < ActiveRecord::Migration
  def change
    create_join_table :teams, :users do |t|
      # t.index [:team_id, :user_id]
      # t.index [:user_id, :team_id]
    end
  end
end

您可以看到索引被注释掉了.为什么是这样?我应该决定要使用哪一个吗?或者他们是否暗示索引是可选的?我想确保每个字段都有某种索引以避免性能问题，但我不确定如何正确定义这些字段的索引.

You can see that the indices are commented out. Why is this? Am I supposed to make a decision about which one I want to use? Or are they implying that indices are optional? I want to make sure there is some sort of index on each field to avoid performance problems, but I'm not sure how to properly define the indices for these fields.

推荐答案

要完成 xlembouras 的回答，您可以根据查询表的方式做出选择.

To complete xlembouras answer, you make your choice based on how you query your table.

例如，如果您有一个视图显示给定团队的用户，查询将如下所示:

For example, if you have a view that shows the users of a given team, the query will look like this :

SELECT user.name FROM team_users tu 
INNER JOIN users u 
ON tu.user_id = u.id
WHERE tu.team_id = X

这得益于 t.index [:team_id, :user_id] 而不是 t.index [:user_id, :team_id]

数据库只会在第一个索引上使用一个索引查找来检索用户 ID

The database will only use one index seek on the first index to retrieve the user ids

如果您有一个视图显示给定用户的团队，则查询将类似于以下内容:

If you have a view that shows the teams of a given user, the query would be something like the following :

SELECT team.name FROM team_users tu 
INNER JOIN team t 
ON tu.team_id = t.id
WHERE u.user_id = X

这得益于 t.index [:user_id, :team_id] 而不是 t.index [:team_id, :user_id]

数据库将只在第一个索引上使用一个索引查找来检索团队 ID

The database will only use one index seek on the first index to retrieve the team ids

这是因为组合索引的存储方式:

That's because of the way composed index are stored :

这篇关于如何正确索引 Rails 连接表迁移上的字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！