sql - 在 Microsoft SQL Server 中存储昂贵的数据库计算

Question

我正在使用 Microsoft SQL Server。我想存储/缓存昂贵的计算，以便可以重复使用它们。在我的示例中，我有用户评价的项目，数据库架构如下所示:

User
 Id : int

Item
 Id : int

UserItemRating
 UserId (User)
 ItemId (Item)
 Rating : int

为了获得评分最高的项目，我需要计算每个项目的平均评分(假设操作成本高，并且 UserItemRating 经常更改/更新)。缓存此计算的好方法是什么？我应该向 Item 添加一列“AverageRating”并创建一个更新它的触发器吗？创建 View ？保存计算的临时表？

Answer 1

在 SQL Server 中，您将创建一个索引 View ，如下所示:

create view dbo.ItemRatings
with schemabinding
as
    select
        ItemId,
        COUNT_BIG(*) as Cnt,
        SUM(Rating) as TotalRating
    from
        dbo.UserItemRating
    group by
        ItemId
go
create unique clustered index IX_ItemRatings on dbo.ItemRatings (ItemId)

有各种restrictions关于索引 View 的创建和使用，但上述内容有效(假设 UserItemRating 在 dbo 架构中)。

注意事项:

1. 必须是 WITH SCHEMABINDING，这反过来意味着我们必须使用两部分名称来引用表。
2. 必须使用 COUNT_BIG() 才能使用 GROUP BY 子句
3. 不直接包含平均值(事实上，索引 View 中不允许 AVG)，但是，您可以通过将 SUM() 除以来计算它COUNT_BIG()。
4. 要在此 View 上使用索引，您需要使用 NOEXPAND 提示查询它，或者运行企业版或更高版本。

为什么这优于基于触发器/表的解决方案？因为执行维护的代码是内置到 SQL Server 中的，并且总体开销比任何其他解决方案都低。 (而且您不必花时间确保它正确)

如果您使用的是企业版，它甚至可以在您的查询不直接引用它的情况下使用此索引 View - 例如，如果您要对请求 COUNT 的基表进行查询>、SUM，甚至是 AVG，它都可能使用这个索引。