Teljesítményhangolás materializált nézetekkel dedikált SQL-készlet használatával az Azure Synapse Analyticsben
A dedikált SQL-készletben a materializált nézetek 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ával kapcsolatos általános útmutatást ismerteti.
Materializált nézetek és standard nézetek
Az SQL-készlet standard és materializált nézeteket is támogat. 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 feltárják a gyakori adatszámítás összetettségét, és hozzáadnak egy absztrakciós réteget a számítási módosításokhoz, í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. Kapcsolatok általában szabványos nézeteket használnak olyan eszközként, amely segít az adatbázisok logikai objektumainak és lekérdezéseinek rendszerezésében. 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 a táblákhoz hasonlóan előre kiszámítják, tárolják és karbantartják az adatokat a dedikált SQL-készletben. Nincs szükség újraszámításra minden alkalommal, amikor materializált nézetet használ. Ez az oka annak, hogy a materializált nézetekben az adatok egy részét vagy egészét használó lekérdezések gyorsabb teljesítményt érhetnek el. Még jobb, hogy a lekérdezések materializált nézetet használhatnak anélkül, hogy közvetlen hivatkozást kellene létrehozniuk rá, így nem kell módosítani az alkalmazás kódját.
A szabványos nézetkövetelmények többsége továbbra is érvényes a materializált nézetre. A materializált nézet szintaxisával és egyéb követelményeivel kapcsolatos részletekért lásd: CREATE MATERIALIZED VIEW AS SELECT (MATERIALIZÁLT NÉZET LÉTREHOZÁSA KIVÁLASZTÁSKÉNT).
| Összehasonlítás | Nézet | Materialized View |
|---|---|---|
| Meghatározás megtekintése | Az Azure-adattárházban tárolva. | Az Azure-adattárházban tárolva. |
| Tartalom megtekintése | A nézet használatakor minden alkalommal létre lesz hozva. | Előre feldolgozva és tárolva az Azure-adattárházban a nézet létrehozása során. A frissítés az alapul szolgáló táblák adatainak hozzáadásakor történik. |
| 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árterület | Nem | Igen |
| Syntax | CREATE VIEW | MATERIALIZÁLT NÉZET LÉTREHOZÁSA KIVÁLASZTÁSKÉNT |
A materializált nézetek előnyei
A megfelelően megtervezett materializált nézet a következő előnyöket nyújtja:
A JOIN-ekkel és összesítő függvényekkel rendelkező összetett lekérdezések végrehajtási ideje csökkent. 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őny akkor érhető 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 lekérdezésoptimalizálója automatikusan használhatja az üzembe helyezett materializált nézeteket a lekérdezés-végrehajtási tervek javításához. Ez a folyamat átlátható a gyorsabb lekérdezési teljesítményt biztosító felhasználók számára, és nem igényli a lekérdezések közvetlen hivatkozását a materializált nézetekre.
Alacsony karbantartást igényel a nézeteken. A materializált nézet két helyen tárolja az adatokat, egy fürtözött oszlopcentrikus indexet a kezdeti adatokhoz a nézet létrehozásakor, és egy deltatárolót a növekményes adatváltozásokhoz. Az alaptáblák összes adatváltozása automatikusan szinkron módon lesz hozzáadva a különbözeti tárolóhoz. Egy háttérfolyamat (rekordátvétel) rendszeres időközönként áthelyezi az adatokat a különbözeti tárolóból a nézet oszlopcentrikus indexére. Ez a kialakítás lehetővé teszi, hogy 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 oszthatók el.
A materializált nézetekben 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 többi adattárház-szolgáltatóhoz képest a dedikált SQL-készletben implementált materializált nézetek a következő további előnyöket is biztosítják:
- Automatikus és szinkron adatfrissítés az alaptáblák adatváltozásaival. Nincs szükség felhasználói beavatkozásra.
- 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ézetre vonatkozó javaslat támogatása. Lásd: EXPLAIN (Transact-SQL).
Gyakori forgatókönyvek
A materializált nézeteket általában a következő esetekben használják:
Javítani kell az összetett elemzési lekérdezések teljesítményét nagy méretű adatokon
Az összetett elemzési lekérdezések általában több aggregációs függvényt és táblaillesztést használnak, ami nagyobb számítási terhelést eredményez, például a lekérdezések végrehajtásában történő elosztást és illesztést. Ez az oka annak, hogy ezek a lekérdezések hosszabb időt vesznek 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 lekérdezések igényelnek, ami alacsonyabb számítási költségeket és gyorsabb lekérdezési választ tesz lehetővé.
Gyorsabb teljesítményre van szükség lekérdezési módosítások nélkül vagy minimális módosításokkal
Az adattárházak séma- és lekérdezésváltozásait általában minimálisra tartják a rendszeres ETL-műveletek és -jelentések támogatása érdekében. Kapcsolatok materializált nézeteket használhat 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 növekedésével.
Más hangolási lehetőségekkel, például a skálázással és a statisztikák kezelésével összehasonlítva az éles környezetben sokkal kevésbé jelentős változás jön létre és tart fenn materializált nézetet, és a lehetséges teljesítménynövekedése is magasabb.
- A materializált nézetek létrehozása vagy 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ást használ a 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
Az Azure Data Warehouse egy elosztott és nagymértékben párhuzamos feldolgozási (MPP) rendszer.
A Synapse SQL egy elosztott lekérdezési rendszer, amely lehetővé teszi a vállalatok számára az adatraktározási és adatvirtualizálási forgatókönyvek implementálását az adatmérnökök számára jól ismert standard T-SQL-élmények használatával. Emellett kiterjeszti az SQL képességeit a streamelési és gépi tanulási forgatókönyvek kezelésére. Az adattárháztáblák adatai három terjesztési stratégia (kivonat, round_robin vagy replikált) egyikével vannak elosztva 60 csomópont között.
Az adateloszlás a tábla létrehozásakor van megadva, és a tábla elvetéséig változatlan marad. A lemezen lévő virtuális táblaként létrehozott materializált nézet támogatja a kivonatokat és round_robin adateloszlásokat. A felhasználók az alaptábláktól eltérő, de a nézeteket gyakran 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 számítási feladathoz
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ényül kapott adatok mérete tekintetében.
A felhasználók az EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> futtathatják 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 lesz optimális az ugyanabban a számítási feladatban lévő többi lekérdezéshez.
Értékelje ki ezeket a javaslatokat a számítási feladat igényeinek szem előtt tartásával. 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. Ahogy az adatok változnak az alaptáblákban, a materializált nézet mérete is nő, és a fizikai szerkezete is megváltozik.
A lekérdezési teljesítmény romlásának elkerülése érdekében az adatraktár-motor minden materializált nézetet külön kezel, beleértve a sorok áthelyezését a deltatárolóból az oszlopcentrikus indexszegmensekbe, valamint az adatváltozások összesítését.
A karbantartási számítási feladat magasabbra emelkedik, 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ény növekedésével.
Ezt a lekérdezést az adatbázis materializált nézeteinek listájához futtathatja:
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 kihasználtságú vagy már nem szükséges materializált nézeteket. A letiltott materializált nézet nincs fenntartva, de tárolási költségekkel jár.
Kombinálja az azonos vagy hasonló alaptáblákon létrehozott materializált nézeteket, még akkor is, ha az 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égének 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 mateiralized view of this form
SELECT A, C, SUM(B), SUM(D)
FROM T
GROUP BY A, C
Nem minden teljesítményhangolás igényel lekérdezésmódosítást
Az adattárház-optimalizáló automatikusan üzembe helyezett materializált nézeteket használhat a lekérdezési teljesítmény javítása érdekében. Ez a támogatás átláthatóan alkalmazható azokra a lekérdezésekre, amelyek nem hivatkoznak a nézetekre, és olyan lekérdezésekre, amelyek a materializált nézetek létrehozásakor nem támogatott összesítéseket használnak. Nincs szükség lekérdezésmódosításra. Ellenőrizheti egy lekérdezés becsült végrehajtási tervét annak ellenőrzéséhez, hogy használ-e materializált nézetet.
- A tényleges végrehajtási terv lekéréséről további információt a Azure Synapse Analytics dedikált SQL-készlet számítási feladatainak monitorozása DMV-k használatával című témakörben talál.
- A becsült végrehajtási tervet SQL Server Management Studio (SSMS) vagy SET SHOWPLAN_XML keresztül kérdezheti le.
Materializált nézetek monitorozása
A materializált nézeteket a rendszer ugyanúgy tárolja az adattárházban, 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 az index beolvasását és a változástároló módosításainak alkalmazását. Ha a deltatároló sorainak száma túl magas, a materializált nézetben lévő lekérdezések 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 futtatásával monitorozni a nézet overhead_ratio (total_rows/ base_view_row). Ha a overhead_ratio túl magas, érdemes újraépíteni a materializált nézetet, hogy a deltatároló összes sora átkerüljön az oszlopcentrikus indexbe.
Materializált nézet és eredményhalmaz gyorsítótárazása
Ez a két funkció a dedikált SQL-készletben körülbelül ugyanabban az időben jelenik meg a lekérdezési teljesítmény finomhangolásához. Az eredményhalmaz gyorsítótárazásával magas egyidejűséget és gyors válaszidőt é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 adatok módosítását az alaptáblákban. 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 használják a különböző lekérdezések, amelyek számításokat osztanak meg a gyorsabb teljesítmény érdekében.
Példa
Ez a példa egy TPCDS-hez hasonló lekérdezést használ, amely megkeresi azokat az ügyfeleket, akik több pénzt költenek katalóguson keresztül, mint az áruházakban. Emellett azonosítja 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 SUB-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 már nincs keverés. Válassza a Szűrőművelet ikont a csomagban. A kimeneti lista azt mutatja, hogy az adatok az alaptáblák helyett a materializált nézetekből lesznek beolvasva.
A materializált nézetek esetében ugyanaz a lekérdezés sokkal gyorsabban fut, kódmódosítás nélkül.
Következő lépések
További fejlesztési tippekért lásd: Synapse SQL-fejlesztés áttekintése.