Optimalizace výkonu pomocí materializovaných zobrazení

Materializovaná zobrazení pro vyhrazené fondy SQL ve službě Azure Synapse poskytují metodu nízké údržby pro složité analytické dotazy, aby se zajistil rychlý výkon beze změny dotazů. Tento článek popisuje obecné pokyny k používání materializovaných zobrazení.

Materializovaná zobrazení vs. standardní zobrazení

Vyhrazený fond SQL ve službě Azure Synapse podporuje standardní a materializovaná zobrazení. Obě jsou virtuální tabulky vytvořené pomocí výrazů SELECT a předávají se dotazům jako logické tabulky. Zobrazení zapouzdřují složitost běžného výpočtu dat a přidávají do změn výpočtů abstrakční vrstvu, takže není nutné přepisovat dotazy.

Standardní zobrazení vypočítá data při každém použití zobrazení. Na disku nejsou uložená žádná data. Lidé obvykle používají standardní zobrazení jako nástroj, který pomáhá uspořádat logické objekty a dotazy ve vyhrazeném fondu SQL. Pokud chcete použít standardní zobrazení, musí dotaz na něj přímo odkazovat.

Materializované zobrazení předem vypočítá, ukládá a udržuje svá data ve vyhrazeném fondu SQL stejně jako tabulka. Při každém použití materializovaného zobrazení není potřeba žádné výpočty. Proto dotazy, které používají všechna data nebo podmnožinu dat v materializovaných zobrazeních, můžou dosáhnout rychlejšího výkonu. Ještě lepší je, že dotazy můžou použít materializované zobrazení bez přímého odkazu na něj, takže není nutné měnit kód aplikace.

Většina požadavků na standardní zobrazení se stále vztahuje na materializované zobrazení. Podrobnosti o syntaxi materializovaného zobrazení a dalších požadavcích najdete v tématu CREATE MATERIALIZED VIEW AS SELECT.

Porovnání	Zobrazit	Materializované zobrazení
Zobrazit definici	Uloženo ve vyhrazeném fondu SQL.	Uloženo ve vyhrazeném fondu SQL.
Zobrazení obsahu	Vygeneruje se při každém použití zobrazení.	Předzpracované a uložené ve vyhrazeném fondu SQL během vytváření zobrazení. Aktualizováno při přidávání dat do podkladových tabulek.
Aktualizace dat	Vždy aktualizováno	Vždy aktualizováno
Rychlost načítání dat ze složitých dotazů	Pomalá	Rychlý
Extra úložiště	Ne	Ano
Syntaxe	VYTVOŘIT ZOBRAZENÍ	VYTVOŘENÍ MATERIALIZOVANÉHO ZOBRAZENÍ JAKO VÝBĚRU

Výhody použití materializovaných zobrazení

Správně navržené materializované zobrazení poskytuje následující výhody:

Zkraťte dobu provádění složitých dotazů pomocí joinů a agregačních funkcí. Čím složitější je dotaz, tím vyšší je potenciál pro úsporu času provádění. Největší výhodu získáte, když jsou náklady na výpočet dotazu vysoké a výsledná datová sada je malá.
Optimalizátor ve vyhrazeném fondu SQL může automaticky použít nasazená materializovaná zobrazení ke zlepšení plánů provádění dotazů. Tento proces je pro uživatele transparentní a poskytuje rychlejší výkon dotazů a nevyžaduje dotazy k přímému odkazování na materializovaná zobrazení.
Zobrazení vyžadují minimální údržbu. Všechny přírůstkové změny dat ze základních tabulek se automaticky přidají do materializovaných zobrazení synchronním způsobem, což znamená, že základní tabulky i materializovaná zobrazení se aktualizují ve stejné transakci. Tento návrh umožňuje dotazování materializovaných zobrazení vrátit stejná data jako přímé dotazování základních tabulek.
Data v materializovaném zobrazení je možné distribuovat odlišně od základních tabulek.
Data v materializovaných zobrazeních získávají stejné výhody vysoké dostupnosti a odolnosti jako data v běžných tabulkách.

Materializovaná zobrazení implementovaná ve vyhrazeném fondu SQL také poskytují následující výhody:

Ve srovnání s jinými poskytovateli datového skladu poskytují materializovaná zobrazení implementovaná ve vyhrazeném fondu SQL také následující výhody:

Podpora širokých agregačních funkcí Podívejte se na CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL).
Podpora doporučení materializovaného zobrazení specifického pro dotazy. Viz EXPLAIN (Transact-SQL).
Automatická a synchronní aktualizace dat se změnami dat v základních tabulkách Není nutná žádná akce uživatele.

Obvyklé scénáře

Materializovaná zobrazení se obvykle používají v následujících scénářích:

Potřeba zlepšit výkon složitých analytických dotazů s velkými objemy dat

Složité analytické dotazy obvykle používají více agregačních funkcí a spojení tabulek, což způsobuje více operací náročných na výpočetní výkon, jako jsou náhodné prohazování a spojení při provádění dotazů. To je důvod, proč dokončení složitých analytických dotazů trvá déle, zejména u velkých tabulek.

Uživatelé mohou vytvářet materializovaná zobrazení pro data vrácená z běžných výpočtů dotazů, takže není třeba opětovných výpočtů, když dotazy tato data potřebují, což umožňuje nižší náklady na výpočetní výkon a rychlejší odezvu dotazů.

Potřebujete rychlejší výkon beze změn dotazů nebo minimálních změn dotazů.

Změny schématu a dotazů ve vyhrazených fondech SQL se obvykle omezují na minimum pro podporu pravidelných operací ETL a reportování. Lidé můžou použít materializovaná zobrazení pro ladění výkonu dotazů, pokud náklady vzniklé zobrazením můžou být posunuty zvýšením výkonu dotazů.

Ve srovnání s dalšími možnostmi ladění, jako je škálování a správa statistik, se jedná o méně výraznou změnu produkce vytvořit a udržovat materializované zobrazení, a jeho potenciální zvýšení výkonu je také větší.

Vytváření nebo údržba materializovaných zobrazení nemá vliv na dotazy spuštěné na základní tabulky.
Optimalizátor dotazů může automaticky používat nasazená materializovaná zobrazení bez přímého odkazu na zobrazení v dotazu. Tato funkce snižuje potřebu změny dotazů při ladění výkonu.

K zajištění rychlejšího výkonu dotazů potřebujete jinou strategii distribuce dat.

Vyhrazený fond SQL je distribuovaný systém zpracování dotazů. Data v tabulce SQL je rozdělena až do 60 uzlů pomocí jedné ze tří distribučních strategií (hash, round_robin nebo replikace).

Distribuce dat se zadává při vytváření tabulky a zůstává beze změny, dokud se tabulka neodstraní. Materializované zobrazení, které je virtuální tabulkou uloženou na disku, podporuje distribuce dat pomocí hash a round_robin. Uživatelé můžou zvolit distribuci dat, která se liší od základních tabulek, ale optimální pro výkon dotazů, které používají zobrazení.

Pokyny pro návrh

Tady jsou obecné pokyny k použití materializovaných zobrazení ke zlepšení výkonu dotazů:

Návrh pro úlohu

Než začnete vytvářet materializovaná zobrazení, je důležité, abyste svoji úlohu podrobně pochopili z hlediska vzorů dotazů, důležitosti, frekvence a velikosti výsledných dat.

Uživatelé mohou spouštět EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> pro materializovaná zobrazení doporučená optimalizátorem dotazů. Vzhledem k tomu, že tato doporučení jsou specifická pro dotazy, materializované zobrazení, které má výhody jednoho dotazu, nemusí být optimální pro jiné dotazy ve stejné úloze.

Vyhodnoťte tato doporučení s ohledem na potřeby vašich úloh. Ideální materializovaná zobrazení jsou ta, která přispívají k výkonu úlohy.

Mějte na paměti kompromis mezi rychlejšími dotazy a náklady

U každého materializovaného zobrazení jsou náklady na úložiště dat a náklady na údržbu zobrazení. Při změnách dat v základních tabulkách se velikost materializovaného zobrazení zvyšuje a také se mění její fyzická struktura. Aby se zabránilo snížení výkonu dotazů, udržuje každé materializované zobrazení modul SQL samostatně.

Zátěž údržby se zvýší, když vzroste počet materializovaných zobrazení a změny v základních tabulkách. Uživatelé by měli zkontrolovat, jestli náklady vzniklé ze všech materializovaných zobrazení mohou být vyváženy zlepšením výkonu dotazu.

Tento dotaz můžete spustit a vygenerovat seznam materializovaných zobrazení ve vyhrazeném fondu 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;

Možnosti snížení počtu materializovaných zobrazení:

Identifikujte běžné datové sady, které často používají složité dotazy ve vaší úloze. Vytvořte materializovaná zobrazení pro ukládání těchto sad dat, aby je optimalizátor mohl použít jako stavební bloky při vytváření plánů provádění.
Odstraňte materializovaná zobrazení s nízkým využitím nebo ta, která už nejsou potřebná. Zakázané materializované zobrazení se neudržuje, ale přesto způsobuje náklady na úložiště.
Zkombinujte materializovaná zobrazení vytvořená ve stejných nebo podobných základních tabulkách, i když se jejich data nepřekrývají. Kombinace materializovaných zobrazení může vést k větší velikosti zobrazení, než je součet samostatných zobrazení, ale náklady na údržbu zobrazení by se měly snížit. Například:


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

Ne všechna ladění výkonu vyžadují změnu dotazu.

Optimalizátor dotazů SQL může automaticky použít nasazená materializovaná zobrazení ke zlepšení výkonu dotazů. Tato podpora se používá transparentně u dotazů, které neodkazují na zobrazení a dotazy, které používají agregace nepodporované při vytváření materializovaných zobrazení. Není nutná žádná změna dotazu. Můžete zkontrolovat odhadovaný plán provádění dotazu a ověřit, jestli se používá materializované zobrazení.

Sledujte materializovaná zobrazení

Materializované zobrazení je uložené ve vyhrazeném fondu SQL stejně jako tabulka s clusterovaným indexem columnstore (CCI). Čtení dat z materializovaného zobrazení zahrnuje skenování segmentů indexu CCI a použití jakýchkoli přírůstkových změn ze základních tabulek. Pokud je počet přírůstkových změn příliš vysoký, překlad dotazu z materializovaného zobrazení může trvat déle než přímé dotazování základních tabulek.

Abyste se vyhnuli snížení výkonu dotazů, je vhodné spustit DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD pro monitorování poměru režie zobrazení (total_rows / max(1, base_view_row)). Uživatelé by měli znovu sestavit materializované zobrazení, pokud je jeho overhead_ratio příliš vysoká.

Ukládání do mezipaměti materializovaného zobrazení a sady výsledků

Tyto dvě funkce ve vyhrazeném fondu SQL se používají k ladění výkonu dotazů. Ukládání do mezipaměti sady výsledků se používá k zajištění vysoké souběžnosti a rychlé odpovědi z opakovaných dotazů na statická data.

Pokud chcete použít výsledek uložený v mezipaměti, musí se formulář dotazu žádajícího o mezipaměť shodovat s dotazem, který mezipaměť vytvořil. Kromě toho se výsledek uložený v mezipaměti musí použít pro celý dotaz.

Materializovaná zobrazení umožňují změny dat v základních tabulkách. Data v materializovaných zobrazeních je možné použít u části dotazu. Tato podpora umožňuje použití stejných materializovaných zobrazení různými dotazy, které sdílejí některé výpočty, aby se zrychlil výkon.

Příklad

Tento příklad používá dotaz podobný TPCDS, který vyhledá zákazníky, kteří tráví více peněz prostřednictvím katalogu než v obchodech, identifikují upřednostňované zákazníky a jejich zemi/oblast původu. Dotaz zahrnuje výběr prvních 100 záznamů ze union tří příkazů sub-SELECT zahrnujících SUM() a 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');

Zkontrolujte odhadovaný plán provádění dotazu. Existuje 18 operací zamíchání a 17 operací spojení, které trvají déle na provedení. Teď vytvoříme jedno materializované zobrazení pro každý ze tří příkazů 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

Znovu zkontrolujte plán provádění původního dotazu. Počet spojení se nyní změní z 17 na 5 a nedojde k žádnému prohazování. Vyberte ikonu operace filtru v plánu, její výstupní seznam ukazuje, že data se čtou z materializovaných zobrazení místo základních tabulek.

Plan_Output_List_s_materializovanými_pohledy

V materializovaných zobrazeních se stejný dotaz spustí rychleji bez změny kódu.

Další kroky

Další tipy pro vývoj najdete v tématu Přehled vývoje vyhrazeného fondu SQL.

Váš názor

Byla tato stránka užitečná?

Last updated on 2025-04-07