更新非规范化数据库表 [英] Updating denormalized database tables
问题描述
我使用的是Ruby on Rails 3.0.7和MySQL 5.在我的应用程序中，我有两个数据库表，比如TABLE1和TABLE2，为了性能原因，我已经去正规化了TABLE2中的一些数据，所以我重复了TABLE1的值那个。现在，在TABLE1中，我需要更新一些涉及的值，当然，我必须正确更新TABLE2中的非规范化值。
I am using Ruby on Rails 3.0.7 and MySQL 5. In my application I have two database tables, say TABLE1 and TABLE2, and for performance reasons I have denormalizated some data in TABLE2 so that I have repeated values of TABLE1 in that one. Now, in TABLE1 I need to update some of those involved values and, of course, I must update properly also denormalized values in TABLE2.
我能做什么以高性能的方式更新这些值？也就是说，如果TABLE2包含很多值（1.000.000或更多），那么保持更新两个表（技术，pratices ...）的最佳方法是什么？
更新数据库表时会发生什么？例如，用户在加入一些涉及那些非正规化值的网站页面时可能会有一些问题？
What can happen during the time it takes to update the database tables? For example, an user can have some problems on acceding some web site pages involving those denormalized values? If so, what those are and how can I handle the situation?
推荐答案
有几种方法可以处理这种情况：
There are a few ways to handle this situation:
- 您可以使用数据库触发器。这不是一个数据库不可知的选项，它的RoR支持是不存在的，据我所知。如果你的情况需要绝对没有数据不一致这可能是最高效的方式来实现你的目标，但我不是数据库专家。
- 可以使用批处理操作定期同步两个表。此方法允许您的两个表漂移分开，然后经常重新同步数据。如果你的情况允许这种漂移发生，这可以是一个很好的选择，因为它允许DB在休息时间更新。如果你需要每隔5分钟做一次同步，你可能会想看看其他选项。这可以通过你的ruby代码来处理，但需要某种类型的后台作业运行器（cron，delayed_job，redis等）。
- 你可以使用Rails模型。您可以使用
after_update：sync_denormalized_data。此回调将包装在数据库级事务中（假设您的数据库支持事务）。你将有Rails级别的代码，一致的数据，并且不需要一个后台进程，每次做两次写入。 - 一些机制我没有想到....
- You can use a database trigger. This is not a database agnostic option and the RoR support of it is non-existent as far as I know. If your situation requires absolutely no data-inconsistency This would probably be the most performant way to achieve your goal, but I'm not a DB expert.
- You can use a batch operation to sync the two tables periodically. This method allows your two tables to drift apart and then re-synchronizes the data every so often. If your situation allows this drift to occur, this can a good option as it allows the DB to be updated during off hours. If you need to do the sync every 5 minutes you will probably want to look into other options. This can be handled by your ruby code, but will require a background job runner of some sort (cron, delayed_job, redis, etc.)
- You can use a callback from inside your Rails model. You can use
"after_update :sync_denormalized_data". This callback will be wrapped in a database level transaction (assuming your database supports transactions). You will have Rails level code, consistent data, and no need for a background process at the expense of making two writes every time. - Some mechanism I haven't thought of....
这些类型的问题是非常特定应用程式。即使在同一个应用程序中，您可以使用多种方法，具体取决于所涉及的灵活性和性能要求。
These types of issues are very application specific. Even within the same application you may use more than one of the methods depending on the flexibility and performance requirements involved.
这篇关于更新非规范化数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
