Teljesítmény optimalizálás materializált nézetekkel

2025-04-02

Az Azure Synapse dedikált SQL-készleteinek materializált nézetei alacsony karbantartási módot biztosítanak az összetett elemzési lekérdezésekhez, hogy lekérdezésmódosítás nélkül gyors teljesítményt nyújtsanak. Ez a cikk a materializált nézetek használatának általános útmutatását ismerteti.

Materializált nézetek vs. alapértelmezett nézetek

Az Azure Synapse dedikált SQL-készlete támogatja a szabványos és materializált nézeteket. Mindkettő a SELECT kifejezésekkel létrehozott virtuális táblák, amelyek logikai táblákként jelennek meg a lekérdezésekben. A nézetek befoglalják a gyakori adatszámítás összetettségét, és hozzáadnak egy absztrakciós réteget a számítások módosításaihoz, így nem kell újraírni a lekérdezéseket.

A standard nézet minden alkalommal kiszámítja az adatokat, amikor a nézetet használják. Nincs lemezen tárolt adat. A felhasználók általában szabványos nézeteket használnak olyan eszközként, amely segít a logikai objektumok és lekérdezések rendszerezésében egy dedikált SQL-készletben. A szabványos nézet használatához a lekérdezésnek közvetlen hivatkozást kell készítenie rá.

A materializált nézetek előre kiszámítják, tárolják és karbantartják az adatokat egy dedikált SQL-készletben, akárcsak egy táblában. Nincs szükség újraszámításra minden alkalommal, amikor materializált nézetet használ. Ezért gyorsabb teljesítményt érhetnek el azok a lekérdezések, amelyek az adatok egészét vagy egy részhalmazát használják a materializált nézetekben. Még jobb, ha a lekérdezések materializált nézetet használnak anélkül, hogy közvetlenül hivatkoznak rá, így nincs szükség az alkalmazáskód módosítására.

A szabványos nézetre vonatkozó követelmények többsége továbbra is érvényes a materializált nézetre. A materializált nézet szintaxisáról és egyéb követelményeiről a CREATE MATERIALIZED VIEW AS SELECT (ANYAGALAPÚ NÉZET KIVÁLASZTÁSA) című témakör tartalmaz további információt.

Összehasonlítás	Megtekintés	Materializált nézet
Definíció megtekintése	Dedikált SQL-készletben tárolva.	Dedikált SQL-készletben tárolva.
Tartalom megtekintése	A nézet használatakor minden alkalommal létrejön.	Előre feldolgozva és tárolva a dedikált SQL-készletben a nézet létrehozása során. Az alapul szolgáló táblák adatainak hozzáadásakor frissül.
Adatfrissítés	Mindig frissítve	Mindig frissítve
Adatok megtekintése összetett lekérdezésekből való lekérésének sebessége	Lassú	Gyors
Extra tárhely	Nem	Igen
Szemantika	NÉZET LÉTREHOZÁSA	MATERIALIZÁLT NÉZET LÉTREHOZÁSA KIVÁLASZTÁSKÉNT

A materializált nézetek használatának előnyei

A megfelelően megtervezett materializált nézet a következő előnyöket nyújtja:

Csökkentse az összetett lekérdezések végrehajtási idejét JOIN-ekkel és összesítő függvényekkel. Minél összetettebb a lekérdezés, annál nagyobb a végrehajtási idő megtakarításának lehetősége. A legnagyobb előnyt akkor nyeri el, ha egy lekérdezés számítási költsége magas, és az eredményként kapott adathalmaz kicsi.
A dedikált SQL-készlet optimalizálója automatikusan használhat üzembe helyezett materializált nézeteket a lekérdezés-végrehajtási tervek javítása érdekében. Ez a folyamat átlátható a gyorsabb lekérdezési teljesítményt nyújtó felhasználók számára, és nem igényel lekérdezéseket a materializált nézetek közvetlen hivatkozásához.
A nézetek kevés karbantartást igényelnek. Az alaptáblák összes növekményes adatváltozása automatikusan szinkron módon lesz hozzáadva a materializált nézetekhez, ami azt jelenti, hogy az alaptáblák és a materializált nézetek is ugyanabban a tranzakcióban frissülnek. Ezzel a kialakítással a materializált nézetek lekérdezése ugyanazokat az adatokat adja vissza, mint az alaptáblák közvetlen lekérdezése.
A materializált nézetben lévő adatok az alaptábláktól eltérően terjeszthetők.
A materializált nézetben lévő adatok ugyanolyan magas rendelkezésre állási és rugalmassági előnyökkel járnak, mint a normál táblák adatai.

A dedikált SQL-készletben implementált materializált nézetek a következő előnyöket is biztosítják:

Más adattárház-szolgáltatókhoz képest a dedikált SQL-készletben megvalósított materializált nézetek a következő előnyöket is biztosítják:

Széles körű aggregátumfüggvény-támogatás. Lásd: CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL).
A lekérdezésspecifikus materializált nézetjavaslat támogatása. Lásd: EXPLAIN (Transact-SQL).
Automatikus és szinkron adatfrissítés az alaptáblák adatváltozásaival. Nincs szükség felhasználói beavatkozásra.

Gyakori forgatókönyvek

A materializált nézeteket általában a következő helyzetekben használják:

Növelni kell az összetett elemzési lekérdezések teljesítményét nagy méretű adatok esetén

Az összetett elemzési lekérdezések rendszerint több aggregátumfüggvényt és táblaösszeillesztést alkalmaznak, ami nagyobb számítási terhelést eredményez, például a lekérdezések végrehajtásában történő átrendezések és illesztések miatt. Ezért az összetett elemzési lekérdezések végrehajtása hosszabb időt vesz igénybe, különösen nagy táblák esetén.

A felhasználók materializált nézeteket hozhatnak létre a lekérdezések gyakori számításaiból visszaadott adatokhoz, így nincs szükség újraszámításra, ha ezekre az adatokra a lekérdezések szükség van, így alacsonyabb számítási költség és gyorsabb lekérdezési válasz érhető el.

Gyorsabb teljesítményre van szükség a lekérdezési módosítások nélkül vagy minimálisan módosítva

A dedikált SQL-készletek séma- és lekérdezésmódosításai általában a minimális szinten maradnak a rendszeres ETL-műveletek és -jelentések támogatása érdekében. A felhasználók materializált nézeteket használhatnak a lekérdezési teljesítmény finomhangolásához, ha a nézetek költségei ellensúlyozhatók a lekérdezési teljesítmény nyereségével.

A skálázáshoz és a statisztikakezeléshez hasonló finomhangolási lehetőségekhez képest, a materializált nézet létrehozása és fenntartása kevésbé jelentős változás a produkciós környezetre nézve, és a teljesítménynövekedés potenciálja is nagyobb.

A materializált nézetek létrehozása és karbantartása nem befolyásolja az alaptáblákon futó lekérdezéseket.
A lekérdezésoptimalizáló automatikusan használhatja az üzembe helyezett materializált nézeteket anélkül, hogy közvetlen nézethivatkozás lenne egy lekérdezésben. Ez a képesség csökkenti a lekérdezések teljesítményhangolásának szükségességét.

Eltérő adatelosztási stratégiára van szükség a gyorsabb lekérdezési teljesítmény érdekében

A dedikált SQL-készlet egy elosztott lekérdezésfeldolgozó rendszer. Az SQL-táblák adatai három terjesztési stratégia (kivonat, round_robin vagy replikált) egyikével 60 csomópontra oszlanak el.

Az adateloszlás a tábla létrehozásakor van megadva, és a tábla elvetéséig változatlan marad. A materializált nézet, amely egy lemezen található virtuális tábla, támogatja a hash és a round_robin adateloszlásokat. A felhasználók az alaptábláktól eltérő, de a nézeteket használó lekérdezések teljesítményéhez optimális adatelosztást választhatnak.

Tervezési útmutató

Az alábbiakban általános útmutatást talál a materializált nézetek lekérdezési teljesítményének javításához:

Tervezés a munkafolyamathoz

Mielőtt megkezdené a materializált nézetek létrehozását, fontos, hogy alapos ismeretekkel rendelkezzen a számítási feladatokról a lekérdezési minták, a fontosság, a gyakoriság és az eredményként kapott adatok mérete tekintetében.

A felhasználók futtathatják EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> a lekérdezésoptimalizáló által javasolt materializált nézetekhez. Mivel ezek a javaslatok lekérdezésspecifikusak, előfordulhat, hogy az egyetlen lekérdezés előnyeit élvező materializált nézet nem optimális más lekérdezésekhez ugyanabban a számítási feladatban.

Ezeket a javaslatokat a számítási feladat igényeinek figyelembe vételével értékelheti ki. Az ideális materializált nézetek azok, amelyek kihasználják a számítási feladat teljesítményét.

Vegye figyelembe a gyorsabb lekérdezések és a költségek közötti kompromisszumot

Minden materializált nézethez tartozik egy adattárolási költség és a nézet fenntartásának költsége. Az alaptáblák adatváltozásai során a materializált nézet mérete és fizikai szerkezete is megváltozik. A lekérdezési teljesítmény romlásának elkerülése érdekében az SQL-motor minden materializált nézetet külön tart fenn.

A karbantartási számítási feladat nagyobb lesz, ha a materializált nézetek és az alaptábla változásainak száma növekszik. A felhasználóknak ellenőriznie kell, hogy az összes materializált nézet költségei ellensúlyozhatók-e a lekérdezési teljesítménynövekedéssel.

Ezt a lekérdezést futtatva létrehozhatja a materializált nézetek listáját egy dedikált SQL-készletben:

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;

A materializált nézetek számának csökkentésére vonatkozó lehetőségek:

Azonosítsa a számítási feladat összetett lekérdezései által gyakran használt gyakori adatkészleteket. Hozzon létre materializált nézeteket az adatkészletek tárolásához, hogy az optimalizáló építőelemként használhassa őket végrehajtási tervek létrehozásakor.
Elvetheti az alacsony használatú vagy már nem szükséges materializált nézeteket. A kikapcsolt materializált nézet nem tartható fenn, de tárolási költséggel jár.
Kombinálja az azonos vagy hasonló alaptáblákon létrehozott materializált nézeteket akkor is, ha adataik nem fedik egymást. A materializált nézetek kombinálása nagyobb méretű nézetet eredményezhet, mint a különálló nézetek összege, azonban a nézet karbantartási költségeinek csökkennie kell. Például:


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

Nem minden teljesítmény-finomhangolás igényel lekérdezésmódosítást

Az SQL-lekérdezésoptimalizáló automatikusan használhat üzembe helyezett materializált nézeteket a lekérdezési teljesítmény javítása érdekében. Ez a támogatás transzparens módon lesz alkalmazva azokra a lekérdezésekre, amelyek nem hivatkoznak a materializált nézetek létrehozása során nem támogatott összesítéseket használó nézetekre és lekérdezésekre. Nincs szükség lekérdezésmódosításra. A lekérdezés becsült végrehajtási tervét ellenőrizheti, hogy megerősítse, használ-e materializált nézetet.

Materializált nézetek monitorozása

A materializált nézeteket a rendszer a dedikált SQL-készletben tárolja, ugyanúgy, mint egy fürtözött oszlopcentrikus indexet (CCI) tartalmazó táblát. Az adatok materializált nézetben való olvasása magában foglalja a CCI-indexszegmensek vizsgálatát és az alaptáblák növekményes változásainak alkalmazását. Ha a növekményes módosítások száma túl magas, a lekérdezések materializált nézetben való feloldása hosszabb időt vehet igénybe, mint az alaptáblák közvetlen lekérdezése.

A lekérdezési teljesítmény romlásának elkerülése érdekében ajánlott a DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD parancsot futtatni a nézet túlterhelési arányának figyelemmel kíséréséhez (total_rows / max(1, base_view_row)). A felhasználóknak újra kell építeniük a materializált nézetet, ha az overhead_ratio túl magas.

Anyagiasított nézet és eredményhalmaz gyorsítótárazás

Ez a két funkció a dedikált SQL-készletben a lekérdezési teljesítmény finomhangolására szolgál. Az eredményhalmazok gyorsítótárazásával nagy fokú párhuzamosság és gyors válaszidő érhető el a statikus adatok ismétlődő lekérdezéseiből.

A gyorsítótárazott eredmény használatához a gyorsítótár-kérő lekérdezés formájának meg kell egyeznie a gyorsítótárat előállító lekérdezéssel. Emellett a gyorsítótárazott eredménynek a teljes lekérdezésre is érvényesnek kell lennie.

A materializált nézetek lehetővé teszik az alaptáblák adatváltozását. A materializált nézetekben lévő adatok egy lekérdezésre alkalmazhatók. Ez a támogatás lehetővé teszi, hogy ugyanazokat a materializált nézeteket különböző lekérdezések használják, amelyek megosztanak bizonyos számításokat a gyorsabb hatékonyság érdekében.

Példa

Ez a példa egy TPCDS-hez hasonló lekérdezést használ, amely megkeresi azokat az ügyfeleket, akik katalóguson keresztül több pénzt költenek, mint a boltokban, azonosítják az előnyben részesített ügyfeleket és azok származási országát/régióját. A lekérdezés során a SZUM() és a GROUP BY függvényt tartalmazó három sub-SELECT utasítás uniójából kiválasztja a TOP 100 rekordot.

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');

Ellenőrizze a lekérdezés becsült végrehajtási tervét. 18 shuffles és 17 illesztési művelet van, amelyek végrehajtása több időt vesz igénybe. Most hozzunk létre egy materializált nézetet a három rész-SELECT utasítás mindegyikéhez.

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

Ellenőrizze újra az eredeti lekérdezés végrehajtási tervét. Most az illesztések száma 17-ről 5-re változik, és nincs shuffle. Válassza a Szűrőművelet ikont a tervben, a kimeneti lista pedig azt mutatja, hogy az adatok az alaptáblák helyett a materializált nézetekből lesznek beolvasva.

Anyagiasított nézetekkel rendelkező kimeneti lista terv

A materializált nézetekben ugyanaz a lekérdezés gyorsabban fut kódmódosítás nélkül.

Következő lépések

További fejlesztési tippekért tekintse meg a dedikált SQL-készlet fejlesztési áttekintését.