8.1 为什么需要反规范化？

大表之间计算量最大的操作是连接。因此，如果在一个查询中需要“通风”几个由数百万行组成的表，那么 DBMS 将花费大量时间进行此类处理。

此时用户可以移开去喝咖啡。处理的交互性几乎消失，接近批处理的交互性。更糟糕的是，在批处理模式下，用户在早上收到前一天请求的所有数据并冷静地处理它们，为晚上准备新的请求。

为了避免大量连接的情况，表被非规范化。但无论如何都不是。有一些规则允许您根据为数据仓库构建表的规则将事务非规范化表视为“规范化”。

在分析处理中被认为是“正常”的主要方案有两种：“雪花”和“星”。名称很好地反映了本质，直接从相关表的图片中提取。

在这两种情况下，所谓的事实表都是模式的核心元素，包含事件、事务、文档和分析师感兴趣的其他有趣内容。但是，如果在事务数据库中，一个文档被“涂抹”在多个表中（至少两个：标题和内容行），那么在事实表中，一个文档，更准确地说，它的每一行或一组分组行，对应到一个记录。

这可以通过对上面的两个表进行非规范化来完成。

8.2 反规范化示例

现在，您可以评估 DBMS 执行查询（例如，以下类型的查询）的容易程度：确定该期间 Pirozhki LLC 和 Vatrushki CJSC 的客户的面粉销售量。

在规范化事务数据库中：


SELECT
   SUM(dl.qty) AS total qty, SUM(dl.price) AS total amount, c.name 
FROM 
   docs d
   INNER JOIN doc lines dl ON d.id doc = dl.id doc 
   INNER JOIN customers c ON d.id customer = c.id customer 
   INNER JOIN products p ON dl.id product = p.id product 
WHERE
   c.name IN (’Pirozhki LLC’,	’Vatrushki CJSC’) AND
   p.name = ’Flour’ AND
   d.date BETWEEN ’2014-01-01’ AND ’2014-02-01’
GROUP BY c.name

在分析数据库中：


SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, c.name
FROM
   sales s
   INNER JOIN customers c ON d.id_customer = c.id_customer
   INNER JOIN products p ON dl.id_product = p.id_product
WHERE
   c.name IN ('Pirozhki LLC', 'Vatrushki CJSC') AND
   p.name = 'Flour' AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY c.name
Java webinar

DBMS 无需在两个文档表及其由数百万行的组合之间进行大量连接，而是直接使用事实表并使用小型辅助表进行轻量连接，您也可以在不知道标识符的情况下进行这些操作。


SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, s.id_customer
FROM
   sales s
WHERE
   s.id_customer IN (1025, 20897) AND
   s.id_product = 67294 AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY s.id_customer

让我们回到“明星”和“雪花”计划。在第一张图片的背后是顾客、他们的团体、商店、卖家以及实际上是商品的表格。非规范化后，这些表（称为维度）也连接到事实表。如果事实表引用了与其他维度（二级及以上维度）有链接的维度表，那么这样的模式称为“雪花”。

如您所见，对于包含按客户端组过滤的查询，您必须建立额外的连接。


SELECT sum(amount)
FROM sales s
   INNER JOIN customers c ON s.id_customer = c.id_customer
WHERE c.id_customer_group IN (1, 2, 10, 55)

在这种情况下，反规范化可以继续下去，将二级维度降到一级，从而更容易查询事实表。

事实表仅引用没有第二层的维度的模式称为星型模式。测量表的数量对应于恒星中“射线”的数量。

星型模式完全消除了维度的层次结构以及在单个查询中连接相应表的需要。


SELECT sum(amount)
FROM sales s
WHERE s.id_customer_group IN (1, 2, 10, 55)

非规范化的缺点总是冗余，这会导致事务和分析应用程序中数据库的大小增加。让我们计算上述将“snowflake”转换为“star”的示例的近似增量。

在某些 DBMS 中，例如 Oracle，在数据库模式定义级别没有特殊的整数类型，您必须使用通用的布尔类型numeric(N)，其中 N 是存储的位数。这种数字的存储大小是使用物理数据存储文档中给出的特殊公式计算的，并且通常比“16 位整数”等低级类型多 1-3 个字节。

假设sales表没有使用数据压缩，包含约5亿行，客户组数约1000个，此时我们可以使用占2个字节的短整型（shortint，smallint）作为标识符类型id_customer_group。

我们假设我们的 DBMS 支持两字节整数类型（例如，PostgreSQL、SQL Server、Sybase 等）。然后将相应的列添加id_customer_group到 sales 表将至少增加其大小500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte。

8.3 什么时候需要反规范化？

让我们看一下反规范化可能有用的一些常见情况。

大量表连接

在对完全规范化的数据库进行查询时，您通常需要连接多达十几个甚至更多的表。而且每个连接都是一个非常耗费资源的操作。因此，此类请求会消耗服务器资源并且执行缓慢。

在这种情况下，它可以帮助：

  • 通过减少表的数量来进行反规范化。最好将几个表合并为一个表，这些表的大小较小，包含很少更改（正如他们常说的，有条件地保持不变或引用）的信息，并且在含义上密切相关的信息。
  • 一般来说，如果你需要在大量查询中连接超过五六个表，你应该考虑对数据库进行反规范化。
  • 通过向其中一个表添加附加字段来进行非规范化。在这种情况下，会出现数据冗余，需要采取额外的措施来维护数据库的完整性。

估计值

通常，查询很慢并且会消耗大量资源，其中会执行一些复杂的计算，尤其是在使用分组和聚合函数（Sum、Max 等）时。有时向包含常用（且难以计算）计算数据的表添加 1-2 个附加列是有意义的。

假设您要确定每个订单的总成本。为此，您必须首先确定每个产品的成本（根据公式“产品单位数”*“产品单价”-折扣）。之后，您需要按订单对成本进行分组。

执行此查询非常复杂，如果数据库存储有关大量订单的信息，可能需要很长时间。您可以在下订单阶段确定其成本并将其存储在订单表的单独列中，而不是执行这样的查询。在这种情况下，要获得所需的结果，从该列中提取预先计算的值就足够了。

创建包含预先计算值的列可以在运行查询时节省大量时间，但需要您及时更新该列中的数据。

长帽檐

如果我们在数据库中有包含长字段（Blob、Long 等）的大表，那么如果我们将长字段移动到单独的表中，我们可以大大加快对此类表的查询执行速度。例如，我们想要在数据库中创建一个照片目录，包括将照片本身存储在 blob 字段中（专业质量、高分辨率和适当的大小）。从规范化的角度来看，下面的表结构是绝对正确的：

  • 照片身份证
  • 作者编号
  • 相机型号 ID
  • 照片本身（blob 字段）

现在让我们想象一下查询将运行多长时间，计算任何作者拍摄的照片数量......

在这种情况下，正确的解决方案（尽管违反规范化原则）是创建另一个仅包含两个字段的表——照片 ID 和包含照片本身的 blob 字段。然后从主表（其中不再有一个巨大的 blob 字段）中的选择将立即进行，但是当我们想要查看照片本身时，好吧，让我们等一下......

如何确定何时非规范化是合理的？

8.4 反规范化的优缺点

确定某些步骤是否合理的一种方法是根据成本和可能的收益进行分析。非规范化数据模型的成本是多少？

确定需求（我们要实现的）→确定数据需求（我们需要遵循的）→找到满足这些需求的最小步骤→计算实施成本→实施。

成本包括物理方面，例如磁盘空间、管理此结构所需的资源，以及由于与维护此过程相关的时间延迟而失去的机会。您必须为非规范化付费。非规范化数据库增加了数据冗余，这可以提高性能但需要更多的努力来控制相关数据。创建应用程序的过程将变得更加困难，因为数据将重复并且更难跟踪。此外，参照完整性的实现并不容易——相关的数据被划分到不同的表中。

好处包括更快的查询性能和获得更快响应的能力。您还可以获得其他好处，包括提高吞吐量、客户满意度和生产力，以及更有效地使用外部开发人员工具。

请求率和性能一致性

例如，企业每天产生的 1000 个查询中，有 72% 是汇总级查询，而不是下钻查询。使用汇总表时，查询运行时间约为 6 秒而不是 4 分钟，从而减少了 3,000 分钟的处理时间。即使在调整每周必须花在维护数据透视表上的 100 分钟之后，每周也可以节省 2,500 分钟，这证明创建数据透视表是合理的。随着时间的推移，可能会出现大多数查询不会针对摘要数据，而是针对详细数据的情况。使用汇总表的查询越少，就越容易在不影响其他进程的情况下删除它。

和…

在决定是否采取下一步优化时，上面列出的标准并不是唯一要考虑的标准。还需要考虑其他因素，包括业务优先级和最终用户需求。用户必须了解，从技术角度来看，系统架构如何受到用户要求在几秒钟内完成所有请求的影响。实现这种理解的最简单方法是概述与创建和管理此类表相关的成本。

8.5 如何胜任地实施反规范化。

保存明细表

为了不限制对业务重要的数据库的能力，需要采取共存而不是替代的策略，即保留详细的表进行深度分析，并在其中添加非规范化的结构。例如，点击计数器。对于业务，您需要知道网页的访问次数。但对于分析（按时期、按国家...），我们很可能需要详细数据 - 包含每次访问信息的表格。

使用触发器

通过使用数据库触发器来保持重复数据的完整性，可以对数据库结构进行非规范化并仍然享受规范化的好处integrity。

例如，在添加计算字段时，计算字段所依赖的每个列都挂起一个调用单个存储过程的触发器（这很重要！），它将必要的数据写入计算字段。只需要不跳过计算字段所依赖的任何列即可。

软件支持

如果您不使用内置触发器和存储过程，那么应用程序开发人员应该注意确保非规范化数据库中数据的一致性。

类比触发器，应该有一个函数来更新依赖于被更改字段的所有字段。

结论

反规范化时，重要的是要在提高数据库速度和增加数据不一致的风险之间保持平衡，在使程序员的工作更轻松与使提供Select-s数据库填充和数据更新的人员的任务复杂化之间保持平衡。因此，有必要非常小心、非常有选择性地对数据库进行非规范化，只有在不可或缺的地方才进行。

如果无法提前计算反规范化的利弊，那么首先需要使用规范化表来实现模型，然后才能优化有问题的查询，进行反规范化。

重要的是逐步引入非规范化，并且仅针对从不同表重复获取相关数据的情况。请记住，复制数据时，记录数会增加，但读取次数会减少。将计算的数据存储在列中也很方便，以避免不必要的聚合选择。