如果一个表有很多（20+）外键约束，这有什么缺点吗？
假设我有一个表，其中有许多字段链接到其他“值表”中的值。 当然，我对它们中的每一个都声明了外键约束以强制完整性。
如果我最终得到的此类字段的数量在 20-30 范围内怎么办？ 它会以某种方式“减慢”表操作吗？
添加：值表预计只有很少的记录，通常是 5-10 条左右。 数据库是SQL Server 2008。
Let's says I have a table which has many fields linked to values from other "value tables". Naturally, I declare foreign key constraints on each and evary of them to enforce integrity.
What if I finally get the number of such fields in the range of 20-30? Will it somehow "slow" table operations or not really?
ADDED: Value tables are expected to have only few records, normally 5-10 or something. The database is SQL Server 2008.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
当您向子表中插入一行时，数据库引擎将查找父表中是否存在相应的值 - 这将占用一些 CPU 和一些逻辑读取。 如果您的父表很小，它们很可能位于缓存中，因此一旦您的手杖变热，您就不会期望出现许多缓慢的物理读取。
我更担心的是，如果您要从父表中删除：如果您的子表上没有相应的索引，则整个子表将被锁定并扫描。 另一方面，如果您的所有外键都有相应的索引，那么您的子表上可能会出现多达 20-30 个额外索引，这会导致速度显着下降。
您可能想运行自己的基准测试并亲自查看。
When you insert a row into your child table, the DB engine will look up if the corresponding values in the parent tables exist - that will use up some CPU and some logical reads. If your parent tables are small, they will most likely be in the cache, so you would not expect many slow physical reads as soon as your cane is warm.
What would concern me more is if you are ever going to delete from your parent tables: if you don;t have a corresponding index on your child table, the whole child table will be locked and scanned. On the other hand, if you do have corresponding indexes for all your foreign keys, then you may end up with up to 20-30 additional indexes on your child table, which is a considerable slowdown.
You might want to run your own benchmarks and see for yourself.
与拥有零个外键相比，拥有一个外键会减慢插入/更新操作的速度 - 只是因为数据库必须检查外键值是否确实存在。 有 30 个外键会比没有外键慢。
也就是说，它会慢多少取决于很多因素，包括您正在使用的值表/数据库引擎/索引/等的大小......并且在最好的情况下可能几乎可以忽略不计。
Having one foreign key slows down insert / update operations versus having zero foreign keys - just because database has to check that foreign key value actually exists. Having 30 foreign keys will be slower then having none.
That said, just how much slower it'll be depends on many things, including size of your value tables / database engine you're using / indexes / etc... and may be virtually negligible under best case scenario.
是的，由于检查了所有相关约束，插入和更新会产生一些性能损失，但除非您尝试以高速率插入数据，否则不太可能导致任何问题。 通常，正确维护数据比快速维护数据更重要，因此这种惩罚是值得的。
如果您对几列执行 UPDATE，则只需检查这些列上的约束，并且大多数 DBMS 将仅检查这些约束。
当然，SELECT 语句根本不会减慢，在某些（可能很少见）情况下，甚至可以从优化器了解正在连接的两个表之间的外键关系中受益。
Yes, there is some performance penalty on inserts and updates as all the relevant constraints are checked, but it is unlikely to cause any issues unless you are trying to insert data at a high rate. It is usually more important for data to be maintained correctly rather than quickly, so the penalty is well worth incurring.
If you perform an UPDATE of a few columns, only the constraints on those columns need to be checked, and most DBMSs will only check those constraints.
SELECT statements will not be slowed down at all of course, and in some (probably rare) cases could even benefit from the optimizer being aware of the foreign key relationship between 2 tables that are being joined.
在这 20-30 个字段中，有多少很少用到？ 也许可以建立其他一些表。 从编码的角度来看，更新两个表变得更加困难，但如果您可以在大多数情况下省略更新第二个表，则会加快速度。
我处理一个第三方应用程序，该应用程序具有带有相应“自定义”表的主表，我们可以在其中设置自己的字段。 不幸的是，我们一直使用“自定义”字段，并且很少能够只处理主表。
Of the 20-30 fields, how many rarely get used? Maybe some other table could be built. Makes it tougher to have to update two tables from a coding perspective, but would speed things up if you can omit updating the second table most of the time.
I deal with a 3rd party app that has main tables with corresponding 'custom' tables where we can setup our own fields. Unfortunately, we use the 'custom' fields all the time and rarely can get away with just dealing with the main table.
我认为这将取决于您的查询是否使用任何约束。 如果您的查询由于约束而需要检查另一个表，那么您将看到性能下降。 如果您的查询未引用约束中的任何列，则性能影响可能可以忽略不计。
I think it's going to depend on if your queries use any of the contraints. If your query is going to need to check another table because of a constraint, then you'll see a performance hit. If your query doesn't reference any columns in the contraints, the performance hit will probably be negligible.
就像大多数与数据库设计相关的事情一样，这“取决于”。 如果您的应用程序大量插入、更新和删除，您将遇到性能问题。 在这种情况下，反规范化可能是合理的，尤其是在“值”表没有改变的情况下。
Like most everything database design related, this 'depends.' If your application does heavy inserting, updating and deleting you will run into performance issues. This could be a case where de-normalizing could be justified, especially if the 'value' tables are not changing.