Ruby on Rails 中多列的索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6169996/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Index on multiple columns in Ruby on Rails
提问by Emil Ahlb?ck
I'm implementing functionality to track which articles a user has read.
我正在实施功能来跟踪用户阅读了哪些文章。
create_table "article", :force => true do |t|
t.string "title"
t.text "content"
end
This is my migration so far:
到目前为止，这是我的迁移：
create_table :user_views do |t|
t.integer :user_id
t.integer :article_id
end
The user_views table will always be queried to look for both columns, never only one. My question is how my index should look like. Is there a difference in the order of these tables, should there be some more options to it or whatever. My target DB is Postgres.
将始终查询 user_views 表以查找两列，从不只查找一列。我的问题是我的索引应该是什么样子。这些表格的顺序是否有区别，是否应该有更多选项或其他什么。我的目标数据库是 Postgres。
add_index(:user_views, [:article_id, :user_id])
Thanks.
谢谢。
UPDATE:
Because only one row containing the same values in both columns can exist (since in knowing if user_id HAS read article_id), should I consider the :unique option? If I'm not mistaken that means I don't have to do any checking on my own and simply make an insert every time a user visits an article.
更新：
因为在两列中只能存在包含相同值的一行（因为知道 user_id 是否已读取 article_id），我应该考虑 :unique 选项吗？如果我没记错的话，那意味着我不需要自己做任何检查，只需在用户每次访问文章时插入。
回答by sscirrus
The order does matter in indexing.
顺序在索引中很重要。
- Put the most selective field first, i.e. the field that narrows down the number of rows fastest.
- The index will only be used insofar as you use its columns in sequence starting at the beginning. i.e. if you index on
[:user_id, :article_id], you can perform a fast query onuser_idoruser_id AND article_id, but NOT onarticle_id.
- 将最具选择性的字段放在最前面，即最快缩小行数的字段。
- 只有当您从开头开始按顺序使用其列时，才会使用索引。即，如果您建立索引
[:user_id, :article_id]，则可以对user_idor执行快速查询user_id AND article_id，但不能对执行快速查询article_id。
Your migration add_indexline should look something like this:
您的迁移add_index行应如下所示：
add_index :user_views, [:user_id, :article_id]
Question regarding 'unique' option
关于“唯一”选项的问题
An easy way to do this in Rails is to use validatesin your model with scoped uniquenessas follows (documentation):
在 Rails 中执行此操作的一种简单方法是validates在您的模型中使用范围uniqueness如下（文档）：
validates :user, uniqueness: { scope: :article }
回答by olivier
Just a warning about checking uniqueness at validation time vs. on index: the latter is done by database while the primer is done by the model. Since there might be several concurrent instances of a model running at the same time, the validation is subject to race conditions, which means it might fail to detect duplicates in some cases (eg. submit twice the same form at the exact same time).
关于在验证时检查唯一性与在索引上检查唯一性的警告：后者由数据库完成，而引物由模型完成。由于一个模型可能有多个并发实例同时运行，因此验证会受到竞争条件的影响，这意味着在某些情况下它可能无法检测到重复项（例如，在完全相同的时间提交两次相同的表单）。
