使用具体化视图优化性能

项目
2025/04/24

Azure Synapse 中专用 SQL 池的具体化视图为复杂的分析查询提供了较低的维护方法，无需更改任何查询即可获得快速性能。本文讨论有关使用具体化视图的一般指南。

具体化视图与标准视图

Azure Synapse 中的专用 SQL 池支持标准和具体化视图。这两个表都是使用 SELECT 表达式创建的虚拟表，并作为逻辑表呈现给查询。视图封装了常见数据计算的复杂性，并向计算更改添加抽象层，因此无需重写查询。

每次使用视图时，标准视图都会计算其数据。磁盘上没有存储的数据。人们通常使用标准视图作为工具，帮助组织专用 SQL 池中的逻辑对象和查询。若要使用标准视图，查询需要直接引用它。

和表一样，具体化视图在专用 SQL 池中预先计算、存储和维护其数据。每次使用具体化视图时都不需要重新计算。这就是为什么在具体化视图中使用所有数据或子集的查询可以获得更快的性能。更妙的是，查询可以使用物化视图而无需直接引用它，因此不需要更改应用程序代码。

标准视图的大多数要求仍适用于具体化视图。有关具体化视图语法和其他要求的详细信息，请参阅 CREATE MATERIALIZED VIEW AS SELECT

比较	视图	具体化视图
查看定义	存储在专用 SQL 池中。	存储在专用 SQL 池中。
查看内容	每次使用视图时生成。	在创建视图期间预先处理并存储在专用 SQL 池中。随着在基础表中添加数据而不断更新。
数据刷新	始终保持更新	始终更新
从复杂查询中检索视图数据的速度	较慢	快速
额外存储空间	否	是的
语法	创建视图	CREATE MATERIALIZED VIEW AS SELECT

使用具体化视图的好处

设计合理的具体化视图具有以下优势：

使用联接和聚合函数减少复杂查询的执行时间。查询越复杂，就越有可能节省执行时间。当查询的计算成本较高并且生成的数据集较小时优势最大。
专用 SQL 池中的优化器可以自动使用部署的具体化视图来改进查询执行计划。此过程对于用户是透明的，提供更快的查询性能，且不需要查询直接引用具体化视图。
对视图的维护需求较低。基表中的所有增量数据更改都以同步方式自动添加到具体化视图，这意味着基表和具体化视图都在同一事务中更新。这种设计可以使查询具体化视图后返回的数据与直接查询基表后返回的数据相同。
具体化视图中的数据可以采用与基表不同的方式分发。
具体化视图中的数据与常规表中的数据具有相同的高可用性和复原优势。

专用 SQL 池中实现的物化视图还提供以下优势：

与其他数据仓库提供程序相比，专用 SQL 池中实现的具体化视图也具有以下优势：

广泛的聚合函数支持。请参阅 CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)。
支持特定于查询的具体化视图建议。请参阅 EXPLAIN (Transact-SQL)。
根据基表中的数据更改，自动、同步刷新数据。不需要任何用户操作。

常见应用场景

物化视图通常用于以下情形：

需要提高针对大型数据的复杂分析查询的性能

复杂的分析查询通常使用更多的聚合函数和表连接，从而导致更多的计算密集型作业，例如查询执行中的数据洗牌和连接。这就是为什么复杂的分析查询需要更长的时间才能完成，尤其是在大型表上。

用户可以为从查询的常见计算返回的数据创建具体化视图，因此查询需要此数据时无需重新计算，从而降低计算成本和更快的查询响应速度。

需要在不更改或最少更改查询的情况下提升性能

专用 SQL 池中的架构和查询更改通常保持在最低水平，以支持常规 ETL作和报告。如果视图产生的成本可以抵消查询性能提升，则人们可以使用具体化视图进行查询性能优化。

与其他优化选项（如缩放和统计信息管理）相比，创建和维护物化视图作为生产变更的影响较小，但其潜在的性能提升可能更高。

创建或维护具体化视图不会影响针对基表运行的查询。
查询优化器可以自动使用部署的具体化视图，而无需在查询中直接引用视图。此功能减少了对性能优化中的查询更改的需求。

需要不同的数据分发策略来提高查询性能

专用 SQL 池是分布式查询处理系统。 SQL 表中的数据使用三种分布策略之一（哈希、round_robin或复制）最多分布 60 个节点。

在表创建时指定数据分布，在删除表之前保持不变。物化视图是磁盘上的虚拟表，支持哈希和循环数据分布。用户可以选择与基表不同的数据分布，但最适合使用视图的查询性能。

设计指南

下面是有关使用具体化视图提高查询性能的一般指南：

工作负荷设计

在开始创建具体化视图之前，必须深入了解工作负荷的查询模式、重要性、频率和生成的数据的大小。

用户可以针对查询优化器建议的具体化视图运行 EXPLAIN WITH_RECOMMENDATIONS <SQL_statement>。由于这些建议是特定于查询的，因此对于同一工作负荷中的其他查询而言，有利于单个查询的具体化视图可能并非最佳。

根据工作负荷需求评估这些建议。理想的具体化视图是那些有利于工作负荷性能的视图。

请注意更快的查询与成本之间的权衡

对于每个具体化视图，都有数据存储成本和维护视图的成本。随着基表中的数据变化，具体化视图的大小也会增加，其物理结构也会发生变化。为了避免查询性能下降，每个具体化视图都由 SQL 引擎单独维护。

当具体化视图和基表更改增加时，维护工作负荷会更高。用户应检查是否可以通过查询性能提升来抵消所有具体化视图产生的成本。

可以运行此查询以在专用 SQL 池中生成具体化视图的列表：

SELECT V.name as materialized_view, V.object_id
FROM sys.views V
JOIN sys.indexes I ON V.object_id= I.object_id AND I.index_id < 2;

用于减少具体化视图数量的选项：

确定工作负荷中复杂查询经常使用的常见数据集。创建具体化视图来存储这些数据集，以便优化器可以在创建执行计划时将它们用作构建基块。
删除使用率较低或不再需要的具体化视图。已禁用的具体化视图不再受到维护，但仍会产生存储成本。
即便数据不重叠，也要合并在相同或类似基表上创建的物化视图。合并具体化视图可能会导致视图大小大于单独视图的总和，但视图维护成本应降低。例如：


-- Query 1 would benefit from having a materialized view created with this SELECT statement

SELECT A, SUM(B)
FROM T
GROUP BY A

-- Query 2 would benefit from having a materialized view created with this SELECT statement

SELECT C, SUM(D)
FROM T
GROUP BY C

-- You could create a single materialized view of this form

SELECT A, C, SUM(B), SUM(D)
FROM T
GROUP BY A, C

并非所有性能优化都需要查询更改

SQL 查询优化器可以自动使用已部署的具体化视图来提高查询性能。此项支持以透明方式应用到不引用视图的查询，以及使用不支持用于创建具体化视图的聚合的查询。无需进行任何查询更改。可以检查查询的估计执行计划，以确认是否使用了具体化视图。

监视具体化视图

具体化视图与具有聚集列存储索引（CCI）的表一样存储在专用 SQL 池中。读取具体化视图中的数据涉及到扫描 CCI 索引段，并应用基表中的任何增量更改。当增量更改数过高时，从具体化视图中解析查询所需的时间可能比直接查询基表要长。

为了避免查询性能下降，最佳做法是运行 DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD 来监视视图的 overhead_ratio (total_rows / max(1, base_view_row))。如果具体化视图的 overhead_ratio（开销比）过高，则用户应重新生成具体化视图。

具体化视图和结果集缓存

专用 SQL 池中的这两项功能用于查询性能优化。结果集缓存用于获取针对静态数据的重复查询的高并发性和快速响应。

若要使用缓存结果，请求查询的缓存形式必须与生成缓存的查询匹配。此外，缓存的结果必须应用于整个查询。

具体化视图允许基表中的数据更改。具体化视图中的数据可应用到查询片段。此支持允许不同查询使用相同的具体化视图，这些查询共享某些计算以提高性能。

示例：

此示例使用类似于 TPCDS 的查询来查找通过目录花费比在商店中花费更多资金的客户，并确定首选客户及其来源国家/地区。该查询涉及从涉及 SUM（）和 GROUP BY 的三个子 SELECT 语句的 UNION 中选择前 100 条记录。

WITH year_total AS (
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt+ss_ext_sales_price, 0)/2) year_total
       ,'s' sale_type
FROM customer
     ,store_sales
     ,date_dim
WHERE c_customer_sk = ss_customer_sk
   AND ss_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
UNION ALL
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt+cs_ext_sales_price, 0)/2) year_total
       ,'c' sale_type
FROM customer
     ,catalog_sales
     ,date_dim
WHERE c_customer_sk = cs_bill_customer_sk
   AND cs_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
UNION ALL
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt+ws_ext_sales_price, 0)/2) year_total
       ,'w' sale_type
FROM customer
     ,web_sales
     ,date_dim
WHERE c_customer_sk = ws_bill_customer_sk
   AND ws_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
         )
  SELECT TOP 100
                  t_s_secyear.customer_id
                 ,t_s_secyear.customer_first_name
                 ,t_s_secyear.customer_last_name
                 ,t_s_secyear.customer_birth_country
FROM year_total t_s_firstyear
     ,year_total t_s_secyear
     ,year_total t_c_firstyear
     ,year_total t_c_secyear
     ,year_total t_w_firstyear
     ,year_total t_w_secyear
WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_c_secyear.customer_id
   AND t_s_firstyear.customer_id = t_c_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_w_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_w_secyear.customer_id
   AND t_s_firstyear.sale_type = 's'
   AND t_c_firstyear.sale_type = 'c'
   AND t_w_firstyear.sale_type = 'w'
   AND t_s_secyear.sale_type = 's'
   AND t_c_secyear.sale_type = 'c'
   AND t_w_secyear.sale_type = 'w'
   AND t_s_firstyear.dyear+0 =  1999
   AND t_s_secyear.dyear+0 = 1999+1
   AND t_c_firstyear.dyear+0 =  1999
   AND t_c_secyear.dyear+0 =  1999+1
   AND t_w_firstyear.dyear+0 = 1999
   AND t_w_secyear.dyear+0 = 1999+1
   AND t_s_firstyear.year_total > 0
   AND t_c_firstyear.year_total > 0
   AND t_w_firstyear.year_total > 0
   AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END
           > CASE WHEN t_s_firstyear.year_total > 0 THEN t_s_secyear.year_total / t_s_firstyear.year_total ELSE NULL END
   AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END
           > CASE WHEN t_w_firstyear.year_total > 0 THEN t_w_secyear.year_total / t_w_firstyear.year_total ELSE NULL END
ORDER BY t_s_secyear.customer_id
         ,t_s_secyear.customer_first_name
         ,t_s_secyear.customer_last_name
         ,t_s_secyear.customer_birth_country
OPTION ( LABEL = 'Query04-af359846-253-3');

检查查询的估计执行计划。有 18 个洗牌操作和 17 个联接操作，这导致需要更多时间来执行。现在，让我们为三个子 SELECT 语句中的每个语句创建一个具体化视图。

CREATE materialized view nbViewSS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt+ss_ext_sales_price, 0)/2) year_total
          , count_big(*) AS cb
FROM dbo.customer
     ,dbo.store_sales
     ,dbo.date_dim
WHERE c_customer_sk = ss_customer_sk
   AND ss_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
GO
CREATE materialized view nbViewCS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt+cs_ext_sales_price, 0)/2) year_total
          , count_big(*) as cb
FROM dbo.customer
     ,dbo.catalog_sales
     ,dbo.date_dim
WHERE c_customer_sk = cs_bill_customer_sk
   AND cs_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year

GO
CREATE materialized view nbViewWS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt+ws_ext_sales_price, 0)/2) year_total
          , count_big(*) AS cb
FROM dbo.customer
     ,dbo.web_sales
     ,dbo.date_dim
WHERE c_customer_sk = ws_bill_customer_sk
   AND ws_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year

再次检查原始查询的执行计划。现在，联接数从 17 个更改为 5 个，并且没有随机排布。在计划中选择“筛选操作图标”，其输出列表显示数据是从物化视图读取的，而不是从基础表读取的。

Plan_Output_List_with_Materialized_Views

使用具体化视图时，相同的查询运行速度更快，无需更改代码。

后续步骤

有关更多开发提示，请参阅专用 SQL 池开发概述。

通过