我们有一个数据仓库,其记录数量相当大(1000万到2000万行),并且经常运行查询来计算某些日期之间的记录,或者计算具有某些标记的记录,例如：
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
);
结果,我的第一个查询的性能现在< 100毫秒,结果视图& index是< 100k(尽管我们的行数很大,但日期和标志ID的范围意味着此视图仅包含1000-2000行). 我认为这可能会破坏写入Widget表的性能,但是没有 – 就我所知,插入和更新到这个表的性能几乎不受影响(另外,作为数据仓库,这个表很少更新无论如何) 对我来说,这看起来好得令人难以置信 – 是吗？以这种方式使用索引视图时需要注意什么？
解决方法
正如您所指出的,视图本身仅实现了少量行 – 因此,即使您更新整个表,更新视图所涉及的额外I / O也可以忽略不计.您可能已经感受到创建视图时您将感受到的最大痛苦.下一个最接近的是,如果您向基表添加了大量行,其中包含一堆需要视图中新行的新ID.
这不是太好,不可能.您正在使用索引视图来确切地使用它们 – 或者至少是一种最有效的方法：在写入时支付将来的查询聚合.当结果比源小得多时,这种方法效果最好,当然,比基础数据更新时更频繁地请求聚合(通常在DW中比OLTP更常见).
不幸的是,许多人认为索引视图是神奇的 – 索引不会使所有视图更有效,尤其是简单地连接表和/或产生与源相同数量的行(甚至是乘法)的视图.在这些情况下,视图中的I / O与原始查询相同甚至更差,不仅因为存在相同或更多行,而且通常它们也存储和实现更多列.因此,提前实现这些并不会带来任何好处,因为 – 即使使用SSD – I / O,网络和客户端处理/呈现仍然是将大型结果集返回给客户端的主要瓶颈.与您仍在使用的所有其他资源相比,在运行时避免连接所节省的成本是无法衡量的.
与非聚集索引一样,请注意不要过度执行.如果向一个表添加10个不同的索引视图,您将看到对工作负载的写入部分的更多影响,尤其是在分组列不在(在)群集密钥中时.
天哪,我一直想写关于这个话题的博客.
