blocks|key|177251|text|保存您的外键-它确保，无论您有什么木偶为您编写代码，您的数据保持在一种可用的和预期的格式。除非你看到的是大量的流量，否则我甚至不会考虑移除它们。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|177252|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Keep your foreign keys - it ensures, no matter what muppet you have writing code for you, that your data stays in a usable and expected format. Unless you're looking at seriously high volumes of traffic, I wouldn't even consider removing them.

blocks|key|177299|text|外键将确保表order_details中包含引用orders表的字段order_id的行永远不会有不存在于orders表中的order_id值。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|177300|外键不需要有一个工作的关系数据库，但它们绝对是必要的，以避免破裂的关系和孤儿行(即。参考完整性)。酸中的C必须具备在数据库级别强制执行引用完整性的能力。|177301|至于您对性能的关注，一般来说，性能的影响(如果有的话)可以忽略不计。我建议把你所有的外键约束，只有当你有真正的性能问题，你无法解决的情况下，没有它们的实验。|177302|此外，作为附带说明，虽然与MySQL没有直接关系，但这是Microsoft模式和实践:第14章改进Server性能的引文。|177303|当主键和外键被定义为数据库架构中的约束时，服务器可以使用这些信息创建最佳执行计划。|blockquote|177304|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Foreign_key|1|http://en.wikipedia.org/wiki/Referential_integrity|2|http://en.wikipedia.org/wiki/ACID|3|http://msdn.microsoft.com/en-us/library/ms998577.aspx^0|6|D|O|6|Y|8|1H|6|1Q|8|0|2|0|0|16|5|1|1D|1|2|0|0|S|T|3|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]|$9|15|A|16|B|C]|$9|17|A|18|B|C]|$9|19|A|1A|B|C]|$9|1B|A|1C|B|C]]|D|@$9|1D|A|1E|1|1F]]|E|$]]|$1|F|3|G|5|6|7|1G|8|@]|D|@$9|1H|A|1I|1|1J]|$9|1K|A|1L|1|1M]]|E|$]]|$1|H|3|I|5|6|7|1N|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|1O|8|@]|D|@$9|1P|A|1Q|1|1R]]|E|$]]|$1|L|3|M|5|N|7|1S|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|1T|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]|W|$5|R|S|T|E|$U|X]]|Y|$5|R|S|T|E|$U|Z]]|10|$5|R|S|T|E|$U|11]]]]

<a href="http://en.wikipedia.org/wiki/Foreign_key" rel="nofollow noreferrer">Foreign keys</a> will guarantee that a row in a table <code>order_details</code> with a field <code>order_id</code> referencing an <code>orders</code> table will never have an <code>order_id</code> value that doesn't exist in the <code>orders</code> table. 

Foreign keys aren't required to have a working relational database, but they are definitely essential to avoid broken relationships and orphan rows (ie. <a href="http://en.wikipedia.org/wiki/Referential_integrity" rel="nofollow noreferrer">referential integrity</a>). The ability to enforce referential integrity at the database level is required for the C in <a href="http://en.wikipedia.org/wiki/ACID" rel="nofollow noreferrer">ACID</a> to stand.

As for your concerns regarding performance, in general the performance hit, if any, will be negligible. I suggest putting in all your foreign key constraints, and only experiment without them if you have real performance issues that you cannot solve otherwise.

In addition, as a side-note, while not directly related to MySQL, this is quote from <a href="http://msdn.microsoft.com/en-us/library/ms998577.aspx" rel="nofollow noreferrer">Microsoft Patterns and Practices: Chapter 14 Improving SQL Server Performance</a>:

<blockquote>
 When primary and foreign keys are
 defined as constraints in the database
 schema, the server can use that
 information to create optimal
 execution plans.
</blockquote>

blocks|key|1393449|text|Web应用程序与任何其他类型的应用程序没有什么不同。当然，您可以从省略数据完整性检查中获得一个边际的加速，但这不会太大，而且代价太高。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1393450|在没有引用完整性的情况下，一旦您得到一个悬空的外键(由于一些测试不当的代码路径或部分更新失败)，许多依赖该键指向某物的查询就会中断。这通常会让您的整个网站崩溃，直到您深入数据库并手动删除悬空引用。|1393451|*：除非你用的是交易。但让我们面对现实吧，如果你是曾傑瑞那种没有引用完整性的网络应用程序的作者，那么你也很难使用交易。)|1393452|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Web applications are no different from any other kind of application. Sure, you may be able to get a marginal speedup from omitting data integrity checks, but it's not going to be a lot, and the price is too high.

Without referential integrity, as soon as you get a dangling foreign key (because of some improperly-tested code path or a failed partial update*) a lot of your queries, that were relying on that key pointing to something, will break. This very commonly makes your whole web site fall over until you delve into the database and delete the dangling reference manually.

(*: unless you're using transactions. But let's face it, if you're the kind of author who jerry-rigs web applications without referential integrity, you're hardly going to be using transactions either.)

blocks|key|177363|text|将来，您可能会有其他一些软件在您的应用程序的数据库中操作数据。例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|177364|从第三方系统导入大量数据|unordered-list-item|177365|批处理，即大量的实时处理。|177366|等|177367|通过使用外键，您将确保数据库的完整性得到维护，因为任何违反密钥的操作都会导致错误。|177368|IMHO，性能增益(如果有的话？)不值得冒破坏数据库完整性的潜在风险。|177369|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|Q|8|@]|9|@]|A|$]]|$1|G|3|H|5|D|7|R|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|S|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|T|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|N|$]]

In the future, you may have some other piece of software manipulating data in your application's database. For example:

<ul>
<li>Bulk data imports from a 3rd party system</li>
<li>Batch processing that is to heavy to do in real time</li>
<li>etc</li>
</ul>

By using the foreign keys, you will ensure that the integrity of your database is maintained as any operations that violate the keys will result in an error.

IMHO, the performance gain (if any??) is not worth the potential risk of corrupting the integrity of your database.

blocks|key|177397|text|外键都是关于完整性和功能的，而不是性能。如果希望在两组列之间强制执行引用完整性，那么外键通常是最有效和最有效的方法。如果您不需要支持这样的完整性约束，那么显然性能问题也不会出现。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|177398|外键可能通过支持某些类型的查询重写来提高性能，否则这是不可能的。然而，这主要是附带的好处--这是确保强制执行的副作用。|177399|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Foreign keys are all about integrity and functionality, not performance. If you wish to enforce referential integrity between two sets of columns then a foreign key is typically the most efficient and effective way to do it. If you don't need to support such an integrity constraint then obviously the performance question doesn't arise anyway.

It is possible that a foreign key could improve performance by supporting certain types of query rewrite that wouldn't otherwise be possible. Howevever that's mainly a fringe benefit - a side effect of ensuring that the constraint is being enforced.

blocks|key|1727517|text|外部密钥是您和数据完整性问题之间的一个有用的障碍。他们告诉数据库代表您做额外的工作，以确保您定义的完整性被强制执行，不管一些应用程序代码有多么糟糕。这在他们的职业生涯中至少挽救了每个DB程序员的脖子。你绝对应该在你的模型中设计外键，特别是当你认为你不需要外键的时候。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1727518|也就是说，如果您在数据库层上限制可伸缩性，那么FKs可能会被关闭，因为理论上说，FKs是在重复您的应用程序已经在做的工作(也就是说，您的应用程序不应该被编码成蓄意破坏关系完整性的事情，如果不是FKs的话)。这并不是针对心灰意冷的人，因此并不总是性能上的胜利(一些联接更有效率，因为DB知道某些关系是否可以产生行)。|1727519|与所有优化一样，规则1是“不要做它”，第2条规则是“不要做它(还)”。确保如果你要沿着这条路走：|1727520|1)您有一个特定的性能负载数据来支持您的决定|1727521|2)您有能力用开关打开或关闭它们(即不要删除它们并丢弃代码，而且绝对不要忽略设计它们)。|1727522|3)您有能力定期验证您设计的完整性约束是否被应用程序代码正确地强制执行(例如，一项夜间作业，它验证没有孤儿)。|1727523|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Q|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

Foreign Keys are a useful barrier between you and data integrity issues. They tell the database to do extra work on your behalf to make SURE that the integrity you define is enforced, no matter how badly some application code screws up. This has saved every DB programmer's neck at least once in their career. You should absolutely design foreign keys into your model, especially if you think you don't need them.

That said, if you're pushing the limits of scalability on your database tier, FKs can be something you turn off, because in theory, they're doing duplicate work that your app is already doing (that is, your app shouldn't be coded to intentionally do things that would break relational integrity, if not for the FKs). This isn't for the faint of heart, and for that matter, it isn't ALWAYS a performance win (some JOINs are more efficient because the DB knows whether certain relationships can or cannot have resulting rows).

As in all things optimization, rule 1 is "Don't do it" and rule 2 is "Don't do it (yet)". Make sure that if you're going down this path: 

1) you have a specific performance load data to back up your decision

2) you have the ability to turn them on or off with a switch (i.e. don't delete them and throw away the code, and definitely don't just neglect to design them in the first place)

3) you have some ability to periodically verify that the integrity constraints you designed are being properly enforced by the application code (for example, a nightly job that verifies there are no orphans)

blocks|key|1392642|text|你的问题就像说赛车不需要方向盘一样，因为它减慢了它们的速度--毫无意义。所有的汽车都需要一个方向盘(至少今天制造的汽车是这样)。其他人已经详细解释了为什么外键对数据完整性有用。此外，我想指出，现代语言和框架可以使用数据库中的外键元数据来建立对象关系。Linq非常优雅地做到了这一点。在数据关系授权时，如果不定义外键，就不能真正地使用对象-关系映射框架。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1392643|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Your question is like arguing that race cars don't need steering wheels because it slows them down - nonsensical. All cars need a steering wheel (at least the cars that are built today).
Others have already explained in detail why foreign keys are useful for data integrity.
In addition, I would like to point out that modern languages and frameworks can use the foreign key metadata in the database to establish object relationships. Linq-to-SQL does this very elegantly. You cannot really work with an Object-relational mapping framework without defining foreign keys whenever data relationships mandate them.

blocks|key|1727589|text|除了这里提到的所有好理由(实际上没有没有理由不使用它们)之外，还有另外一个很好的理由：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1727590|外键记录您的数据模型，使人们更容易理解它(这类似于cdonner提到的优点)。|1727591|entityMap^0|I|2|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

Apart from all the good reasons mentioned here (actually there is no reason not using them), there is another good one: 

Foreign keys document your data model and makes it easier for humans to understand it (this is similar to the advantage that cdonner mentioned)

I've encountered this discussion related to FK's and web applications. Basically some people say that FK's in web applications doesn't represent a real improvement and can even make the application slower in some cases.

What do you guys think, what's your experience?

edit : note that I'm aware of FK's job and objective, I'm just not sure if they would have a significant negative impact on the performance of a web application like youtube or something similar. 

--

A quote from Heikki Tuuri, creator of InnoDB engine, founder and CEO of Innobase:

<blockquote>
 InnoDB checks foreign keys as soon as
 a row is updated, no batching is
 performed or checks delayed till
 transaction commit Foreign keys are
 often serious performance overhead,
 but help maintain data consistency
 
 Foreign Keys increase amount of row
 level locking done and can make it
 spread to a lot of tables besides the
 ones directly updated
</blockquote>

MySQL - are FK's useful / viable in a web app?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我遇到过与FK和web应用程序相关的讨论。基本上，有些人说FK在web应用程序中并不代表真正的改进，在某些情况下甚至可以使应用程序变慢。你们觉得呢，你们有什么经验？编辑：请注意，我知道FK的工作和目标，我只是不确定它们是否会对youtube之类的web应用程序的性能产生显著的负面影响。--这句话来自Heikki Tuu...

问MySQL -FK在网络应用程序中有用/可行吗？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL -FK在网络应用程序中有用/可行吗？EN