如何阻止EF Core索引所有外键 [英] How to stop EF Core from indexing all foreign keys

问题描述

如在所有外键列中建立索引的实体框架，EF Core似乎会自动为每个外键生成一个索引.对我来说，这是一个默认的声音(这里不要进入舆论战……)，但是在某些情况下，这只是浪费空间并减慢了插入和更新的速度.如何逐案预防?

As documented in questions like Entity Framework Indexing ALL foreign key columns, EF Core seems to automatically generate an index for every foreign key. This is a sound default for me (let's not get into an opinion war here...), but there are cases where it is just a waste of space and slowing down inserts and updates. How do I prevent it on a case-by-case basis?

我不想完全关闭它，因为它弊大于利；我不想为我要做想要的所有那些索引手动配置它.我只是想在特定 FK上阻止它.

I don't want to wholly turn it off, as it does more good than harm; I don't want to have to manually configure it for all those indices I do want. I just want to prevent it on specific FKs.

相关的补充问题:在EF文档的任何地方都提到了自动创建这些索引的事实吗?我在任何地方都找不到它，这可能就是为什么我找不到如何禁用它的原因?

Related side question: is the fact that these index are automatically created mentioned anywhere in the EF documentation? I can't find it anywhere, which is probably why I can't find how to disable it?

有人肯定会问我为什么要这样做...所以为了节省时间，链接问题的操作者在评论中举了一个很好的例子:

例如，我们有一个 People 表和一个 Addresses 表.这 People.AddressID FK被EF索引，但我只从 People 行并搜索 Addresses 记录；我从来没有找到 Addresses 行，然后在 People.AddressID 列中搜索匹配记录.

We have a People table and an Addresses table, for example. The People.AddressID FK was Indexed by EF but I only ever start from a People row and search for the Addresses record; I never find an Addresses row and then search the People.AddressID column for a matching record.

推荐答案

如果真的有必要避免在.Net Core中使用某些外键索引(据我所知(目前))，则有必要删除将在生成的迁移代码文件中设置索引的代码.

If it is really necessary to avoid the usage of some foreign keys indices - as far as I know (currently) - in .Net Core, it is necessary to remove code that will set the indices in generated migration code file.

另一种方法是结合属性或避免索引创建的扩展方法来实现自定义迁移生成器.您可以在此答案中找到有关EF6的更多信息:在外键上创建索引.但是我不确定它是否也可以在.Net Core中使用.该方法似乎有些不同，这是一个 MS文档文章应该会有所帮助.

Another approach would be to implement a custom migration generator in combination with an attribute or maybe an extension method that will avoid the index creation. You could find more information in this answer for EF6: EF6 preventing not to create Index on Foreign Key. But I'm not sure if it will work in .Net Core too. The approach seems to be bit different, here is a MS doc article that should help.

但是，我强烈建议您不要这样做！我反对这样做，因为您必须修改生成的迁移文件，而不是因为没有为FK使用索引.就像您在问题评论中提到的那样，在现实世界中，某些情况下需要这种方法.

But, I strongly advise against doing this! I'm against doing this, because you have to modify generated migration files and not because of not using indices for FKs. Like you mentioned in question's comments, in real world scenarios some cases need such approach.

对于其他人，他们不确定是否必须避免在FK上使用索引，因此他们必须修改迁移文件:

For other people they are not really sure if they have to avoid the usage of indices on FKs and therefor they have to modify migration files:

在采用这种方式之前，我建议使用FK上的索引来实现该应用程序，并检查性能和空间使用情况.因此，我会产生很多测试数据.如果确实在测试或质量保证阶段导致性能和空间使用问题，则仍然可以删除迁移文件中的索引.

Before you go that way, I would suggest to implement the application with indices on FKs and would check the performance and space usage. Therefor I would produce a lot test data. If it really results in performance and space usage issues on a test or QA stage, it's still possible to remove indices in migration files.

因为我们已经在这里谈论了 EnsureCreated 与 migrations 的完整性，以确保有关确保创建和迁移(，即使您不需要它:-)<的更多信息):

Because we already chat about EnsureCreated vs migrations here for completeness further information about EnsureCreated and migrations (even if you don't need it :-)):

• 有关 EnsureCreated()(如果您进行了一些模型更改，它将不会更新您的数据库-迁移可以做到)
(甚至对于EF7也很有趣) EF7确保创建与迁移方法

• MS doc about EnsureCreated() (It will not update your database if you have some model changes - migrations would do it)
• interesting too (even if for EF7) EF7 EnsureCreated vs. Migrate Methods

这篇关于如何阻止EF Core索引所有外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！