SQL Server - 分区表与聚集索引？

Question

假设您有一个包含三列的大型表格，如下所示:

[id] INT NOT NULL,

[date] SMALLDATETIME NOT NULL,

[sales] FLOAT NULL

同时假设您仅限于一个物理磁盘和一个文件组 (PRIMARY)。您希望此表包含 10,000,000 多个 ID、跨越 100 个日期(很容易 1B+ 记录)的销售额。

与许多数据仓库场景一样，数据通常会按日期顺序增长(即，每次执行数据加载时，您都会插入新日期，并且可能会更新一些较新日期的数据)。出于分析目的，数据通常会被查询和聚合，以获得大约 10,000 个 id 的随机集合，这些 id 将通过与另一个表的连接指定。通常，这些查询不指定日期范围，或者指定非常宽的日期范围，这引出了我的问题:索引/分区此表的最佳方法是什么？

我已经考虑了一段时间，但一直坚持使用相互矛盾的解决方案:

选项 #1: 由于数据将按日期顺序加载，因此将聚簇索引(和主键)定义为 [date]、[id]。还创建一个“滑动窗口”分区函数/日期方案，允许新数据快速移入/移出表。可能会在 id 上创建一个非聚集索引以帮助查询。

预期结果#1:此设置对于数据加载目的来说非常快，但在分析读取方面不是最佳的，因为在最坏的情况下(不受日期限制，不幸的是查询一组id)，可能会读取100%的数据页。

选项#2:由于一次只会查询一小部分 ID 数据，因此将聚簇索引(和主键)定义为 [id]、[date]。不必费心创建分区表。

预期结果 2:由于我们无法再按日期快速限制，因此在加载数据时预期会出现巨大的性能下降。当涉及到我的分析查询时，预期会带来巨大的性能优势，因为它将最大限度地减少读取的数据页数。

选项 #3: 集群(和主键)如下:[id]、[date]； “滑动窗口”分区函数/方案日期。

预期结果 #3: 不确定会发生什么。鉴于聚集索引中的第一列是 [id]，因此(据我所知)数据是按 ID 排列的，我希望我的分析查询具有良好的性能。但是，数据按日期分区，这与聚集索引的定义相反(但仍然对齐，因为日期是索引的一部分)。我还没有找到很多文档来说明这种情况以及我可以从中获得什么性能优势(如果有的话)，这让我想到了最后一个奖金问题:

如果我在一个磁盘上的一个文件组上创建一个表，在一列上有一个聚集索引，那么在同一列上定义一个分区是否有任何好处(除了加载数据时的分区切换)？

Answer 1

这张 table 非常窄。如果真正的表这么窄，您应该乐于使用表扫描而不是索引->查找。

我会这样做:

CREATE TABLE Narrow
(
  [id] INT NOT NULL,
  [date] SMALLDATETIME NOT NULL,
  [sales] FLOAT NULL,
  PRIMARY KEY(id, date)  --EDIT, just noticed your id is not unique.
)

CREATE INDEX CoveringNarrow ON Narrow(date, id, sales)

这会处理带有搜索的点查询和根据日期条件和 ID 条件进行有限扫描的大范围查询。索引中没有每条记录的查找。是的，我已经将写入时间(和使用的空间)增加了一倍，但没关系，imo。

---

如果需要特定的数据片段(并且这种需要通过分析来证明!!)，我会针对表的该部分创建一个集群 View 。

CREATE VIEW Narrow200801
AS
SELECT * FROM Narrow WHERE '2008-01-01' <= [date] AND [date] < '2008-02-01'
--There is some command that I don't have at my finger tips to make this a clustered view.

聚簇 View 可以按名称在查询中使用，或者优化器会在 FROM 和 WHERE 子句合适时选择使用聚簇 View 。例如，此查询将使用集群 View 。请注意，查询中引用了基表。

SELECT SUM(sales) FROM Narrow WHERE '2008-01-01' <= [date] AND [date] < '2008-02-01'

索引让您可以方便地访问特定列...集群 View 让您可以方便地访问特定行。