sql - 如何正确索引多对多关联表？

Question

在像这样的典型多对多排列中...

Movies       Actors       Movies_Actors------       ------       -------------movie_ID     actor_ID     FK_movie_IDtitle        name         FK_actor_ID

... how should the association table ('Movies_Actors') be indexed for optimal read speed?

I usually see this done only with the composite primary key in the association table, like so:

CREATE TABLE Movies_Actors (
  FK_movie_ID INTEGER,
  FK_actor_ID INTEGER,
  PRIMARY KEY (FK_movie_ID, FK_actor_ID)
)

然而，这似乎索引只有在搜索 both movie_ID 和 actor_ID 时才有用(尽管我不确定复合索引是否也适用于各个列)。

由于“电影 X 中有哪些 Actor ”和“ Actor Y 曾出演过哪些电影”都是此表的常见查询，因此似乎每一列上都应该有一个单独的索引以快速定位 Actor 和电影他们自己。综合指数能有效地做到这一点吗？如果没有，那么在此表上使用复合索引似乎毫无意义。如果复合索引毫无意义，那么如何处理主键呢？候选键显然是两列的组合，但如果生成的组合索引毫无意义(一定不是吗？)，这似乎是一种浪费。

此外，this link增加了一些困惑，并指出实际指定 两个 复合索引甚至可能有用......其中一个为 (FK_movie_ID, FK_actor_ID)，另一个相反为(FK_actor_ID, FK_movie_ID)，可以选择哪个是主键(因此通常是聚集的)，哪个“只是”一个唯一的复合索引，基于查询的方向更多。

什么是真实的故事？复合索引是否会自动有效地索引每一列以进行搜索？最佳(读取速度，而不是大小)关联表是否应该在每个方向上都有一个复合索引并且在每一列上都有一个？什么是幕后机制？

---

编辑:我发现了这个相关问题，出于某种原因我在发布之前没有找到...... How to properly index a linking table for many-to-many connection in MySQL?

Answer 1

(although I'm not certain on whether acomposite index also works for theindividual columns).

是的，它可以。但只有前缀:http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys

Also, this link adds some confusionand indicates that it might even beuseful to actually specify twocomposite indices... one of them as(FK_movie_ID, FK_actor_ID), and theother in reverse as (FK_actor_ID,FK_movie_ID),

这才是真正要做的事情。

将一个作为聚簇索引，将另一个作为非聚簇索引，无论如何都会包含聚簇索引键——因此不需要再次包含该列(thx to JNK)。

CREATE CLUSTERED INDEX a on Movies_Actors (fk_movie_id, fk_actor_id);
CREATE NONCLUSTERED INDEX b on Movies_Actors (fk_actor_id);

What is the real story?

http://Use-The-Index-Luke.com/ :)

Does a composite index automaticallyeffectively index each column forsearching on one or the other?

没有。只有索引的前缀。如果你有一个索引 (a,b,c)，查询 a=?和 b=?可以使用索引。然而c=?不能，b= 也不可以？和 c=?。

Should the optimal (in read speed, notsize) association table have acomposite index in each direction andone on each column?

如果需要双向join，yes(“每个方向的复合索引”)和no(“每列一个”)。

What are the behind-the-scene mechanics?

嗯，又是同一个链接。

谈到 SQL Server，您最终可能还会考虑索引 View 。那是一种预加入。如上所述，两个索引也可能足够快。