Dostrajanie wydajności za pomocą zmaterializowanych widoków

Artykuł
06/01/2023

Zmaterializowane widoki dla dedykowanych pul SQL w 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 Azure Synapse obsługuje standardowe i zmaterializowane widoki. Obie są tabelami wirtualnymi utworzonymi za pomocą 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 zwykle używać widoków standardowych 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 tak samo jak tabela. Za każdym razem, gdy zmaterializowany widok jest używany, 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ływanie 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	Widok	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.	Wstępnie przetworzone i przechowywane w dedykowanej puli SQL podczas tworzenia widoku. Zaktualizowano dane w miarę dodawania danych do bazowych tabel.
Odświeżanie danych	Zawsze aktualizowane	Zawsze aktualizowane
Szybkość pobierania danych widoku ze złożonych zapytań	Mała	Duża
Dodatkowy magazyn	Nie	Tak
Składnia	CREATE VIEW	UTWÓRZ ZMATERIALIZOWANY WIDOK JAKO WYBIERZ

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

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

Skrócenie czasu wykonywania złożonych zapytań przy użyciu numerów JOIN i funkcji agregujących. Im bardziej złożone zapytanie, tym większa jest możliwość oszczędzania czasu wykonywania. Największą korzyścią jest korzyść, 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 niewidoczny dla użytkowników, zapewniając szybszą wydajność zapytań i nie wymaga od zapytań bezpośredniego odwoływanie się do zmaterializowanych widoków.
Wymagaj niskiej konserwacji widoków. Wszystkie zmiany danych przyrostowych z tabel podstawowych 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 wykonywanie zapytań dotyczących zmaterializowanych widoków w celu zwrócenia tych samych danych co bezpośrednie wykonywanie zapytań względem tabel podstawowych.
Dane w zmaterializowanym widoku mogą być dystrybuowane inaczej niż w tabelach podstawowych.
Dane w zmaterializowanych widokach mają takie same korzyści z wysokiej dostępności i odporności, jak 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. Zobacz CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL) (CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL) (TWORZENIE ZMATERIALIZOWANEGO WIDOKU JAKO SELECT (Transact-SQL).
Obsługa rekomendacji widoku zmaterializowanego specyficznego dla zapytań. Zobacz EXPLAIN (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ększej liczby funkcji agregujących i sprzężeń tabel, powodując większe obciążenie obliczeniowe operacje, takie jak tasowanie i sprzężenia w wykonywaniu zapytań. Dlatego wykonywanie złożonych zapytań analitycznych 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 ma potrzeby ponownego obliczania, gdy te dane są potrzebne przez zapytania, co pozwala na niższe koszty obliczeniowe i szybsze reagowanie na zapytania.

Potrzebna jest szybsza wydajność bez zmian zapytań lub minimalnych

Zmiany schematu i zapytań w dedykowanych pulach SQL są zwykle przechowywane do minimum, aby obsługiwać regularne operacje ETL i raportowanie. Osoby mogą używać zmaterializowanych widoków do dostrajania wydajności zapytań, jeśli koszt ponoszony przez widoki może zostać przesunięty przez wzrost wydajności zapytań.

W porównaniu z innymi opcjami dostrajania, takimi jak zarządzanie skalowaniem i statystykami, jest to mniej wpływowa zmiana produkcji w celu utworzenia i utrzymania zmaterializowanego widoku, a jego potencjalny wzrost wydajności jest również wyższy.

Tworzenie lub utrzymywanie 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 zwiększenia wydajności zapytań

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

Rozkład danych jest określony w czasie tworzenia tabeli i pozostaje niezmieniony, dopóki tabela nie zostanie porzucona. Zmaterializowany widok, będący tabelą wirtualną na dysku, obsługuje dystrybucje skrótów i round_robin danych. Użytkownicy mogą wybrać dystrybucję danych, która różni się od tabel podstawowych, ale optymalna dla wydajności zapytań korzystających z widoków.

Wskazówki dotyczące projektowania

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 dokładne zrozumienie obciążenia pod względem wzorców zapytań, ważności, częstotliwości i rozmiaru danych wynikowych.

Użytkownicy mogą uruchamiać dla EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> zmaterializowanych widoków zalecanych przez optymalizator zapytań. Ponieważ te zalecenia są specyficzne dla zapytań, zmaterializowany widok, który korzysta z pojedynczego zapytania, może nie być optymalny dla innych zapytań w tym samym obciążeniu.

Oceń te zalecenia z uwzględnieniem potrzeb związanych z obciążeniem. Idealne zmaterializowane widoki to te, które korzystają z wydajności obciążenia.

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

W przypadku każdego zmaterializowanego widoku istnieje koszt przechowywania danych i koszt utrzymania widoku. Wraz ze zmianą danych w tabelach podstawowych rozmiar zmaterializowanego widoku zwiększa się, a jego struktura fizyczna również ulega zmianie. Aby uniknąć obniżenia wydajności zapytań, każdy zmaterializowany widok jest utrzymywany oddzielnie przez aparat SQL.

Obciążenie konserwacji zwiększa się, gdy liczba zmaterializowanych widoków i zmienia się tabela podstawowa. Użytkownicy powinni sprawdzić, czy koszt ponoszony ze wszystkich zmaterializowanych widoków może zostać przesunięty 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;

Opcje zmniejszenia liczby zmaterializowanych widoków:

Identyfikowanie typowych zestawów danych często używanych przez złożone zapytania w obciążeniu. Utwórz zmaterializowane widoki do przechowywania tych zestawów danych, aby optymalizator mógł używać ich jako bloków konstrukcyjnych podczas tworzenia planów wykonania.
Upuść zmaterializowane widoki, które mają niskie użycie lub nie są już potrzebne. Wyłączony zmaterializowany widok nie jest utrzymywany, ale nadal wiąże się z kosztami magazynowania.
Połącz zmaterializowane widoki utworzone na 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. 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 mateiralized 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ń. Ta obsługa jest stosowana w sposób niewidoczny dla zapytań, które nie odwołują się do widoków i zapytań korzystających z agregacji nieobsługiwanych w materializowanym tworzeniu widoków. Nie jest wymagana żadna zmiana zapytania. Możesz sprawdzić szacowany plan wykonywania zapytania, aby sprawdzić, czy jest używany zmaterializowany widok.

Monitorowanie zmaterializowanych widoków

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 duża, rozpoznawanie 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ń, dobrym rozwiązaniem jest uruchomienie PDW_SHOWMATERIALIZEDVIEWOVERHEAD DBCC w celu monitorowania overhead_ratio widoku (total_rows /max(1, base_view_row)). Użytkownicy powinni ponownie skompilować zmaterializowany widok, jeśli jego overhead_ratio jest zbyt wysoka.

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ń względem danych statycznych.

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

Zmaterializowane widoki umożliwiają zmiany danych w tabelach bazowych. 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 uzyskania szybszej 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. Zapytanie obejmuje wybranie pierwszych 100 rekordów z unii trzech instrukcji sub-SELECT z udziałem SUM() i 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. Istnieje 18 operacji przetasowania i 17 sprzężeń, co zajmuje więcej czasu na wykonanie. 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. Teraz liczba sprzężeń zmienia się z 17 na 5 i nie ma mieszania. Wybierz ikonę Operacji filtru w planie. Lista danych wyjściowych pokazuje, że dane są odczytywane z zmaterializowanych widoków zamiast 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 wskazówek dotyczących programowania, zobacz Omówienie tworzenia dedykowanej puli SQL.