☰

索引这个非常大的表的最佳方法

tag-icon • tag-icon sql-server sql-server-2008 indexing
索引这个非常大的表的最佳方法

我有下表

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 行）。

一旦插入记录，日期和用户 ID 就无法更改。

现在我的问题是-如何最好地安排该表的索引？我只能选择两种方案：

  1. (userId，setOn) 上的聚集索引 - 这将使我能够快速搜索，但我担心过多的页面分割，因为我们将插入很多中间值（相同的 userId 但不同的日期）。
  2. (userId) 和 (setOn) 上的非聚集索引 - 这也会导致 (userId) 索引上的页面拆分（但它是否与第一个选项一样昂贵？）。由于我们使用 NC 索引，因此搜索速度变慢。
  3. 在附加列 (id) 上建立聚集索引，在 (userId、setOn) 上建立非聚集索引 - 这将消除数据表的页面拆分，但仍会导致 NC 索引出现一些页面拆分。由于我们使用 NC 索引进行搜索，因此此选项对于搜索来说也不是最佳选择。

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

PS-感谢您的时间。

经过两天的思考，我想到了一个不同的方法来解决这个问题。

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 次减少到每个用户每月 1 次。显然存在缺点，以下是其中一些

  • 行大小很大 - 然而 99.999％ 的时间里我只从 MonthlyDiaries 中查询一行。
  • 我可能使用了比我需要更多的空间，因为有些天可能没有条目。没什么大不了的。
  • 要查找特定日期的条目，需要在 DiaryEntries 上进行额外的索引查找。我相信这不会带来太大的成本，因为我检索的行数不超过 90 行，而且在 80% 的情况下，我只检索 1 行。

总体而言，我认为这是一个很好的权衡：从每天每个用户拆分 3 个页面减少到每月每个用户拆分 1 个页面，但作为回报，我的搜索速度会稍微慢一些。您觉得呢？

答案1

我假设您有充分的理由使用 guid 作为 id。

碎片主要是扫描的问题，而搜索的问题则较少。碎片对预读有很大影响，而搜索不使用也不需要预读。列选择不佳的未碎片化索引的性能总是比具有良好、可用列的 99% 碎片索引差。如果您描述了扫描表的 DW 报告样式查询，那么我会建议重点消除碎片，但对于您描述的负载，更有意义的是专注于高效（覆盖）搜索和（小）范围扫描。

鉴于您的访问模式始终由 @userId 驱动，这必须是聚集索引中最左边的列。我还会将 setOn 添加为聚集索引中的第二列，因为它会在大多数查询中添加一些边际值（我说边际是因为 @userId 的选择性很强，最差的情况下是 9000 万条记录中的 90 条记录，因此 @setOn 添加的额外过滤并不重要）。我不会添加任何非聚集索引，从您描述的查询来看，不需要任何非聚集索引。

唯一的问题是删除旧记录（30 天保留期）。我不建议使用辅助 NC 索引来满足此要求。我宁愿部署带有滑动窗口的每周分区方案，请参阅如何在 SQL Server 2005 的分区表中实现自动滑动窗口。使用此解决方案，旧记录通过分区切换删除，这是最有效的方式。每日分区方案可以更准确地满足 30 天的保留要求，也许值得尝试和测试。我犹豫是否直接推荐 30 个分区，因为您描述了一些查询，这些查询有可能在每个分区中寻找特定的 @userId 记录，而 31 个分区可能会在重负载下产生性能问题。最好同时进行测试和测量。

答案2

首先，在表上添加默认约束。其次，添加分区方案。第三，重写最常见的查询。

应将聚集索引设置为 setOn，用户 ID。这样可以消除索引碎片化的可能性。您应该使用表分区来拆分表，以便每个月都存储在单独的文件中。这将减少维护。您可以在线查找分区滑动窗口脚本，您可以每月运行该脚本来为下个月创建新表，删除最旧的月份并调整分区方案。如果您不关心存储问题，您还可以将真正旧的月份移动到存档表。

您的查询 where 子句应采用以下形式：

WHERE setOn > @setOn AND userId = @userId

或者当您返回整个月时：

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

没有分区的新架构设计将如下所示：

-- 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 年的解决方案可能更简单。

答案3

我在这里不是为了批评您的解决方案，我也没有资格这样做，因为我对这个问题了解不够。以下是我的反馈：

  • 如果你唯一不满意的是由于行大小而占用了太多的磁盘空间，请查看 稀疏列 这样，所有的空值就不会占用太多空间！
  • 拥有外键会大大减慢你的插入速度，你测试过吗？

答案4

我不太喜欢你的新解决方案。它只会引入新问题，最大的问题是 UPDATES（通常）比 INSERTS 慢，并且在进行更新时会产生更大的阻塞风险。

如果你担心页面分裂，你需要做的就是调整“填充因子“用于聚集索引。FillFactor 定义每页留空多少（默认情况下）以允许更改或插入。

设置合理的 FillFactor 意味着插入不应该导致（尽可能多的）页面分割，并且清除旧记录意味着应该在这些页面中释放更多空间，从而保持每个页面（某种程度上）一致的可用空间。

不幸的是，SQL 默认值通常为 0（与 100 相同），这意味着所有页面都已完全填满，从而导致大量页面拆分。许多人建议将值设为 90（每个数据页面中有 10% 的可用空间）。我无法告诉您最适合您的表的值是多少，但是如果您对页面拆分极为敏感，请尝试将值设为 75 甚至更低，前提是您有足够的磁盘空间。您可以监视一些 perfmon 计数器来观察页面拆分，或者您可以运行查询来告诉您每个数据页面中的可用空间百分比。

关于表（原始版本）上索引的具体情况，我建议在（[userId]，[setOn]）上使用聚集索引，原因如 Remus 所述。

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

大多数时候，我也不喜欢使用 GUID 作为简单标识符，但我想改变这一点可能有点晚了。

编辑：对该表的估计填充因子进行一些初步计算。

对于每个用户，每天 3 个新条目，保留 30 天，因此总共约 90 个条目。假设你做了一个日常的清除所有超过 30 天的记录（而不是每 30 天仅清除一次），那么您每天仅添加/删除不到 5% 的记录。

因此，填充因子为 90（每页有 10% 的可用空间）应该足够了。

如果你只是清除每月，那么在删除最旧的 30 天之前，您将需要堆积近 60 天的时间，这意味着您需要 50% 左右的填充因子。

我强烈建议每天进行一次清除。

编辑2：经过进一步考虑，[setOn] 上的非聚集索引可能没有足够的选择性，无法供您的清除查询使用（一天是 1/30 或 3.3% 的行，这正好处于“有用”的边缘）。即使索引存在，它也可能只是进行聚集索引扫描。可能值得在有和没有这个附加索引的情况下进行测试。

相关内容