我应该规范化我的DB吗？ [英] Should I normalize my DB or not?
问题描述
在设计数据库（例如MySQL）的模式时，会出现是否完全规范化表的问题。
When designing a schema for a DB (e.g. MySQL) the question arises whether or not to completely normalize the tables.
一方面，连接，等等）非常慢，另一方面，你会得到冗余的数据和潜在的不一致。
On one hand joins (and foreign key constraints, etc.) are very slow, and on the other hand you get redundant data and the potential for inconsistency.
优化最后一个正确的方法在这里？即创建一个书籍规范化的DB，然后看看什么可以被非规范化以实现最佳的速度增益。
Is "optimize last" the correct approach here? i.e. create a by-the-book normalized DB and then see what can be denormalized to achieve the optimal speed gain.
我的恐惧，关于这种方法，解决可能不够快的DB设计 - 但在那个阶段重构模式（同时支持现有数据）将是非常痛苦的。这是为什么我试图暂时忘记我所学到的关于适当的RDBMS实践的一切，并尝试一次平台方法。
My fear, regarding this approach, is that I will settle on a DB design that might not be fast enough - but at that stage refactoring the schema (while supporting existing data) would be very painful. This is why I'm tempted to just temporarily forget everything I learned about "proper" RDBMS practices, and try the "flat table" approach for once.
推荐答案
哲学答案：次优（关系）数据库有插入，更新和删除异常。这些都导致数据不一致，导致数据质量差。如果你不能相信你的数据的准确性，它有什么好处？问自己这个：你想要正确的答案更慢还是你想更快地得到错误的答案？
A philosophical answer: Sub-optimal (relational) databases are rife with insert, update, and delete anomalies. These all lead to inconsistent data, resulting in poor data quality. If you can't trust the accuracy of your data, what good is it? Ask yourself this: Do you want the right answers slower or do you want the wrong answers faster?
作为一个实际问题：在你快速得到它之前。我们人类很难预测瓶颈会发生在哪里。使数据库更好，在一个体面的时间段测量性能，然后决定是否需要使其更快。在你去正规化和牺牲准确性之前尝试其他技术：你能得到更快的服务器，连接，数据库驱动程序等吗？可能的存储过程加快了事情？如何索引和它们的填充因子？如果那些和其他性能和调优技术不做的伎俩，只有那时考虑反规范化。然后测量性能，以验证您的付费的速度的增加。
As a practical matter: get it right before you get it fast. We humans are very bad at predicting where bottlenecks will occur. Make the database great, measure the performance over a decent period of time, then decide if you need to make it faster. Before you denormalize and sacrifice accuracy try other techniques: can you get a faster server, connection, db driver, etc? Might stored procedures speed things up? How are the indexes and their fill factors? If those and other performance and tuning techniques do not do the trick, only then consider denormalization. Then measure the performance to verify that you got the increase in speed that you "paid for". Make sure that you are performing optimization, not pessimization.
[edit]
Q：所以如果我最后优化，你可以
推荐一个合理的方式迁移
数据后改变模式？如果，
例如，我决定摆脱
查找表 - 我如何将
现有数据库迁移到这个新设计？
Q: So if I optimize last, can you recommend a reasonable way to migrate data after the schema is changed? If, for example, I decide to get rid of a lookup table - how can I migrate existing databased to this new design?
A：当然。
- 备份。
- 对其他装置进行其他备份。
- 使用select into newtable from oldtable ...类型命令创建新表。
- 删除旧表格。
- 重命名新表格。
- Make a backup.
- Make another backup to a different device.
- Create new tables with "select into newtable from oldtable..." type commands. You'll need to do some joins to combine previously distinct tables.
- Drop the old tables.
- Rename the new tables.
但 ...考虑采用更稳健的方法：
BUT... consider a more robust approach:
立即在完全规范化的表上创建一些视图。这些视图（虚拟表，数据上的窗口...问我是否想了解更多关于此主题）将具有与上述步骤3相同的定义查询。当您编写应用程序或DB层逻辑时，使用视图（至少对于读访问;可更新视图是...好，兴趣）。然后，如果你以后denormalize，创建一个新的表如上，删除视图，重命名新的基表，无论视图是什么。你的应用程序/数据库层不会知道差异。
Create some views on your fully normalized tables right now. Those views (virtual tables, "windows" on the data... ask me if you want to know more about this topic) would have the same defining query as step three above. When you write your application or DB-layer logic, use the views (at least for read access; updatable views are... well, interestsing). Then if you denormalize later, create a new table as above, drop the view, rename the new base table whatever the view was. Your application/DB-layer won't know the difference.
实际上，这实际上有更多的东西，但这应该让你开始。
There's actually more to this in practice, but this should get you started.
这篇关于我应该规范化我的DB吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
