在SQL Server中自动更新冗余/非规范化数据

Question

在我的数据库设计中，使用高度冗余的非规范化数据来提高性能。 我经常存储通常需要连接或计算的数据。例如，如果我有一个“用户”表和一个“任务”表，我会在每个“任务”记录中冗余存储“用户名”和“用户显示名称”。另一个例子是存储聚合，例如在“用户”表中存储“任务数”。

这对性能很有好处，因为应用程序具有比插入、更新或删除操作更多的读取，并且某些值（如“用户名”）很少更改。然而，缺点是完整性必须通过应用程序代码或触发器执行。这在更新时可能非常麻烦。

我的问题是，在SQL Server 2005/2010中是否可以自动完成这个过程...也许通过一个持久/永久视图。是否有人推荐其他可能的解决方案或技术？我听说基于文档的数据库，如CouchDB和MongoDB，可以更有效地处理非规范化数据。

- Sterling Nichols

Answer 1

在尝试使用 NoSQL 解决方案之前，您可能需要先尝试使用 Indexed View：

http://msdn.microsoft.com/en-us/library/ms187864.aspx

并且：

http://msdn.microsoft.com/en-us/library/ms191432.aspx

使用索引视图可以让您将基础数据保存在规范化的表中，并在保持数据完整性的同时为您提供该数据的非规范化“视图”。我不建议在高事务性表上使用此方法，但您说它读取的次数比写入的次数多，因此您可能想看看这是否适用于您。

根据您的两个示例表之一，一个选项是：

1）向用户表添加一列，定义为：

TaskCount INT NOT NULL DEFAULT (0)

2）在任务表上添加触发器，定义如下：

CREATE TRIGGER UpdateUserTaskCount
ON dbo.Task
AFTER INSERT, DELETE
AS

;WITH added AS
(
    SELECT  ins.UserID, COUNT(*) AS [NumTasks]
    FROM    INSERTED ins
    GROUP BY    ins.UserID
)
UPDATE  usr
SET     usr.TaskCount = (usr.TaskCount + added.NumTasks)
FROM    dbo.[User] usr
INNER JOIN  added
        ON  added.UserID = usr.UserID


;WITH removed AS
(
    SELECT  del.UserID, COUNT(*) AS [NumTasks]
    FROM    DELETED del
    GROUP BY    del.UserID
)
UPDATE  usr
SET     usr.TaskCount = (usr.TaskCount - removed.NumTasks)
FROM    dbo.[User] usr
INNER JOIN  removed
        ON  removed.UserID = usr.UserID
GO

3) 然后创建一个视图，其中包含：

SELECT   u.UserID,
         u.Username,
         u.UserDisplayName,
         u.TaskCount,
         t.TaskID,
         t.TaskName
FROM     User u
INNER JOIN   Task t
        ON   t.UserID = u.UserID

然后按照上面的链接建议（使用SCHEMABINDING，唯一聚集索引等）进行优化，使其“持久化”。虽然在SELECT子查询中执行聚合操作效率低下，但这种情况旨在将其非规范化，在读操作高于写操作的情况下。因此，创建索引视图将保留整个结构，包括聚合操作，物理存储，因此每次读取都不需要重新计算。

现在，如果需要LEFT JOIN，如果某些用户没有任何任务，则由于创建它们的5000限制，索引视图将无法工作。在这种情况下，您可以创建一个真实的表（UserTask），它是您的非规范化结构，并通过触发器仅在用户表上填充（假设您执行我上面展示的触发器，该触发器基于任务表中的更改更新用户表），或者您可以跳过用户表中的TaskCount字段，只需在两个表上创建触发器以填充UserTask表即可。最后，这基本上就是索引视图所做的事情，只是您不必编写同步触发器。

- Solomon Rutzky

---

持久化的计算列在此处也可能是一个很好的策略。由于很少发出DML语句，因此它们不需要经常更新，并且据我所知，它们与表上的物理记录一样好。 - Jeremy Pridemore

@Jeremy：如果聚合是索引视图的一部分，那么它应该自然地与其余的物化字段一起物理存储，因此如果解决方案是使用索引视图，则不需要显式的持久计算列。但是，如果不使用索引视图，则我同意（并且您是正确的），持久计算列是值得考虑的东西，因为它是物理存储而不是每次读取行时计算。 - Solomon Rutzky

计算列听起来是一个不错的选择，但是是否可能创建一个从另一个表中计算出来的计算列（例如User.DisplayName --> Task.UserDisplayName），并且当其他表/记录更新时自动更新该列？ - Sterling Nichols

@Jonathan：不是和是，还有一个“但是”。不可以从计算机列中使用来自表本身的字段。是的，“可以”创建一个标量UDF来接受行中的值（例如UserID），然后加入到另一个表中返回单个值（例如DisplayName）。然而，在计算列中执行UDF会有性能问题（我不确定是否可以标记为PERSISTED），在您的情况下，通过在具有聚集索引的视图中两个表之间进行简单的JOIN即可解决所有问题，并且您可以访问两个表中的所有字段。 - Solomon Rutzky

@Jonathan：我刚刚添加了一些细节到答案中，以使其更清晰。现在你可以看到，计算列只会对一个表有帮助（而且只有在它能被标记为PERSISTED的情况下，我认为它不能）。 - Solomon Rutzky