使用具體化視圖進行效能微調

Azure Synapse 中專用 SQL 池的實體化檢視，提供了一種低維護的方法，讓複雜分析查詢能快速達成效能，且無需更改查詢。本文討論使用具體化視圖的一般指引。

實體化視圖與標準視圖

Azure Synapse 的專用 SQL 池支援標準與實體化檢視。兩者皆為使用 SELECT 表達式建立的虛擬資料表，並以邏輯資料表形式呈現給查詢。檢視封裝了常見資料計算的複雜性，並為計算變更增加了抽象層，因此不需要重寫查詢。

標準視圖每次使用時都會計算其資料。磁碟上沒有儲存任何資料。人們通常會使用標準視圖作為工具，幫助組織邏輯物件和查詢，建立專用的 SQL 池。要使用標準視圖，查詢必須直接參照該視圖。

實體化檢視會預先計算、儲存並維護資料在專用的 SQL 池中，就像資料表一樣。每次使用實體化視圖時，都不需要重新計算。這也是為什麼使用全部或部分資料在實體化檢視中的查詢能獲得更快的效能。更棒的是，查詢可以使用實體化的視圖，而不必直接參考它，因此不需要更改應用程式代碼。

標準視圖的大多數要求仍適用於具體化視圖。關於具體化視圖語法及其他要求，請參考「以選擇方式建立具體化視圖」

比較	檢視	具體化檢視
檢視定義	儲存在專用的 SQL 池中。	儲存在專用的 SQL 池中。
查看內容	每次使用視圖時都會產生。	在建立檢視時，會預先處理並儲存在專用的 SQL 池中。隨著資料新增到基底資料表而更新。
資料重新整理	持續更新	持續更新
從複雜查詢中擷取檢視資料的速度	緩慢	快速
額外儲存空間	No	是的
語法	建立視圖	建立實體化檢視 AS SELECT

使用具體化視圖的好處

妥善設計的具體化觀點能帶來以下好處：

透過 JOIN 與彙總函式，減少複雜查詢的執行時間。查詢越複雜，執行時間節省的可能性越大。對於計算成本高但所產生之資料集很小的查詢，就能獲得最大效益。
專用 SQL 池中的優化器能自動利用已部署的實體化視圖來改善查詢執行計畫。對於使用者而言，此流程是透明的，因此能加速查詢效能，而且不需要查詢就能直接參考具體化視圖。
視圖的維護需求低。所有來自基底資料表的增量資料變更都會以同步方式自動加入實體化檢視，意即基礎資料表與實體化檢視在同一筆交易中同時更新。此設計允許查詢物質化檢視時，回傳與直接查詢基底資料表相同的資料。
具體化檢視中的資料可以與基底資料表不同地分配。
實體化檢視中的資料享有與一般資料表資料相同的高可用性與韌性優勢。

專用 SQL 池中實作的實體化檢視也帶來以下好處：

與其他資料倉儲提供者相比，專用 SQL 池中實作的實體化檢視還提供以下優點：

廣泛的綜合功能支援。請參見 CREATE MATERIALIZED VIEW AS SELECT（Transact-SQL）。
支援查詢特定的具體化視圖建議。請參見「解釋」（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 查詢優化器能自動利用已部署的實體化視圖來提升查詢效能。這種支援透明套用於不參考視圖的查詢，以及使用具體化視圖建立時不支援之彙總的查詢。不需要更改查詢。你可以查看查詢的預估執行計畫，以確認是否使用了具體化的檢視。

監控具體化視圖

在專用的 SQL 池中，具現化檢視會像具有叢集欄位儲存索引（CCI）的資料表一樣被儲存。從實體化視圖讀取資料包括掃描 CCI 索引區段，並從基底表套用任何增量變更。當增量變更數量過多時，從具體化檢視解決查詢可能比直接查詢基底資料表花費更長時間。

為了避免查詢效能下降，建議執行 DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD 以監控視圖的 overhead_ratio (total_rows / max(1, base_view_row))。如果系統負荷率過高，使用者應該重新建置具象化檢視。

具現化視圖與結果集快取

這兩個專用 SQL 池的功能用於查詢效能調整。結果集快取用於獲得高並發性及對靜態資料重複查詢的快速回應。

要使用快取結果，請求快取查詢的形式必須與產生快取的查詢相符。此外，快取結果必須適用於整個查詢。

實體化檢視允許在基底資料表中進行資料變更。實體化檢視中的資料可以套用到查詢的某個部分。此支援允許不同查詢共用部分運算，使用相同的具體視圖以提升效能。

範例

這個範例使用類似 TPCDS 的查詢，找出那些在目錄中花費比在實體店花費更多的顧客，並識別偏好的顧客及其國家/地區。查詢涉及從三個子 SELECT 語句的 UNION 中選擇 TOP 100 紀錄，這些語句包含 SUM（）和 GROUP BY。

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，且沒有隨機演算。在計畫中選擇篩選操作圖示，其輸出清單會顯示資料是從實體化的視圖讀取，而非基底資料表。

使用具現化檢視的計劃輸出清單

有了實體化檢視，同一查詢在不改程式碼的情況下執行得更快。

下一步

更多開發技巧，請參閱專用 SQL 池開發概述。

意見反應

此頁面對您有幫助嗎？

Last updated on 2026-03-30