多对多索引创建约定 [英] Many to Many index creation convention

问题描述

我的问题与这篇SO帖子有关，但不是阻止我想了解创建索引的约定是什么，因为我找不到任何东西.

My question is related to this SO post, but instead of preventing I want to understand what's the convention of creating an index, as I can't find anything.

给定实体:

public class Entity1
{
  public Guid Id {get;set;}
  public string Name {get;set;}
  public ICollection<Entity1Entity2Link> Links {get;set;}
}

public class Entity2
{
  public Guid Id {get;set;}
  public string Name {get;set;}
  public ICollection<Entity1Entity2Link> Links {get;set;}
}

public class Entity1Entity2Link
{
  public Guid Entity1Id {get;set;}
  public Entity1 Entity1 {get;set;}

  public Guid Entity2Id {get;set;}
  public Entity2 Entity2 {get;set;}
}

以及多对多关系设置:

modelBuilder
                .Entity<Entity1Entity2Link>()
                .HasKey(ofl => new
                {
                    ofl.Entity1Id ,
                    ofl.Entity2Id 
                });

            modelBuilder
                .Entity<Entity1Entity2Link>()
                .HasOne(ofl => ofl.Entity1)
                .WithMany(e => e.Links)
                .HasForeignKey(ofl => ofl.Entity1Id);

            modelBuilder
                .Entity<Entity1Entity2Link>()
                .HasOne(ofl => ofl.Entity2)
                .WithMany(e => e.Links)
                .HasForeignKey(ofl => ofl.Entity2Id);

由此产生的迁移在 Entity2Id 上有索引

The resulting migration has index on Entity2Id

// create table etc...

migrationBuilder.CreateIndex(
    name: "IX_Entity1Entity2Link_Entity2Id",
    table: "Entity1Entity2Link",
    column: "Entity2Id");

我正在寻找创建此索引的约定是什么?我能找到的一个约定是:

I'm looking to find what's the convention to create this index ? One convention I could find states:

EF Core 将始终为外键和备用键创建索引.

EF Core will always create indexes for foreign key and alternate keys.

但是为什么我在 Entity1Id 列上没有看到另一个索引?

But then why I don't see another index on Entity1Id column ?

我还注意到我可以通过交换组合键定义中的列来交换在列上创建的索引，例如

Also I noticed I can swap index created on a column, by swapping column in composite key definition, e.g.

modelBuilder
    .Entity<Entity1Entity2Link>()
    .HasKey(ofl => new
    {
        ofl.Entity2Id,
        ofl.Entity1Id
    });

这将在 Entity1Id 列上创建一个索引:

This will create an index on Entity1Id column:

migrationBuilder.CreateIndex(
    name: "IX_Entity1Entity2Link_Entity1Id",
    table: "Entity1Entity2Link",
    column: "Entity1Id");

我使用的是 EF Core 3.1.2.

I'm using EF Core 3.1.2.

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.1.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.2" />

推荐答案

基本上就是你提到的

按照惯例，在用作外键的每个属性(或属性集)中创建索引.

By convention, an index is created in each property (or set of properties) that are used as a foreign key.

除了一个例外，以下摘录部分涵盖了综合索引部分:

with one exception, partially covered by the following excerpt from Composite Index section:

多列上的索引，也称为复合索引，可以加快对索引列进行过滤的查询，也可以加快只对索引覆盖的第一列进行过滤的查询.

Indexes over multiple columns, also known as composite indexes, speed up queries which filter on index's columns, but also queries which only filter on the first columns covered by the index.

因此，按照惯例，所有 FK 都会自动创建索引，除非，如果 FK 属性是其他组合索引中的前导列.由于复合索引的前导列提供与包含这些列的单独索引相同的高效搜索能力，因此单独索引是多余的，EF Core 足够聪明，不会创建它.

So, indexes are created automatically by convention for all FKs, except if the FK properties are leading columns in some other composite index. Because leading columns of a composite index provide the same efficient search capability as separate index containing these columns, thus separate index is redundant, and EF Core is smart enough to not create it.

在您的示例中，已经存在与 PK (Entity1Id, Entity2Id) 列关联的复合索引，该列涵盖了 Entity1Id FK，因此未创建其他索引.如果交换 PK 中的列，则 Entity2Id 将被覆盖，因此只会创建 Entity1Id FK 的附加索引.

In your example, there is already composite index associated with the PK (Entity1Id, Entity2Id) columns, which covers Entity1Id FK, hence no additional index is created. If you swap the columns in the PK, then Entity2Id will be covered, so only additional index for Entity1Id FK will be created.

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