ruby-on-rails - 连接表的最佳 SQL 索引

Question

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

模型和表格设置

我的型号是Foo和Bar根据 Rails 约定，我有一个名为 bars_foos 的连接表。没有主键或时间戳使得此表中的旧字段 bar_id:integer和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 的组合

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

可能的用途

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

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

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

数据库

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

Answer 1

答案

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

tl;dr 解释

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

完整说明

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

由于外键通常是另一个 AR 类的 ID，因此基数通常会很高。但同样，这取决于您的数据。在我的示例中，如果有许多 Foo 而很少 Bar，则连接表中的许多条目将具有类似的 bar_id。由于 bar_id 的基数较低，因此 bar_id 上的索引可能永远不会被使用，并且可能会因为让数据库投入时间和资源*来添加索引而造成妨碍每次创建新的 bars_foos 条目时都会索引。对于许多 Bar 和少量 Foo 以及两者都很少的情况也是如此。

一般的教训是，在考虑表上的索引时，确定是否通过该字段查找条目以及该字段是否具有高基数。也就是说，这个字段是否有许多不同的值？对于大多数连接表来说，“这取决于”，我们必须更仔细地考虑数据代表什么以及关系本身。就我而言，我将同时拥有许多 Foo 和 Bar，并且将通过以下方式查找 Foo:它们相关的bar，反之亦然。

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

脚注

* 在类似的问题中on indexes on STI有人指出，索引的成本非常低，因此当有疑问时，只需添加它即可。