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

When to add what indexes in a table in Rails

提问by TK.

I have a question about Rails database.

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

• 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
  

• 我应该向所有外键添加“索引”，例如“xxx_id”吗？
• 我应该在自动创建的“id”列中添加“index”吗？

• 我应该将“index(unique)”添加到自动创建的“id”列吗？

• 如果我一次为两个外键添加索引 ( 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 添加“具有唯一性的索引”，对吗？

回答by jigfox

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.

会更好，因为它加速了该列中排序的搜索。外键是经常搜索的东西。

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

由于 Rails 版本 5 将自动创建索引，有关更多信息，请参见此处。

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

我应该在自动创建的“id”列中添加“index”吗？

No, this is already done by rails

不，这已经由 rails 完成了

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

我应该将“index(unique)”添加到自动创建的“id”列吗？

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_idANDone state_id(It should be category_idnot category) at the same time.

那么索引就是两列的组合索引。这没有任何意义，除非您希望同时为一个category_idAND一个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 manyuser into one category. In your Usermodel you have something like this belongs_to :categoryand in your Category model something like has_many :users. If you have a has_manyrelationship the foreign_keyfield must not be unique!

不，因为如果你这样做，只有一个用户可以在一个类别，但类别的意思是，你可以把更多的许多用户归为一类。在你的User模型中，你有这样的东西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.

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

回答by tadman

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_manyrelationship pairing, you need to have an index on the foreign key used.
• 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.
• If you have a has_many :throughrelationship, your join table should have a unique index on both properties involved in the join as a compound key.
• 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_manyrelationship using a scope, make sure there's an index that includes the has_manyforeign key and the scope column in that order.

• 如果您有belongs_to-has_many关系配对，则需要在使用的外键上有一个索引。
• 如果您对记录进行排序，并且其中有大量将被分页，则应将该排序列添加到索引的末尾。
• 如果您有has_many :through关系，则您的连接表应该在连接中涉及的两个属性上都有一个唯一索引作为复合键。
• 如果您直接使用唯一标识符（例如用户名或电子邮件）获取记录，则应该是唯一索引。
• 如果您has_many使用范围从关系中获取记录集，请确保有一个索引，该索引has_many按该顺序包含外键和范围列。

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 WHEREor HAVINGconditions and ORDER BYclauses are represented in that order.

简单来说，查看应用程序生成的查询，并确保WHERE或HAVING条件和ORDER BY子句中引用的列按该顺序表示。

回答by Will Taylor

• 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)
• 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:

• 总是索引外键
• 始终索引您要排序的列
• 所有独特的字段（以确保唯一性在数据库级别实例迁移。add_index :users, :email, unique: true）
• 如果你按两个东西排序，或者按两个东西搜索，例如: order by [a, b]or find where( a and 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/using-indexes-in-rails-choosing-additional-indexes, rails - created_at 当用户订购时，是否应该向表中添加索引？，以及上面的答案。