Ruby on Rails 中多列的索引 [英] Index on multiple columns in Ruby on Rails

问题描述

我正在实现跟踪用户阅读了哪些文章的功能.

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.

推荐答案

顺序在索引中很重要.

1. 将最具选择性的字段放在首位，即最快缩小行数的字段.
2. 只有当您按顺序从头开始使用其列时，才会使用索引.即，如果您在 [:user_id, :article_id] 上建立索引，您可以对 user_id 或 user_id AND article_id 执行快速查询，但不能在article_id.

1. Put the most selective field first, i.e. the field that narrows down the number of rows fastest.
2. 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 on user_id or user_id AND article_id, but NOT on article_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 中多列的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！