Notatka
Dostęp do tej strony wymaga autoryzacji. Może spróbować zalogować się lub zmienić katalogi.
Dostęp do tej strony wymaga autoryzacji. Możesz spróbować zmienić katalogi.
Tip
Microsoft Fabric Data Warehouse to magazyn relacyjny w skali przedsiębiorstwa na podstawie bazy danych data lake z architekturą gotową do użycia w przyszłości, wbudowaną sztuczną inteligencją i nowymi funkcjami. Jeśli dopiero zaczynasz korzystać z magazynowania danych, zacznij od Fabric Data Warehouse. Istniejące obciążenia dedykowanej puli SQL mogą zostać zaktualizowane do Fabric, aby uzyskać dostęp do nowych możliwości w zakresie nauki o danych, analizy w czasie rzeczywistym i raportowania.
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. | Wstępne przetwarzanie i przechowywanie odbywa się w magazynie danych platformy Azure podczas tworzenia widoku. 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 | UTWÓRZ WIDOK | UTWÓRZ ZMATERIALIZOWANY WIDOK PODCZAS WYBIERANIA |
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.
Wymaga niskiej konserwacji widoków. Zmaterializowany widok przechowuje dane w dwóch miejscach: indeks kolumnowy z klastrowaniem dla początkowych danych w momencie tworzenia widoku oraz magazyn delta dla przyrostowych zmian danych. Wszystkie zmiany danych z tabel bazowych są automatycznie dodawane do delta magazynu i synchronizowane. Proces w tle (przenośnik krotek) okresowo przenosi dane z magazynu różnicowego do indeksu kolumnowego widoku. 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. Zobacz CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL).
- Obsługa rekomendacji dotyczącej zmaterializowanego widoku specyficznego dla zapytania. 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
Złożone zapytania analityczne zazwyczaj używają większej liczby funkcji agregujących i łączeń tabel, co prowadzi do bardziej obciążających operacji obliczeniowych, takich jak przemieszczanie i łączenia podczas wykonywania zapytań. 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. Dane w tabeli magazynu danych są dystrybuowane między 60 węzłami 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 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.
Użytkownicy mogą uruchamiać EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> dla zmaterializowanych widoków zalecanych przez optymalizator zapytań. 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. Idealne zmaterializowane widoki to te, które poprawiają wydajność obciążenia.
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.
Aby uniknąć obniżenia wydajności zapytań, każdy zmaterializowany widok jest utrzymywany oddzielnie przez aparat magazynu danych, obejmując przenoszenie wierszy z magazynu różnicowego do segmentów indeksu magazynu kolumnowego i konsolidowanie zmian danych.
Obciążenie konserwacji zwiększa się, gdy rośnie liczba zmaterializowanych widoków i następują zmiany w tabeli bazowej. Użytkownicy powinni sprawdzić, czy koszty poniesione ze wszystkich zmaterializowanych widoków mogą zostać zrównoważone przez wzrost wydajności zapytań.
Możesz uruchomić to zapytanie, aby uzyskać listę zmaterializowanych widoków w bazie danych.
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:
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. Zmaterializowany widok, który jest wyłączony, nie jest utrzymywany, ale nadal wiąże się z kosztem przechowywania danych.
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). Odczytywanie danych z zmaterializowanego widoku obejmuje przeszukiwanie indeksu i zastosowanie zmian z magazynu różnic (delta store). Jeśli liczba wierszy w magazynie delty jest zbyt duża, przetwarzanie zapytania z widoku materializowanego może trwać dłużej niż bezpośrednie zapytania do tabel podstawowych.
Aby uniknąć obniżenia wydajności zapytań, dobrą praktyką jest uruchomienie DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD w celu monitorowania wskaźnika overhead_ratio widoku (total_rows / base_view_row). Jeśli overhead_ratio jest zbyt wysoki, rozważ przebudowanie zmaterializowanego widoku, aby wszystkie wiersze w delta store zostały przeniesione do indeksu kolumnowego.
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. Zapytanie polega na wybraniu TOP 100 rekordów z UNION trzech instrukcji sub-SELECT z wykorzystaniem 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 mieszania i 17 łączeń, które wymagają 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 łączeń zmienia się z 17 do 5 i nie ma już żadnego przetasowania. Wybierz ikonę Operacji filtrowania w planie. Jego lista danych wyjściowych pokazuje, że dane są odczytywane z zmaterializowanych widoków zamiast tabel bazowych.
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.