在HABTM连接表上需要两个索引吗? [英] Need two indexes on a HABTM join table?

问题描述

简单的has_and_belongs_to_many关联:

Person has_and_belongs_to_many :products
Product has_and_belongs_to_many :persons

以下两个指标中的两者都有助于优化性能吗?

Are both of the following indexes helpful for optimal performance?

add_index :person_products, [:person_id, :product_id]
add_index :person_products, [:product_id, :person_id]

推荐答案

关闭-您很可能需要以下条件:

Close - you most likely want the following:

add_index :person_products, [:person_id, :product_id], :unique => true
add_index :person_products, :product_id

并非严格要求:unique => true，它取决于多次与一个产品相关联的人是否有意义.我想说的是，如果不确定，您可能要做要使用:unique标志.

The :unique => true is not strictly required and it depends whether or not it makes sense to have a person associated with a product multiple times. I would say if you're not sure, you probably do want the :unique flag.

采用索引结构的原因是，所有现代数据库都可以使用第一个索引在person_id和product_id上执行查询，而不必考虑查询中指定的顺序.例如

The reason for the index structure is that all modern databases can execute queries on both person_id and product_id using the first index regardless of the order specified in a query. E.g.

SELECT foo FROM bar WHERE person_id = 1 AND product_id = 2
SELECT foo FROM bar WHERE product_id = 2 AND person_id = 1

被视为相同，并且数据库足够智能，可以使用第一个索引.

are treated as the same and the database is smart enough to use the first index.

同样，也可以使用第一个索引来运行仅使用person_id的查询.如果从原始声明的左侧指定多列b树索引，则它们可以使用的列数少于它们所能使用的列数.

Likewise, queries using only person_id can also be run using the first index. Multi-column b-tree indexes can use fewer columns than they have provided they are specified from the left of the original declaration.

对于仅使用product_id的查询，无法针对第一个索引执行此操作(因为该索引是在最左边的位置使用person_id定义的).因此，您需要一个单独的索引来单独启用对该字段的查找.

For queries using only product_id, this cannot be executed against the first index (since that index is defined with person_id in the leftmost position). Hence you need a separate index to enable lookups on that field alone.

多列b树索引属性还扩展到具有更多列数的索引.如果在(person_id, product_id, favorite_color, shirt_size)上有索引，则可以使用该索引来运行使用person_id，(person_id, product_id)等的查询，只要顺序与定义匹配即可.

The multi-column b-tree index property also extends to indexes with higher numbers of columns. If you had an index on (person_id, product_id, favorite_color, shirt_size), you could use that index to run queries using person_id, (person_id, product_id), etc, so long as the order matches the definition.

这篇关于在HABTM连接表上需要两个索引吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！