sql-server – 索引这个非常大的表的最佳方法

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条新记录.一旦记录超过1个月,我们将其删除.

大多数查询都有以下WHERE子句：

WHERE userId = @userId AND setOn > @setOn

大多数查询返回不超过3行,除了返回在这个月内插入的所有行(最多90行)的行.

插入记录后,无法更改日期和userId.

现在我的问题是 – 如何最好地安排此表的索引？我坚持两种选择：

>聚合索引(userId,setOn) – 这将给我快速搜索,但我担心过多的页面拆分,因为我们将插入许多中间值(相同的userId但不同的日期).
>(userId)和on(setOn)上的非聚簇索引 – 这也会导致(userId)索引上的页面拆分(但是它和第一个选项一样昂贵吗？).由于我们正在使用NC索引,因此搜索速度变慢.
>附加列(id)上的聚簇索引和(userId,setOn)上的非聚集索引 – 这将消除数据表的页面拆分,但仍会导致NC索引上的一些.此选项对于搜索也不是最佳选择,因为我们使用NC索引进行搜索.

你有什么建议？还有其他选择吗？

PS – 感谢您的时间.

经过2天的思考,我想出了一个不同的解决方案来解决这个问题.

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天分成一排.这意味着我每个用户每月只插入一次新记录.这将页面拆分从每个用户每天3次减少到每个用户每月一次.显然有缺点,这里有一些

>行大小 – 但是在99.999％
我只查询一行的时间
来自MonthlyDiaries.
>我可能会使用更多空间
比我需要的,因为有些日子可能
没有条目.没有大碍.
>查找特定日期的条目
将需要额外的索引搜索
DiaryEntries.我相信它不会发生
因为我是一个如此巨大的代价
检索不超过90行和
在80％的情况下,我只检索1行.

总的来说,我认为这是一个很好的权衡：从3页分割/每天/用户减少到只有1页分/月/用户,但作为回报,通过使我的搜索稍微慢一点来支付一小笔费用.你怎么看？

解决方法

碎片主要是扫描的问题,对于搜索来说则更少.碎片对预读具有很大影响,并且寻求不使用也不需要预读.具有较差列选择的未分段索引将始终比具有良好可用列的99％片段索引更糟糕.如果您已经描述了扫描表格的DW报告样式查询,那么我建议专注于消除碎片,但对于您描述的负载,更有意义的是关注有效(覆盖)搜索和(小)范围扫描.

鉴于您的访问模式始终由@userId驱动,这必须是聚集索引中最左侧的列.我还要将setOn作为聚集索引中的第二列添加,因为它在大多数查询中添加了一些边际值(我说边缘值是因为@userId是如此选择性,最差的是90密耳的90条记录,额外的过滤由@setOn并不重要).我没有添加任何非聚集索引,从您描述的查询中不需要任何索引.

唯一的问题是删除旧记录(保留30天).我建议不要使用辅助NC索引来满足这一要求.我宁愿使用滑动窗口部署每周分区方案,请参阅How to Implement an Automatic Sliding Window in a Partitioned Table on SQL Server 2005.使用此解决方案,旧记录将通过分区交换机删除,这是最有效的方法.每日分区方案将更准确地满足30天保留要求,并且可能值得尝试和测试.我毫不犹豫地直接推荐30个分区,因为您描述了一些有可能在每个分区中寻找特定@userId记录的查询,并且31个分区可能会在高负载下产生性能问题.更好地测试和测量.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!