SQL Magazin 7. cikk – SQL Server: Turbózza fel a lekérdezéseit indexelt nézetekkel

admin 0 Comments Articles

SQL Magazin 7. cikk – SQL Server: Turbózza fel a lekérdezéseit indexelt nézetekkel

Kérdését jelezze befejezettként Annotate

A cikk célja, hogy bemutassa az indexelt nézetek fogalmát az SQL Serverben, és bemutassa, hogyan lehet ezt a fajta nézetet megvalósítani és használni a lekérdezések optimalizálására.

Nézet fogalma

A nézeteket “virtuális tábláknak” is nevezik, mivel az adatok eléréséhez a táblák használatának alternatíváját jelentik. A nézet nem más, mint egy objektumba zárt SELECT utasítás. A nézet létrehozásának szintaxisa az 1. listában látható.

CREATE VIEW nome_da_visão ...) ] AS subconsulta;

A nézet létrehozásának és használatának példáját az 1. listában láthatjuk.

Use NorthWind go create view vi_vendas_mes As Select ano = datepart(yyyy,OrderDate), mes = datepart(mm,OrderDate), qtde_total = sum(Quantity) from Orders o inner join od on o.OrderId = od.OrderId group by datepart(yyyy,o.OrderDate), datepart(mm,o.OrderDate) go select * from vi_vendas_mês go ano mes qtde_total contador ----------- ----------- ----------- -------------------- 1996 7 1462 59 1996 8 1322 69 1996 9 1124 57 1996 10 1738 73 1996 11 1735 66

A nézetek használatának előnyei között megemlíthetjük :

• A kód egyszerűsítése: egyszer írhatunk összetett SELECT-eket, ezeket a nézetben kapszulázhatjuk, és onnan indíthatjuk, mintha bármelyik táblázat lenne;
• Biztonsági kérdések: tegyük fel, hogy néhány táblában bizalmas információk vannak, és ezért azt szeretnénk, hogy csak bizonyos felhasználók férjenek hozzá. E táblák egyes oszlopaihoz azonban minden felhasználónak hozzá kell férnie. A probléma megoldásának hatékony módja egy nézet létrehozása és az érzékeny oszlopok elrejtése. Ily módon elnyomhatjuk az eredeti táblához való hozzáférési jogokat, és felszabadíthatjuk a nézethez való hozzáférést;
• A lekérdezés optimalizálásának lehetősége indexált nézetek megvalósításával.

Indexált nézetek a gyakorlatban

A nézetek SELECT utasításokat kapszuláznak, ami azt jelenti, hogy amikor elindítják őket, a hozzájuk tartozó SELECT utasítások végrehajtásra kerülnek. A nézetek nem hoznak létre tárolókat az általuk visszaadott adatokhoz (mint a táblázatok). Nos, nagyszerű lenne, ha egy táblázatban “materializálhatnánk” a nézetben található SELECT parancs eredményét, indexeket létrehozva, hogy megkönnyítsük a hozzáférést. Nos, az indexelt nézetek pontosan ezt teszik. Egy SELECT végrehajtása egy indexelt nézetben ugyanolyan hatású, mint egy select végrehajtása egy hagyományos táblában.

Az indexelt nézetek fő célja a teljesítmény növelése, és az SQL Server előnye, hogy a végrehajtási tervek figyelembe veszik az indexelt nézetet az adatok elérésének eszközeként, még akkor is, ha a nézet neve nem volt explicit a lekérdezésben. Ez az SQL Server 2000 Enterprise Edition kiadásában lehetséges, ahol a parancsoptimalizáló közvetlenül az indexelt nézetben lévő adatokat választhatja ki (a táblában meglévő nyers adatok kiválasztása helyett), amint azt a következőkben látni fogjuk.

Indexelt nézet létrehozása lépésről lépésre

1. A környezet beállítása, első lépésként néhány paraméter állapotát kell beállítani abban a munkamenetben, ahol a nézetet létrehozni és használni szeretnénk, hiszen mivel az indexelt nézet egy táblában “materializálódik”, semmi sem zavarhatja az eredményét. Képzeljük el például a következő forgatókönyvet:
2. Egy bizonyos beállítás, amely befolyásolja a SELECT eredményét (pl. concat_null_yelds_null), az indexelt nézet létrehozása előtt kerül beállításra;
3. Az indexelt nézet létrehozásra kerül; megjegyezzük, hogy a nézet eredménye az előző lépésben beállított beállításnak megfelelően lesz “materializálva” a lemezen;
4. Azután a beállítás kikapcsolásra kerül, és az indexelt nézetet a felhasználó végrehajtja. Mivel a nézet materializálódott, olyan eredményt kapunk, amely nincs összhangban az aktuális konfigurációval.

A 2. lista például azt mutatja, hogy a concat_null_yields_null tulajdonság megváltoztatásakor milyen különbség van egy parancs eredményében.

set concat_null_yields_null ON print null + 'abc' -------------------------------------- Set concat_null_yields_null OFF print null + 'abc' -------------------------------------- abc

Képzelje el, mi történne, ha az indexelt nézet a concat_null_yields_null tulajdonság engedélyezésével jönne létre, de az aktuális munkamenetben ez a tulajdonság ki lenne kapcsolva – ugyanaz a SELECT eltérő eredményekhez vezetne!

Ezt a problémát egyszerű módon oldották meg – az indexelt nézetek létrehozásához és használatához kötelező a környezetet egy alapértelmezett értékekből álló lista szerint konfigurálni. Így nem lehet eltérő eredményeket kapni, mert a nézet egyszerűen nem fog működni, ha bármelyik beállítás nem szabványos értékre van állítva.

Az 1. táblázat ezeket a beállításokat és a hozzájuk tartozó alapértelmezett értékeket mutatja.

(*) Az azonosítót az sp_configure parancs használja. Az egyes beállítások működésének ellenőrzéséhez olvassa el az “Indexelt nézetekhez szükséges beállítások” című részt. A konfiguráció értékének módosítására két lehetőség van:

• Közvetlenül a munkamenetben: futtassa a set ON | OFF parancsot
• A meglévő kiszolgáló alapértelmezett értékének módosítása: futtassa a sp_Configure ‘user options’, . Az egyes konfigurációk azonosító száma az 1. táblázatban látható.
  Megjegyzés: Az AritHabort azonosítója 64, a Quoted_Identifier azonosítója pedig 256. Például a Quoted_Identifier + AritHabort összekapcsolásához az sp_cofigure parancsot kell végrehajtanunk, paraméterként átadva a 64+256 (=320) eredményét: sp_configure ‘user options’, 320. Az egyes konfigurációkhoz rendelt azonosítók teljes listáját a http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_9b1q.asp.

Az 1. táblázatban szereplő egyes paraméterek állapotának megerősítéséhez használja a SessionProperty(‘parameter name’) függvényt vagy a DBCC UserOptions parancsot.

Ezzel a módszerrel állítsa be az összes paramétert az 1. táblázat “indexelt nézetekhez szükséges állapot” oszlopának megfelelően – ha ez nem történik meg, az SQL Server nem fogja engedélyezni az indexelt nézet létrehozását/futtatását.

Az indexelt nézet létrehozása

Létrehozunk egy nézetet a NorthWind adatbázisban található Order Details táblában található napi eladott összeg összesítésére. Lásd a 3. listát.

use NorthWind go create view vi_vendas_mes with SchemaBinding as select ano = datepart(yyyy,OrderDate), mes = datepart(mm,OrderDate), qtde_total = sum(Quantity), contador = count_big(*) from dbo.Orders o inner join dbo. od on o.OrderId = od.OrderId group by datepart(yyyy,o.OrderDate),datepart(mm,o.OrderDate) go

Az indexelt nézetek létrehozásakor néhány sajátosságot kell betartanunk:

• A nézetnek determinisztikusnak kell lennie. Egy nézet akkor lesz determinisztikus, ha csak determinisztikus függvényeket használunk a kódjában. Ugyanaz a SELECT parancs ismételt végrehajtása egy indexelt nézeten (statikus alapot figyelembe véve) nem adhat különböző eredményeket. A determinisztikus függvények biztosítják, hogy a függvény eredménye változatlan marad, függetlenül attól, hogy hányszor hajtják végre. A DatePart függvény például determinisztikus, mivel mindig ugyanazt az eredményt adja vissza egy adott dátumra. A getdate() függvény minden egyes végrehajtásakor más értéket ad vissza. Az SQL Server 2000 determinisztikus függvények teljes listáját a http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_95v7.asp
• oldalon találja meg.Ellenőrizze, hogy nincsenek-e szintaktikai korlátozások. Az alább felsorolt záradékok, függvények és lekérdezéstípusok nem integrálhatják egy indexelt nézet sémáját:
  • MIN, MAX,TOP
  • VARIANCE,STDEV,AVG
  • COUNT(*)
  • SUM null értékeket megengedő oszlopokon
  • DISTINCT
  • ROWSET függvény
  • Derived tables, self joins, subqueries, outer joins
  • DISTINCT
  • UNION
  • Float, text, ntext és image
  • COMPUTE és COMPUTE BY
  • HAVING, CUBE és ROLLUP

Az indexelt nézeteket SchemaBinding segítségével kell létrehozni. A nézet tartalmának konzisztenciája érdekében nem módosíthatja a nézetet létrehozó táblák szerkezetét. Az ilyen jellegű problémák elkerülése érdekében indexelt nézetek létrehozásakor kötelező a SchemaBinding használata, mivel ez az opció nem teszi lehetővé a táblaszerkezet megváltoztatását a nézet előzetes törlése nélkül.

A GROUP BY záradék használatához kötelező a COUNT_BIG(*) függvény használata. A count_big(*) függvény ugyanazt teszi, mint a count(*), de egy bigint (8 bájt) típusú értéket ad vissza.

Az indexelt nézetben hivatkozott objektumok tulajdonosát mindig tájékoztassa. Használja a select * from dbo.Orders helyett a select * from Orders parancsot, mivel lehetséges, hogy azonos nevű, de különböző tulajdonosú táblák vannak. Mivel a schemabinding opció kötelező, az SQL Server-nek szüksége van a pontos objektum specifikációra a séma módosításának megfékezéséhez.

Fürtindex létrehozása a nézetben (materializálás)

A 2. pontban létrehozott nézet még nem viselkedik indexelt nézetként, mivel a select parancs eredménye még nem materializálódott táblává. Ezt az állítást megerősíthetjük a Query Analyzerben a sp_spaceused parancs futtatásával, amely visszaadja a táblák által használt sorok számát és helyet (4. lista).

sp_SpaceUsed vi_vendas_mes -------------------------------------------------------------------------------------- Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91 Views do not have space allocated.

Set statistics io ON Select * from vi_vendas_mesgo---------------------------------------------------------ano mes qtde_total contador ----------- ----------- ------------- -------------------- 1996 7 1462 591996 8 1322 691996 9 1124 57.....(23 row(s) affected)Table 'Order Details'. Scan count 830, logical reads 1672, physical reads 0, read-ahead reads 0.Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.

Az ellenőrzéshez, hogy a nézet indexelhető (materializált)-e, azaz az indexelt nézetekhez szükséges szabványok és beállítások betartásával készült-e, a következő SELECT eredményének egyenlőnek kell lennie 1-gyel.

select ObjectProperty(object_id('vi_vendas_mes'),'IsIndexable')

Az előfeltételeket megerősítve most már létrehozhatjuk az indexet. A szintaxis ugyanaz a formátum, mint a hagyományos táblák indexeinek létrehozásakor:

create unique clustered index pk_ano_mes on vi_vendas_mes (ano,mes)

Megjegyezzük, hogy a fürtindexre azért van szükség, mert oldalakat generál. Az indexelt nézetekben csak a fürtindex létrehozása után hozhat létre nem fürtindexeket.

Most a nézetben talált SELECT materializálódott, ami a Query Analyzer sp_spaceused parancsával bizonyítható (6. lista).

sp_SpaceUsed vi_vendas_mêsgo-----------------------------------------------------Name Rows Reserved data index_size Unusedvi_vendas_mes 23 24 KB 8 KB 16 KB 0 KB

Indexelt nézetek használata

Az indexelt nézetek (és a hagyományos nézetek) elérésének egyik módja, hogy a SELECT parancsban hivatkozunk a nevére:

select * from vi_vendas_mes

Hasonlítsuk össze az 5. listában mozgó oldalak mennyiségét (1672+4=1676) a 7. listában (2+0=2). A különbség igen jelentős – az indexelt nézet létrehozása 1674 oldallal csökkentette az összes szükséges I/O-t.

Set statistics io ON select * from vi_vendas_mes go --------------------------------------------------------- ano mes qtde_total contador ----------- ----------- ------------- -------------------- 1996 7 1462 59 1996 9 1124 57 ..... (23 row(s) affected) Table 'vi_vendas_mes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Nézzünk egy másik példát. Az 1. ábra egy lekérdezés végrehajtási tervét mutatja. Megerősíti, hogy a nézet akkor is ki lett választva, ha nem volt jelen a SELECT sorban.

A lekérdezés végrehajtási tervének összeállítása során az optimalizáló megállapította, hogy a vi_sales_mes-ben már vannak előre összesített adatok a lekérdezéshez, és úgy döntött, hogy az adatokat közvetlenül az indexelt nézetben választja ki.

Megjegyezzük, hogy az 1. ábrán végrehajtott lekérdezés megegyezik a vi_sales_mes nézetben találhatóval. Az optimalizáló által a nézethez való hozzáférés azonban független a végrehajtott lekérdezés és a nézeti lekérdezés közötti hasonlóságtól. A lekérdezés feldolgozója az indexelt nézet kiválasztásakor csak a költség-haszon arányt veszi figyelembe. Ily módon a végrehajtott lekérdezéseknek nem kell azonosnak lenniük a nézettel (lásd a 3. ábrát).

Az indexelt nézetnek azonban követnie kell néhány szabályt ahhoz, hogy a lekérdezésoptimalizáló figyelembe vegye azt:

• A nézetben lévő joinnak “benne kell lennie” a lekérdezésben: ha a lekérdezés A és B táblák között végez egy join-t, a nézet pedig A és C között, akkor a nézet nem fog szerepelni a végrehajtási tervben. Ha azonban a lekérdezés az A,B és C közötti összekapcsolást hajtja végre, a nézet kiváltható.
• A lekérdezésben meghatározott feltételeknek meg kell egyezniük a nézetben szereplő feltételekkel: a 2. ábrán látható SELECT-ben a vi_sales_mes indexelt nézet nem lesz figyelembe véve, mert a nézet kódjában nem volt jelen a where záradék, ami miatt a mennyiségi <= 5 sorok kiszámítására került sor az összekapcsolás során.

Másrészt, ha a lekérdezésben a where feltétel where sum(Quantity) > 5, akkor a vi_sales_mes nézetet figyelembe vesszük a végrehajtási tervben, mivel a lekérdezési feltétel a nézetben jelen lévő SELECT részhalmaza.

A lekérdezésben szereplő aggregációs funkcióval rendelkező oszlopoknak “benne kell lenniük” a nézetdefinícióban: ha a nézet a qtde=sum(Mennyiség) oszlopot adja vissza, és a lekérdezésben van egy vlr_unit=sum(Egységár) oszlop, akkor a nézet nem lesz figyelembe véve.

A 3. ábra egy SELECT parancsot mutat, amely lehetővé teszi a parancsoptimalizáló intelligenciájának bizonyítását – az AVG(Mennyiség) számítás helyébe a SUM(Mennyiség) / Count_Big(*) osztás lépett, amelyet a Compute Scalar ikon ábrázol. A where sum(Quantity) > 1500 predikátumot (amelyet a Szűrő ikon ábrázol) is figyelembe vesszük.

Általános megfontolások az indexelt nézetek használatával kapcsolatban:

• Az indexelt nézetek az SQL Server 2000 bármely verziójában létrehozhatók. A lekérdezésoptimalizáló azonban csak az Enterprise Edition verzióban választja ki őket automatikusan.
• Az Enterprise verziótól eltérő verziókban a NoExpand súgót kell használnia ahhoz, hogy az indexelt nézetet hagyományos táblaként érje el. Ha a NoExpand funkciót nem használja, az indexelt nézet “normál” nézetnek minősül.
  Megjegyzés: Az Expand súgó a NoExpand fordítottját teszi: az indexelt nézetet “normál” nézetként kezeli, és arra kényszeríti az SQL Server-t, hogy a futási fázisban hajtsa végre a SELECT utasítást.
• Az indexelt nézet karbantartása automatikus (mint az indexek esetében); nem igényel további szinkronizálást. Saját tulajdonságánál fogva (általában előre összegzett adatokat tárol), frissítése általában valamivel lassabb, mint a hagyományos indexeké.
• Az indexelt nézetek használata az OLTP bázisokon óvatosságot igényel, mert bár a lekérdezésekben nagy teljesítményt nyújtanak, a nézetben kapcsolódó táblákat módosító folyamatokban túlterhelést okoznak. Azokban a helyzetekben, ahol nagy írási teljesítményre van szükség, gyakori frissítésekkel, az indexelt nézetek létrehozása nem ajánlott.
• Az indexekhez hasonlóan az optimalizáló az Enterprise verziókban lévő nézeti kódot is elemzi a lekérdezés legjobb végrehajtási tervének kiválasztása során. Ha azonban ugyanazon lekérdezéshez több indexelt nézet is lefuttatható, a választási idő jelentősen megnövekedhet, mivel az összes nézet elemzése megtörténik. Ezért a nézetek implementálásakor használjon józan észt.

Szükséges beállítások az indexelt nézetekhez

ANSI_NULLS

Meghatározza, hogy a null értékekkel való összehasonlítások hogyan történjenek (8. lista).

set ANSI_NULLS ON declare @var char(10) set @var = null if @var = null print 'VERDADEIRO' else print 'FALSO' ------------------------------------- FALSO set ANSI_NULLS OFF declare @var char(10) set @var = null if @var = null print 'VERDADEIRO' else print 'FALSO' -------------------------------------- VERDADEIRO