对事务性数据进行规范化是否可取？

Question

我读过很多关于规范化和非规范化模式的文章，但我发现很少提到插入行的成本。我正在考虑将100%的非规范化数据库系统转换为更规范化的数据库系统的影响。下面是我认为与我的问题相关的信息，本质上是:与非规范化表的效率相比，我能否有效地将历史事务数据插入到规范化表中？

• 我在使用MySQL，所有的表都是myISAM
• 系统目前可以描述为95%的插入、5%的选择和0%的更新。(这是销售事务数据，因此是不可变的。)
• 我想在应用程序中引入大量的统计特性，所以选择的数量会大大增加，但它仍然主要是插入的。

在插入记录时，数据包括：

storeid - int (we track multiple stores)
itemname - varchar(64)
customeruuid - char(36)
customername - varchar(64)
amount (int - this currency is integer values only, e.g. tokens.)
location - varchar(64)

我的非规范化销售表与上面的数据完全相同，在insert查询中添加了一个日期字段，该字段被设置为NOW()。所以现在，我的插入内容很简单，由一个sql语句组成。

但是，我想在应用程序中提供更多的统计特性，需要大量的聚合。所以这让我考虑正常化..。

我对规范化结构的第一个滑动应该是这样的：

customers
----------
id - int, PK, AI
uuid - char(36)
name - varchar(64)

products
----------
id - int, PK, AI
storeid - int
name - varchar(64)
(with storeid + name being a composite unique index)

locations (a location is akin to a city)
-----------
id - int, PK, AI
name - varchar(64)

sales
-----------
id - int, PK, AI (needed elsewhere in the system)
customerid - int (fk to customers table)
productid - int (fk to products table)
amount
locationid - int (fk to locations table)
date

目前为止非常简单的事情。但在这里，我开始失去插入过程的情节。

客户、产品和地点都不提前知道。因此，事务可能首次将这些值引入系统，需要对适当的查找表进行插入。

那么，我真的需要执行三个“插入.关于重复键更新”的操作才能将每个插入的客户、产品和位置ID输入到sales表中吗？我错过了什么捷径吗？

下一个明显的问题是：“在这种情况下，正常化是正确的做法吗？”

所有报告都将使用一个存储id和一个日期范围来完成。示例查询如下：

• 在所有地点销售了多少份A项？
• 每个地点的总销售额是多少？
• 十大最受欢迎的商品是什么？
• 哪10个项目产生的收入最多？

这些查询对规范化数据是否更有效，即使它们需要联接？

我还考虑过其他可能性，但不确定该如何权衡：

• 对非正规化销售表进行适当的索引能比规范化表提供更好的选择结果吗？
• 既然我们谈论的是不可变的数据，那么在前几个月使用预先计算的汇总数据是否是一种好方法呢？如果数据不存在的话，我可以想象一个及时生成的数据，并将其保存下来，这样页面刷新或随后的报告将加载摘要数据，而不是运行摘要查询。这似乎有点复杂，并增加了许多应用程序逻辑，但似乎对DB更友好。

最后，缩放信息，将所有这些都放在正确的角度。DB目前包含约800万行，目前每月插入率为appx 125 k行，来自数千个客户和数千个产品。有几百家商店，每家商店等于一人，报告通常只由这一人进行，频率完全无法预测(即他们可能每天加载帐户10次，或一年一次，或几乎从不)。

对不起，我是赫拉·龙。我可能会被摩托打了一巴掌，所以提前道歉。这是一个复杂的系统，我已经在这篇文章中简化了很多！

谢谢你的建议！

Answer 1

您绝对可以将所有维度和度量值保存在一个事实表中，而不使用任何维度表。不过，确保您的OLAP工具支持这一点。

将维度规范化为其他表主要是为了最小化事实表的大小，因为事实表很快就会变大。

如果没有维度表，您将看到每年大约336 MB (不包括索引)，这并不是很糟糕。

有了维度表，您将看到每年大约34 MB，再加上几十MB用于存储维度详细信息。索引也会更小。

您将希望将date列展开为更可分析的内容(年份、月份、季度等)，这将增加数据的大小。

您需要对所有字段进行索引。在插入之前删除索引，在插入后添加索引。

您可以使用像宾得聚合设计器这样的工具来查找有用的聚合并为您生成它们。