最好的方式来索引这个非常大的表

我有下面的表格

CREATE TABLE DiaryEntries ( [userId] [uniqueidentifier] NOT NULL, [setOn] [datetime] NOT NULL, -- always set to GETDATE(). [entry] [nvarchar](255) NULL ) 

每个用户每天将插入大约3个条目。 将有大约1'000'000用户。 这意味着每天在这张桌子上有3'000'000个新logging。 一旦logging超过1个月,我们将其删除。

大多数查询具有以下WHERE子句:

 WHERE userId = @userId AND setOn > @setOn 

大多数查询返回不超过3行,除了返回本月插入的所有行(最多90行)。

一旦logging被插入,date和用户ID就不能被改变。

现在我的问题是 – 如何最好地安排这张表的索引? 我坚持两个select:

  1. (userId,setOn)上的聚集索引 – 这会给我快速的search,但是我担心过多的页面拆分,因为我们会插入很多中间值(相同的userId,但是不同的date)。
  2. (userId)和on(setOn)上的非聚集索引 – 这也会导致页面拆分(userId)索引(但与第一个选项一样昂贵?)。 由于我们使用NC索引,search速度变慢。
  3. (userId,setOn)上的附加列(id)和非聚集索引上的聚簇索引 – 这将消除数据表的页面拆分,但仍会在NC索引上引起一些拆分。 由于我们使用NC索引进行search,因此该选项对于search也不是最佳的。

你有什么build议? 任何其他选项?

PS – 感谢您的时间。


经过两天的思考,我想出了一个不同的解决scheme来解决这个问题。

 CREATE TABLE MonthlyDiaries ( [userId] uniqueidentifier NOT NULL, [setOn] datetime NOT NULL, -- always set to GETDATE(). [entry1_1] bigint NULL, -- FK to the 1st entry of the 1st day of the month. [entry1_2] bigint NULL, -- FK to the 2nd entry of the 1st day of the month. [entry1_3] bigint NULL, [entry2_1] bigint NULL, [entry2_2] bigint NULL, [entry2_3] bigint NULL, ... [entry31_1] bigint NULL, [entry31_2] bigint NULL, [entry31_3] bigint NULL, PRIMARY KEY (userId, setOn) ) CREATE TABLE DiaryEntries ( [id] bigint IDENTITY(1,1) PRIMARY KEY CLUSTERED, [entry] nvarchar(255) NOT NULL ) 

基本上我把31天分成一排。 这意味着每个用户每月只能插入一次新的logging。 这将每个用户每天3次的页面拆分减less到每个用户每月一次。 显然有缺点,这里有一些

  • 行大小是巨大的 – 但是在99.999%的时间我只查询MonthlyDiaries中的一行。
  • 我可能会使用比我需要更多的空间,因为有些日子可能没有条目。 没什么大不了的。
  • 要查找特定date的条目,需要在DiaryEntries上进行额外索引查找。 我相信这不会是一个很大的代价,因为我检索不超过90行,在80%的情况下,我只检索1行。

总的来说,我认为这是一个很好的权衡:从3页/每天/用户减less到1页/月/用户,但是通过让我的search稍微慢一些来支付一小笔费用。 你怎么看?

我会假设你有充分的理由使用GUID作为IDS。

碎片对于扫描来说大多是一个问题,而对于寻找来说则更less。 碎片化对预读有很大的影响,并且寻求不使用,也不需要预先读取。 列select较差的非片段化索引的性能总是比具有良好可用列的99%片段索引差。 如果您已经描述了扫描表格的DW报告样式查询,那么我会build议重点关注消除碎片,但是对于您描述的负载,将重点放在高效(覆盖)search和(小)范围扫描上更有意义。

假设您的访问模式总是由@userId驱动,则这必须是聚簇索引中最左边的一列。 我还将setOn作为聚集索引中的第二列添加,因为它在大多数查询中增加了一些边际值(我说边际是因为@userId是如此的有select性,最坏的情况是90个logging中有90个logging, @setOn不重要)。 我不会添加任何非聚集索引,从您描述的查询中不需要任何。

唯一的问题是删除旧logging(保留30天)。 我build议不要使用辅助NC指数来满足这个要求。 我宁愿使用滑动窗口部署每周分区scheme,请参阅如何在SQL Server 2005的分区表中实现自动滑动窗口 。 有了这个解决scheme,旧的logging被分区交换机删除,这是最有效的方式。 日常分区scheme可以更准确地满足30天的保留要求,也许值得尝试和testing。 我毫不犹豫地直接推荐30个分区,因为您描述了一些查询,这些查询有可能在每个分区中寻找特定的@userIdlogging,并且31个分区可能在重负载下产生性能问题。 testing和测量都更好。

首先在你的表上添加一个默认约束。 其次,添加一个分区scheme。 第三重写你最常用的查询。

聚集索引应该设置为setOn,用户ID。 这消除了索引变得分散的可能性。 您应该使用表分区来拆分表,这样每个月都存储在一个单独的文件中。 这将减less维护。 您可以在线查看分区滑动窗口脚本,您可以每月运行该脚本以创build下个月的新表格,删除最早的月份并调整分区scheme。 如果您不关心存储,也可以将真正的旧月份移到归档表中。

你的查询where子句应该是这样的forms:

 WHERE setOn > @setOn AND userId = @userId 

或者当你整个月回来的时候:

 WHERE setOn BETWEEN @setOnBegin AND @setOnEnd AND userId = @userId 

你的新的模式devise,没有分区将看起来像这样:

 -- Stub table for foreign key CREATE TABLE Users ( [userId] [uniqueidentifier] NOT NULL CONSTRAINT PK_Users PRIMARY KEY NONCLUSTERED CONSTRAINT DF_Users_userId DEFAULT NEWID(), [userName] VARCHAR(50) NOT NULL ) GO CREATE TABLE DiaryEntries ( [userId] [uniqueidentifier] NOT NULL CONSTRAINT FK_DiaryEntries_Users FOREIGN KEY REFERENCES Users, [setOn] [datetime] NOT NULL CONSTRAINT DF_DiaryEntries_setOn DEFAULT GETDATE(), [entry] [nvarchar](255) NULL, CONSTRAINT PK_DiaryEntries PRIMARY KEY CLUSTERED (setOn, userId) ) GO 

你得到这个工作后,你必须添加分区。 为此,从这个博客文章开始的一些理论。 然后开始阅读这个MSDN白皮书 。 这份白皮书是为2005年编写的,2008年还有我没有调查过的分区改进,所以2008年的解决scheme可能会更简单。

不是在这里批评你的解决scheme,我也不能这样做,因为我对这个问题不够了解。 这是我的反馈:

  • 如果你不喜欢的东西是由于行的大小而使用了太多的磁盘空间,那么检查一下稀疏的列这样所有的空值都不占用太多的空间!
  • 有外键会大大减慢你的插入,你有没有testing过这个?

您希望将新行插入到表的物理文件的末尾,如日志文件,因为每天都插入太多的行。

因此,行应按时间顺序sorting

因此setOn应该是主键的第一部分。 – 或者,理想情况下,添加一个“postId”列,它只是一个自动增量的整数

如果你不想要postId列,那么主键将是(setOn,userId),否则它可以简单地是postId。

因此,我们已经获得了快速的插入时间。 现在我们希望在每个用户ID的基础上select快速检索时间。

为此,我们需要在表中添加一个二级索引,它应该在useId上。 由于每个用户只有90条logging,这足以使rdbms能够快速检索该用户的所有行(全部90行,因为一次只有一个月的行),然后对这90行进行表扫描,这将是非常快的。

索引可以是任何标准的B树,红黑树,索引,不pipe你的数据库是什么。

插入索引时会稍微减慢,但不会太多。 树结构在处理随机插入方面非常好。

由于UserId索引基于一组UserIds(这是一个稳定的集合),因此树应该相当稳定,不需要太多的重新平衡:只有在结尾的叶节点会随着日记条目的添加和清除而改变,这将会并没有真正改变树的形状太多。

我不是你的新解决scheme的粉丝。 它只会引入新的问题,最大的问题是UPDATE(通常)比INSERTS慢,并且在更新发生时创build更大的阻塞风险。

如果您担心页面拆分,则只需调整聚簇索引的“ FillFactor ”即可。 FillFactor定义每个页面有多less空白(默认)以允许更改或插入。

设置一个合理的FillFactor意味着插入不应该导致(尽可能多的)页面拆分,并且清除旧的logging意味着在这些页面中应该释放更多的空间,每个页面保持(稍微)一致的可用空间。

不幸的是,SQL的默认值通常是0(意思是100),这意味着所有的页面都是完整的,这会导致大量的页面拆分。 许多人推荐90的值(每个数据页面有10%的可用空间)。 我不能告诉你什么是你的桌子的理想,但如果你的超偏执的页面分裂,尝试75甚至更less,如果你可以腾出额外的磁盘空间。 有一些perfmon计数器,您可以监视以查看页面拆分,或者您可以运行查询来告诉您每个数据页面的可用空间百分比。

关于你的表(原始版本)的索引的具体情况,我会build议([userId],[setOn])上的聚集索引,因为Remus提到的原因。

您还需要([setOn])上的非聚簇索引,以便“删除旧logging”查询不必执行全表扫描来查找所有旧logging。

在大多数情况下,我也不是简单标识符的GUID的粉丝,但我想这可能有点晚。

编辑 :对这张表的一个估计的fillfactor的一些初步计算。

对于每个用户,每天3个新的条目,保持30天,所以总共90个条目。 假设您每天清除超过30天的所有logging(而不是每30天只清除一次),那么您每天只能添加/删除less于5%的logging。

所以90的填充因子(每页10%的可用空间)应该足够了。

如果你只是每月清理一次 ,那么在删除最老的30个之前,你将会放置近60天,这意味着你需要50%的填充因子。

我强烈build议每天清除。

编辑2 :经过进一步考虑,[setOn]上的非聚集索引可能不够清晰查询所使用的select性(一天是行数的1/30或3.3%,这就是“有用”)。 即使索引存在,也可能只是执行聚集索引扫描。 可能值得用这个额外的索引来testing。

我build议:

  1. 用户标识的聚集索引
  2. seton&entry上的非聚簇覆盖索引,或seton上的非聚簇索引

解决这个问题的一个办法是每天都有一张桌子。

对于3Mlogging表,在userid和seton上不存在聚集索引的问题。 您的插入时间会低得多。

您可以在当天结束的时候在当天的桌子上进行维护,以便表格不会被分割,并且响应时间也不会有问题。

您也可以在表中创build一个视图,以获取整个月的数据。