规范化数据库对资源的影响是什么？ [英] What is the resource impact from normalizing a database?

问题描述

当从相对非规范化的形式获取数据库并将其标准化时，资源利用率中可能会出现什么（如果有）变化

？

例如，规范化通常意味着从更少的表创建更多的表，这意味着数据库现在具有更多的表，但是其中许多表非常小，允许经常使用的表更好地适应内存。

表的数量越多，还意味着需要更多的连接（潜在地）来获取被抽出的数据，所以人们会期望来自更高数量的连接的某种影响系统需要做。

那么，对资源使用（即什么会改变）的影响是什么，归一化非规范化数据库有什么？

---

编辑：
要添加一点上下文，我有一个现有的（即遗留）数据库，有300多个可怕的表。大约1/2的数据是TEXT，另一半是字段或整数。没有任何形式的限制。我问的原因主要是获得更多的信息，说服他人的事情需要改变，不会降低性能或可维护性。不幸的是，我必须说服我们知道一个非规范化数据库的性能优势，希望尽可能避免规范化。

解决方案

这不能真正地以一般的方式回答，因为影响会根据有问题的数据库和使用它的应用程序的具体情况严重变化。

因此，您基本上说明了对影响的一般预期：

1. 存储的总体内存需求应该下降
可能会上升，因为查询可能会更昂贵（请注意，在许多情况下，查询对于标准化数据库，实际上会更快，即使它们更复杂，因为查询引擎还有更多的优化选项）。

2. 开发资源需求因为开发人员需要构建更精细的查询（但另一方面，您需要更少的开发工作来维护数据完整性）

所以唯一真正的答案是通常的：它取决于）

注意：这假设我们在谈论谨慎有意的反规范化。如果你正在引用'只是抛出一些表，因为数据来自与无经验的开发人员的常见方法，我冒风险说，规范化将减少所有级别的资源需求的声明）;

---

编辑：关于cdeszaq添加的特定上下文，我会说祝你好运， / p>

显然，有超过300个表和没有约束（！），你的问题的答案肯定是正常化将减少所有级别的资源需求但

重构这样的混乱将是一项重大的工作。如果只有一个应用程序使用这个数据库，它已经是可怕的 - 如果有很多，它可能成为一个噩梦！

因此，即使规范化将大大减少资源需求从长远看，它可能不值得的麻烦，根据情况。这里的主要问题是关于长期范围 - 这个数据库有多重要，它将被使用多长时间，将来有更多的应用程序使用它，是当前的维护努力常数还是增加等。 p>

不要忽略它是一个运行系统 - 即使它是丑陋和可怕的，根据你的描述 ）broken ; - ）

When taking a database from a relatively un-normalized form and normalizing it, what, if any, changes in resource utilization might one expect?

For example, normalization often means more tables get created from fewer which means the database now has a higher number of tables, but many of them are quite small, allowing the often used ones to fit into memory better.

The higher number of tables also means that more joins are needed (potentially) to get at the data that was abstracted out, so one would expect some sort of impact from the higher number of joins the system needs to do.

So, what impact on resource usage (ie. what will change) does normalizing an un-normalized database have?

---

Edit: To add a bit of context, I have an existing (ie. legacy) database with over 300 horrible tables. About 1/2 of the data is TEXT and the other half is either char fields or integers. There are no constraints of any kind. The reason I ask is primarily to get more information for convincing others that things need to change and that there won't be a decrease in performance or maintainability. Unfortunately, those I have to convince know just enough about the performance benefits of a de-normalized database to want to avoid normalization as much as possible.

解决方案

This can not really be answered in a general manner, as the impact will vary heavily depending on the specifics of the database in question and the apps using it.

So you basically stated the general expectations concerning the impact:

1. Overall memory demands for storage should go down, as redundant data gets removed
2. CPU needs might go up, as queries might get more expensive (Note that in many cases queries on a normalized database will actually be faster, even if they are more complex, as there are more optimization options for the query engine)
3. Development resource needs might go up, as developers might need to construct more elaborate queries (But on the other hand, you need less development effort to maintain data integrity)

So the only real answer is the usual: it depends ;)

Note: This assumes that we are talking about cautious and intentional denormalization. If you are referring to the 'just throw some tables together as data comes along' approach way to common with inexperienced developers, I'd risk the statement that normalization will reduce resource needs on all levels ;)

---

Edit: Concerning the specific context added by cdeszaq, I'd say 'Good luck getting your point through' ;)

Oviously, with over 300 Tables and no constraints (!), the answer to your question is definitely 'normalizing will reduce resource needs on all levels' (and probably very substantially), but:

Refactoring such a mess will be a major undertaking. If there is only one app using this database, it is already dreadful - if there are many, it might become a nightmare!

So even if normalizing would substantially reduce resource needs in the long run, it might not be worth the trouble, depending on circumstances. The main questions here are about long term scope - how important is this database, how long will it be used, will there be more apps using it in the future, is the current maintenance effort constant or increasing, etc. ...

Don't ignore that it is a running system - even if it's ugly and horrible, according to your description it is not (yet) broken ;-)

这篇关于规范化数据库对资源的影响是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！