Performance tune with materialized views

2025-04-12

Zmaterializowane widoki dla dedykowanych pul SQL w usłudze Azure Synapse zapewniają niską metodę konserwacji dla złożonych zapytań analitycznych w celu uzyskania szybkiej wydajności bez żadnych zmian zapytań. W tym artykule omówiono ogólne wskazówki dotyczące używania zmaterializowanych widoków.

Zmaterializowane widoki a widoki standardowe

Dedykowana pula SQL w usłudze Azure Synapse obsługuje standardowe i zmaterializowane widoki. Oba są tabelami wirtualnymi utworzonymi przy użyciu wyrażeń SELECT i przedstawianymi zapytaniami jako tabelami logicznymi. Widoki hermetyzują 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. Osoby zazwyczaj używają standardowych widoków jako narzędzia, które ułatwia organizowanie obiektów logicznych i zapytań w dedykowanej puli SQL. Aby użyć widoku standardowego, zapytanie musi odwoływać się bezpośrednio do niego.

Zmaterializowany widok wstępnie oblicza, przechowuje i utrzymuje swoje dane w dedykowanej puli SQL, jak tabela. Za każdym razem, gdy jest używany zmaterializowany widok, nie jest wymagana ponowna kompilacja. Dlatego zapytania korzystające ze wszystkich lub podzestawu danych w zmaterializowanych widokach mogą uzyskać szybszą 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

Porównanie	View	Zmaterializowany widok
Definicja widoku	Przechowywane w dedykowanej puli SQL.	Przechowywane w dedykowanej puli SQL.
Wyświetlanie zawartości	Generowane za każdym razem, gdy widok jest używany.	Pre-processed and stored in dedicated SQL pool 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

Korzyści wynikające z używania zmaterializowanych widoków

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

Zmniejsz czas wykonywania złożonych zapytań z wykorzystaniem łączeń 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 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.
Require low maintenance on the views. Wszystkie przyrostowe zmiany danych z tabel bazowych są automatycznie dodawane do zmaterializowanych widoków w sposób synchroniczny, co oznacza, że zarówno tabele podstawowe, jak i zmaterializowane widoki są aktualizowane w tej samej transakcji. 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.

Zmaterializowane widoki zaimplementowane w dedykowanej puli SQL zapewniają również następujące korzyści:

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

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).
Automatyczne i synchroniczne odświeżanie danych ze zmianami danych w tabelach podstawowych. Nie jest wymagana żadna akcja ze strony użytkownika.

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

Złożone zapytania analityczne zwykle używają więcej funkcji agregujących i łączenia tabel, powodując większe obciążenie obliczeniowe, takie jak przemieszczanie i łączenia w wykonywaniu zapytań. Dlatego wykonywanie złożonych zapytań analitycznych trwa dłużej, zwłaszcza w przypadku dużych tabel.

Użytkownicy mogą tworzyć zmaterializowane widoki dla danych zwracanych na podstawie typowych obliczeń zapytań, więc nie ma potrzeby ponownej kompilacji, gdy te dane są potrzebne przez zapytania, co pozwala na niższe koszty obliczeniowe i szybsze reagowanie na zapytania.

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

Zmiany schematu i zapytań w dedykowanych pulach SQL 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 skalowanie i zarządzanie statystykami, stworzenie i utrzymanie materializowanego widoku jest mniej inwazyjną zmianą w środowisku produkcyjnym, a jego potencjalny wzrost wydajności jest również wyższy.

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ń

Dedykowana pula SQL to rozproszony system przetwarzania zapytań. Dane w tabeli SQL są dystrybuowane do 60 węzłów przy użyciu jednej z trzech strategii dystrybucji (haszowanie, round_robin lub replikacja).

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 oraz round_robin. Użytkownicy mogą wybrać dystrybucję danych, która różni się od tabel bazowych, ale optymalna dla wydajności zapytań korzystających 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. Gdy dane zmieniają się w tabelach podstawowych, rozmiar zmaterializowanego widoku wzrasta, a jego struktura fizyczna również się zmienia. Aby uniknąć obniżenia wydajności zapytań, każdy widok materializowany jest utrzymywany oddzielnie przez silnik SQL.

Obciążenie konserwacji jest wyższe, gdy liczba zmaterializowanych widoków i zmian w tabeli bazowej wzrośnie. Użytkownicy powinni sprawdzić, czy koszty poniesione ze wszystkich zmaterializowanych widoków mogą zostać zrównoważone przez wzrost wydajności zapytań.

To zapytanie można uruchomić, aby wygenerować listę zmaterializowanych widoków w dedykowanej puli 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;

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 zapytań SQL może automatycznie używać wdrożonych zmaterializowanych widoków w celu zwiększenia wydajności zapytań. This support is applied transparently to queries that don't reference the views and queries that use aggregates unsupported in materialized views creation. Nie jest wymagana żadna zmiana zapytania. Możesz zweryfikować przybliżony plan wykonania zapytania i potwierdzić, czy jest używany zmaterializowany widok.

Monitoruj widoki zmaterializowane

Zmaterializowany widok jest przechowywany w dedykowanej puli SQL, podobnie jak tabela z klastrowanym indeksem magazynu kolumn (CCI). Odczytywanie danych z zmaterializowanego widoku obejmuje skanowanie segmentów indeksu CCI i stosowanie wszelkich zmian przyrostowych z tabel bazowych. Jeśli liczba zmian przyrostowych jest zbyt wysoka, rozwiązywanie zapytania z zmaterializowanego widoku może trwać dłużej niż bezpośrednie wykonywanie zapytań względem tabel podstawowych.

Aby uniknąć obniżenia wydajności zapytań, dobrą praktyką jest uruchomienie DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD, by monitorować współczynnik nadmiarowości widoku (total_rows / max(1, base_view_row)). Users should REBUILD the materialized view if its overhead_ratio is too high.

Zmaterializowany widok i buforowanie zestawu wyników

Te dwie funkcje w dedykowanej puli SQL są używane do dostrajania wydajności zapytań. Buforowanie zestawu wyników służy do uzyskiwania wysokiej współbieżności i szybkiej 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 wynik buforowany musi mieć zastosowanie 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, identyfikują 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. Wybierz ikonę operacji filtrowania w planie. Lista danych wyjściowych pokazuje, że dane są odczytywane z zmaterializowanych widoków zamiast z tabel podstawowych.

Plan_Output_List_with_Materialized_Views

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

Następne kroki

Aby uzyskać więcej porad dotyczących programowania, zobacz Omówienie tworzenia dedykowanej puli SQL.