sql - 规范化一个非常大的表

Question

我面临以下问题。我有一张非常大的 table 。这张 table 是以前参与该项目的人的遗产。该表位于 MS SQL Server 中。

该表具有以下属性:

1. 它有大约 300 列。它们都具有“文本”类型，但其中一些最终应该表示其他类型(例如整数或日期时间)。因此，在使用这些文本值之前，必须将其转换为适当的类型
2. 该表的行数超过 100 百万。表的空间很快就会达到 1 TB
3. 该表没有任何索引
4. 该表没有任何已实现的分区机制。

正如您可能猜到的那样，不可能对此表运行任何合理的查询。现在人们只向表中插入新记录，但没有人使用它。所以我需要重组它。我计划创建一个新结构，并用旧表中的数据重新填充新结构。显然，我将实现分区，但这不是唯一要做的事情。

该表最重要的功能之一是那些纯文本字段(即它们不必转换为另一种类型)通常具有频繁重复的值。因此，给定列中的实际值范围为 5-30 个不同值。这引发了进行标准化的想法:对于每个这样的文本列，我将创建一个附加表，其中包含可能出现在该列中的所有不同值的列表，然后我将在这个附加表中创建一个(tinyint)主键，并然后将在原始表中使用适当的外键，而不是将这些文本值保留在原始表中。然后我将在这个外键列上放置一个索引。这样处理的列数约为100列。

它提出了以下问题:

1. 这种标准化真的会提高查询者对这 100 个字段中的某些字段施加条件的速度吗？如果我们忘记保留这些列所需的大小，由于用tinyint列替换初始文本列，性能是否会有所提高？如果我不进行任何标准化，只是在这些初始文本列上放置索引，性能是否与计划的tinyint-column 上的索引相同？
2. 如果我进行所描述的标准化，那么构建一个显示文本值的 View 将需要将我的主表与大约 100 个附加表连接起来。一个积极的时刻是我将为“主键”=“外键”对进行这些连接。但仍然需要连接相当大量的表。这里的问题是:与对初始非规范化表的查询性能相比，对此 View 进行的查询的性能是否不会更差？ SQL Server 优化器是否真的能够以允许受益于规范化的方式优化查询？

抱歉这么长的文字。

感谢您的每一条评论!

PS我创建了一个关于连接 100 个表的相关问题； Joining 100 tables

Answer 1

除了针对数据运行查询的速度之外，您还会发现标准化数据的其他好处...例如大小和可维护性，仅凭这一点就可以证明对其进行标准化...

但是，它也可能会提高查询速度；当前包含 300 个文本列的单行是巨大的，并且几乎肯定超过了 8,060 byte limit for storing the row data page ...而是存储在 ROW_OVERFLOW_DATA 或 LOB_DATA 分配单元中。

通过规范化减少每行的大小，例如使用 TINYINT 外键替换冗余文本数据，以及将不依赖于此大表主键的列删除到另一个表中表中，数据不应再溢出，并且您还可以在每页存储更多行。

就通过执行JOIN 获取标准化数据所增加的开销而言...如果您正确地为表建立索引，这不会增加大量的开销。但是，如果它确实增加了 Not Acceptable 开销，您可以根据需要有选择地对数据进行反规范化。