向MySQL引入外键可以降低性能 [英] Does introducing foreign keys to MySQL reduce performance

问题描述

我正在构建Ruby on Rails 2.3.5应用程序。默认情况下，Ruby on Rails不提供外键限制，所以我必须手动执行。我想知道如果引入外键可以减少数据库端的查询性能，使其不值得做。在这种情况下，性能是我的首要任务，因为我可以检查与代码的数据一致性。你的建议是什么？你推荐使用外键吗？你如何建议我测量这个？

解决方案

假设：

<您已经在使用支持FK的存储引擎（例如：InnoDB）

您已经在所涉及的列上有索引

然后我会猜测，通过让MySQL执行完整性，您将获得更好的性能。执行引用完整性，毕竟是数据库引擎优化做的事情。编写自己的代码来管理Ruby的完整性将比较慢。

如果您需要从MyISAM移动到InnoDB以获取FK功能，则需要考虑两个引擎之间的性能折衷。

如果您还没有指标，则需要决定是否要求它们。一般来说，如果您的读取次数超过写入次数，那么您需要（甚至需要）这些指标。

在当前编入索引的内容之上堆叠FK应该会在应用程序代码中实现更少的整体性能，而不是执行这些类型的检查。

I'm building Ruby on Rails 2.3.5 app. By default, Ruby on Rails doesn't provide foreign key contraints so I have to do it manually. I was wondering if introducing foreign keys reduces query performance on the database side enough to make it not worth doing. Performance in this case is my first priority as I can check for data consistency with code. What is your recommendation in general? do you recommend using foreign keys? and how do you suggest I should measure this?

解决方案

Assuming:

1. You are already using a storage engine that supports FKs (ie: InnoDB)
2. You already have indexes on the columns involved

Then I would guess that you'll get better performance by having MySQL enforce integrity. Enforcing referential integrity, is, after all, something that database engines are optimized to do. Writing your own code to manage integrity in Ruby is going to be slow in comparison.

If you need to move from MyISAM to InnoDB to get the FK functionality, you need to consider the tradeoffs in performance between the two engines.

If you don't already have indicies, you need to decide if you want them. Generally speaking, if you're doing more reads than writes, you want (need, even) the indicies.

Stacking an FK on top of stuff that is currently indexed should cause less of an overall performance hit than implementing those kinds of checks in your application code.

这篇关于向MySQL引入外键可以降低性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！