如何正确索引多对多关联表? [英] How to properly index a many-many association table?

问题描述

以典型的许多这种安排...

In a typical many-many arrangement like this...

Movies       Actors       Movies_Actors
------       ------       -------------
movie_ID     actor_ID     FK_movie_ID
title        name         FK_actor_ID

...应该如何索引关联表('Movies_Actors')以获得最佳读取速度?

... 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)
)

但是，似乎索引仅在同时搜索 movie_ID和actor_ID时才有用(尽管我不确定复合索引是否也适用于各个列) ).

However, this seems like the index will only be useful when searching for both movie_ID and actor_ID (although I'm not certain on whether a composite index also works for the individual columns).

由于此表的常见查询是电影X中的演员是什么"和演员Y在电影中是什么"，因此似乎在每列上都应该有一个单独的索引来快速定位演员和电影.他们自己.复合索引是否可以有效地做到这一点?如果没有，则在此表上拥有复合索引似乎毫无意义.如果复合索引毫无意义，那么对主键怎么办?候选键显然是两列的组合，但是如果结果组合索引没有意义(一定不是吗?)，那似乎是一种浪费.

Since both "what actors are in Movie X" and "what movies has actor Y been in" will be the common queries for this table, it seems like there should be an individual index on each column to quickly locate actors and movies on their own. Does a composite index effectively do this? If not, having a composite index seems pointless on this table. And if a composite index is pointless, what to do about a primary key? The candidate key is clearly the composite of the two columns, but if the resulting composite index is pointless (it mustn't be?) it seems like a waste.

此外，此链接增添了一些混乱，并表明实际上指定两个两个复合索引甚至可能会有用……其中一个为(FK_movie_ID, FK_actor_ID)，另一个为(FK_actor_ID, FK_movie_ID) ，选择哪个是主键(因此通常是集群键)，哪个才是唯一"唯一索引，该索引基于要查询的方向更多.

Also, this link adds some confusion and indicates that it might even be useful to actually specify two composite indices... one of them as (FK_movie_ID, FK_actor_ID), and the other in reverse as (FK_actor_ID, FK_movie_ID), with the choice of which is the primary key (and thus usually clustered) and which is 'just' a unique composite index being based on which direction is queried more.

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

What is the real story? Does a composite index automatically effectively index each column for searching on one or the other? Should the optimal (in read speed, not size) association table have a composite index in each direction and one on each column? What are the behind-the-scene mechancs?

我发现了这个相关问题，由于某种原因我在发布之前找不到了... 如何正确索引一个在MySQL中用于多对多连接的链接表?

I found this related question that for some reason I didn't locate before posting... How to properly index a linking table for many-to-many connection in MySQL?

推荐答案

(尽管我不确定 综合指数也适用于 各个列).

(although I'm not certain on whether a composite index also works for the individual columns).

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

Yes, it can. But only the prefix: http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys

此外，此链接也增加了一些混乱 并表明它甚至可能是 实际指定两个有用 综合指数...其中之一 (FK_movie_ID，FK_actor_ID)和 其他则为(FK_actor_ID， FK_movie_ID)，

Also, this link adds some confusion and indicates that it might even be useful to actually specify two composite indices... one of them as (FK_movie_ID, FK_actor_ID), and the other in reverse as (FK_actor_ID, FK_movie_ID),

那实际上就是要做的事.

That's actually the thing to do.

将一个作为聚簇索引，将另一个作为非聚簇索引，它们将始终包含聚簇索引键-因此无需再次包含that列(thx到JNK).

Take one as clustering index, and the other as non-clustering index that will anyways include the clustering index key--hence no need to include the that column again (thx to JNK).

CREATE CLUSTERING INDEX a on Movies_Actors (fk_movie_id, fk_actor_id);
CREATE NONCLUSTERING INDEX b on Movies_Actors (fk_actor_id);

真实的故事是什么?

What is the real story?

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

自动执行复合索引 有效地为每个列编制索引 在一个或另一个上搜索?

Does a composite index automatically effectively index each column for searching on one or the other?

不.仅索引的前缀.如果您有索引(a，b，c)，则查询a =?和b =?可以使用索引.但是c =?不能，b =也不能?和c =?.

No. Only the prefix of the index. If you have an index (a,b,c), the query a=? and b=? can use the index. However c=? can't, nor can b=? and c=?.

应该最佳(以读取速度，而不是 大小)关联表中有一个 每个方向的综合指数和 每列一个?

Should the optimal (in read speed, not size) association table have a composite index in each direction and one on each column?

如果您需要双向连接，请选择是(每个方向上的复合索引")，而不选择否(每个列上的一个索引").

If you need to join in both directions, yes ("composite index in each direction") and no ("one on each column").

幕后机制是什么?

What are the behind-the-scene mechanics?

好吧，再次链接.

说到SQL Server，您最终可能还会考虑索引视图.这是一种预先加入.如上所述，两个索引可能也足够快.

Speaking SQL Server, you might eventually also consider an indexed view. That's kind of pre-joining. Two indexes, as above, might also be fast enough.

这篇关于如何正确索引多对多关联表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！