[id] INT NOT NULL,[date] SMALLDATETIME NOT NULL,[sales] FLOAT NULL
还假定您仅限于一个物理磁盘和一个文件组(PRIMARY).您预计这个表可以在100个日期(容易有1B条记录)中持有10,000,000个ids的销售额.
与许多数据仓库方案一样,数据通常会按日期顺序增长(即每次执行数据加载时,您都将插入新的日期,并且可能会更新一些最新的数据日期).为了分析目的,数据将经常被查询并聚合为约10,000个ID的随机集合,这些集合将通过与另一个表的连接来指定.通常,这些查询不会指定日期范围,也可以指定非常宽的日期范围,这将导致我的问题：索引/分区此表的最佳方式是什么？
我已经想过了一段时间,但是我遇到了矛盾的解决方案：
选项1：由于数据将按日期顺序加载,请将聚簇索引(和主键)定义为[date],[id].还可以在日期上创建一个“滑动窗口”分区功能/方案,允许新数据快速移动到表中.潜在地创建一个非聚集索引的id来帮助查询.
预期成果#1：对于数据加载,此设置将非常快速,但在分析读取方面则为最佳,在最坏的情况下(不受日期限制,不符合id的查询集),100％的可以读取数据页.
选项#2：由于一次只能查询一小部分ids的数据,因此将聚簇索引(和主键)定义为[id],[date].不要打扰创建分区表.
预期成果#2：在加载数据时,预计会有很大的性能下降,因为我们不能再按日期快速限制.对于我的分析查询来说,预期会有很大的性能优势,因为它会最小化读取的数据页数.
选项3：集群(和主键)如下：[id],[date]; “滑动窗口”分区功能/方案日期.
预期成果#3：不知道会有什么期望.鉴于聚集索引中的第一列是[id],因此(这是我的理解),数据按ID排列,我希望从我的分析查询获得良好的性能.然而,数据按日期分区,这与聚集索引的定义相反(但是仍然对齐,因为日期是索引的一部分).我没有找到很多关于这种情况的文档,如果有的话,我可以从中获得什么样的性能优势,这给我带来了最后的奖金问题：
如果我在一个磁盘上的一个文件组上创建一个表,一列上有一个聚簇索引,在同一列上定义一个分区时,是否有任何好处(加载数据时除了分区切换)？
解决方法
我会这样做：
CREATE TABLE Narrow ( [id] INT 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'
通过索引,您可以方便地访问特定的列…集群视图可让您方便地访问特定行.
