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表的写入性能,但不会-据我所知,对该表的插入和更新操作几乎不受影响(此外,作为数据仓库,该表无论如何都不经常更新)
要我,这看起来真是太好了-是吗?以这种方式使用索引视图时,我需要注意什么?
#1 楼
正如您已经指出的,视图本身只包含少量行-因此,即使您更新整个表,更新视图所涉及的其他I / O也可以忽略不计。创建视图时,您可能已经感受到了最大的痛苦。下一个最接近的是如果您向基表中添加了数以百万计的行,并且在视图中添加了一堆需要在视图中添加新行的新ID。这太不正确了。您正在使用索引视图准确地使用它们-或至少是最有效的方法之一:在写入时为将来的查询聚合付费。当结果远小于源时,当然,当请求聚合的次数比更新基础数据的次数更多时(在DW中通常比OLTP更常见),这最有效。
不幸的是,许多人认为索引视图是神奇的-索引不会使所有视图更高效,尤其是那些简单地连接表和/或产生与源相同数量的行(甚至是行)的视图。乘)。在这些情况下,视图中的I / O与原始查询相同或什至更差,这不仅是因为存在相同或更多的行,而且通常它们还存储并实现了更多的列。因此,提前实现这些目标不会带来任何收益,因为-即使使用SSD,I / O,网络和客户端处理/渲染仍然是将大型结果集返回给客户端的主要瓶颈。与仍在使用的所有其他资源相比,避免在运行时避免联接所节省的成本无法衡量。
像非聚集索引一样,请注意不要过度使用它。如果将10个不同的索引视图添加到一个表中,将会对工作量的写入部分产生更多影响,尤其是如果分组列不在集群键中。
天哪,我的意思是写有关此主题的博客。
#2 楼
Aarons的回答很好地涵盖了这个问题。要添加两点:聚合索引视图可能导致跨行争用和死锁。通常,两个插入不会死锁(除了极少数情况,例如锁升级或锁哈希冲突)。但是,如果两个插入都在视图中指向同一组,则它们将争用。同一点代表需要锁定的任何其他内容(DML,锁定提示)。
未聚合的索引视图也很有用。它们使您可以索引多个表中的列。这样,您可以有效地对一个表进行筛选,并按联接表中的列进行排序。这种模式可以将全表联接转换为微小的恒定时间查询。
我同时使用了聚合和联接视图。
在所有用例中似乎都就像一个完美的案例。索引视图是一种未被充分利用的技术。
评论
您可以重写脚本,使它们实际上是有效的SQL吗?您的SELECT和CREATE VIEW脚本是错误的,因为我相信这是您的CREATE INDEX脚本。@MarkSinkinson Apologies,事实证明,尝试为虚表编写有效的SQL
当我想要更高级的视图时(例如包含MAX,自联接或外部联接的视图,或索引本身引用了另一个视图的视图),对我来说,“太好了,不能实现”部分来了-至少在SQL Server中所有这些都不是允许docs.microsoft.com/zh-CN/sql/relational-databases/views/…。因此,我总是最终变得过于野心勃勃,然后不得不缩小规模。但是对于更简单的聚合而言,它们确实很棒-甚至支持SUM。