是真的更好使用规范化表吗？ [英] Is it really better to use normalized tables?

问题描述

我听说我的团队领导说，在过去的一些项目中，他们不得不消除正常化，使查询速度更快。

I heard my team leader say that in some past projects they had to do away with normalization to make the queries faster.

我认为它可能有些事情

有更多的精益表比少数胖表效率低吗？

Is having more lean tables really less efficient than having few fat tables?

推荐答案

它依赖...连接表本质上比有一个大表是预连接，即非规范化。但是，通过取消规范，您将创建数据重复，您的表将更大。规范化被视为一件好事，因为它创建了可以回答任何问题的数据库，如果正确完成，您可以构建一个选择以获取您的数据。在DB的一些其他形式中不是这样，并且那些是现在（大多数）历史不相关的，规范化/关系DB赢得了这场战斗。

It depends ... joining tables is inherently slower than having one big table that is 'pre-joined' ie de-normalised. However, by denormalising you're going to create data duplication and your tables are going to be larger. Normalisation is seen as a good thing, because it creates databases that can answer 'any' question, if it is properly done you can build a select to get to your data. This is not the case in some other forms of DB, and those are now (mostly) historic irrelevancies, the normalised/relation DB won that battle.

返回到您的问题，使用反标准化来使事情变得更快是一种公认​​的技术。通常最好是运行你的DB一段时间，所以你知道什么去反标准化和什么独自留下，也常见的是将数据保留在其正确规范化形式，并将数据拉入一组反规范化报告表定期。如果该过程是作为报表运行本身的一部分完成的，那么数据总是最新的。

Back to your question, using de-normalisation to make things go faster is a well accepted technique. It's normally best to run your DB for a while so you know what to de-normalise and what to leave alone, and it's also common to leave the data in its 'correct' normalised form and pull data into a set of de-normalised reporting tables on a regular basis. If that process is done as part of the report run itself then the data is always up to date too.

作为过度规范化的一个例子，我看到DBs过去的一周中的几天和一年中的几个月被拉出成单独的表 - 日期本身被规范化 - 你可以走得太远。

As an example of over-normalisation I've seen DBs in the past where the days of the week, and months of the year were pulled out into separate tables - dates themselves were normalised - you can go too far.

这篇关于是真的更好使用规范化表吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！