DB关联实体和索引 [英] DB associative entities and indexing

问题描述

这是一个一般的DB设计问题。如果有一个关联实体表，即一个交叉引用，其中包含基本上只包含两个FK引用的记录，应该以某种方式进行索引？是否有必要对该表进行明确的索引，因为关联表中的PK已经按定义索引了？如果要索引，应该是组合索引，由两个FK字段组合在一起吗？

This is kind of a general DB design question. If one has an associative entity table, i.e. a cross-reference, containing records that basically just consist of two FK references, should it be indexed in some way? Is it necessary to explicitly index that table, since the PKs in the associated tables are already indexed by definition? If one should index it, should it be a combination index, consisting of the two FK fields together?

推荐答案

引用的索引其他表中的pk列不覆盖。

Indexes on the referenced pk columns in the other tables do not cover it.

通过将两个fk列定义为复合主键的关联实体表（在大多数情况下应该是 - 如果关联是唯一的），您隐式地创建一个多列索引。

By defining the two fk columns as composite primary key of the "associative entity" table (as you should in most cases - provided that associations are unique), you implicitly create a multi-column index.

涵盖所有查询涉及两列或第一列最优。

它还涵盖第二列上的查询，但效果较差。

如果您有涉及第二列的重要查询，请在该列上创建一个附加索引。

That covers all queries involving both or the first columns optimally.
It also covers queries on the second column, but in a less effective way.
If you have important queries involving just the second column, create an additional index on that one, too.

在此关于dba.SE的相关问题

或关于SO的这个问题，也是涵盖这个话题。

Read all the details about the topic at this related question on dba.SE.
Or this question on SO, also covering this topic.

这篇关于DB关联实体和索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！