I have read that all foreign keys should be indexed for better join performance. Do that mean, by definition, that all bridge tables should have all fields indexed
我已经读过所有外键都应该被索引以获得更好的连接性能。这意味着，根据定义，所有桥接表都应该将所有字段编入索引
for example lets say i have 3 table
例如，假设我有3个表
Project: Id, Name
ProjectApplication: Id, ProjectId, ApplicationId
Application: Id, Name
in these cases, should ProjectId and ApplicationId both have indexes on them?
在这些情况下，ProjectId和ApplicationId是否都有索引？
1 个解决方案
#1
1
In your given example Id column in Project table have to be a Primary key(or atleast UNIQUE constraint) in order to be able to reference it in any other column i.e creating a foreign key constraint which references it same is true for Id column in Application table. So by default it will have a Clustered Index defined on it.
在您的给定示例中，Project表中的Id列必须是主键（或至少UNIQUE约束）才能在任何其他列中引用它，即创建引用它的外键约束对于Application中的Id列也是如此表。因此，默认情况下，它将定义一个Clustered Index。
Now in your ProjectApplication table if you do create a foreign Key and create an Index on that column, and obviously when ever you need to retrieve information from these tables you will be joining these tables on these two fields so having a Clustered Index on one side and a nonclustered index on other side will most definitely have a great impact on the performance of your queries, well worth it , go for it .
现在在ProjectApplication表中，如果你确实创建了一个外键并在该列上创建了一个索引，显然当你需要从这些表中检索信息时，你将在这两个字段上加入这些表，从而在一侧有一个聚簇索引而另一方面的非聚集索引肯定会对您的查询的性能产生很大的影响，非常值得，去吧。
#1
1
In your given example Id column in Project table have to be a Primary key(or atleast UNIQUE constraint) in order to be able to reference it in any other column i.e creating a foreign key constraint which references it same is true for Id column in Application table. So by default it will have a Clustered Index defined on it.
在您的给定示例中，Project表中的Id列必须是主键（或至少UNIQUE约束）才能在任何其他列中引用它，即创建引用它的外键约束对于Application中的Id列也是如此表。因此，默认情况下，它将定义一个Clustered Index。
Now in your ProjectApplication table if you do create a foreign Key and create an Index on that column, and obviously when ever you need to retrieve information from these tables you will be joining these tables on these two fields so having a Clustered Index on one side and a nonclustered index on other side will most definitely have a great impact on the performance of your queries, well worth it , go for it .
现在在ProjectApplication表中，如果你确实创建了一个外键并在该列上创建了一个索引，显然当你需要从这些表中检索信息时，你将在这两个字段上加入这些表，从而在一侧有一个聚簇索引而另一方面的非聚集索引肯定会对您的查询的性能产生很大的影响，非常值得，去吧。