主页 / dba / 问题 / 87317
Accepted
Justin
Asked: 2015-01-01 03:49:58 +0800 CST

为聚合使用索引视图 - 好得令人难以置信？

  • 772

我们有一个记录数相当大（10-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 毫秒，结果视图和索引 < 100k（尽管我们的行数很大，但日期和标志 ID 的范围意味着该视图仅包含 1000-2000 行）。

我认为这可能会削弱写入 Widget 表的性能，但不会 - 据我所知，插入和更新该表的性能几乎不受影响（另外，作为一个数据仓库，该表不经常更新反正）

对我来说，这似乎好得令人难以置信——是吗？以这种方式使用索引视图时需要注意什么？

sql-server index

2 个回答

  1. Best Answer

    正如您所指出的，视图本身仅实现了少量行 - 因此即使您更新整个表，更新视图所涉及的额外I/O 也可以忽略不计。当您创建视图时，您可能已经感受到了最大的痛苦。下一个最接近的情况是，如果您向基表中添加大量行，其中包含一堆需要视图中的新行的新 ID。

    这不太好，不可能是真的。您正在使用索引视图的用途，或者至少是最有效的方法之一：在写入时为未来的查询聚合支付费用。当结果远小于源时，当然，当请求聚合的频率高于更新基础数据的频率时（通常在 DW 中比在 OLTP 中更常见），这种方法效果最好。

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

    与非聚集索引一样，请注意不要过度使用。如果您将 10 个不同的索引视图添加到一张表中，您将看到对工作负载的写入部分的影响更大，尤其是在分组列不是（在）集群键的情况下。

    天哪，我一直想写关于这个话题的博客。

    • 29

  2. Aarons 的回答很好地涵盖了这个问题。补充两点：

    1. 聚合索引视图可能导致跨行争用和死锁。通常，两个插入不会死锁（除了非常罕见的情况，例如锁升级或锁哈希冲突）。但是，如果两个插入都针对视图中的同一组，它们将竞争。同一点代表任何其他需要锁定的东西（DML，锁定提示）。
    2. 不聚合的索引视图也很有用。它们允许您对来自多个表的列进行索引。这样，您可以有效地过滤一个表并按连接表中的列排序。该模式可以将全表连接转换为微小的恒定时间查询。

    我已经使用了聚合视图和连接视图，并从中受益匪浅。

    总而言之，您的用例似乎是一个完美的案例。索引视图是一种远未得到充分利用的技术。

    • 22

相关问题