我应该联合索引 ActiveRecord 多态关联吗? [英] Should I joint-index an ActiveRecord polymorphic association?

问题描述

我有一个我预计会非常大的指标表.它具有多态关联，因此它可以属于其他想要记录某些指标的模型.我通常像这样索引关联列以加快关联加载.我听说人们在谈论联合- 索引此关联.这看起来像:

I have a metric table that I expect to be very large. It has a polymorphic association so that it can belongs_to other models that want to record some metric. I typically index association columns like this to speed up association loading. I've heard people talking about joint-indexing this association. This looks like:

add_index :comments, [:commentable_type, :commentable_id]

但我也听到过反对创建低基数索引的建议，因为index 不会抵消维护它的开销.由于我的多态关联的 _type 一半在数百万行中可能只有 4-5 个值，所以我倾向于只索引多态关联的 _id 部分.我可能会使用 _id 列和其他一些未提及的整数和日期时间列创建一些额外的联合索引，但我也不会在这些索引中包含 _type.

But I've also heard counsel against creating indexes of low-cardinality, because the payoff of the index doesn't offset the overhead of maintaining it. Since the _type half of my polymorphic association will probably only have 4-5 values across the millions of rows, I'm inclined to only index on the _id portion of the polymorphic association. I will probably create some additional joint-indexes using the _id column and some other unmentioned integer and datetime columns, but I won't include the _type in these indexes either.

这是你会做/推荐的吗?

Is this what you would do/recommend?

推荐答案

最终，这值得在添加索引之前和之后，在一个真实的数据集上进行基准测试——在大小和数据上都是真实的.

Ultimately, this is worth benchmarking before and after adding the index, on a realistic data set - realistic in size and data.

但是，您不会在只有几个值的字段上创建索引.索引在两个字段的组合上，很可能有很多个不同的值组合.组合字段上的索引是一个聪明的主意.

However, you're not creating an index on a field with just a few values. The index is on the combination of the two fields, which is likely to have a lot of different value combinations. The index on the combined fields is a smart idea.

这篇关于我应该联合索引 ActiveRecord 多态关联吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！