何时在 Rails 中的表中添加哪些索引

2021-12-23 00:00:00 ruby-on-rails mysql activerecord

我有一个关于 Rails 数据库的问题.

  • 我是否应该为所有外键添加索引"，例如xxx_id"?
  • 我应该在自动创建的id"列中添加index"吗?

  • 是否应该在自动创建的id"列中添加index(unique)"?

  • 如果我一次为两个外键添加索引 (add_index (:users, [:category, :state_id])，会发生什么?这与添加索引有什么不同?每个键?

    class CreateUsers 真的 ?add_index :users, :id# 我不认为我需要 ":unique => true here"，对吗?add_index :users, :category_id # 我需要这个吗?add_index :users, :state_id # 我需要这个吗?# 上面的和下面的一样吗?add_index (:users, [:category, :state_id])结尾结尾

<小时>

到目前为止很好的答案.补充问题.

  • 我应该为 xxx_id 添加具有唯一性的索引"，对吗?

解决方案

我是否应该为所有外键添加索引"，例如xxx_id"?

这样会更好，因为它可以加快该列中排序的搜索速度.外键是经常搜索的东西.

从 rails 的第 5 版开始，索引将自动创建，有关更多信息，请参阅此处.

<块引用>

我应该在自动创建的id"列中添加index"吗?

不，这已经由 rails 完成了

<块引用>

是否应该在自动创建的id"列中添加index(unique)"?

不，同上

<块引用>

如果我一次为两个外键添加索引 (add_index (:users, [:category_id, :state_id])，会发生什么?这与为每个键添加索引有什么不同?

那么索引就是两列的组合索引.这没有任何意义，除非您想要一个 category_id AND 一个 state_id 的所有条目(它应该是 category_idcode> 而不是 category) 同时.

这样的索引会加快以下请求的速度:

# rails 2User.find(:all, :conditions => { :state_id => some_id, :category_id => some_other_id })# 轨道 3User.where(:state_id => some_id, :category_id => some_other_id)

哪里

add_index :users, :category_idadd_index :users, :state_id

将加速这些请求:

# rails 2+3User.find_by_category_id(some_id)User.find_by_state_id(some_other_id)# 或者# 轨道 2User.find(:all, :conditions => {:category_id => some_id})User.find(:all, :conditions => {:state_id => some_other_id})# 轨道 3User.where(:category_id => some_id)User.where(:state_id => some_other_id)

<块引用>

我应该为 xxx_id 添加具有唯一性的索引"，对吗?

不，因为如果你这样做，一个类别只能有一个用户，但类别的意思是你可以把更多的许多用户放在一个类别中.在您的 User 模型中，您有类似 belongs_to :category 的内容，而在您的 Category 模型中，有类似 has_many :users 的内容.如果您有 has_many 关系，则 foreign_key 字段不能是唯一的！

有关这方面的更多详细信息，您应该查看 tadman 的精彩 答案.

I have a question about Rails database.

  • Should I add "index" to all the foreign keys like "xxx_id"?
  • Should I add "index" to the automatically created "id" column?

  • Should I add "index(unique)" to the automatically created "id" column?

  • If I add index to two foreign keys at once (add_index (:users, [:category, :state_id]), what happens? How is this different from adding the index for each key?

    class CreateUsers < ActiveRecord::Migration
  def self.up
    create_table :users do |t|
      t.string :name
      t.integer :category_id 
      t.integer :state_id
      t.string :email
      t.boolean :activated
      t.timestamps
    end
  # Do I need this? Is it meaningless to add the index to the primary key?
  # If so, do I need :unique => true ?
  add_index :users, :id 
  # I don't think I need ":unique => true here", right?
  add_index :users, :category_id # Should I need this?
  add_index :users, :state_id # Should I need this?
  # Are the above the same as the following?
  add_index (:users, [:category, :state_id])
  end
end

Great answer so far. Additional question.

  • I should add "index with unique" for xxx_id, right?

解决方案

Should I add "index" to all the foreign keys like "xxx_id"?

It would be better, because it accelerates the search in sorting in this column. And Foreign keys are something searched for a lot.

Since Version 5 of rails the index will be created automatically, for more information see here.

Should I add "index" to the automatically created "id" column?

No, this is already done by rails

Should I add "index(unique)" to the automatically created "id" column?

No, same as above

If I add index to two foreign keys at once (add_index (:users, [:category_id, :state_id]), what happens? How is this different from adding the index for each key?

Then the index is a combined index of the two columns. That doesn't make any sense, unless you want all entries for one category_id AND one state_id (It should be category_id not category) at the same time.

An Index like this would speed the following request up:

# rails 2
User.find(:all, :conditions => { :state_id => some_id, :category_id => some_other_id })

# rails 3
User.where(:state_id => some_id, :category_id => some_other_id)

Where

add_index :users, :category_id
add_index :users, :state_id

will speed up these requests:

# rails 2+3
User.find_by_category_id(some_id)
User.find_by_state_id(some_other_id)

# or
# rails 2
User.find(:all, :conditions => {:category_id => some_id})
User.find(:all, :conditions => {:state_id => some_other_id})

# rails 3
User.where(:category_id => some_id)
User.where(:state_id => some_other_id)

I should add "index with unique" for xxx_id, right?

No, because if you do this, only one user can be in one category, but the meaning of category is that you can put more many user into one category. In your User model you have something like this belongs_to :category and in your Category model something like has_many :users. If you have a has_many relationship the foreign_key field must not be unique!

For more detailed information on this you should take a look at tadman's great answer.

相关文章