Ladění výkonu s materializovanými zobrazeními

Článek
06/01/2023

Materializovaná zobrazení pro vyhrazené fondy SQL v Azure Synapse poskytují metodu s nízkou údržbou pro komplexní analytické dotazy, aby se zajistil rychlý výkon bez jakýchkoli změn dotazů. Tento článek popisuje obecné pokyny k používání materializovaných zobrazení.

Materializovaná zobrazení vs. standardní zobrazení

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

Standardní zobrazení vypočítá data pokaždé, když se toto zobrazení použije. 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í na něj dotaz 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 přepočítá. Proto můžou dotazy, které v materializovaných zobrazeních používají všechna data nebo jejich podmnožinu, dosáhnout vyššího výkonu. Ještě lepší je, že dotazy můžou používat materializované zobrazení, aniž by na něj přímo odkazovali, takže není potřeba 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í	Zobrazení	Materialized View
Zobrazení definice	Uložené ve vyhrazeném fondu SQL.	Uložené ve vyhrazeném fondu SQL.
Zobrazení obsahu	Generuje se při každém použití zobrazení.	Předpracované a uložené ve vyhrazeném fondu SQL během vytváření zobrazení. Aktualizují se 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 zobrazení ze složitých dotazů	Pomalá	Rychlý
Další úložiště	No	Yes
Syntax	CREATE VIEW	VYTVOŘIT MATERIALIZOVANÉ ZOBRAZENÍ JAKO VÝBĚR

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í sítí JOIN a agregačních funkcí. Čím složitější je dotaz, tím vyšší je potenciál pro úsporu času spuštění. Největší výhody 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žívat 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, aby dotazy přímo odkazovali na materializovaná zobrazení.
Vyžadovat nízkou údržbu zobrazení. Všechny přírůstkové změny dat ze základních tabulek jsou automaticky přidány 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í se dají distribuovat odlišně od základních tabulek.
Data v materializovaných zobrazeních mají 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é agregační funkce Viz VYTVOŘENÍ MATERIALIZOVANÉHO ZOBRAZENÍ JAKO 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ů u velkých objemů dat

Složité analytické dotazy obvykle používají více agregačních funkcí a spojení tabulek, což způsobuje operace náročné na výpočetní výkon, jako jsou 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é můžou vytvářet materializovaná zobrazení pro data vrácené z běžných výpočtů dotazů, takže v případě, že dotazy tato data potřebují, není potřeba přepočítat, což umožní snížit náklady na výpočetní prostředky a zrychlit odpověď na dotazy.

Potřeba rychlejšího výkonu bez nebo minimálních změn dotazů

Změny schématu a dotazů ve vyhrazených fondech SQL se obvykle udržují na minimu kvůli podpoře pravidelných operací ETL a generování sestav. Lidé můžou k ladění výkonu dotazů použít materializovaná zobrazení, pokud se náklady na tato zobrazení můžou vykompenzovat zvýšením výkonu dotazů.

V porovnání s jinými možnostmi ladění, jako je škálování a správa statistik, je vytvoření a udržování materializovaného zobrazení méně působivá produkční změna a jeho potenciální zvýšení výkonu je také vyšší.

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 odkazu na přímé zobrazení v dotazu. Tato funkce snižuje potřebu změn dotazů při ladění výkonu.

Potřebujete jinou strategii distribuce dat pro rychlejší výkon dotazů.

Vyhrazený fond SQL je distribuovaný systém zpracování dotazů. Data v tabulce SQL se distribuují až do 60 uzlů pomocí jedné ze tří strategií distribuce (hash, round_robin nebo replikovaná).

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

Pokyny k návrhu

Tady jsou obecné pokyny ke zlepšení výkonu dotazů pomocí materializovaných zobrazení:

Návrh pro vaše úlohy

Než začnete vytvářet materializovaná zobrazení, je důležité porozumět úlohám z hlediska vzorů dotazů, důležitosti, četnosti a velikosti výsledných dat.

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

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

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

Pro každé materializované zobrazení jsou k dispozici náklady na úložiště dat a náklady na údržbu zobrazení. Se změnami dat v základních tabulkách se zvětšuje velikost materializovaného zobrazení a mění se i jeho fyzická struktura. Aby se zabránilo snížení výkonu dotazů, udržuje modul SQL každé materializované zobrazení samostatně.

Úloha údržby se zvýší, když se zvýší počet materializovaných zobrazení a změn základní tabulky. Uživatelé by měli zkontrolovat, jestli se náklady vzniklé ve všech materializovaných zobrazeních dají vykompenzovat zvýšením výkonu dotazu.

Spuštěním tohoto dotazu můžete 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šich úlohách. Vytvořte materializovaná zobrazení pro uložení těchto datových sad, aby je optimalizátor mohl použít jako stavební bloky při vytváření plánů provádění.
Odstraňte materializovaná zobrazení, která mají nízké využití nebo už nejsou potřeba. Zakázané materializované zobrazení se neudržuje, ale stále se za něj účtují 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. Pří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žívat nasazená materializovaná zobrazení ke zlepšení výkonu dotazů. Tato podpora se transparentně aplikuje na dotazy, 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í.

Monitorování materializovaných 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 kontrolu segmentů indexu CCI a použití všech přírůstkových změn ze základních tabulek. Pokud je počet přírůstkových změn příliš vysoký, může překlad dotazu z materializovaného zobrazení trvat déle než přímé dotazování základních tabulek.

Pokud se chcete vyhnout snížení výkonu dotazů, je vhodné spustit PDW_SHOWMATERIALIZEDVIEWOVERHEAD DBCC a monitorovat overhead_ratio zobrazení (total_rows / max(1, base_view_row)). Uživatelé by měli znovu vytvořit 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 slouží k ladění výkonu dotazů. Ukládání sady výsledků do mezipaměti se používá k získání vysoké souběžnosti a rychlé odezvy opakovaných dotazů na statická data.

Pokud chcete použít výsledek uložený v mezipaměti, musí se forma dotazu, který žádá o mezipaměť, shodovat s dotazem, který mezipaměť vytvořil. Výsledek uložený v mezipaměti se navíc musí vztahovat na 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 na část dotazu. Tato podpora umožňuje používat stejná materializovaná zobrazení různými dotazy, které sdílejí určité výpočty, aby se zrychlil výkon.

Příklad

Tento příklad používá dotaz podobný TPCDS, který vyhledá zákazníky, kteří utratí více peněz prostřednictvím katalogu než v obchodech, a identifikuje upřednostňované zákazníky a jejich zemi nebo oblast původu. Dotaz zahrnuje výběr TOP 100 záznamů z 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í shuffles a 17 spojení, jejichž spuštění trvá déle. 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 teď změní ze 17 na 5 a nedochází k žádnému náhodnému prohazování. V plánu vyberte ikonu operace filtru a v seznamu výstupu se zobrazují data, která se čtou z materializovaných zobrazení, a ne ze základních tabulek.

Plan_Output_List_with_Materialized_Views

S materializovanými zobrazeními běží stejný dotaz 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.

Sdílet prostřednictvím