考虑到性能改进，我想知道哪些索引对连接表（特别是在 Rails 3 has_and_belongs_to_many 上下文中使用）是否有帮助以及哪些索引有帮助。

模型和表格设置

我的模型是Foo and Bar根据 Rails 约定，我有一个名为bars_foos。没有主键或时间戳使得该表中的旧字段bar_id:integer and foo_id:integer。我有兴趣知道以下哪个索引最好并且没有重复：

1. 复合索引：add_index :bars_foos, [:bar_id, :foo_id]
2. • 两个指标
   • A. add_index :bars_foos, :bar_id
   • B. add_index :bars_foos, :foo_id
3. 1 和 2-B 的组合

基本上，我不确定复合索引是否足够，假设它一开始就有帮助。我相信复合索引可以用作第一项的单个索引，这就是我的原因pretty确保使用所有三行肯定会导致不必要的重复。

可能的用途

最常见的用法将给出模型的实例Foo，我会询问其相关的bars使用 RoR 语法foo.bars反之亦然bar.foos对于模型的一个实例Bar.

这些将生成以下类型的查询SELECT * FROM bars_foos WHERE foo_id = ? and SELECT * FROM bars_foos WHERE bar_id = ?分别然后使用这些结果 ID 来SELECT * FROM bars WHERE ID in (?) and SELECT * FROM foos WHERE ID in (?).

如果我不正确，请在评论中纠正我，但我不相信，在 Rails 应用程序的上下文中，它会尝试执行指定两个 ID 的查询，例如SELECT * FROM bars_foos where bar_id = ? AND foo_id = ?.

数据库

如果有特定于数据库的优化技术，我很可能会使用 PostgreSQL。然而，其他使用此代码的人可能希望在 MySQL 或 SQLite 中使用它，具体取决于他们的 Rails 配置，因此我们感谢所有答案。

答案

经常重复的答案往往是“这取决于情况”。更具体地说，这取决于您的数据是什么以及如何使用这些数据。

tl;dr 解释

针对我的具体案例（并涵盖所有未来基础）的简短 tl;dr 答案是选择#2这正是我所怀疑的。但是，选择 #3 效果很好，因为根据我对数据的使用情况，创建复合索引所使用的额外时间和空间可能会减少未来的查询查找。

完整的解释

原因是数据库试图变得智能，并尝试尽可能快地完成任务，而不管程序员的输入如何。添加索引时要考虑的最基本的事项是该对象是否可以通过该键查找。如果是，索引可能有助于加快速度。然而，是否使用该索引都取决于字段的选择性和基数。

由于外键通常是另一个 AR 类的 ID，因此基数通常会很高。但同样，这取决于您的数据。在我的例子中，如果有很多Foo但很少Bars，我的连接表中的许多条目都会有类似的bar_ids. With bar_ids 的基数较低，索引为bar_id可能永远不会被使用，并且可能会妨碍数据库在每次新索引时投入时间和资源*来添加到此索引bars_foos条目已创建。许多人也是如此Bar和少数Foos 并且两者都很少。

一般的教训是，在考虑表上的索引时，确定是否将通过该字段查找条目以及该字段是否具有高基数。也就是说，这个字段是否有许多不同的值？对于大多数连接表来说，“这取决于”，我们必须更仔细地考虑数据表示的内容以及关系本身。就我而言，我将有both many Foos and Bars并将抬头Foos 由其相关的bars，反之亦然。

我在办公室得到的另一个很好的答案是，“你为什么担心你的索引？构建你的应用程序！”

脚注

* 在类似的问题中关于 STI 指数 https://stackoverflow.com/questions/33486907/will-a-compound-index-with-a-second-column-of-low-cardinality-effect-performance#answer-33973140有人指出，索引的成本非常低，因此当有疑问时，只需添加它即可。