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

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

[id] INT NOT NULL,

[date] SMALLDATETIME NOT NULL,

[sales] FLOAT NULL

还假设您仅限于一个物理磁盘和一个文件组(PRIMARY).您希望此表能够在100个日期(10,000个以上的记录)中保持10,000,000+ ID的销售额.

与许多数据仓库方案一样,数据通常按日期顺序增长(即,每次执行数据加载时,您将插入新日期,并可能更新一些最近的数据日期).出于分析目的,通常会查询和聚合数据,以便随机设置~10,000个ID,这些ID将通过与另一个表的连接来指定.通常,这些查询不指定日期范围,或指定非常宽的日期范围,这引出了我的问题:索引/分区此表的最佳方法是什么？

我已经考虑了一段时间了,但我遇到了相互矛盾的解决方案:

选项#1: 由于数据将按日期顺序加载,请将聚集索引(和主键)定义为[date],[id].还可以在日期创建"滑动窗口"分区功能/方案,允许新数据快速移入/移出表格.可以在id上创建非聚集索引以帮助查询.

预期结果#1: 这种设置对于数据加载来说非常快,但在分析读取方面是次优的,在最坏的情况下(不受日期限制,不满意查询的id集),100%可以读取数据页面.

选项#2: 由于一次只查询一小部分id的数据,因此将聚簇索引(和主键)定义为[id],[date].不要费心去创建分区表.

预期结果#2: 在加载数据时预计会有巨大的性能损失,因为我们无法再按照日期快速限制.对于我的分析查询,预计会有巨大的性能优势,因为它可以最大限度地减少读取的数据页数.

选项#3:聚集(和主键)如下:[id],[date]; "滑动窗口"分区功能/方案日期.

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

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

这张桌子非常窄.如果真实表格会变窄,您应该乐于进行表扫描而不是索引 - >查找.

我会这样做:

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.

如果需要某个特定的数据(并且需要通过分析来证明 !!),我将创建一个聚集视图,目标是该表的那一部分.

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.

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

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

通过索引,您可以方便地访问特定列..." 聚集"视图使您可以方便地访问特定行.