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

Question

在我的数据库设计中使用高级别的冗余、非规范化数据来提高性能。我经常会存储通常需要连接或计算的数据。例如，如果我有一个 User 表和一个 Task 表，我将冗余存储 Username 和 UserDisplayName在每个任务记录中。另一个示例是存储聚合，例如将 TaskCount 存储在 User 表中。

• 用户
  • 用户ID
  • 用户名
  • 用户显示名称
  • 任务计数
• 任务
  • 任务ID
  • 任务名称
  • 用户ID
  • 用户名
  • 用户显示名称

这对于性能来说非常有用，因为应用程序的读取次数比插入、更新或删除操作多得多，而且一些值(例如用户名)很少更改。然而，最大的缺点是必须通过应用程序代码或触发器来强制完整性。这对于更新来说可能非常麻烦。

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

Answer 1

在转向 NoSQL 解决方案之前，您可能希望首先尝试索引 View :

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

和:

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

使用索引 View 可以让您将基础数据保存在正确规范化的表中并保持数据完整性，同时为您提供该数据的非规范化“ View ”。对于高度事务性的表，我不建议这样做，但您说它的读取量比写入量大，因此您可能想看看这是否适合您。

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

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)然后创建一个具有以下内容的 View :

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

然后按照上面链接中的建议(WITH SCHEMABINDING、唯一聚集索引等)使其“持久化”。虽然如上所示在 SELECT 的子查询中进行聚合效率低下，但这种特定情况旨在在读取次数多于写入次数的情况下进行非规范化。因此，执行索引 View 将保留整个结构，包括聚合，物理存储，因此每次读取都不会重新计算它。

现在，如果某些用户没有任何任务，则需要 LEFT JOIN，则索引 View 将无法工作，因为创建它们时有 5000 次限制。在这种情况下，您可以创建一个真实的表(UserTask)，它是您的非规范化结构，并通过仅在用户表上的触发器来填充它(假设您执行了我上面显示的触发器，它根据Task 表)，或者您可以跳过 User 表中的 TaskCount 字段，只在两个表上使用触发器来填充 UserTask 表。最后，这基本上就是索引 View 所做的事情，而无需您编写同步触发器。