从零开始 利用MS SQL物化视图创建完美查询

1. 什么是MS SQL物化视图

物化视图（Materialized view）是一种基于查询结果的虚拟数据库对象，它可以存储预定义的 SQL 查询，并将查询结果缓存到本地磁盘中。这样可以在某些情况下提高查询的性能和响应时间，尤其是在大型数据集和复杂查询环境下。

2. 创建MS SQL物化视图

2.1 创建视图

在本文中，我们将创建一个具有以下列的视图：首先，我们 创建 表 t ，并将其填充一些实验数据，我们将使用 t 表来创建视图。t 表有以下列：

CREATE TABLE [dbo].[t](
   [id] [int] NOT NULL,
   [date] [datetime] NOT NULL,
   [device] [nvarchar](50) NOT NULL,
   [temperature] [float] NOT NULL,
   [humidity] [float] NOT NULL
) ON [PRIMARY]

我们可以使用以下代码来创建视图：

CREATE VIEW [temperature_average]
AS
SELECT 
   [date], 
   [device],
   AVG([temperature]) AS [average_temperature]
FROM 
   [dbo].[t]
GROUP BY 
   [date], [device]

上面的代码创建了一个名为 temperature_average 的视图，它显示了每个日期和设备的平均温度。 AVG 函数用于计算每个日期和设备的平均温度。

2.2 创建物化视图

要将普通视图转换为物化视图，请使用 CREATE MATERIALIZED VIEW 语句。示例如下：

CREATE MATERIALIZED VIEW [temperature_average_cached]
AS
SELECT 
   [date], 
   [device],
   AVG([temperature]) AS [average_temperature]
FROM 
   [dbo].[t]
GROUP BY 
   [date], [device]
WITH 
   (   
      DISTRIBUTION = HASH(id),
      CLUSTERED COLUMNSTORE INDEX
   )

上述 CREATE MATERIALIZED VIEW 代码创建了一个名为temperature_average_cached的物化视图，该视图包含了每个日期和设备的平均温度，与普通视图不同的是，该视图会缓存结果。在这里，我们使用 DISTRIBUTION = HASH(id) 来分发数据。我们还通过 CLUSTERED COLUMNSTORE INDEX 来创建列存储索引，以优化查询性能。这样，我们就可以通过重新运行查询来更新物化视图中的数据，来保证数据的实时性。

3. 使用MS SQL物化视图以提高查询性能

下面，我们将使用 temperature_average_cached 物化视图，来演示如何提高查询性能。考虑以下查询：

SELECT 
   [device], 
   [date],
   AVG([temperature]) AS [average_temperature]
FROM 
   [dbo].[t]
WHERE 
   [date] BETWEEN '20210101' AND '20211231'
GROUP BY 
   [device], [date]

在没有物化视图的情况下，上述查询将需要动态计算查询语句的结果。因此，执行时间将随着数据集的增长而增长。现在，我们可以通过使用物化视图来优化这个查询。在这里，我们可以使用以下查询来查询物化视图的内容：

SELECT 
   [device], 
   [date], 
   [average_temperature]
FROM 
   [temperature_average_cached]
WHERE
   [date] BETWEEN '20210101' AND '20211231'

这样做的好处是，物化视图已经把平均温度结果缓存起来，不需要对数据进行计算操作。这个查询将从物化视图中直接读取数据，从而实现更快的查询速度。由于物化视图具有缓存结果的功能，因此它们适用于经常查询但不经常修改的数据集合，以提高查询性能。

4. 总结

在本文中，我们介绍了 MS SQL 物化视图，并演示了如何以提高查询性能。物化视图是一种基于查询结果的虚拟数据库对象，它可以存储预定义的SQL查询，并将查询结果缓存到本地磁盘中。此外，我们还展示了如何创建物化视图，并给出了一个具体示例，该示例演示了如何针对大型数据集和复杂查询环境来使用物化视图来优化查询性能。这表明，物化视图是一种非常有价值的工具，可用于提高查询性能，特别是在大型数据库中。