我们有一个数据仓库,它的记录数很大(10-20百万行),并且经常运行查询来对特定日期之间的记录进行计数,或者对具有某些标志的记录进行计数,例如

SELECT
    f.IsFoo,
    COUNT(*) AS WidgetCount
FROM Widgets AS w
JOIN Flags AS f
    ON f.FlagId = w.FlagId
WHERE w.Date >= @startDate
GROUP BY f.IsFoo


性能不是很糟糕,但是相对较慢(在冷缓存中可能是10秒)。

最近我发现我可以在索引视图中使用GROUP BY并尝试了一些方法类似于以下内容

CREATE VIEW TestView
WITH SCHEMABINDING
AS
    SELECT
        Date,
        FlagId,
        COUNT_BIG(*) AS WidgetCount
    FROM Widgets
    GROUP BY Date, FlagId;
GO

CREATE UNIQUE CLUSTERED INDEX PK_TestView ON TestView
(
    Date,
    FlagId
);


结果,我的第一个查询的性能现在为<100ms,结果视图和索引为<100k(尽管我们的行数为日期和标志ID的范围很大,这意味着该视图仅包含1000-2000行)。

我认为这可能会降低对Widget表的写入性能,但不会-据我所知,对该表的插入和更新操作几乎不受影响(此外,作为数据仓库,该表无论如何都不经常更新)

要我,这看起来真是太好了-是吗?以这种方式使用索引视图时,我需要注意什么?

评论

您可以重写脚本,使它们实际上是有效的SQL吗?您的SELECT和CREATE VIEW脚本是错误的,因为我相信这是您的CREATE INDEX脚本。

@MarkSinkinson Apologies,事实证明,尝试为虚表编写有效的SQL

当我想要更高级的视图时(例如包含MAX,自联接或外部联接的视图,或索引本身引用了另一个视图的视图),对我来说,“太好了,不能实现”部分来了-至少在SQL Server中所有这些都不是允许docs.microsoft.com/zh-CN/sql/relational-databases/views/…。因此,我总是最终变得过于野心勃勃,然后不得不缩小规模。但是对于更简单的聚合而言,它们确实很棒-甚至支持SUM。

#1 楼

正如您已经指出的,视图本身只包含少量行-因此,即使您更新整个表,更新视图所涉及的其他I / O也可以忽略不计。创建视图时,您可能已经感受到了最大的痛苦。下一个最接近的是如果您向基表中添加了数以百万计的行,并且在视图中添加了一堆需要在视图中添加新行的新ID。

这太不正确了。您正在使用索引视图准确地使用它们-或至少是最有效的方法之一:在写入时为将来的查询聚合付费。当结果远小于源时,当然,当请求聚合的次数比更新基础数据的次数更多时(在DW中通常比OLTP更常见),这最有效。

不幸的是,许多人认为索引视图是神奇的-索引不会使所有视图更高效,尤其是那些简单地连接表和/或产生与源相同数量的行(甚至是行)的视图。乘)。在这些情况下,视图中的I / O与原始查询相同或什至更差,这不仅是因为存在相同或更多的行,而且通常它们还存储并实现了更多的列。因此,提前实现这些目标不会带来任何收益,因为-即使使用SSD,I / O,网络和客户端处理/渲染仍然是将大型结果集返回给客户端的主要瓶颈。与仍在使用的所有其他资源相比,避免在运行时避免联接所节省的成本无法衡量。

像非聚集索引一样,请注意不要过度使用它。如果将10个不同的索引视图添加到一个表中,将会对工作量的写入部分产生更多影响,尤其是如果分组列不在集群键中。

天哪,我的意思是写有关此主题的博客。

#2 楼

Aarons的回答很好地涵盖了这个问题。要添加两点:


聚合索引视图可能导致跨行争用和死锁。通常,两个插入不会死锁(除了极少数情况,例如锁升级或锁哈希冲突)。但是,如果两个插入都在视图中指向同一组,则它们将争用。同一点代表需要锁定的任何其他内容(DML,锁定提示)。
未聚合的索引视图也很有用。它们使您可以索引多个表中的列。这样,您可以有效地对一个表进行筛选,并按联接表中的列进行排序。这种模式可以将全表联接转换为微小的恒定时间查询。

我同时使用了聚合和联接视图。

在所有用例中似乎都就像一个完美的案例。索引视图是一种未被充分利用的技术。