我有下面的表格
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:
你有什么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到每个用户每月一次。 显然有缺点,这里有一些
总的来说,我认为这是一个很好的权衡:从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,我也不能这样做,因为我对这个问题不够了解。 这是我的反馈:
您希望将新行插入到表的物理文件的末尾,如日志文件,因为每天都插入太多的行。
因此,行应按时间顺序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议:
解决这个问题的一个办法是每天都有一张桌子。
对于3Mlogging表,在userid和seton上不存在聚集索引的问题。 您的插入时间会低得多。
您可以在当天结束的时候在当天的桌子上进行维护,以便表格不会被分割,并且响应时间也不会有问题。
您也可以在表中创build一个视图,以获取整个月的数据。