mysql - 数据库索引用于同一组列的多个查询组合？

Question

我正在寻求有关如何考虑对同一列集执行不同查询组合的表所需的最少索引数的指导。理想情况下，您的答案将从这个特定示例中抽象出一些经验法则(如果可能的话)。

此项目符号列表代表三种通常在我的表上执行的不同查询条件:

• 哪里 race_type = ?和可记录类型=？和事件=？
• 哪里 race_type = ?和 recordable_id =？和可记录类型=？和事件=？
• 哪里 user_id = ?和种族类型=？和 recordable_id =？和可记录类型=？和事件=？

注:user_id(int), race_type(varchar), recordable_id(int), recordable_type(varchar), active(boolean)

我可以为其中的每一个创建单独的多列索引，但是你们这些数据库性能专家可能会采用不同的方法。

如果我需要提供更多信息以获得最佳答案，请告诉我。

Answer 1

如果您的条件是分层的(如您的示例)，您可以使用组合索引。 DBMS 无法同时处理多个索引。尽管这是可能的，并且他们会尽力从这种情况中获得最大 yield 。

这不会改变您应该尝试为某个 where 子句创建特定索引的事实。如果可以将更多 WHERE 的索引组合成一个索引，那么您可以释放一些空间和 CPU 周期。

让我们从为每个 WHERE 指定一个索引开始:

index1 (race_type, recordable_type, active)
index2 (race_type, recordable_id, recordable_type, active)
index3 (user_id, race_type, recordable_id, recordable_type, active)

一般来说，您可以通过提升基数来优化您的订单。基数是列在您的数据集中可能具有的值的数量。在你的例子中 active是一个 bool 值。 (请注意，boolean 只能有两个值这一事实并不重要。它可能是 int 如果您知道它只有两个值:0 和 1)。

您的 active 的基数较低field 意味着通过一次查找，我们可以消除一半可能的记录(当然取决于您的数据集)。完成此步骤后，您的第一个索引将如下所示:

index1 (active, race_type, recordable_type)

除了基数之外，您还应该注意字段之间的任何逻辑层次结构。在不知道这些名称的确切含义的情况下，我根据经验推测某些种族将有他们自己的记录。 - 当然，这不会消除可记录用于多种比赛类型的可能性，但您必须选择一个顺序，这似乎是更合乎逻辑的顺序。 - 所以我们将使用 race_type , recordable_type订单。

现在让我们看一下第二个索引。你介绍了recordable_id这里。在不知道您的数据集的情况下，我可以放心地假设 recordable_id 的基数将大于 recordable_type 的基数。换句话说，id 的数量将多于类型。我还怀疑 type 和 id 之间存在层次结构(闻起来像一对多)。所以让我们把它放在像这样的类型之后:

index2 (active, race_type, recordable_type, recordable_id)

现在是时候关注另一个重要的角度了。当修改您的数据库时，索引在您的 HDD 上有自己的成本(基本上是免费的)和 CPU 周期。任何索引的子集都可以从左到右使用。 index2基本上包含 index1因为它是index1 + recordable_id , 所以你可以去掉它，最后得到一个。

随之而来user_id .作为 ID 字段，它表示高基数(许多可能的值)，但请注意，不是规则“基数越高，越晚提交”。我们宁愿使用基数作为灯塔来帮助发现字段之间类似层次结构的关系。 (并缩小索引大小)。

是否 user_id指向我们正在查看数据的个人参赛者(许多可能性)？还是客户端上传了数据(可能性很小)？这很难说。您可以将其附加到我们现有的 index2你最终会得到一个可以在所有三个场景中使用的索引:

search_index (active, race_type, recordable_type, recordable_id, user_id)

...或者可能值得为此 scanario 添加第二个索引...

你的问题很特别，因为你只用了=在你的 where 子句中。如果您有类似 AND (race_type = 1 OR race_type=8) 的内容，还有许多其他注意事项更不用说>或 < .另外，如果您使用 ORDER BY可以将其纳入您使用的索引中。