实体框架索引所有外键列 [英] Entity Framework Indexing ALL foreign key columns

问题描述

这可能是一个过多的基于意见的问题，但这里有：

This may be too much of an opinion-based question but here goes:

我发现了关于实体框架和数据库迁移的一个有趣的怪癖。看来，每当我们创建外键时，它也会在该列上创建索引。

I've found an interesting quirk with Entity Framework and database migrations. It seems that whenever we create a foreign key it also creates an index on that column.

我读了这样一个问题：实体框架代码第一个外键也添加了索引，每个人似乎都说这是一个很好的，有效的主意，但我不知道如何做。索引列是非常特定于环境的。例如，EF正在索引我的表上几乎从来没有（〜1％）用于搜索的FK，而且也索引了源表，这意味着即使我加入其他表，我也正在使用它的PK搜索FK的链接表...在那种情况下（我知道）对FK进行索引并没有任何好处。

I read this SO question: Entity Framework Code First Foreign Key adding Index as well and everyone seems to say it's a great, efficient idea but I don't see how; indexing a column is very circumstance-specific. For instance, EF is indexing FKs on my table that are almost never (~1%) used for searches and are also on a source table, meaning that even when I join other tables, I'm searching the FK's linked table using it's PK...there's no benefit from having the FK indexed in that scenario (that I'm aware of).

我的问题

我错过了什么吗？有什么原因为什么我想索引一个FK列，该列从不被搜索并且在任何联接中始终位于源表上？

Am I missing something? Is there some reason why I would want to index a FK column that is never searched and is always on the source table in any joins?

我的计划是删除其中一些可疑的索引，但我想确认没有缺少一些优化概念。

My plan is to remove some of these questionable indexes but I wanted to to confirm that there's not some optimization concept that I'm missing.

推荐答案

首先，在EF代码中，建模外键关系的一般原因是为了实体之间的可导航性。考虑一个简单的方案，即 Country 和 City ，并为以下LINQ语句定义了预加载：

In EF Code First, the general reason why you would model a foreign key relationship is for navigability between entities. Consider a simple scenario of Country and City, with eager loading defined for the following LINQ statement:

var someQuery = 
   db.Countries
     .Include(co => co.City)
     .Where(co => co.Name == "Japan")
     .Select(...);

这将导致以下查询：

SELECT *
FROM Country co
INNER JOIN City ci
  ON ci.CountryId = co.ID
WHERE co.Name = 'Japan';

在 City.CountryId ，SQL将需要扫描城市表，以便在JOIN期间为国家/地区过滤城市。

Without an Index on the foreign key on City.CountryId, SQL will need to scan the Cities table in order to filter the cities for the Country during a JOIN.

TL; DR

即使您不推荐使用外键索引，不会直接根据外键进行过滤，因此Joins中仍然需要使用它。

Indexes on Foreign Keys are recommended, even if you don't filter directly on the foreign key, it will still be needed in Joins. The exceptions to this seem to be quite contrived:

• 如果外键的选择性很低，例如在上述情况下，如果国家/地区表中所有城市的50％位于日本，则该索引将无用。

• If the selectivity of the foreign key is very low, e.g. in the above scenario, if 50% of ALL cities in the countries table were in Japan, then the Index would not be useful.

如果您不这样做，

另一个优化考虑因素是是否在<$ c中使用外键子表的$ c>聚集索引（即按国家对城市进行集群）。这通常在parent：child表关系中是有益的，在该关系中，通常可以同时检索父级的所有子行。

One additional optimization consideration is whether to use the foreign key in the Clustered Index of the child table (i.e. cluster Cities by Country). This is often beneficial in parent : child table relationships where it is common place to retrieve all child rows for the parent simultaneously.

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