索引视图及其如何帮助提高查询性能

引言

SQL Server 视图可以成为数据库开发人员非常有用的工具，它允许我们将许多复杂的查询封装成一个单一的查询，并帮助其他开发人员更轻松地使用我们的数据库。但如果我们只创建一个常规视图，它的作用仅仅是帮助使复杂的 SELECT 查询的执行更快，并且更具可读性和可理解性。这种类型的视图没有任何性能改进，因为视图只是检索数据的快捷方式。幸运的是，现在我们可以创建索引视图，这将有助于我们提高查询性能并节省成本。如果我们能很好地理解它并以正确的方式操作它，它将带来许多好处。但也没有免费午餐，所以我们应该关心什么？何时何地使用索引视图？

在这篇非常基础的文章中，我将向您展示什么是索引视图以及如何在 SQL Server 中使用它来提高查询性能。

什么是视图？

视图是 SQL Server 中的一个对象，它允许我们将一个复杂而繁琐的 SELECT 子句，包含许多子句，如 JOIN 操作符或聚合函数如 SUM COUNT AVG 等，组合成一个单一的查询。对于一个非常常见的客户发票管理数据库，假设我们有一个包含许多 JOIN 子句的查询，用于列出所有居住在 Danang 市的客户的订单详情。

SELECT a.Id, a.FirstName, a.LastName, d.ProductId, d.ProductName
FROM Customers a JOIN Orders b
ON a.CustomerId = b.CustomerId
JOIN OrderDetails c 
ON b.OrderId = c.OrderId
JOIN Products d
ON c.ProductId = d.ProductId
WHERE a.City = 'Danang'

因此，无论何时我们想要一个来自居住在 Danang 市的客户的订单详情列表，我们都必须运行上述查询。它有时并非一个简短的查询，而是非常非常长且复杂的查询，如果数据库工程师将这个复杂的查询交给后端开发人员，他会感到困惑。在这种情况下，数据库工程师将创建一个名为 vOrderDetail_DanangCity 的视图，如下所示：

CREATE VIEW dbo.vOrderDetail_DanangCity AS
SELECT a.Id, a.FirstName, a.LastName, d.ProductId, d.ProductName
FROM Customers a JOIN Orders b 
ON a.CustomerId = b.CustomerId 
JOIN OrderDetails c 
ON b.OrderId = c.OrderId 
JOIN Products d 
ON c.ProductId = d.ProductId 
WHERE a.City = 'Danang'

现在，数据库工程师向后端开发人员提供了一个名为 vOrderDetail_DanangCity 的视图。开发人员只需要知道视图的作用，而无需关心它是如何工作的。无论他何时需要使用该视图，他只需

 SELECT * FROM dbo.vOrderDetail_DanangCity

太棒了，现在他可以使用一行查询获取结果。但是视图内部发生了什么？在这种情况下，创建视图只是一个快捷方式，当视图被调用时，数据库管理系统 (DBMS) 仍然必须重新运行上述查询来获取结果。这意味着许多 join 子句、子句和聚合函数……将被再次执行。

索引视图

索引视图（在某些书中称为物化视图），是具有唯一聚集索引的视图。让我们创建一个索引视图来清楚地理解它。

假设我有一个具有以下关系 的简单数据库

一个 student 可以加入多个 course。一个 course 可以有多个 student 加入。加入课程的 student 将参加考试并获得分数。数据库中有 2000 行 Courses，5000 行 Students，以及 10,000,000 行 Student_Course。您可以在本文末尾下载此演示数据库备份文件 (bak)。

假设我们需要列出所有至少有 1 个 student 加入的课程，以及该 course 的平均分数。

SELECT crs.CourseName, AVG(std_crs.Score) AS Average
FROM dbo.Courses crs JOIN Student_Course std_crs
ON crs.Id = std_crs.CourseId
GROUP BY crs.CourseName

执行此查询需要 4 秒。我们将使用此查询来创建视图

CREATE VIEW dbo.AverageScrores
WITH SCHEMABINDING
AS
SELECT crs.CourseName, SUM(std_crs.Score) AS TotalScore, COUNT_BIG(*) AS NumberOfRecords
FROM dbo.Courses crs JOIN dbo.Student_Course std_crs
ON crs.Id = std_crs.CourseId
GROUP BY crs.CourseName

请注意，我们将在此案例中使用 SUM 和 COUNT_BIG(*)。在 SQL Server 中创建索引视图有一些限制和要求，我们必须处理它，在这种情况下，通过将 AVG 转换为 SUM 和 COUNT，并且将来，我们将计算 AVG = SUM / COUNT。我们在这里不讨论这些要求。

WITH SCHEMABINDING 是创建索引视图的强制要求。它有助于保护视图中出现的列，您不能修改或删除基表中的列。如果您想这样做，首先必须删除视图，修改或删除列，然后重新创建另一个视图。

最后，在该视图上创建一个唯一聚集索引。我们可以基于一个或多个列创建索引。

CREATE UNIQUE CLUSTERED INDEX CIX_AverageScrores
ON dbo.AverageScrores(CourseName);

现在，我们有了一个索引视图。当我们为视图创建唯一索引时，我们就是在“物化”它。

• 如果视图没有唯一索引，它仅仅是一个快捷方式。当视图被调用时，DBMS 将重新运行查询以获取结果。这意味着 DBMS 将运行许多 join 子句、许多复杂的聚合函数……全部重新执行。
• 如果视图有唯一索引，它就是“物化”的。该视图的一个副本被创建。这个物化视图用于存储我们用来创建视图的查询的结果。当视图被调用时，DBMS 不会重新运行具有许多复杂子句的查询，而是从物化视图中读取。

让我们运行下面的查询

SELECT CourseName, TotalScore / NumberOfRecords AS Average FROM dbo.AverageScrores WITH (NOEXPAND)

我们现在正在使用索引视图，通过指出查询应使用 WITH (NOEXPAND)。WITH (NOEXPAND) 将让 DBMS 知道这是一个索引视图，并且我们想要使用之前存储的结果。如果您忘记添加 WITH (NOEXPAND)，DBMS 将重新运行查询。

结果在 00:00:00 秒后出现。立即。

比较索引视图和视图

运行以下 2 个查询：第一个查询是用于创建视图的查询。第二个查询是索引视图。

SELECT crs.CourseName, SUM(std_crs.Score) / COUNT_BIG(*) AS Average
FROM dbo.Courses crs JOIN dbo.Student_Course std_crs
ON crs.Id = std_crs.CourseId
GROUP BY crs.CourseName
 
SELECT CourseName, TotalScore / NumberOfRecords AS Average FROM dbo.AverageScrores  WITH (NOEXPAND)

我们可以看到结果是相同的，没有区别。

但是执行时间完全不同。当我们使用索引视图时，是 00:00:00。当我们使用基础查询时，是 4 秒。这个差异是因为索引视图 WITH NOEXPAND 不必重新计算结果。它没有连接表并计算聚合函数。它只是读取视图中存储的结果并显示给我们。

何时使用

使用索引视图似乎是一个不错的选择。但这是免费的午餐吗？

不，不是。事实上，如果我们滥用它们，它将是一顿昂贵的午餐。每当基础表（在创建视图时在 FROM 子句中连接的表）发生更改时，DBMS 不仅需要更新表，重新索引视图，还需要重新计算存储的值。在这种情况下，当某个 student 加入任何 course 时，DBMS 需要重新计算该课程的 SUM 和 COUNT，这些值存储在物化视图中。这也意味着查询越复杂，视图的维护就越复杂。

视图维护是一个大问题。最有效的维护存储结果而不重新运行原始查询的方法是什么？由于视图维护问题，创建索引视图存在许多限制和要求，例如，不允许使用 OUTER JOIN 的查询。

所以我们知道索引视图将

使 SELECT 子句运行得更快。

使 INSERT、UPDATE、DELETE 或某些操作事件变慢。

因此，请明智地使用它们。仅将索引视图应用于我们经常使用的查询，但基础表不经常更新。在这种情况下，性能和效率将达到最大。否则，请小心。如果视图不经常使用，但基础表已不时更新，您可能会使系统运行得比预期的慢。

您可以在下面的链接下载演示数据库（我使用的是 SQL Server 2014）

• http://codingamazing.com/Files/DemoIndexedViews.zip

历史

• 2019年3月23日：初始发布