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

I have a question about Rails database.

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

• Should I add "index" to all the foreign keys like "xxx_id"?
• 我应该向所有外键(如“xxx_id”)添加“索引”吗?
• Should I add "index" to the automatically created "id" column?
• 我应该向自动创建的“id”列添加“索引”吗?

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

  我应该向自动创建的“id”列添加“index(unique)”吗?

• 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?

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

  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?
• 我应该为xxx_id添加“具有惟一性的索引”，对吗?

3 个解决方案

#1

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

我应该向所有外键(如“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.

这样更好，因为它加快了在本专栏中进行排序的搜索。外键是搜索很多的东西。

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

我应该向自动创建的“id”列添加“索引”吗?

No, this is already done by rails

不，这已经由rails完成了

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

我应该向自动创建的“id”列添加“index(unique)”吗?

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?

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

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.

那么索引就是这两列的组合索引。这没有任何意义，除非您希望同时拥有一个category_id和一个state_id的所有条目(它应该是category_id而不是category)。

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?

我应该为xxx_id添加“具有惟一性的索引”，对吗?

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!

不，因为如果你这样做，只有一个用户可以在一个类别中，但是类别的意思是你可以把更多的用户放在一个类别中。在您的用户模型中，您有类似于belongs_to:category和您的类别模型的东西，比如has_many:users。如果您有一个has_many关系，那么foreign_key字段不能是唯一的!

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

关于这个问题的更详细的信息，你应该看看泰德曼的伟大的答案。

#2

Indexing can be a tricky, subtle thing, but there are general rules that apply that can make determining which to use a lot easier.

索引可能是一件棘手的、微妙的事情，但是有一些通用的规则可以使确定使用哪个索引更容易。

The first thing to remember is that indexes can work in more than one way. An index on A, B, C also works for A, B and simply A, so you can design your indexes to be more versatile if you order them correctly. The phone book is indexed on Last Name, First Name, so you can look up people easily by their last name, or a combination of last name and first name. You cannot, however, look them up directly by their first name. You'd need a separate index for that. The same goes for phone number, which you would have to index as well.

首先要记住的是索引可以以多种方式工作。A、B、C上的索引也适用于A、B和简单的A，因此，如果您对索引进行了正确的排序，您可以将索引设计成更通用的索引。电话簿被编入姓氏、名字的索引中，所以你可以很容易地通过人们的姓来查找，或者通过姓氏和名字的组合来查找。然而，你不能直接用他们的名字查他们。你需要一个单独的索引。电话号码也是一样，你也需要索引。

With that in mind, there are many things that will dictate how you create indexes:

考虑到这一点，有很多东西将决定如何创建索引:

• If you have a belongs_to-has_many relationship pairing, you need to have an index on the foreign key used.
• 如果您有一个belongs_to-has_many关系配对，则需要使用外键上的索引。
• If you order your records, and there is a large number of them that will be paginated, you should add that order column to the end of the index.
• 如果您对记录进行排序，并且有大量记录将被分页，那么您应该将order列添加到索引的末尾。
• If you have a has_many :through relationship, your join table should have a unique index on both properties involved in the join as a compound key.
• 如果您有一个has_many:通过关系，您的联接表应该有一个惟一的索引，在连接的两个属性中作为复合键。
• If you fetch a record directly using a unique identifier such as username or email, that should be a unique index.
• 如果您直接使用唯一标识符(如用户名或电子邮件)获取一条记录，那么这应该是唯一索引。
• If you fetch sets of records from a has_many relationship using a scope, make sure there's an index that includes the has_many foreign key and the scope column in that order.
• 如果您使用范围从has_many关系中获取记录集，请确保有一个索引包含has_many外键和按照该顺序的scope列。

The goal with indexes is to eliminate the dreaded "table scan" or "file sort" operations that occur when your data is not indexed properly.

使用索引的目的是消除当数据没有被正确索引时出现的可怕的“表扫描”或“文件排序”操作。

In simple terms, look at the queries being generated by your application and ensure that columns referenced in WHERE or HAVING conditions and ORDER BY clauses are represented in that order.

简单地说，请查看应用程序生成的查询，并确保在WHERE中引用的列或具有子句的条件和顺序的列按该顺序表示。

#3

• Always index foreign keys
• 总指数外键
• Always index columns you will order by
• 总是索引你要订购的列
• All unique fields (to ensure uniqueness at a database level. Example migration: add_index :users, :email, unique: true)
• 所有唯一字段(确保数据库级别的惟一性)。示例迁移:add_index:users，:email, unique: true)
• If you order by two things, or search by two things, for example: order by [a, b] or find where( a and b ), then you need a double index:
• 如果你点两件东西，或者搜索两件东西，例如:order by [a, b]，或者找到where(a和b)，那么你需要一个双重索引:

Concrete example:

具体的例子:

If you have:

如果你有:

default_scope :order => 'photos.created_at DESC, photos.version DESC'

You should add:

你应该添加:

add_index :photos, [:created_at, :version]

Note: An index takes up extra space on the disk and makes it slower to create and update each record, because it has to rebuild each index.

注意:索引占用磁盘上的额外空间，并使创建和更新每个记录的速度变慢，因为它必须重建每个索引。

Credit:

信贷:

https://tomafro.net/2009/08/using-indexes-in-rails-choosing-additional-indexes, rails - created_at when user for ordering, Should you add an Index to the table?, and the answers above.

https://tomafro.net/2009/08/ use -indexes-in- railschoose - additionalindexes rails- created_at当用户订购时，是否应该向表中添加索引?，以及上面的答案。