如何为具有类型 2 缓慢变化维度的表建立索引以获得最佳性能

Question

假设您有一个具有类型 2 缓慢变化维度的表。

让我们用以下列来表达该表：

* [Key]
* [Value1]
* ...
* [ValueN]
* [StartDate]
* [ExpiryDate]

在此示例中，我们假设 [StartDate] 实际上是系统已知给定 [Key] 的值的日期。因此我们的主键将由 [StartDate] 和 [Key] 组成。

当给定 [Key] 的一组新值到达时，我们将 [ExpiryDate] 分配给一些预定义的高代理值，例如“12/31/9999”。然后，我们将该 [Key] 的现有“最新”记录设置为具有等于新值的 [StartDate] 的 [ExpiryDate]。基于连接的简单更新。

---

因此，如果我们总是想获取给定[Key]的最新记录，我们知道我们可以创建一个聚集索引，即：

* [ExpiryDate] ASC
* [Key] ASC

尽管键空间可能非常宽（例如，一百万个键），但我们可以最大限度地减少读取之间的页面，最初按 [ExpiryDate] 排序。由于我们知道给定键的最新记录的 [ExpiryDate] 始终为“12/31/9999”，因此我们可以利用它来发挥我们的优势。

但是...如果我们想获取给定时间所有 [Key] 的时间点快照怎么办？理论上，整个密钥空间不会同时更新。因此，对于给定的时间点，[StartDate] 和 [ExpiryDate] 之间的窗口是可变的，因此按 [StartDate] 或 [ExpiryDate] 排序永远不会产生您要查找的所有记录都在其中的结果。连续的。当然，您可以立即丢弃 [StartDate] 大于您定义的时间点的所有记录。

---

本质上，在典型的 RDBMS 中，哪种索引策略提供了最佳方式来最大限度地减少读取给定时间点的所有键的值的次数？我意识到我至少可以通过按 [Key] 对表进行分区来最大化 IO，但这肯定不理想。

或者，是否有一种不同类型的缓慢变化的维度可以以更高效的方式解决这个问题？

Answer 1

懒惰的 DBA

您是在谈论恢复维度表中的所有值吗？如果是这样，那么为什么不添加具有额外覆盖范围的非聚集索引，这样您只从索引本身而不是从表中提取值呢？这样您就可以扫描带有一些附加“覆盖”值的 B 树，而不是潜在地执行表扫描？我不能保证相对性能，但值得针对您正在处理的场景进行测试。

欢呼

奥齐梅德斯
http://ozziemedes.blogspot.com/

Answer 2

如果这确实是一个“缓慢变化的维度”表，我会考虑聚集列存储索引。我知道当你问这个问题时，这个功能不可用，但无论如何。你会在这里找到一些很棒的文档：
“https://msdn.microsoft.com/en-us/library/gg492088。 .aspx"
在这里
“http://www.nikoport.com /2013/07/05/clustered-columnstore-indexes-part-1-intro/”。

现在，如果您想坚持使用行存储索引，如果您按顺序将数据插入表中，我过去所做的就是利用身份字段。您的查询将类似于：

    declare @id;
    select @id = min(ID) from table where date = '12/31/9999';
    select fields from table where key = 112 and id > @id;