Prestandastäm med materialiserade vyer

Artikel
06/01/2023

Materialiserade vyer för dedikerade SQL-pooler i Azure Synapse tillhandahåller en låg underhållsmetod för komplexa analysfrågor för att få snabba prestanda utan någon frågeändring. Den här artikeln beskriver den allmänna vägledningen om hur du använder materialiserade vyer.

Materialiserade vyer jämfört med standardvyer

Dedikerad SQL-pool i Azure Synapse stöder standardvyer och materialiserade vyer. Båda är virtuella tabeller som skapas med SELECT-uttryck och presenteras för frågor som logiska tabeller. Vyer kapslar in komplexiteten i vanlig databeräkning och lägger till ett abstraktionslager till beräkningsändringar så att du inte behöver skriva om frågor.

En standardvy beräknar sina data varje gång vyn används. Inga data lagras på disken. Personer använder vanligtvis standardvyer som ett verktyg som hjälper dig att organisera logiska objekt och frågor i en dedikerad SQL-pool. Om du vill använda en standardvy måste en fråga referera direkt till den.

En materialiserad vy förberäknar, lagrar och underhåller sina data i en dedikerad SQL-pool precis som en tabell. Ingen omberäkning krävs varje gång en materialiserad vy används. Det är därför frågor som använder alla eller en delmängd av data i materialiserade vyer kan få snabbare prestanda. Ännu bättre är att frågor kan använda en materialiserad vy utan att direkt referera till den, så det finns inget behov av att ändra programkoden.

De flesta kraven för en standardvy gäller fortfarande för en materialiserad vy. Mer information om syntaxen för materialiserad vy och andra krav finns i CREATE MATERIALIZED VIEW AS SELECT

Jämförelse	Visa	Materialiserad vy
Visa definition	Lagras i en dedikerad SQL-pool.	Lagras i en dedikerad SQL-pool.
Visa innehåll	Genereras varje gång vyn används.	Förbearbetade och lagrade i en dedikerad SQL-pool när vyn skapades. Uppdateras när data läggs till i de underliggande tabellerna.
Datauppdatering	Alltid uppdaterad	Alltid uppdaterad
Hastighet för att hämta visningsdata från komplexa frågor	Långsam	Snabb
Extra lagringsutrymme	Inga	Ja
Syntax	CREATE VIEW	SKAPA MATERIALISERAD VY SOM SELECT

Fördelar med att använda materialiserade vyer

En korrekt utformad materialiserad vy ger följande fördelar:

Minska körningstiden för komplexa frågor med JOID:er och mängdfunktioner. Ju mer komplex frågan är, desto högre är risken för att köra tidsbesparande. Den största fördelen uppnås när en frågas beräkningskostnad är hög och den resulterande datauppsättningen är liten.
Optimeraren i en dedikerad SQL-pool kan automatiskt använda distribuerade materialiserade vyer för att förbättra frågekörningsplanerna. Den här processen är transparent för användare som ger snabbare frågeprestanda och kräver inte frågor för att direkt referera till de materialiserade vyerna.
Kräver lågt underhåll av vyerna. Alla inkrementella dataändringar från bastabellerna läggs automatiskt till i de materialiserade vyerna på ett synkront sätt, vilket innebär att både bastabellerna och de materialiserade vyerna uppdateras i samma transaktion. Med den här designen kan du köra frågor mot materialiserade vyer för att returnera samma data som när du frågar bastabellerna direkt.
Data i en materialiserad vy kan distribueras på ett annat sätt än bastabellerna.
Data i materialiserade vyer får samma fördelar med hög tillgänglighet och återhämtning som data i vanliga tabeller.

De materialiserade vyerna som implementeras i en dedikerad SQL-pool ger också följande fördelar:

Jämfört med andra datalagerleverantörer ger de materialiserade vyerna som implementeras i en dedikerad SQL-pool också följande fördelar:

Brett stöd för mängdfunktioner. Se SKAPA MATERIALISERAD VY SOM SELECT (Transact-SQL).
Stöd för frågespecifik materialiserad vyrekommendations. Se EXPLAIN (Transact-SQL).
Automatisk och synkron datauppdatering med dataändringar i bastabeller. Användaren behöver inte göra någonting.

Vanliga scenarier

Materialiserade vyer används vanligtvis i följande scenarier:

Behov av att förbättra prestandan för komplexa analysfrågor mot stora data i storlek

Komplexa analysfrågor använder vanligtvis fler mängdfunktioner och tabellkopplingar, vilket orsakar mer beräkningsintensiva åtgärder, till exempel blandningar och kopplingar i frågekörningen. Det är därför komplexa analysfrågor tar längre tid att slutföra, särskilt i stora tabeller.

Användare kan skapa materialiserade vyer för data som returneras från vanliga beräkningar av frågor, så det behövs ingen omberäkning när dessa data behövs av frågor, vilket ger lägre beräkningskostnader och snabbare frågesvar.

Behöver snabbare prestanda utan eller minsta frågeändringar

Schema- och frågeändringar i dedikerade SQL-pooler hålls vanligtvis till ett minimum för att stödja vanliga ETL-åtgärder och rapportering. Personer kan använda materialiserade vyer för justering av frågeprestanda, om kostnaden för vyerna kan kompenseras av ökningen av frågeprestanda.

Jämfört med andra justeringsalternativ som skalning och statistikhantering är det en mindre effektfull produktionsförändring att skapa och underhålla en materialiserad vy och dess potentiella prestandavinst är också högre.

Att skapa eller underhålla materialiserade vyer påverkar inte de frågor som körs mot bastabellerna.
Frågeoptimeraren kan automatiskt använda de distribuerade materialiserade vyerna utan direkt visningsreferens i en fråga. Den här funktionen minskar behovet av frågeändringar i prestandajusteringen.

Behöver en annan datadistributionsstrategi för snabbare frågeprestanda

En dedikerad SQL-pool är ett distribuerat frågebearbetningssystem. Data i en SQL-tabell distribueras upp till 60 noder med någon av tre distributionsstrategier (hash, round_robin eller replikerad).

Datadistributionen anges när tabellen skapas och förblir oförändrad tills tabellen tas bort. Materialiserad vy, som är en virtuell tabell på disk, stöder hash- och round_robin datadistributioner. Användare kan välja en datadistribution som skiljer sig från bastabellerna men som är optimal för prestanda för frågor som använder vyerna.

Designvägledning

Här är den allmänna vägledningen om hur du använder materialiserade vyer för att förbättra frågeprestanda:

Designa för din arbetsbelastning

Innan du börjar skapa materialiserade vyer är det viktigt att du har en djup förståelse för din arbetsbelastning vad gäller frågemönster, prioritet, frekvens och storleken på resulterande data.

Användare kan köra EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> för de materialiserade vyer som rekommenderas av frågeoptimeraren. Eftersom dessa rekommendationer är frågespecifika kanske en materialiserad vy som gynnar en enskild fråga inte är optimal för andra frågor i samma arbetsbelastning.

Utvärdera dessa rekommendationer med dina arbetsbelastningsbehov i åtanke. De ideala materialiserade vyerna är de som gynnar arbetsbelastningens prestanda.

Var medveten om kompromissen mellan snabbare frågor och kostnaden

För varje materialiserad vy finns det en kostnad för datalagring och en kostnad för att underhålla vyn. När data ändras i bastabeller ökar storleken på den materialiserade vyn och dess fysiska struktur ändras också. För att undvika försämring av frågeprestanda underhålls varje materialiserad vy separat av SQL-motorn.

Underhållsarbetsbelastningen blir högre när antalet materialiserade vyer och ändringar i bastabellen ökar. Användare bör kontrollera om kostnaden för alla materialiserade vyer kan kompenseras av prestandavinsten för frågan.

Du kan köra den här frågan för att generera en lista över materialiserade vyer i en dedikerad SQL-pool:

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;

Alternativ för att minska antalet materialiserade vyer:

Identifiera vanliga datauppsättningar som ofta används av komplexa frågor i din arbetsbelastning. Skapa materialiserade vyer för att lagra dessa datauppsättningar så att optimeraren kan använda dem som byggstenar när du skapar körningsplaner.
Ta bort de materialiserade vyer som har låg användning eller som inte längre behövs. En inaktiverad materialiserad vy underhålls inte, men den medför fortfarande lagringskostnader.
Kombinera materialiserade vyer som skapats i samma eller liknande bastabeller även om deras data inte överlappar varandra. Att kombinera materialiserade vyer kan resultera i en större vy i storlek än summan av de separata vyerna, men kostnaden för visningsunderhåll bör minska. Exempel:


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

Det är inte alla prestandajusteringar som kräver frågeändring

SQL-frågeoptimeraren kan automatiskt använda distribuerade materialiserade vyer för att förbättra frågeprestanda. Det här stödet tillämpas transparent på frågor som inte refererar till vyer och frågor som använder aggregeringar som inte stöds när materialiserade vyer skapas. Ingen frågeändring krävs. Du kan kontrollera en frågas uppskattade körningsplan för att bekräfta om en materialiserad vy används.

Övervaka materialiserade vyer

En materialiserad vy lagras i den dedikerade SQL-poolen precis som en tabell med ett grupperat columnstore-index (CCI). Läsning av data från en materialiserad vy omfattar genomsökning av CCI-indexsegmenten och tillämpning av inkrementella ändringar från bastabeller. När antalet inkrementella ändringar är för högt kan det ta längre tid att matcha en fråga från en materialiserad vy än att fråga bastabellerna direkt.

För att undvika försämrad frågeprestanda är det bra att köra DBCC-PDW_SHOWMATERIALIZEDVIEWOVERHEAD för att övervaka vyns overhead_ratio (total_rows/max(1, base_view_row)). Användarna bör ÅTERSKAPA den materialiserade vyn om dess overhead_ratio är för hög.

Cachelagring av materialiserad vy och resultatuppsättning

Dessa två funktioner i en dedikerad SQL-pool används för frågeprestandajustering. Cachelagring av resultatuppsättningar används för att få hög samtidighet och snabba svar från repetitiva frågor mot statiska data.

Om du vill använda det cachelagrade resultatet måste formatet för cachen som begär frågan matcha med frågan som skapade cachen. Dessutom måste det cachelagrade resultatet gälla för hela frågan.

Materialiserade vyer tillåter dataändringar i bastabellerna. Data i materialiserade vyer kan tillämpas på en del av en fråga. Det här stödet gör att samma materialiserade vyer kan användas av olika frågor som delar viss beräkning för snabbare prestanda.

Exempel

I det här exemplet används en TPCDS-liknande fråga som hittar kunder som spenderar mer pengar via katalog än i butiker, identifierar önskade kunder och deras land/ursprungsregion. Frågan omfattar att välja TOP 100-poster från UNION för tre sub-SELECT-instruktioner som involverar SUM() och 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');

Kontrollera frågans uppskattade körningsplan. Det finns 18 blandningar och 17 kopplingsåtgärder, vilket tar mer tid att köra. Nu ska vi skapa en materialiserad vy för var och en av de tre sub-SELECT-uttrycken.

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

Kontrollera körningsplanen för den ursprungliga frågan igen. Nu ändras antalet kopplingar från 17 till 5 och det finns ingen blandning. Välj ikonen Filteråtgärd i planen. Utdatalistan visar att data läss från de materialiserade vyerna i stället för bastabellerna.

Plan_Output_List_with_Materialized_Views

Med materialiserade vyer körs samma fråga snabbare utan kodändring.

Nästa steg

Fler utvecklingstips finns i Översikt över utveckling av dedikerad SQL-pool.