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
1. lista. Nézetek létrehozása és használata

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
2. lista. Példa a concat_null_yields_null beállításra

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
1. táblázat. A nézet eredményét befolyásoló beállítások

(*) 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
Listing 3. Nézet az összes eladott mennyiségre

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.
4. lista A sp_spaceused parancs használata egy nézeten

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.
Listing 5. A nézethez tartozó összes I/O a fürtindex létrehozása ELŐTT

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
6. lista . Az sp_spaceused parancs használata indexelt nézeten

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.
7. lista A nézethez tartozó összes I/O az indexfürt létrehozása után

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.

1. ábra. A létrehozott indexelt nézethez hozzáférő lekérdezés végrehajtási terve

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.

2. ábra A létrehozott indexelt nézethez való hozzáférés nélküli lekérdezés végrehajtási terve

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.

3. ábra “Általános” kiválasztás indexelt nézettel

Á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
8. lista. Példák az ansi_null

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:

  1. összesítési függvényeket használ és null értékeket talál a lekérdezési tartományban;
  2. 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!

Vélemény, hozzászólás?

Az e-mail-címet nem tesszük közzé.