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

问题描述

当从一个相对非规范化的形式中取出一个数据库并对其进行规范化时，如果有的话，资源利用率可能会发生什么变化?

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

更多的表也意味着(可能)需要更多的联接来获取被抽象出来的数据，因此人们会期望系统需要执行的更多联接产生某种影响.

>

那么，规范化未规范化的数据库对资源使用(即会发生什么变化)有什么影响?

<小时>

为了添加一些上下文，我有一个现有(即遗留)数据库，其中包含 300 多个可怕的表.大约 1/2 的数据是 TEXT，另一半是字符字段或整数.没有任何限制.我询问的原因主要是为了让其他人相信事情需要改变并且不会降低性能或可维护性.不幸的是，那些我必须说服的人对非规范化数据库的性能优势有足够的了解，因此希望尽可能避免规范化.

解决方案

这真的不能用一般的方式回答，因为影响会很大取决于所讨论的数据库的细节以及使用它的应用程序.

所以您基本上说明了对影响的总体预期:

1. 随着冗余数据被删除，存储的整体内存需求应该会下降
2. CPU 需求可能上升，因为查询可能变得更昂贵(请注意，在许多情况下，标准化数据库上的查询实际上会更快，即使它们更多复杂，因为查询引擎有更多优化选项)
3. 开发资源需求可能上升，因为开发人员可能需要构建更精细的查询(但另一方面，您需要更少的开发工作来维护数据完整性)

所以唯一真正的答案是通常的:这取决于;)

注意:这假设我们正在谈论谨慎和有意非规范化.如果您指的是 '在数据出现时只将一些表格放在一起' 方法对于没有经验的开发人员来说是通用的，我会冒着这样的风险声明规范化将减少所有级别的资源需求 ;)

<小时>

关于 cdeszaq 添加的特定上下文，我想说祝你好运"；)

显然，有超过 300 个表并且没有限制(！)，您的问题的答案肯定是规范化将减少所有级别的资源需求"(并且可能非常显着)，但是:

重构这样一团糟将是一项重大任务.如果只有一个应用程序使用这个数据库，那已经很可怕了——如果有很多应用程序，它可能会变成一场噩梦！

因此，即使从长远来看，规范化会大大减少资源需求，但根据情况，它可能不值得.这里的主要问题是关于长期范围的——这个数据库有多重要，它会被使用多久，未来是否会有更多的应用程序使用它，当前的维护工作是不变的还是增加的，等等......

不要忽视它是一个正在运行的系统 - 即使它很丑陋和可怕，根据你的描述它(还)没有坏 ;-)

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屋！