Dostrajanie wydajności za pomocą zmaterializowanych widoków przy użyciu dedykowanej puli SQL w usłudze Azure Synapse Analytics

2025-04-12

W dedykowanej puli SQL materializowane widoki zapewniają łatwą w utrzymaniu metodę dla złożonych zapytań analitycznych, aby osiągnąć wysoką wydajność bez konieczności zmiany zapytania. W tym artykule omówiono ogólne wskazówki dotyczące używania zmaterializowanych widoków.

Zmaterializowane widoki a widoki standardowe

Pula SQL obsługuje zarówno standardowe, jak i zmaterializowane widoki. Oba są tabelami wirtualnymi utworzonymi przy użyciu wyrażeń SELECT i przedstawianymi zapytaniami jako tabelami logicznymi. Widoki ujawniają złożoność typowych obliczeń danych i dodają warstwę abstrakcji do zmian obliczeniowych, dzięki czemu nie trzeba ponownie pisać zapytań.

Widok standardowy oblicza swoje dane za każdym razem, gdy widok jest używany. Na dysku nie są przechowywane żadne dane. Ludzie zazwyczaj używają standardowych widoków jako narzędzia, które ułatwia organizowanie obiektów logicznych i zapytań w bazie danych. Aby użyć widoku standardowego, zapytanie musi odwoływać się bezpośrednio do niego.

Zmaterializowany widok wstępnie oblicza, przechowuje i utrzymuje te dane w dedykowanej puli SQL, podobnie jak tabela. Ponowne obliczanie nie jest potrzebne za każdym razem, gdy jest używany zmaterializowany widok. Dlatego zapytania korzystające ze wszystkich lub podzestawu danych w zmaterializowanych widokach mogą zwiększyć wydajność. Jeszcze lepiej, zapytania mogą używać zmaterializowanego widoku bez bezpośredniego odwołowania się do niego, więc nie trzeba zmieniać kodu aplikacji.

Większość wymagań dotyczących widoku standardowego nadal ma zastosowanie do zmaterializowanego widoku. Aby uzyskać szczegółowe informacje na temat zmaterializowanej składni widoku i innych wymagań, zobacz CREATE MATERIALIZED VIEW AS SELECT (TWORZENIE ZMATERIALIZOWANEGO WIDOKU JAKO SELECT).

Porównanie	View	Zmaterializowany widok
Definicja widoku	Przechowywane w magazynie danych platformy Azure.	Przechowywane w magazynie danych platformy Azure.
Wyświetlanie zawartości	Generowane za każdym razem, gdy widok jest używany.	Pre-processed and stored in Azure data warehouse during view creation. Zaktualizowano dane w miarę dodawania danych do tabel bazowych.
Odświeżanie danych	Zawsze aktualizowane	Zawsze aktualizowane
Szybkość pobierania danych widoku z złożonych zapytań	Slow	Szybko
Dodatkowy magazyn	Nie.	Tak
Syntax	CREATE VIEW	CREATE MATERIALIZED VIEW AS SELECT

Zalety zmaterializowanych widoków

Prawidłowo zaprojektowany zmaterializowany widok zapewnia następujące korzyści:

Skrócone czasy wykonywania złożonych zapytań z użyciem JOIN-ów i funkcji agregujących. Im bardziej złożone zapytanie, tym większa jest możliwość oszczędzania czasu wykonywania. Największą korzyść przynosi sytuacja, gdy koszt obliczeń zapytania jest wysoki, a wynikowy zestaw danych jest niewielki.
Optymalizator zapytań w dedykowanej puli SQL może automatycznie używać wdrożonych zmaterializowanych widoków w celu ulepszenia planów wykonywania zapytań. Ten proces jest przejrzysty dla użytkowników, zapewnia szybszą wydajność zapytań i nie wymaga, aby zapytania bezpośrednio odnosiły się do zmaterializowanych widoków.
Requires low maintenance on the views. A materialized view stores data in two places, a clustered columnstore index for the initial data at the view creation time, and a delta store for the incremental data changes. All data changes from the base tables are automatically added to the delta store in a synchronous manner. A background process (tuple mover) periodically moves the data from the delta store to the view's columnstore index. Ten projekt umożliwia zapytania zmaterializowanych widoków, które zwracają te same dane, co zapytania bezpośrednio do tabel podstawowych.
Dane w zmaterializowanym widoku mogą być dystrybuowane inaczej niż w tabelach podstawowych.
Dane w zmaterializowanych widokach zyskują te same korzyści wysokiej dostępności i odporności co dane w zwykłych tabelach.

W porównaniu z innymi dostawcami magazynu danych zmaterializowane widoki zaimplementowane w dedykowanej puli SQL zapewniają również następujące dodatkowe korzyści:

Automatyczne i synchroniczne odświeżanie danych ze zmianami danych w tabelach podstawowych. Nie jest wymagana żadna akcja ze strony użytkownika.
Obsługa szerokiej funkcji agregującej. See CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL).
The support for query-specific materialized view recommendation. Zobacz WYJAŚNIENIE (Transact-SQL).

Typowe scenariusze

Zmaterializowane widoki są zwykle używane w następujących scenariuszach:

Konieczność zwiększenia wydajności złożonych zapytań analitycznych dotyczących dużych ilości danych

Complex analytical queries typically use more aggregation functions and table joins, causing more compute-heavy operations such as shuffles and joins in query execution. Dlatego wykonywanie tych zapytań trwa dłużej, szczególnie w przypadku dużych tabel.

Użytkownicy mogą tworzyć zmaterializowane widoki dla danych zwracanych na podstawie typowych obliczeń zapytań, więc nie jest wymagana ponowna kompilacja, gdy te dane są potrzebne przez zapytania, co pozwala na obniżenie kosztów obliczeniowych i szybszej odpowiedzi na zapytania.

Potrzebna jest szybsza wydajność bez żadnych lub z minimalnymi zmianami w zapytaniach

Zmiany schematu i zapytań w magazynach danych są zwykle utrzymywane do minimum, aby obsługiwać regularne operacje ETL i raportowanie. Osoby mogą używać zmaterializowanych widoków do dostrajania wydajności zapytań, jeśli koszty poniesione przez widoki mogą zostać zrównoważone przez wzrost wydajności zapytań.

W porównaniu z innymi opcjami dostrajania, takimi jak zarządzanie skalowaniem i statystykami, utworzenie i utrzymanie widoku zmaterializowanego jest zmianą, która ma znacznie mniejszy wpływ na produkcję, a zapewnia również większy potencjalny wzrost wydajności.

Tworzenie lub obsługa zmaterializowanych widoków nie ma wpływu na zapytania uruchomione względem tabel bazowych.
Optymalizator zapytań może automatycznie używać wdrożonych zmaterializowanych widoków bez bezpośredniego odwołania do widoku w zapytaniu. Ta funkcja zmniejsza potrzebę zmiany zapytań w dostrajaniu wydajności.

Potrzebna jest inna strategia dystrybucji danych w celu zapewnienia szybszej wydajności zapytań

Azure Data Warehouse to rozproszony i masowo równoległy system przetwarzania (MPP).

Synapse SQL to rozproszony system zapytań, który umożliwia przedsiębiorstwom implementowanie scenariuszy magazynowania danych i wirtualizacji danych przy użyciu standardowych środowisk języka T-SQL znanych inżynierom danych. Rozszerza również możliwości języka SQL, aby rozwiązać problem ze scenariuszami przesyłania strumieniowego i uczenia maszynowego. Data in a data warehouse table is distributed across 60 nodes using one of three distribution strategies (hash, round_robin, or replicated).

Rozkład danych jest określony w czasie tworzenia tabeli i pozostaje niezmieniony do momentu porzucenia tabeli. Zmaterializowany widok, będący wirtualną tabelą na dysku, obsługuje dystrybucje danych typu hash i round_robin. Użytkownicy mogą wybrać dystrybucję danych, która różni się od tabel bazowych, ale optymalna dla wydajności zapytań, które często korzystają z widoków.

Wskazówki projektowe

Poniżej przedstawiono ogólne wskazówki dotyczące używania zmaterializowanych widoków w celu zwiększenia wydajności zapytań:

Projektowanie pod kątem obciążenia

Przed rozpoczęciem tworzenia zmaterializowanych widoków ważne jest, aby zapoznać się z obciążeniem pod względem wzorców zapytań, ważności, częstotliwości i rozmiaru danych wynikowych.

Users can run EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> for the materialized views recommended by the query optimizer. Ponieważ te zalecenia są specyficzne dla zapytań, zmaterializowany widok, który przynosi korzyści pojedynczemu zapytaniu, może nie być optymalny dla innych zapytań w tym samym zestawie zapytań.

Oceń te zalecenia, mając na uwadze potrzeby dotyczące obciążenia. The ideal materialized views are those that benefit the workload's performance.

Należy pamiętać o kompromisie między szybszymi zapytaniami a kosztem

Dla każdego zmaterializowanego widoku istnieje koszt przechowywania danych i koszt obsługi widoku. Wraz ze zmianą danych w tabelach podstawowych rozmiar zmaterializowanego widoku zwiększa się, a jego struktura fizyczna również się zmienia.

To avoid query performance degradation, each materialized view is maintained separately by the data warehouse engine, including moving rows from delta store to the columnstore index segments and consolidating data changes.

The maintenance workload climbs higher when the number of materialized views and base table changes increase. Użytkownicy powinni sprawdzić, czy koszty poniesione ze wszystkich zmaterializowanych widoków mogą zostać zrównoważone przez wzrost wydajności zapytań.

You can run this query for the list of materialized view in a database:

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;

Options to reduce the number of materialized views:

Zidentyfikuj typowe zestawy danych często używane przez złożone zapytania w twoim obciążeniu. Utwórz zmaterializowane widoki do przechowywania tych zestawów danych, aby optymalizator mógł ich używać jako bloków konstrukcyjnych podczas tworzenia planów wykonywania.
Usuń zmaterializowane widoki, które mają niskie użycie lub nie są już potrzebne. A disabled materialized view is not maintained but it still incurs storage cost.
Połącz zmaterializowane widoki utworzone w tych samych lub podobnych tabelach podstawowych, nawet jeśli ich dane nie nakładają się na siebie. Połączenie zmaterializowanych widoków może spowodować większy rozmiar widoku niż suma oddzielnych widoków, jednak koszt konserwacji widoku powinien zostać zmniejszony. Na przykład:

-- 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

Nie wszystkie dostrajanie wydajności wymaga zmiany zapytania

Optymalizator magazynu danych może automatycznie używać wdrożonych zmaterializowanych widoków w celu zwiększenia wydajności zapytań. Ta obsługa jest stosowana w sposób przejrzysty do zapytań, które nie odwołują się do widoków, oraz do zapytań korzystających z agregatów, które nie są wspierane podczas tworzenia zmaterializowanych widoków. Nie jest wymagana żadna zmiana zapytania. Możesz zweryfikować przybliżony plan wykonania zapytania i potwierdzić, czy jest używany zmaterializowany widok.

Aby uzyskać więcej informacji na temat pobierania rzeczywistego planu wykonywania, zobacz Monitorowanie obciążenia dedykowanej puli SQL usługi Azure Synapse Analytics przy użyciu widoków DMV.
Szacowany plan wykonania można pobrać za pomocą programu SQL Server Management Studio (SSMS) lub SET SHOWPLAN_XML.

Monitoruj widoki zmaterializowane

Zmaterializowany widok jest przechowywany w magazynie danych tak samo jak tabela z klastrowanym indeksem magazynu kolumn (CCI). Reading data from a materialized view includes scanning the index and applying changes from the delta store. When the number of rows in the delta store is too high, resolving a query from a materialized view can take longer than directly querying the base tables.

To avoid query performance degradation, it's a good practice to run DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD to monitor the view's overhead_ratio (total_rows / base_view_row). If the overhead_ratio is too high, consider rebuilding the materialized view so all rows in the delta store are moved to the columnstore index.

Zmaterializowany widok i buforowanie zestawu wyników

Te dwie funkcje są wprowadzane w dedykowanej puli SQL w tym samym czasie na potrzeby dostrajania wydajności zapytań. Buforowanie zestawu wyników służy do osiągnięcia wysokiej współbieżności i szybkiego czasu odpowiedzi z powtarzających się zapytań dotyczących danych statycznych.

Aby użyć buforowanego wyniku, formularz zapytania żądającego pamięci podręcznej musi być zgodny z zapytaniem, które wygenerowało pamięć podręczną. Ponadto buforowany wynik musi być stosowany do całego zapytania.

Zmaterializowane widoki umożliwiają zmiany danych w tabelach podstawowych. Dane w zmaterializowanych widokach można zastosować do fragmentu zapytania. Ta obsługa umożliwia korzystanie z tych samych zmaterializowanych widoków przez różne zapytania, które współdzielą niektóre obliczenia w celu zwiększenia wydajności.

Przykład

W tym przykładzie użyto zapytania przypominającego TPCDS, które znajduje klientów, którzy wydają więcej pieniędzy za pośrednictwem katalogu niż w sklepach. Identyfikuje również preferowanych klientów i ich kraj/region pochodzenia. The query involves selecting TOP 100 records from the UNION of three sub-SELECT statements involving SUM() and 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');

Sprawdź szacowany plan wykonania zapytania. There are 18 shuffles and 17 joins operations, which take more time to execute.

Teraz utwórzmy jeden zmaterializowany widok dla każdej z trzech instrukcji sub-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

Sprawdź ponownie plan wykonania oryginalnego zapytania. Now the number of joins changes from 17 to 5 and there's no shuffle anymore. Wybierz ikonę Operacji filtrowania w planie. Jego lista danych wyjściowych pokazuje, że dane są odczytywane z zmaterializowanych widoków zamiast tabel bazowych.

Plan_Output_List_with_Materialized_Views

W przypadku zmaterializowanych widoków to samo zapytanie działa znacznie szybciej bez żadnej zmiany kodu.

Następne kroki

Aby uzyskać więcej porad dotyczących programowania, zobacz Omówienie programowania w usłudze Synapse SQL.