Ladění výkonu s využitím materializovaných zobrazení s využitím vyhrazeného fondu SQL ve službě Azure Synapse Analytics

Článek
04/15/2023

Ve vyhrazeném fondu SQL materializovaná zobrazení poskytují metodu nízké údržby pro složité 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í

Fond SQL podporuje standardní i materializovaná zobrazení. Obě jsou virtuální tabulky vytvořené pomocí výrazů SELECT a prezentované dotazům jako logické tabulky. Zobrazení ukazují složitost běžného výpočtu dat a přidávají do výpočtů změny 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 v databázi. Pokud chcete použít standardní zobrazení, musí na něj dotaz přímo odkazovat.

Materializované zobrazení předpočítá, ukládá a udržuje 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á. To je důvod, proč dotazy, které v materializovaných zobrazeních používají všechna data nebo jejich podmnožinu, můžou dosáhnout rychlejšího výkonu. A co je lepší, 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 standardních požadavků na zobrazení stále platí pro 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é v datovém skladu Azure.	Uložené v datovém skladu Azure.
Zobrazení obsahu	Generuje se při každém použití zobrazení.	Předpracované a uložené v datovém skladu Azure 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	CREATE MATERIALIZED VIEW AS SELECT

Výhody materializovaných zobrazení

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

Zkrátila se doba provádění složitých dotazů s funkcemi JONN a agregačními funkcemi. Čím složitější je dotaz, tím vyšší je potenciál pro úsporu času provádě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 dotazů ve vyhrazeném fondu SQL může automaticky používat nasazená materializovaná zobrazení k vylepšení plánů provádění dotazů. Tento proces je pro uživatele transparentní a poskytuje rychlejší výkon dotazů a nevyžaduje přímé odkazy na materializovaná zobrazení.
Vyžaduje nízkou údržbu zobrazení. Materializované zobrazení ukládá data na dvou místech: clusterovaný index columnstore pro počáteční data v době vytvoření zobrazení a rozdílové úložiště pro přírůstkové změny dat. Všechny změny dat ze základních tabulek se synchronně přidají do rozdílového úložiště automaticky. Proces na pozadí (mover řazené kolekce členů) pravidelně přesouvá data z rozdílového úložiště do indexu columnstore zobrazení. Tento návrh umožňuje dotazování materializovaných zobrazení vrátit stejná data jako přímé dotazování na základní tabulky.
Data v materializovaném zobrazení se dají distribuovat jinak než v základních tabulkách.
Data v materializovaných zobrazeních získají stejné výhody vysoké dostupnosti a odolnosti jako data v běžných tabulkách.

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

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

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í prostředky, jako jsou náhodné prohazování a spojení při provádění dotazů. Proto dokončení těchto dotazů trvá delší dobu, 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 pokud dotazy tato data potřebují, nevyžaduje se žádný přepočítání, což umožňuje nižší náklady na výpočetní prostředky a rychlejší odezvu na dotazy.

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

Změny schématu a dotazů v datových skladech 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 zobrazení dají 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, jde o mnohem méně působivou produkční změnu, která vytváří a udržuje materializované zobrazení 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 přímého odkazu na zobrazení v dotazu. Tato funkce snižuje potřebu změny dotazů při ladění výkonu.

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

Datový sklad Azure je distribuovaný systém MPP (Massively Parallel Processing).

Synapse SQL je distribuovaný dotazovací systém, který podnikům umožňuje implementovat scénáře virtualizace dat a skladů dat pomocí standardních prostředí T-SQL, která znají datoví inženýři. Rozšiřuje také možnosti SQL o řešení scénářů streamování a strojového učení. Data v tabulce datového skladu se distribuují napříč 60 uzly pomocí jedné ze tří strategií distribuce (hash, round_robin nebo replikovaná).

Distribuce dat je zadána při vytváření tabulky a zůstává nezměněna, dokud tabulka není vyřazena. Materializované zobrazení jako virtuální tabulka na disku podporuje rozdělení dat hodnot hash a round_robin. Uživatelé mohou zvolit distribuci dat, která se liší od základních tabulek, ale je optimální pro výkon dotazů, které často používají zobrazení.

Pokyny k návrhu

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

Návrh pro vaše úlohy

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

Uživatelé mohou spustit explain WITH_RECOMMENDATIONS <SQL_statement> pro materializovaná zobrazení doporučená optimalizátorem dotazů. Vzhledem k tomu, že jsou tato doporučení specifická pro dotazy, materializované zobrazení, které je přínosem pro jeden dotaz, 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á 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í. Při změnách 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 datového skladu každé materializované zobrazení samostatně, včetně přesouvání řádků z rozdílového úložiště do segmentů indexu columnstore a konsolidace změn dat.

Když se zvýší počet materializovaných zobrazení a změn základní tabulky, zatížení údržby se zvýší. 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 dotazů.

Pro seznam materializovaného zobrazení v databázi můžete spustit tento dotaz:

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 často používané složitými 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 datového skladu 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 na 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í.

Další informace o načtení skutečného plánu spuštění najdete v tématu Monitorování úloh vyhrazeného fondu SQL Azure Synapse Analytics pomocí zobrazení dynamické správy.
Odhadovaný plán spuštění můžete načíst prostřednictvím SQL Server Management Studio (SSMS) nebo set SHOWPLAN_XML.

Monitorování materializovaných zobrazení

Materializované zobrazení je uložené v datovém skladu stejně jako tabulka s clusterovaným indexem columnstore (CCI). Čtení dat z materializovaného zobrazení zahrnuje skenování indexu a použití změn z rozdílového úložiště. Pokud je počet řádků v rozdílovém úložišti příliš vysoký, může překlad dotazu z materializovaného zobrazení trvat déle než přímé dotazování základních tabulek.

Abyste se vyhnuli snížení výkonu dotazů, je vhodné spustit PDW_SHOWMATERIALIZEDVIEWOVERHEAD DBCC a monitorovat overhead_ratio zobrazení (total_rows/ base_view_row). Pokud je overhead_ratio příliš vysoká, zvažte opětovné sestavení materializovaného zobrazení, aby se všechny řádky v rozdílovém úložišti přesunuly do indexu columnstore.

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

Tyto dvě funkce jsou ve vyhrazeném fondu SQL zavedeny přibližně ve stejnou dobu pro ladění výkonu dotazů. Ukládání sady výsledků do mezipaměti se používá k dosažení vysoké souběžnosti a rychlé doby 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

V tomto příkladu se používá dotaz podobný TPCDS, který vyhledá zákazníky, kteří utratí více peněz prostřednictvím katalogu než v obchodech. Také identifikuje upřednostňované zákazníky a jejich zemi/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 už nedochází k náhodnému prohazování. V plánu vyberte ikonu Operace filtru. Jeho výstupní seznam ukazuje, že data se čtou z materializovaných zobrazení místo ze základních tabulek.

Plan_Output_List_with_Materialized_Views

S materializovanými zobrazeními běží stejný dotaz mnohem rychleji bez jakékoli změny kódu.

Další kroky

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

Sdílet prostřednictvím