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
- 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:
- 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;
- 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;
- 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.
beállítás | Id (*) | Szükséges állapot az indexelt nézetekhez | SQL Server 2000 alapértelmezett | OLE DB (=ADO) vagy ODBC kapcsolatoknál alapértelmezett | DB könyvtárat használó kapcsolatoknál alapértelmezett | ||
---|---|---|---|---|---|---|---|
ANSI_NULLS | 32 | ON | OFF | ON | OFF | ||
ANSI_PADDING | 16 | ON | ON | ON | OFF | ||
ANSI_WARNING | 8 | ON | ON | OFF | ON | OFF | |
ARITHABORT | 64 | ON | OFF | OFF | OFF | OFF | OFF |
CONCAT_NULL_YIELDS_NULL | 4096 | ON | OFF | ON | OFF | ON | OFF |
QUOTED_IDENTIFIER | 256 | ON | OFF | ON | OFF | ||
NUMERIC_ROUNDABORT | 8192 | OFF | OFF | OFF | OFF | OFF |
(*) 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.
Az 5. listában megfigyelhető, hogy a nézet feldolgozása tisztán logikai, olyannyira, hogy a Physical Reads értéke nulla. Figyeljük meg a Logikai olvasás és a Fizikai olvasás értékeit (1672+0+4+0+0=1676) – ezeket az értékeket fogjuk használni a későbbi összehasonlításainkban.
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
ANSI_PADDING
Meghatározza, hogy a char, varchar, binary és varbinary oszlopokat hogyan kell tárolni, ha tartalmuk kisebb, mint a táblaszerkezetben meghatározott méret. Az SQL Server 2000 alapértelmezett beállítása az ansi_padding bekapcsolva tartása (=ON); ebben a feltételben a következő szabályok érvényesek:
- A char oszlopok frissítésekor a karakterlánc végére szóköz kerül, ha az kisebb, mint az oszlopszerkezetben meghatározott méret. Ugyanez a szabály vonatkozik a bináris oszlopokra is (ebben az esetben a helyet nullák sorozata tölti ki)
- A vararchar vagy varbinary oszlopok nem követik a fenti szabályt: ezek mindig megtartják eredeti méretüket.
ARITHABORT
Az engedélyezés esetén a nullával való osztás vagy valamilyen túlcsordulás esetén megszakítja a lekérdezés végrehajtását.
QUOTED_IDENTIFIER
Az engedélyezés esetén lehetővé teszi a dupla idézőjelek használatát a táblázatok, oszlopok stb. nevének megadásához. – Így ezek a nevek tartalmazhatnak szóközöket és speciális karaktereket.
CONCAT_NULL_YELDS_NULL
A null értékű karakterláncok összekapcsolásának eredményét szabályozza. Ha engedélyezve van, meghatározza, hogy ennek a csatlakozásnak null értéket kell visszaadnia; ellenkező esetben magát a karakterláncot adja vissza.
ANSI_WARNINGS
Hibaüzenetek generálását határozza meg, ha:
- összesítési függvényeket használ és null értékeket talál a lekérdezési tartományban;
- nullával való osztást vagy aritmetikai túlcsordulást talál.
NUMERIC_ROUNDABORT
Vezérli, hogy az SQL Server hogyan járjon el, ha aritmetikai műveletek során a numerikus pontosság elvesztésével találkozik. Ha a paraméter engedélyezve van, és egy két tizedesjegy pontosságú változó három tizedesjegy pontosságú értéket kap, a művelet megszakad. Ha a paraméter ki van kapcsolva, az értéket két tizedesjegyre vágja le.
Következtetés
A lekérdezés optimalizálásakor az indexelt nézetek jó választásnak bizonyulnak a teljesítmény kihasználásához. Tehát alaposan értékelje az összegzésekkel foglalkozó és bizonyos gyakorisággal végrehajtott lekérdezéseket, és hozzon létre indexelt nézeteket. Az eredmény megéri!