我正在寻找关于如何考虑表所需的最小索引数量的指导,在该表中,您在同一列列上执行不同的查询组合.理想情况下,您的答案将从这个具体示例中抽象出一些经验法则(如果可能的话).
此项目符号列表表示通常在我的表上执行的三种不同查询条件：
> WHERE race_type =？ AND recordable_type =？ AND active =？
> WHERE race_type =？ AND recordable_id =？ AND recordable_type =？ AND active =？
> WHERE user_id =？ AND race_type =？ AND recordable_id =？ AND recordable_type =？ AND active =？
注意：user_id(int),race_type(varchar),recordable_id(int),recordable_type(varchar),active(boolean)
我可以为每个索引创建单独的多列索引,但是您的DB性能专家可能会以不同的方式处理它.
如果我需要提供更多信息以获得最佳答案,请告诉我们.
这并不会改变您应该尝试为某个where子句创建特定索引的事实.如果可以将更多的WHERE索引组合到一个索引中,那么就可以释放一些空间和cpu周期.
让我们从为每个WHERE指定一个索引开始：
index1 (race_type,recordable_type,active)
index2 (race_type,recordable_id,active)
index3 (user_id,race_type,active)
通常,您可以通过提升基数来优化订单.基数是列在数据集中可能具有的值的数量.在您的示例中,active是布尔值. (请注意,布尔值只能有两个值的事实并不重要.如果你知道它只有两个值,那么它可以是int：0和1).
活动字段的低基数意味着通过单个查找,我们可以消除一半可能的记录(当然,这取决于您的数据集).完成此步骤后,您的第一个索引将如下所示：
index1 (active,recordable_type)
除了基数之外,您还应该注意字段之间的任何逻辑层次结构.我不知道这些名字究竟是什么意思,我猜测某些种族类型会有自己的记录. – 这不会消除可记录与多种种类一起使用的可能性,但是你必须选择一个订单,这似乎更合乎逻辑. – 所以我们将使用race_type,recordable_type顺序.
现在让我们来看看第二个指数.你在这里介绍了recordable_id.在不知道您的数据集的情况下,我可以安全地假设recordable_id的基数将是比recordable_type更高的基数.换句话说,会有更多的id而不是类型.我还怀疑类型和id之间的层次结构(闻起来像一对多).所以让我们把它放在类型之后：
index2 (active,recordable_id)
现在是时候关注另一个重要的角度了.修改数据库时,索引在硬盘上有自己的成本(实际上是免费的)和cpu周期.可以从左到右使用任何索引的子集. index2本质上包含index1,因为它是index1 recordable_id,所以你可以摆脱它并最终得到一个.
沿途来了user_id.作为一个ID字段,它表示高基数(许多可能的值),但请注意,“基数越高,后者的基数越高”这一规则就越不规则.我们宁愿使用基数作为信标来帮助发现字段之间的层次结构关系. (并缩小索引大小).
user_id是否指向我们正在查看的数据的个体参赛者(许多可能性)？或者是上传数据的客户(极少数可能性)？这很难说.你可以将它附加到我们现有的index2,你最终会得到一个可以在所有三个secnarios中使用的索引：
search_index (active,user_id)
…或者它可能值得为此scanario提供第二个索引……
您的问题很特殊,因为您只在where子句中使用=.如果你有像AND这样的东西还有很多其他注意事项(race_type = 1或race_type = 8)更不用说>或者<.此外,如果您使用ORDER BY,可以将其纳入您使用的索引中.