OLAP数据库为了提高读取性能应该去规范化吗？

Question

OLAP数据库为了提高读取性能应该去规范化吗？

databasedatabase-designdata-modelingolapdata-warehouse

66

我一直认为数据库应该为读性能而去规范化，就像OLAP数据库设计一样，不要过分强调OLTP设计的3NF。PerformanceDBA在各种帖子中（例如，在不同基于时间数据的方法的性能中）捍卫了数据库应始终通过规范化到5NF和6NF（正常形式）来进行良好设计的范例。

我理解得对吗（我理解得对什么）？

传统的OLAP数据库去规范化方法/范例设计（低于3NF）以及3NF足以满足大多数实际情况下的OLTP数据库的建议有什么问题？

例如：

“简单的真相是...... 6NF，如果正确执行，就是数据仓库”（PerformanceDBA）

我必须承认，我从未理解过去规范化有助于读取性能的理论。有人能给我提供具有良好逻辑解释的参考资料吗？还有相反的信仰？

当我试图说服我的利益相关者OLAP/数据仓储数据库应该被规范化时，我可以参考哪些来源以改善可见性？

为了提高可见性，我从评论中复制了这里：

“参与者最好在讨论中透露一下（公开）他们见过或参与过多少个现实生活中（不包括科学项目）的6NF数据仓库实现。类似于一个快速池。我=0。”- Damir Sudarevic 维基百科的数据仓库文章告诉我们：

“规范化方法[相对于Ralph Kimball的维度方法]，也被称为 3NF模型 （第三正常形式），其支持者被称为“Inmonites”，他们相信Bill Inmon的方法，即应使用E-R模型/规范化模型对数据仓库进行建模。”

看起来，Bill Inmon的规范化数据仓库方法被认为不超过3NF（？）

我只是想了解数据仓库/ OLAP等同于去规范化的神话（或普遍的公理信仰）的起源。

Damir Sudarevic回答说这是一种经过充分铺平的方法。让我回到问题：为什么认为去规范化有助于阅读？

- Gennady Vanin Геннадий Ванин

3

OLTP: 规范化，规范化，规范化... - Mitch Wheat

6

希望参与者能够添加（披露）他们曾见过或参与过多少个现实生活中的（不包括科学项目）6NF 数据仓库的实现。类似于快速汇总。我自己没有经验（0）。 - Damir Sudarevic

@vgv8，哎呀 - 你接手了最初的问题，这是一个极端化的问题，然后通过更新进行了改进和修改；将“我可以参考哪些来源来支持（说服我的利益相关者）OLAP / DataWareHousing数据库应该规范化？”和“传统的反规范化方法/ OLAP数据库的典范设计有什么问题（低于3NF）？”分开并没有错。虽然这些主题是相关的，但更具体的问题更容易得到好的答案。 - Unreason

关系型数据库管理系统（RDBMS）和在线分析处理（OLAP）是两回事。RDBMS必须进行规范化，而OLAP不受这些规则的限制。然而，我不喜欢自称PerformanceDBA的那个人的极端观点。我认为他是一个过于极端的人，超出了常识范围，并使事情变得过于复杂而没有增加任何好处。（我是在谈论他的反“空值”理论） - iDevlop

2

@iDevlop。1）您的技术资格是什么，您在生产高性能数据库方面的经验如何？2）您是否总是因为一个问题而拒绝整个人？3）请指出您认为哪一部分“过于复杂”，以便我指出该项的价值。4）显然，您没有阅读有关“空值问题”的内容，因此不知道这不是我的理论，或者问题是什么。 - PerformanceDBA

1

@DamirSudarevic。完整项目交付（不包括P＆T任务等）OLTP +一些OLAP = 20。完整的OLTP + OLAP = 4。OLAP = 2。 - PerformanceDBA

9个回答

10

在数据仓库中，规范化和聚合是实现性能的两种主要策略。建议不采用这些策略以提高读取性能是愚蠢的！我可能误解了些什么吗？

聚合： 考虑一个包含10亿个购买记录的表。将其与一个只有一行的表进行比较，该行包含所有购买记录之和。现在哪个更快？从拥有10亿行的表中选择sum(amount)还是从只有一行的表中选择amount？这当然是一个愚蠢的例子，但它很清楚地说明了聚合原理。为什么它更快？因为无论我们使用什么神奇的模型/硬件/软件/宗教，读取100字节总是比读取100GB更快。就这么简单。

规范化： 零售数据仓库中的典型产品维度具有大量列。一些列很简单，比如"名称"或"颜色"，但也有一些复杂的东西，比如层次结构。多个层次结构（产品系列（5级）、预期购买者（3级）、原材料（8级）、生产方式（8级））以及几个计算数字，例如平均交货时间（自年初以来）、重量/包装措施等等。我曾维护一个产品维度表，其中有200多列，由来自5个不同源系统的~70个表构建而成。争论在规范化模型上运行查询（如下所示）是否比更简单的非规范化模型更快是愚蠢的。

select product_id
  from table1
  join table2 on(keys)
  join (select average(..)
          from one_billion_row_table 
         where lastyear = ...) on(keys)
  join ...table70
 where function_with_fuzzy_matching(table1.cola, table37.colb) > 0.7
   and exists(select ... from )
   and not exists(select ...)
   and table20.version_id = (select max(v_id from product_ver where ...)
   and average_price between 10 and 20
   and product_range = 'High-Profile'

...相比于去规范化的模型，查询速度更快：

select product_id
  from product_denormalized
 where average_price between 10 and 20
   and product_range = 'High-Profile';

为什么要使用反规范化以及聚合？部分原因与聚合场景相同。但也因为这些查询太过于“复杂”。它们非常复杂，以至于优化器（现在我谈的是Oracle）会变得混乱并且破坏执行计划。如果查询处理的数据量很小，那么次优的执行计划可能并不是什么大问题。但是一旦我们开始加入大型表中，数据库就必须正确地获取执行计划是至关重要的。通过将单个合成键的数据非规范化到一个表中（为什么我不把更多燃料加入这场持续蔓延的火灾之中呢？），过滤器变成了预先处理列上的简单范围/等值过滤器。通过将数据复制到新列中，我们可以收集列的统计信息，帮助优化器估算选择性，并提供适当的执行计划。

显然，使用非规范化和聚合会使得适应模式变更更加困难，这是不好的事情。另一方面，它们提供了读取性能，这是一件好事情。

那么，你应该反规范化你的数据库以实现读取性能吗？当然不！它给系统增加了太多的复杂性，这将在项目交付之前对你造成无尽的困扰。但是，有时候为了满足特定的性能要求而需要这样做。

更新1

PerformanceDBA：每天会有1行数据被更新十亿次

这意味着（几乎）实时的要求（这又会产生完全不同的技术要求）。许多（如果不是大多数）数据仓库并没有这个要求。我选择了一个不切实际的聚合示例，只是为了让人们清楚聚合是如何工作的。我不想再解释聚合策略了。另外，必须对比数据仓库的典型用户和底层OLTP系统的典型用户的需求。一个希望了解什么因素驱动运输成本的用户，不会在乎今天50%的数据是否丢失，或者10辆卡车是否爆炸并杀死司机。即使他拥有按秒更新的最新信息，对两年的数据进行分析也会得出相同的结论。

与此形成对比的是那些卡车司机（幸存者）需要的需求。由于某些愚蠢的聚合过程需要完成，他们不能在某些中转站等待5小时。使用两个数据副本可以解决这两个需求。

在操作系统和报告系统共享相同数据的过程中，另一个主要障碍是发布周期、质量保证(Q&A)、部署、服务等方面非常不同。再次强调，使用两个副本可以更轻松地处理这些问题。

- Ronnis

1

@Unreason。实际上，严格意义上的聚合（或求和）并不会破坏任何正常形式。然而，每个人都意识到它会导致重复和修改异常。这两种不良属性是NF所要解决的问题。所以我想这归结于我们试图实现什么。是满足正常形式，还是消除重复和修改异常。 - Ronnis

@Ronnis，是的，但这就是重点——声称可以找到6NF导致高效DW的说法并不禁止使用聚合和求和。再加上自然键，根据跨越2个以上连接的自然关系“聚集”数据，突然之间声称最佳的DW设计决策是6NF听起来一点也不不切实际。 - Unreason

1

@Ronnis。如果你解决了那个问题（例如1行聚合不现实），我本来想给你一个+1的。更新：我非常清楚（有经验）所有这些。但这些不是维护两个不同副本的好理由；它们不能成本效益。（还有其他理由可以证明DW的必要性。） - PerformanceDBA

1

@Ronnis：你已经很好地阐述了你的立场：“我要反驳的是，任何高于某个正常形式的模型都会在定义上优于同一模型的任何较低正常形式。” 假设你想要支持你的说法，请给我一个2或3NF结构，我将为你生成其5NF版本并运行基准测试。此外，性能是上下文相关的，不仅仅是孤立的一个查询。还要检查Anchor建模。 - PerformanceDBA

1

@Ronnis：当然，这是科学。在数据库方面纯粹的第五范式；在数据仓库方面是第五范式加上OLAP处理所需的聚合等，但不能违反范式。抱歉，我以为如果我质疑你的说法，那么我就是持有相反观点的。根据我的经验，最大的问题是人们认为/声称他们的数据库符合xNF，但实际上并不符合xNF，这导致了速度缓慢等问题。你的结构必须至少包含一个透视表。 - PerformanceDBA

显示剩余3条评论

6

我理解您说的"OLAP"是指用于决策支持的面向主题的关系型/SQL数据库，也称为数据仓库。

对于数据仓库来说，通常最好采用正规化模式（通常是第五/第六范式）。正规化数据仓库的原因与任何其他数据库相同：它减少了冗余并避免了潜在的更新异常；它避免了内置偏见，因此是支持架构更改和新需求的最简单方法。在数据仓库中使用正规化形式还有助于保持数据加载过程的简单和一致性。

不存在“传统”的反规范化方法。良好的数据仓库始终是正规化的。

- nvogel

1

私下里，我指的是通过MDX接口访问的某些内容，但公开地，我只是泛泛地提出概念性问题。你提到了一些我不太理解的不同宗教，能否给我一些参考资料？ - Gennady Vanin Геннадий Ванин

1

@vgv8：Bill Inmon的书《构建数据仓库》是一个很好的起点。 - nvogel

+1 避免内置偏见，事实上，第五范式/第六范式以非常细粒度和可比较的方式公开了每个属性的数据访问路径，因此查询规划器将找到有效的执行计划（好的规划器会这样做——可能的访问路径数量通常与连接数成比例！），并且特定于需要即席分析报告（OLAP / DW）的情况完美地适合其中。 - Unreason

谢谢，但我会感激在互联网上公开可得的（可能更简洁/有针对性的）参考资料/网址。同时，如果我有时间阅读（所有）书籍，我就不会在论坛上提问了。此外，我不能推荐任何人看书，因为那会被视为傲慢。 - Gennady Vanin Геннадий Ванин

内置偏差、冗余、更新异常和加载复杂性都是众所周知的权衡，人们必须仔细权衡潜在的性能收益。例如，您是否不赞成维度表？它们通常处于第二范式。 - Ronnis

5

一个数据库应该为了读取性能而去规范化吗？

好的，这里有一个完整的答案：“你的经验可能不同”，“它取决于情况”，“为每个任务使用适当的工具”，“一种尺码并不适合所有人”，还有一点“不破坏它就不要修复它”的建议：

在某些情况下，规范化是提高查询性能的一种方式。在其他情况下，它可能会降低性能（因为增加了磁盘使用）。它确实使更新更加困难。

仅当您遇到性能问题时（因为您正在获得规范化的好处并引入复杂性）才应考虑它。

规范化的缺点在永远不会更新或仅在批处理作业中更新的数据中不太重要，即非 OLTP 数据。

如果规范化解决了您需要解决的性能问题，并且较少侵入性的技术（如索引或缓存或购买更大的服务器）无法解决，则应该这样做。

- Thilo

3

首先是我的观点，然后是一些分析。

观点
去规范化被认为有助于读取数据，因为常用的去规范化术语不仅包括打破正常形式，还包括将任何插入、更新和删除依赖项引入系统。

严格来说，这是错误的，请参见此问题/答案，严格意义上的去规范化意味着从1NF-6NF中打破任何正常形式，其他插入、更新和删除依赖关系则使用正交设计原则解决。

所以人们会采用空间与时间权衡原则，记住冗余术语（与去规范化相关，但并不等同于它），并得出你应该获得好处的结论。这是错误的推断，但错误的推断不能让你得出相反的结论。

打破正常形式可能确实加快某些数据检索（下面的分析中有详细说明），但通常同时会：

只支持特定类型的查询并减慢所有其他访问路径
增加系统的复杂性（这不仅影响数据库本身的维护，还增加了消耗数据的应用程序的复杂性）
混淆和削弱数据库的语义清晰度
数据库系统的主要目的是作为表示问题空间的中央数据，要在记录事实时保持公正，以便当需求发生变化时，你不必重新设计实际上是独立的系统部分（数据和应用程序）。为了能够做到这一点，应尽量减少人为依赖关系——今天“关键”的要求加速一个查询往往只是次要的。

分析

因此，我声称有时打破正常形式可以帮助检索。现在是提供一些论据的时间。

1) 打破 1NF

假设你有6NF的财务记录。从这样的数据库中，你肯定可以得到每个帐户每个月的余额报告。

假设需要计算此类报告的查询需要通过n条记录，你可以制作一张表：

account_balances(month, report)

这段内容涉及IT技术，讨论一个存储XML结构余额的账户，这将打破第一正规化（请参见后面的注释），但允许执行特定查询时最小化I / O。

同时，假设可以通过插入、更新或删除财务记录来更新任何月份，则系统上的更新查询性能可能会随着n的某个函数的时间成比例地减慢，每次更新都会受到影响。（以上情况说明了一个原则，在现实中，您将有更好的选择，而获得最小I / O的好处也会带来代价，对于实际经常更新数据的实际系统，即使针对目标查询，根据实际工作量的类型，您也会获得不良表现。如果您想要，我可以详细解释这一点）

注：这实际上是一个微不足道的例子，其中有一个问题-第一正规化的定义。假设上述模型违反了1NF是由于要求属性“确切地包含适用域中的一个值”的价值。

这使您可以说报告属性的域是所有可能报告的集合，并且从所有报告中仅有一个值，并声称未破坏1NF（类似于存储单词不会破坏1NF的论点，即使您可能会在模型中的某个地方有一个“字母”关系）。

另一方面，有更好的方法来建模此表格，这将对更广泛的查询更有用（例如检索一年中所有月份的单个账户余额）。在这种情况下，您将通过说该字段不符合1NF来证明其改进。

无论如何，这解释了为什么人们声称打破NF可能会提高性能。

2）违反第三正规化

假设表处于第三正规化状态

CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `member_id` int(10) unsigned NOT NULL,
  `status` tinyint(3) unsigned NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `opening` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`),
  CONSTRAINT `t_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `m` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

CREATE TABLE `m` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

使用样本数据（t中有100万行，m中有10万行）

假设您想要改进的是一个常见的查询。

mysql> select sql_no_cache m.name, count(*) 
       from t join m on t.member_id = m.id 
       where t.id between 100000 and 500000 group by m.name;
+-------+----------+
| name  | count(*) |
+-------+----------+
| omega |       11 |
| test  |        8 |
| test3 |   399982 |
+-------+----------+
3 rows in set (1.08 sec)

您可以将属性name移动到表格m中，但这会破坏第三范式（它具有FD：member_id -> name，而member_id不是t的关键字），以下是建议：

之后：

alter table t add column varchar(255);
update t inner join m on t.member_id = t.id set t.name = m.name;

运行

mysql> select sql_no_cache name, count(*) 
       from t where id 
       between 100000 and 500000 
       group by name;
+-------+----------+
| name  | count(*) |
+-------+----------+
| omega |       11 |
| test  |        8 |
| test3 |   399982 |
+-------+----------+
3 rows in set (0.41 sec)

备注：

上述查询的执行时间减少了一半，但是：

表开始时不符合5NF/6NF
测试使用no_sql_cache，因此大多数缓存机制被避免（在实际情况下，它们对系统性能起到作用）
空间消耗增加了约9倍的列名称大小x 100k行
t应该有触发器来保持数据完整性，这会显著减慢所有更新和添加额外检查的速度
通过放弃替代键并转换为自然键和/或索引，或重新设计更高的NF，可能可以实现更好的结果

规范化是长期的正确方法。但是，您并不总是有重新设计公司ERP的选择（例如已经只有大部分3NF）-有时您必须在给定资源内完成某些任务。当然，这样做只是短期的“解决方案”。

底线

我认为对您的问题最相关的答案是，在行业和教育中使用“去规范化”术语

严格意义上，用于打破NF
松散地，用于引入任何插入，更新和删除依赖关系（原始Codd的引用评论对规范化说：“不良（！）插入，更新和删除依赖关系”，请参见此处的一些详细信息）

因此，在严格定义下，聚合（摘要表）不被认为是去规范化，它们可以在性能方面提供很大帮助（任何缓存都会这样做，但不被视为去规范化）。

松散使用包括打破正常形式和正交设计原则，如前所述。

另一件可能有所启示的事情是逻辑模型和物理模型之间存在非常重要的区别。

例如，索引存储冗余数据，但没有人认为它们是去规范化，即使是松散使用该术语的人也是如此，并且有两个（相关）原因：

它们不是逻辑模型的一部分
它们是透明的，并且保证不会破坏模型的完整性

如果未正确建模逻辑模型，则最终将得到不一致的数据库-实体之间关系的错误类型（无法表示问题空间），冲突的事实（丢失信息的能力）和您应该采用任何方法来获得正确的逻辑模型，这是所有应用程序的基础。

规范化、正交和清晰的谓词语义、定义良好的属性、正确识别的函数依赖关系都对避免陷阱起着重要作用。

当涉及到物理实现时，事情变得更加宽松，例如基于非键的计算列可能会破坏第三范式，但是如果有保证一致性的机制，则允许在物理模型中使用它，就像允许索引一样。但是您必须非常小心地进行证明，因为通常规范化将在各个方面产生相同或更好的改进，并且不会或者对设计的影响较小（这降低了应用程序开发和维护成本），从而节省下来的费用可以轻松用于升级硬件以进一步提高速度，甚至超过破坏范式所实现的速度。

- Unreason

2

你提出了几个有趣的观点。我希望你能就以下问题发表评论：你的分析似乎建立在一个假设的基础上，即系统的所有方面都得到了同等的运用。这对于正式定义来说可能是可以接受的。但在任何实际情况下，情况并非如此。那些“未被充分利用”的方面为你提供了可以利用的权衡空间，以最大化那些被最多使用的方面的性能，有时甚至不会遭受后果。例如，在只修改一次的系统中，你不会遇到更新异常。 - Ronnis

1

@Ronnis，关系型数据库管理系统的整个重点在于数据访问路径的独立性（请参见http://www.scribd.com/doc/14498590/A-Relational-Model-of-Data-for-Large-Shared-Data-Banks），并且假设不是所有，而是几个视图对于数据同等重要。这意味着所有视图都同等重要，因为其中一些视图可能在建模时未知。例如，OLAP范例代表了运行不事先知道的不同分析的需求（数据挖掘也是如此；DW假定所有内容相等）- 这难道不是“系统的所有方面都同等重要”吗？ - Unreason

2

本质上，我反对的是任何模型本身都可以是最优的信念。我们需要一个特定的上下文/用途来评估该模型的可行性。然后我们已经离开了正式分析的领域。我并不是在反对实现任何完全规范化到6NF的数据库。（由于特定的分析要求，我已经决定不使用星型模式多次）。我反对的立场是，任何更高规范形式的模型都将在定义上优于同一模型中的任何较低规范形式。 - Ronnis

1

@Ronnis，这取决于你的指标。关系数据库的概念意味着（理想情况下）：如果你在企业的整个生命周期内衡量成本，并且你有一个所有组织单位都使用的中央系统，那么通过集中和规范化而不是分离子系统、优化和重新集成来最小化总成本。此外，在理论上它应该允许更多的灵活性。但在实践中，由于业务需求的冲突、安全、政治和复杂性（以及现有DBMS的限制），通常会出现子系统和优化。 - Unreason

@Unreason，(+1)。我同意你刚才写的一切。你提出的关键点是，“冲突的要求”决定了某些解决方案的子集，这可能并不总是符合“总体思路”。 - Ronnis

显示剩余3条评论

2

建立数据仓库（DW）的两种最流行的方法似乎是Bill Inmon和Ralph Kimball的方法。Inmon的方法使用规范化方法，而Kimball的方法使用维度建模--非规范化星型模式。两种方法都有详细的文档记录，都有许多成功的实现。两种方法都为DW目标提供了“宽阔、铺好的道路”。我无法评论6NF方法或Anchor Modelling方法，因为我从未见过也没有参与过使用该方法的DW项目。在实施方面，我喜欢走经过充分测试的路径--但这只是我的个人看法。因此，总结一下，DW应该规范化还是非规范化？这取决于您选择的方法--只需选择一个并坚持到项目结束即可。

编辑-一个例子

在我目前工作的地方，我们有一个传统报告，自从生产服务器上运行以来就一直在运行。不是简单的报告，而是每天向每个人和他们的客户发送30个子报告的集合。

最近，我们实施了一个DW。有两个报告服务器和一堆报告，我希望我们可以忘记过去的事情。但是不行，遗留问题就是遗留问题，我们总是需要它，需要它，离不开它等等。

问题在于一个混乱的Python脚本和SQL导致每天运行八个小时（是的，8个小时）。不用说，数据库和应用程序是由几批开发人员多年建立的——所以，不完全符合第五范式。

是时候从DW重新创建遗留问题了。好吧，简单地说，它已经完成了，只需要3分钟（三分钟）就能生成它，每个子报告只需要六秒钟。而且我很匆忙地交付，所以甚至没有优化所有查询。这是速度提高了160倍——更不用说从生产服务器中删除了一个8小时的作业的好处了。我认为我还可以节省一分钟左右的时间，但现在没有人在意。

值得一提的是，我使用了Kimball的方法（维度建模）来进行DW，并且这个故事中使用的所有内容都是开源的。

这就是整个数据仓库的目的，我想。使用规范化还是去规范化的方法并不重要，甚至有关吗？

另外，作为一个有趣的点，比尔·因蒙在他的网站上写了一篇很好的论文 -- 《两种架构的故事》（A Tale of Two Architectures）。（链接）

- Damir Sudarevic

1

Damir，是的，这很重要。规范化在一致性、可维护性和灵活性方面有着天壤之别 - 所有这些都应该对数据仓库的用户和所有者非常重要。我不确定你想说什么，但是暗示两种选择是等价的将是一个巨大的错误。 - nvogel

@Damir：我说认为DW是否非规范化并不重要是一个巨大的错误。你似乎在说这并不重要，但实际上对大多数人来说这非常重要。 - nvogel

1

@Damir。这是一个很棒的故事，我不想贬低它。但我们需要从技术上看待它。它并没有表明DW比DB更好，而是表明一个好的DW比一个庞大的遗留系统要好得多。其次，它并不意味着非规范化比规范化更快。2）我没有读过Kimball或Inmon，但我曾在几个DW项目中与他们的追随者一起工作，所以我很熟悉。 "规范化"设计总是更小、更快、更易于维护。引号是因为它在DB术语中并不是真正的规范化，只是在DW术语中。 - PerformanceDBA

1

@Damir：不，是的，是的，是的和不。如果目标是一个单独的目标，没有相关的目标，那么很好。但是现在仅仅产生一个快速查询并忽略DW需要大量维护费用、不断重构等已经不够了。我总体上同意Inmon的观点；Kimball是个白痴。那条铺好的路会更快地引领旅鼠走向悬崖。 - PerformanceDBA

1

@PerformanceDBA，/Books/Peter F Drucker/The Effective Executive/What Can I Contribute/How To Make the Specialist Effective/get_last_sentence()。 - Damir Sudarevic

显示剩余4条评论

2

“去规范化”这个词的问题在于它没有明确指出要朝哪个方向去。这就像试图从芝加哥到旧金山，而开车却走了离纽约更远的路一样。

星形模式或雪花模式肯定是不规范化的。在某些使用模式下，它比规范化模式表现得更好。但有时设计人员并没有遵循任何纪律，只是按直觉组成表格来进行去规范化的尝试。有时这些努力会失败。

简而言之，不要仅仅去规范化。如果你确定有好处，并且即使与规范化设计不符也要遵循不同的设计原则。但不要把去规范化当作随意设计的借口。

- Walter Mitty

1

简短的回答是不要修复你没有遇到的性能问题！

至于基于时间的表，通常接受的范例是在每一行中都有valid_from和valid_to日期。这仍然基本上是3NF，因为它只是将语义从“这是该实体唯一的版本”更改为“这是该实体在此时的唯一版本”。

- James Anderson

1

简化：一个OLTP数据库应该被规范化（只要合理）。

一个OLAP数据仓库应该被反规范化为事实表和维度表（以减少连接操作）。

- Mitch Wheat

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- PerformanceDBA · Accepted Answer

神话

我一直认为数据库在读取时应该进行去规范化，就像OLAP数据库设计一样，并且不应该夸大OLTP设计的3NF。

这是一个误解。在关系型数据库环境中，我重新实现了六个非常大的所谓“去规范化”数据库，并执行了超过80个任务来纠正其他问题，只是通过规范化、应用标准和工程原则。我从未见过任何证据支持这个神话。只有人们重复这个口号，好像它是某种神奇的祷告一样。

规范化与非规范化

（“去规范化”是一个欺诈性的术语，我拒绝使用它。）

这是一个科学产业（至少是交付不会出错的软件的部分；它将人类送上月球；它运行银行系统等）。它受物理定律的控制，而不是魔法。计算机和软件都是有限的、有形的、物理的对象，受到物理定律的约束。根据我接受的中等和高等教育：

一个更大、更胖、不太有序的对象不可能比一个更小、更瘦、更有序的对象表现更好。
规范化会产生更多的表，但每个表都要小得多。即使有更多的表，实际上也有(a)较少的连接和(b)连接更快，因为集合更小。总体而言，需要较少的索引，因为每个较小的表需要较少的索引。规范化的表还可以产生更短的行大小。
对于任何给定的资源集，规范化的表:
- 将更多的行适合于相同的页面大小
- 因此将更多的行适合于相同的缓存空间，因此整体吞吐量增加)
- 因此将更多的行适合于相同的磁盘空间，因此I/O的数量减少;当调用I/O时，每个I/O更有效率。
  .
一个被大量复制的对象不可能比作为真实版本单独存储的对象表现更好。例如，当我删除表和列级别的5个副本时，所有交易的大小都减小了；锁定减少了；更新异常消失了。这大大减少了争用，因此增加了并发使用。

因此，总体结果表现更高。

根据我的经验，在同一数据库中提供OLTP和OLAP时，从未需要“去规范化”我的规范化结构以获得只读（OLAP）查询的更高速度。这也是一个谬论。

不，其他人要求的“去规范化”降低了速度，并被消除了。对我来说并不奇怪，但是请求者很惊讶。

很多书都是由那些卖谬论的人写的。需要认识到这些人非技术人员；因为他们在销售魔法，所以他们销售的魔法没有科学依据，并且在他们的销售宣传中方便地避开了物理定律。

（对于任何希望质疑上述物理科学的人，仅重复口号是没有任何效果的，请提供支持口号的具体证据。）

为什么这个谬论如此普遍？

首先，科学类型并不普遍，他们不寻求克服物理定律的方法。

根据我的经验，我确定了三个主要原因：

对于那些无法将其数据规范化的人来说，这是不规范化的方便理由。他们可以参考魔法书，在没有任何魔法证据的情况下，虔诚地说“看，一位著名作家证实了我的做法”。准确来说，这样做是错误的。
许多SQL编码人员只能编写简单的单层SQL语句。规范化结构需要一定的SQL能力。如果他们没有这种能力，如果他们不能在不使用临时表的情况下生成SELECTs，如果他们不能编写子查询，他们将心理上与扁平文件（也就是“非规范化”结构）紧密相连，而他们可以处理。
人们喜欢读书，喜欢讨论理论。特别是关于魔法的理论。这是一种替代实际经验的良药。任何实际上正确规范化数据库的人都从未声称“非规范化比规范化更快”。对于任何重复这个口头禅的人，我只会说“给我看证据”，他们从未提供过任何证据。因此，现实情况是，人们出于这些原因重复这个神话，而没有任何规范化的经验。我们是群居动物，未知是我们最大的恐惧之一。

这就是为什么我总是在任何项目中包括“高级”SQL和指导。

我的答案 如果我回答你的每一个问题或者对其他答案中错误的部分做出回应，那么我的答案会变得异常冗长。例如，上面的回答只回答了一个问题。因此，我将总体回答你的问题，而不是针对特定组成部分作出回应，并采用不同的方法。我只会涉及与你的问题相关的科学领域，这是我有资格和非常有经验的。

让我把科学以易于管理的方式呈现给你。 Typical First Generation "databases"

六个大规模全面实施任务的典型模型。

以下是翻译的结果：

这些通常在小公司中发现的“数据库”已关闭，而这些组织是大型银行
对于第一代的启动应用程序的思维方式来说非常不错，但在性能、完整性和质量方面则完全失败了
它们是为每个应用程序单独设计的
无法进行报告，它们只能通过每个应用程序进行报告
由于“去规范化”是一个谬论，准确的技术定义是它们是非规范化的
- 为了“去规范化”，必须先规范化；然后反转这个过程
- 每当有人向我展示他们的“去规范化”的数据模型时，简单的事实是，他们根本没有进行规范化；所以“去规范化”是不可能的；它只是非规范化的
由于它们没有太多的关系技术，也没有数据库的结构和控制，但它们被冠以“数据库”的名义，我已经将这些词放在引号中
因为非规范化结构在科学上是保证存在的，所以它们遭受了多个真相版本（数据重复），因此在每一个结构中都存在高争用和低并发性
它们还有一个数据重复的额外问题跨越“数据库”
组织试图保持所有这些副本同步，因此实施了复制；这当然意味着要开发ETL和同步脚本；以及进行维护等
不用说，同步永远不太够，他们总是在改变它
由于所有这些争用和低吞吐量，很容易为每个“数据库”正当化单独的服务器。但这并没有什么帮助。

所以我们考虑了物理法则，并应用了一些科学。 5NF Corporate Database

我们实施了标准概念，即数据属于公司（而不是部门），公司希望有一个真实版本。数据库是纯关系型的，规范化到第五范式。纯开放架构，因此任何应用程序或报告工具都可以访问它。所有事务都在存储过程中处理（而不是在整个网络上无序的 SQL 字符串）。每个应用程序的相同开发人员在我们的“高级”教育后编写了新应用程序。

显然，这项科学起了作用。好吧，这不是我的私人科学或魔法，而是普通的工程和物理定律。所有这些都在一个数据库服务器平台上运行；两对（生产和DR）服务器已被废弃并交给另一个部门。将总计720GB的5个“数据库”规范化为总计450GB的一个数据库。大约700个表格（许多重复和重复列）被规范化为500个不重复的表格。总体性能提高了10倍，某些功能的性能提高了100倍以上。这并不让我惊讶，因为这是我的意图，科学也预测到了，但这让那些一直念叨的人感到惊讶。

更多规范化

嗯，在每个项目中都成功地进行了规范化，并且对涉及的科学有信心，因此规范化更多而不是更少是自然而然的进展。在旧时代，3NF已经足够好了，后来的NF尚未被确定。在过去的20年中，我只交付了没有更新异常的数据库，因此根据今天的NF定义，我始终交付了5NF。

同样地，第五范式很好但它也有其局限性。例如，对于大型表的透视（不是像MS PIVOT Extension那样的小结果集），速度较慢。因此，我（和其他人）开发了一种提供规范化表的方法，使透视变得（a）容易且（b）非常快速。事实证明，现在已经定义了第六范式，这些表就是第六范式。

由于我从同一个数据库中提供OLAP和OLTP，我发现，与科学一致，结构越规范：

- 它们的性能越快 - 它们可以用更多的方式（例如透视）

因此，我的一贯和不变的经验是，规范化比未规范化或“去规范化”要快得多；更规范化甚至比更少规范化更快。

成功的一个标志是功能增长（失败的标志是尺寸增长而功能不变）。这意味着他们立即要求我们提供更多的报告功能，这意味着我们进行了更多的规范化，并提供了更多的专业表格（后来证明是6NF）。

在这个主题上取得进展。我一直是数据库专家，不是数据仓库专家，因此我的前几个仓库项目并不是完整的实现，而是重要的性能调优任务。它们在我专业的产品中，属于我的权利范围。 Typical Data Warehouse

让我们不用担心规范化的精确级别等，因为我们正在看典型的情况。我们可以认为OLTP数据库已经适当地规范化，但不能进行OLAP，组织已经购买了完全独立的OLAP平台、硬件；投资于开发和维护大量ETL代码等。在实施后，他们花费了一半的时间来管理他们创建的重复内容。在这里，需要指责书籍作者和供应商，因为他们导致组织购买了大量的硬件和单独的平台软件许可证，造成了巨大的浪费。

如果你还没有注意到，我想请你注意一下"典型第一代数据库"和"典型数据仓库"之间的相似之处。

同时，在上面的"5NF数据库"中，我们不断地增加了更多的OLAP功能。当然，应用程序功能增长了，但这只是小问题，业务并没有改变。他们会要求更多的6NF，而提供这些是很容易的（从5NF到6NF只是一个小步骤；从0NF到5NF，甚至是任何级别的步骤，都是一个大步骤；有组织的架构很容易扩展）。

OLTP和OLAP之间的一个主要区别，以及分离OLAP平台软件的基本理由，是OLTP是面向行的，它需要事务安全的行，并且速度快；而OLAP不关心事务问题，它需要列，并且速度快。这就是为什么所有高端BI或OLAP平台都是面向列的原因，也是为什么OLAP模型（星型模式、维度-事实）是面向列的原因。

但是对于6NF表：

没有行，只有列；我们以同样令人眼花缭乱的速度提供行和列
表格（即6NF结构的5NF视图）已经组织成维度-事实。实际上，它们被组织成比任何OLAP模型都要识别的更多维度，因为它们都是维度。
整个表格的透视聚合（而不是少数派生列的PIVOT）是（a）轻松、简单的代码和（b）非常快速的。

多年来，我们一直提供至少具备5NF的关系型数据库用于OLTP使用，并且具备6NF的OLAP要求。

请注意，我们从一开始就使用的是相同的科学方法；从典型的非规范化数据库到5NF企业数据库。我们只是应用了更多经过验证的科学方法，并获得了更高级别的功能和性能。

注意5NF企业数据库与6NF企业数据库之间的相似之处。

所有单独的OLAP硬件、平台软件、ETL、管理和维护成本都被消除了。

只有一个版本的数据，没有更新异常或维护；相同的数据作为行提供给OLTP，作为列提供给OLAP。

我们唯一没有做的是开始一个新项目，并从一开始就宣布纯6NF。这就是我接下来要做的事情。

什么是第六范式？

假设您已经掌握了规范化（我这里不会进行定义），与本主题相关的非学术定义如下。请注意，它适用于表级别，因此您可以在同一数据库中拥有5NF和6NF表的混合：

- 第五范式：解决了整个数据库中的所有功能依赖项

- 除了4NF / BCNF之外 - 每个非PK列都与其PK是1:1的 - 且没有其他PK - 没有更新异常

- 第六范式：是不可约范式，数据无法进一步减少或规范化（将不会有第7NF）

- 除了5NF之外 - 行由一个主键和最多一个非键列组成 - 消除了空值问题 第6NF长什么样？ 数据模型属于客户，我们的知识产权不可免费发布。但我会参加这个网站，并提供具体问题的答案。您需要一个实际的例子，所以我将发布我们内部工具之一的数据模型。

这一个是用于收集任意数量客户的服务器监控数据（企业级数据库服务器和操作系统），并且可以选择任何时间段。我们使用它来远程分析性能问题，并验证我们所做的任何性能调整。这个结构在过去十年中没有改变（只是添加了内容，而没有更改现有结构），它是许多年后被确认为6NF的专业5NF的典型。它允许完全透视；可以绘制任何图表或图形，对任何维度进行操作（提供了22个透视，但这不是限制）；切片和混合。请注意，它们都是所有维度。

监控数据或指标或向量可能会更改（服务器版本更改；我们想要获取更多信息），但不会影响模型（您可能还记得在另一篇文章中我说过EAV是6NF的私生子；好吧，这是完整的6NF，未经稀释的父亲，因此提供了EAV的所有功能，而不牺牲任何标准、完整性或关系能力）；您只需添加行即可。

▶监控统计数据模型◀。 (太大无法内联; 一些浏览器无法内联加载; 点击链接)

它使我能够在收到客户的原始监控统计文件后，仅使用六个按键就可以生成这些▶如此图表◀。注意混搭; 操作系统和服务器在同一张图表上; 各种枢轴。（获得许可后使用。）

对于不熟悉关系数据库建模标准的读者，可能会发现▶IDEF1X符号◀有所帮助。

6NF数据仓库

最近Anchor Modeling验证了这一点，他们现在将6NF作为数据仓库的“下一代”OLAP模型进行展示。(他们没有提供单个版本的OLTP和OLAP，那是我们自己的。)

我的经验只涉及数据仓库(不包括上述的6NF OLTP-OLAP数据库)，有几个重要的任务，结果毫不意外：

- 符合科学规律，规范化结构执行速度更快，更易于维护，并且需要较少的数据同步。Inmon而非Kimball。 - 在我对一堆表进行规范化后，通过应用物理定律实现了显著的性能提升，唯一感到惊讶的是那些带着咒语的魔术师。 - 科学家并不这样做；他们不相信或依赖银弹和魔法；他们使用科学和艰苦的工作来解决问题。

有效的数据仓库有其正当的理由。

这就是为什么我在其他帖子中说过，唯一一个合理的数据仓库平台、硬件、ETL、维护等单独存在的理由是，当有许多数据库或“数据库”全部合并到一个中央仓库中进行报表和OLAP时。

Kimball

需要提及Kimball，他是数据仓库中“去范式化以提高性能”的主要支持者。按照我上面的定义，他是那些显然从未规范化过的人之一；他的起点是非规范化的（伪装成“去范式化”），然后简单地将其实现在维度-事实模型中。

当然，为了获得任何性能，他不得不更多地“去规范化”，创建进一步的副本，并证明所有这些。因此，以一种分裂的方式，“去规范化”非规范化结构，通过创建更专业的副本，“提高读取性能”是正确的。当整体被考虑进来时，这并不是真的; 它只在那个小疯人院里是正确的，而不是在外面。
- 同样，在那种疯狂的方式下，当所有“表”都是庞然大物时，“连接是昂贵的”，应该避免。他们从未有过连接较小的表和集合的经验，因此他们无法相信更多，更小的表是更快的科学事实。
- 他们有创建重复“表”的经验，因此他们无法相信消除重复比这还要快。
他的维度被添加到非规范化数据中。好吧，数据没有被规范化，所以没有维度暴露。而在规范化模型中，维度已经作为数据的一个组成部分暴露出来，不需要添加。
Kimball的这条铺好的道路通向悬崖，更多的旅鼠会更快地跌落死亡。旅鼠是群居动物，只要他们一起走路，一起死去，他们就会幸福地死去。旅鼠不会寻找其他路线。

所有这些只是故事，构成一个神话的部分，并相互支持。

您的使命

如果您选择接受。我要求您自己思考，并停止娱乐任何与科学和物理定律相矛盾的想法。无论它们有多么普遍、神秘或神话。在信任任何东西之前寻找证据。要科学，为自己验证新的信念。反复说“针对性能进行去规范化”不会使您的数据库更快，它只会让您感觉更好。就像坐在场外告诉自己他能比比赛中的所有孩子跑得更快的胖孩子一样。

基于这一点，即使是“针对 OLTP 进行规范化”的概念，但相反，“针对 OLAP 进行去规范化”也是一种矛盾。物理定律如何可以在一个计算机上按照所述方式工作，但在另一个计算机上按相反的方式工作呢？这令人难以置信。它根本不可能，它们在每台计算机上都以相同的方式工作。

问题？