Rails中的多列索引顺序

I understand why the index order matters in Rails (from answers like these), for example, if I have:

我理解为什么索引顺序在Rails中很重要(从这些答案中)，例如，如果我有:

add_index :admin_users_pages, ["user_id", "page_id"]

So I'm supposed to put the field that "narrows down the number of rows" fastest, but I'm not sure what does that mean. Say I have 2 users, with 2 unique IDs, and 300 pages, with 300 unique IDs, which one would be a smarter choice to put first? Say I have 150 pages for the first user and 150 pages for the second user, would the index look something like:

所以我应该把“缩小行数”的字段写得最快，但我不确定这是什么意思。假设我有2个用户，2个唯一的id, 300个页面，300个唯一的id，哪一个更聪明?假设第一个用户有150页，第二个用户有150页，那么索引会是这样的吗?

user_id page_id
1       1
1       2  
1       3

or the page_id won't be sorted at all, only the index so I should get something like:

或者page_id不会被排序，只有索引，所以我应该得到如下内容:

user_id page_id
1       143
1       93  
1       31

2 个解决方案

#1

In your case the selectivity of page_id will be better, because it narrows down number of rows extremely fast (down to 2). It means that if you are given a page_id than you can take 2 records from from table and then filter them by user_id, but if you have user_id then you will take 150 records and filter them. So it is better to put 'page_id' first.

在你的情况下,选择性的page_id会更好,因为它缩小了极快的行数(2)。它的意思是如果你比你可以给定一个page_id从表2记录,然后通过user_id过滤它们,但是如果你有user_id然后你将150条记录和过滤。所以最好先写上page_id。

#2

If for a given user you want to find its pages, use [:user_id, :page_id].

如果要查找给定用户的页面，请使用[:user_id，:page_id]。

If for a given page you want to find its users, use [:page_id, :user_id].

如果要查找给定页面的用户，请使用[:page_id，:user_id]。

If you want to do both, then create [:user_id, :page_id] and [:page_id, :user_id].

如果您想两者都做，那么创建[:user_id，:page_id]和[:page_id，:user_id]。

If you have a user_id and a page_id and you want to find that row (not a very likely situation, IMHO), then for a balanced tree index it doesn't matter which order you have chosen. The entries are sorted within the index for both the first and the second and subsequent columns.

如果您有一个user_id和page_id，并且您想要找到那一行(不太可能的情况，IMHO)，那么对于一个平衡的树索引，您选择的顺序并不重要。在索引中对第1列、第2列和随后的列进行排序。

In some situations it is arguable that the least selective should go first (for Oracle compressed b-tree indexes or for Oracle skip-scan access), but in general it really doesn't matter.

在某些情况下，最不需要选择的选项应该是优先的(对于Oracle压缩的b-tree索引或Oracle skip-scan访问)，这是有争议的，但通常情况下这并不重要。

#1

#2