问题描述
我正在实现跟踪用户阅读了哪些文章的功能.
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
这是我目前的迁移:
create_table :user_views do |t|
t.integer :user_id
t.integer :article_id
end
user_views 表将始终被查询以查找两列,从不只查找一列.我的问题是我的索引应该是什么样子.这些表格的顺序是否有区别,是否应该有更多选项或其他什么.我的目标数据库是 Postgres.
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.
add_index(:user_views, [:article_id, :user_id])
谢谢.
更新:
因为在两列中只能存在包含相同值的一行(因为知道 user_id 是否已读取 article_id),我应该考虑 :unique 选项吗?如果我没记错的话,这意味着我不需要自己做任何检查,只需在每次用户访问文章时插入.
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]
上建立索引,您可以对user_id
或user_id AND article_id
执行快速查询,但不能在article_id
.
- 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_id
oruser_id AND article_id
, but NOT onarticle_id
.
您的迁移 add_index
行应如下所示:
Your migration add_index
line should look something like this:
add_index :user_views, [:user_id, :article_id]
关于唯一"选项的问题
在 Rails 中执行此操作的一种简单方法是在您的模型中使用 validates
和作用域 uniqueness
如下(文档):
Question regarding 'unique' option
An easy way to do this in Rails is to use validates
in your model with scoped uniqueness
as follows (documentation):
validates :user, uniqueness: { scope: :article }
这篇关于Ruby on Rails 中多列的索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!