SQL Server indexek - az adatbázis optimális teljesítményének kulcsa

A legtöbb komoly üzleti alkalmazás továbbra is olyan relációs adatbázis-rendszereket használ, mint például az SQL Server az adattároláshoz. Annak érdekében, hogy az ott tárolt adatok is nagy teljesítmény mellett érhetők el, az indexek nagyon meghatározó tényezők - az adatbázis kialakításától a lekérdezésig. Az MS SQL Server azonban mostantól különféle indexformák egész sorát kínálja: A klasszikus csoportosított és nem csoportosított indexek mellett vannak olyan indexek, amelyek speciális alkalmazási területekre vonatkoznak, például teljes szövegű indexek, XML indexek és térbeli indexek. Ezenkívül később felkerültek az eredetileg adattárházakhoz tervezett oszlopáruház-indexek, amelyek legkésőbb a harmadik generációban más területeken is hatékonyan használhatók.

Általános az indexek működése

Az indexek működésének jobb megértése érdekében hasznos megérteni, hogy az SQL Server hogyan tárolja az adatokat a lemezen. Az adatokat .mdf vagy .ndf kiterjesztésű fájlokban tárolják. Ezek egyenként 8 KB méretű memóriaoldalakra vannak felosztva, amely egyben a legkisebb egység, amelyben az SQL Server adatokat olvas vagy ír. Nyolc egymást követő memóriaoldal mindegyike úgynevezett mértékű. Míg a memóriaoldal csak egy táblázat adatait tartalmazza, a terjedelem egyenletes és vegyes, amelyek különböző táblák memórialapjait tartalmazzák. Például, ha egy lekérdezés egy adott vezetéknévvel rendelkező személyt olvas le egy tábláról, amelynek nincs indexe, akkor el kell olvasnia az összes olyan memória oldalt, amely az adott táblán adatokat tartalmaz.

Annak érdekében, hogy ezeket a lekérdezéseket hatékonyabban lehessen végrehajtani, megfelelő struktúrák hasznosak, amelyek biztosítják, hogy a releváns memóriaoldalak gyorsabban megtalálhatók legyenek, és így ne minden memóriaoldal olvasható legyen. Az indexek pontosan ezt teszik.

Fürtözött indexek

A legegyszerűbb esetben maga a táblázat egy adott oszlop szerint van rendezve (a személytábla esetében például a vezetéknév szerint). A fürtözött index pontosan ezt teszi. Ez azonban nem egy egyszerű lista formájában történik, hanem egy bináris fa formájában, amelyben a fa minden csomópontja a saját memória oldalát használja, amely tartalmazza a betűrendben rendezett névlistát, kiegészítve a memória oldalra való hivatkozással, amelyben tovább keresse meg ezt a nevet (vagy az azt betűrendben követő nevet). Ez - a táblázatban szereplő adatrekordok számától függően - több szakaszban folytatódhat, míg végül hivatkozás történik a memória oldalra, ahol a teljes személyes adatot tárolják. Ez azt jelenti, hogy csak néhány adatlapot kell elolvasni, mielőtt a megfelelő táblázat (ok) megtalálhatók.

Míg egy vezetéknév minden bizonnyal többször is megjelenhet a személyek táblázatában, a gyakorlatban a csoportosított indexhez általában egyedi oszlopot használnak, amely gyakran megfelel a táblázat elsődleges kulcsának, vagyis annak a kulcsnak, amellyel a táblázat összes sora egyértelműen azonosítható. Ezért egy elsődleges kulcs létrehozásakor implicit módon egy csoportosított index jön létre, hacsak a NONCLUSTERED kulcsszó nincs kifejezetten megadva (1. felsorolás).

A fürtözött index leggyakoribb változata az int típusú (vagy nagy tábláknál bigint) Id oszlop, azonosság-specifikációval, amely biztosítja, hogy az oszlop automatikusan egyedi számokat kapjon. Alternatív megoldásként gyakran használják az egyedi azonosító típusú oszlopokat, amelyek szintén egyedi - de nem folyamatos - értékeket kapnak a NewId () függvényen keresztül alapértelmezett korlátozásként. Ez különösen hasznos elosztott környezetekben, ahol új sorok jönnek létre különböző adatbázis-másolatokban, amelyeket később egyesítenek az elsődleges kulcsok átfedése nélkül.
A klaszterezett indexek fő hátránya, hogy csak egy sorrendben rendezhetők. Egy másik oszlopban (például keresztnévben) kereső lekérdezés nem részesül előnyben a Last Name oszlop indexéből. Ennek megoldására n csoportosítatlan indexet használnak.

A végrehajtási tervek megjelenítése

Az indexek lekérdezésekben történő használatának ellenőrzése érdekében az SQL Server Management Studio lehetőséget kínál a lekérdezések várható és tényleges végrehajtási terveinek megjelenítésére. Mindkét opció a legkönnyebben aktiválható a lekérdezés menü megfelelő opcióival vagy az eszköztáron keresztül. Meg kell jegyezni, hogy a várható végrehajtási terv megjelenítése nem hajtja végre magát a lekérdezést, míg a tényleges végrehajtási terv csak a lekérdezés végrehajtása után jelenhet meg. A grafikusan megjelenített végrehajtási tervekben, amelyek a jobb felső sarokból olvashatók, gyorsan láthatja, hogy (és hogyan) hozzáférnek egy indexhez vagy magához a táblához. A „hogyan” kifejezésnél meg kell jegyezni a „keresés” és a „keresés” kulcsszavakat. Az indexkeresés azt jelenti, hogy a releváns sorokat bináris keresés találja meg az indexfában, míg a beolvasás azt jelzi, hogy a táblázat vagy az index teljesen el lett olvasva, ami a legtöbb esetben lényegesen időigényesebb. Részletesebb információk a megvalósítási tervek olvasásáról a cikk hivatkozásaiban találhatók [1], [2], [3].

Nem fürtözött indexek

A nem csoportosított indexek egy további adatstruktúrát képviselnek, amelyet szintén egy fa struktúrában kezelnek, de amelynek levelei nem a tényleges soradatokat, hanem hivatkozást tartalmaznak az adatok címére. Leegyszerűsítve: egy nem csoportosított index úgy viselkedik, mint egy könyv végén található index: önmagát rendezi, így gyorsan kereshet egy adott kifejezést. Miután megtalálta a kifejezést, csak azt az oldalszámot követi, ahol a kifejezés megtalálható. A kívánt információ megtalálásához további módosítás szükséges a vonatkozó könyvoldalon. Az adatbázis indexével a valós adatokra való hivatkozás természetesen egy kicsit összetettebb, mint egy egyszerű oldalszám. Ez a sorkeresésként ismert folyamat egy sorazonosítót (röviden RID-t) használ, amely három részből áll:

Az adatbázis fájl száma (mert egy tábla több fájlra osztható)
A memória oldal száma
Az ezen a memória oldalon található adatrekord száma

Ez azonban csak akkor érvényes, ha a táblának nincs csoportosított indexe, ezért rendezetlen "halomként" tárolják. Ha viszont létezik csoportosított index, akkor a csoportosított index kulcsértékét a nem csoportosított index levélszintjére való hivatkozásként adjuk meg. Egy másik indexfát be kell járni, mielőtt a tényleges adatok megtalálhatók lennének. (Az ennek az úgynevezett kulcsfelmérésnek a további erőfeszítései azonban általában meglehetősen alacsonyak.)

1. ábra: Indexfa egy csoportosított index nélküli táblához

A nem csoportosított indexek nagy előnye, hogy egy táblához több is lehet, amelyek különböző oszlopok szerint vannak rendezve. Ezután kereshet különféle kritériumokat nagy teljesítmény mellett1. ábra).

Mivel a nem csoportosított indexek kiegészítő adatstruktúrák, mindig tisztában kell lenni azzal a ténnyel, hogy egyrészt további memóriára van szükségük, másrészt minden egyes alkalommal, amikor magát az adatot megváltoztatják, minden indexet, amelyben a megváltozott oszlopok találhatók, frissíteni kell. Ha lehetséges, ne hozzon létre külön indexet a táblázat minden oszlopához, hanem mindig mérlegelje, hogy melyik oszlopnak van értelme az indexnek, és hol lehet eltekinteni attól. Alapvető szabály, hogy emlékezhet arra, hogy a további indexek felgyorsíthatják az olvasási hozzáférést, de lassíthatják az íráshoz való hozzáférést. A következő kritériumok alapján dönthetünk arról, hogy melyik oszlopnak van értelme az indexnek:

Ha az oszlopot gyakran keresik vagy szűrik, akkor ez egy index mellett szól.
Ugyanez vonatkozik azokra az oszlopokra, amelyeket idegen kulcs oszlopként használnak, mivel ezeket a JOIN során szűrik.
Ha az oszlop csak néhány különböző értéket tartalmaz (az egyik alacsony szelektivitásról is beszél), akkor ez inkább egy index ellen szól. Ez különösen igaz a bit adattípust használó oszlopokra.
Ha a táblázatot többnyire elolvassák és csak ritkán változtatják meg, akkor több index elfogadható.
Ha a táblázatot elsősorban írják (pl. Naplótábla), és csak ritkán olvassa el, akkor az indexek számát a lehető legkisebbnek kell tartani.

Kombinált indexek

Az egyik módszer a kezelt indexek számának alacsony szinten tartására, de az indexek minél hatékonyabb felhasználhatóságára is, több oszlopon definiált kombinált indexek használata. Például, ha egy személytáblában külön index található az utónév és a vezetéknév oszlopokhoz, a következő lekérdezés aligha fogja tudni használni mindkét indexet:

KIVÁLASZTÁS * FROM Person.Person

WHERE keresztnév = 'János' ÉS vezetéknév = 'fa'

Ehelyett az SQL Server automatikusan azt az indexet fogja használni, amelytől az alacsonyabb elolvasandó oldalak száma várható (a nagyobb szelektivitású oszlop indexe). Mivel azonban mind a keresztnév, mind a vezetéknév lehet több bejegyzés, még hatékonyabb lenne egy index, amely mindkét oszlopot tartalmazza, vagyis egy kombinált index, amely a következőképpen hozható létre:

CREATE NONCLUSTERED INDEX IX_Person_LastName_FirstName

ON Person.Person (vezetéknév, keresztnév)

Míg az index neve (IX_Person_LastName_FirstName) csak elterjedt elnevezési szokás, addig az indexdefiníció oszlopainak sorrendje meghatározó jelentőségű. A vezetéknév első helyezésével az index akkor is hatékonyan használható, ha egy lekérdezés csak a vezetéknevet keresi (mivel ez az index elsődleges rendezési kritériuma). Ha ehelyett csak keresztnevet keres, akkor az index is használható, de nem a bináris indexfán (Indexkeresés) keresztüli tényleges hozzáférés révén. Ehelyett a teljes indexet el kell olvasni (index szkennelés), hogy megtalálja az összes lehetséges kombinációt, amely tartalmazza a keresett keresztnevet (ami a legtöbb esetben még mindig hatékonyabb, mint a teljes tábla index nélküli olvasása).

Mivel a gyakorlatban a keresztnévre való keresés minden bizonnyal sokkal ritkábban fordul elő, mint a vezetéknév keresése, ajánlott a fent kiválasztott oszlopok sorrendje, és ennek olyan mellékhatása van, amelyet a keresztnév és vezetéknév oszlopok külön indexei nélkül is megtehet.

Tárgymutatók és oszlopok

A nem csoportosított indexek magyarázatakor rámutattak arra, hogy a bináris indexfa levélszintjén találhatók tényleges adatrekordok (vagy csoportos indexkulcs formájában, vagy többrészes címként, amely fájlszámból, memóriaoldalból és sorszámból áll). Ideális esetben azonban az összes lekérdezett oszlop magában az indexben található, ezért nem szükséges követni ezt a hivatkozást. Ha a következő lekérdezést egy meglévő kombinált mutatóval hajtják végre a LastName és FirstName oszlopokban:

akkor az index a LastName oszlop segítségével kereshető, de már tartalmazza a lekérdezett LastName-t is, így a teljes adatsor normál esetben szükséges keresése elhagyható. Ezután egy lefedő indexről beszélünk (a lekérdezéshez viszonyítva), mivel ez lefedi a lekérdezés összes oszlopát.

Annak érdekében, hogy a fedőindex előnyeit minél gyakrabban kihasználhassuk anélkül, hogy túl gyakran kellene az indexet átrendezni, oszlopok integrálhatók az indexbe úgynevezett „oszlopként”. Ez azt jelenti, hogy ezen oszlopok értékei az indexfa levélszintjén találhatók, de az index rendezésénél nem veszik őket figyelembe (ezért elegendő, ha az oszlopok csak a levelek szintjén, és a fenti csomópontokon nem léteznek). Az utoljára megjelenített lekérdezéshez a következő index elegendő:

Több olyan oszlop is meghatározható, amelyek sorrendje akkor lényegtelen, mivel nem relevánsak a rendezés szempontjából. Ha az összes oszlopot felvenné az indexbe (akár válogatás céljából, akár oszlopokként), akkor csoportosított indexet szimulált volna, de azzal a hátránnyal, hogy az adatsorra való hivatkozás számára a helyet akkor használják. ami ebben a változatban felesleges, mivel teljesen benne van az indexben. Tehát ez biztosan nem ajánlott, főleg, hogy az indexeket annál hatékonyabban lehet használni, minél kisebbek.

Szűrt indexek

Az SQL Server 2008 óta van egy másik lehetőség a szűrt indexekkel, hogy az index minél kompaktabb legyen, és ezzel minimalizálja az olvasandó memóriaoldalak számát. Az indexet WHERE záradék egészíti ki, így az indexet csak azokhoz a sorokhoz kell létrehozni, amelyek megfelelnek a megadott feltételnek. Ez azt a korlátozást eredményezi, hogy a szűrési feltételek csak nem csoportosított indexekkel együtt használhatók.

A szűrt indexek tipikus használati esete az olyan oszlopokkal rendelkező táblázatok, amelyek nincsenek kitöltve a sorok nagy részében:

HOL A MiddleName NEM NULL

Indexelt nézetek

A 2005-ös verzió óta az SQL Server felajánlotta a nézetek alapján indexek létrehozásának lehetőségét is. Ennek eredményeként a nézet által kért adatok feleslegesen másolatként kerülnek mentésre, de az index kritériumai szerint vannak rendezve. Ezt a fogalmat ezért gyakran materializált nézetnek nevezik (ez a kifejezés különösen gyakori az Oracle környezetben). Az ilyen indexelt nézet fő előnye, hogy az összes lekérdezendő adat már össze van kapcsolva és a megfelelő rendezési sorrendben van. Így a lekérdezésnek csak akkor kell hozzáférnie az indexelt nézethez, anélkül, hogy először egyesítené az adatokat több táblából a JOIN feltételek használatával. A további sorok vagy kulcsok keresése szintén elmarad, mert - amennyiben a nézet ennek megfelelően jön létre - az összes lekérdezendő oszlop benne van a nézetben, így a rajta lévő indexben is.

Az indexelt nézetek használatához azonban néhány korlátozást kell betartani:

Magát a nézetet a WITH SCHEMABINDING opcióval kell létrehozni, amely megakadályozza a nézetben használt oszlopok adatstruktúrájának megváltoztatását.
A nézet indexét UNIQUE CLUSTERED INDEX néven kell létrehozni.
Az indexet nem szabad szűrni, de ez nem jelent valódi korlátozást, mivel egy szűrőkritérium könnyen beilleszthető az index által használt nézetbe.

2. felsorolás megmutatja, hogyan hozható létre egy indexelt nézet két lépésben. Miután a nézet elkészült a SCHEMABINDING kiegészítéssel, ehhez a nézethez létrejön az UNIQUE CLUSTERED INDEX. Ha most egy egyszerű lekérdezést hajt végre a nézeten, a végrehajtási terv alapján megállapíthatja, hogy csak magát az indexet olvasta fel, és nem a mögötte lévő táblázatokat:

Az indexelt nézetek érdekes mellékhatása, hogy akkor is használhatók, ha a mögöttük lévő táblázatok nem kifejezetten, hanem inkább a nézetek vannak címezve. Ez könnyen ellenőrizhető a következő lekérdezés végrehajtási tervének megnézésével:

A végrehajtási terv készítésekor az SQL Server automatikusan felismeri, hogy van egy megfelelő indexelt nézet, amelyhez gyorsabban lehet hozzáférni, mint az alkalmazott táblák és indexeik.

Következtetés

A cikksorozat első részében elkészültek az alapok, és áttekintést kaptak a régóta elérhető "klasszikus" indexváltozatokról. A sorozat következő része az indexek karbantartását és néhány speciálisabb opciót vizsgál, például az index tömörítését.

Linkek és irodalom

[1] Panther, Robert: "SQL lekérdezések optimalizálása", entwickler.press, 2014. június, ISBN: 978-3868021233

[2] Panther, Robert: "SQL Server Performance Ratgeber", entwickler.press, 2010. február, ISBN: 978-3868020304

[3] Fritchey, Grant: "SQL Server futtatási tervek", Simple Talk Publishing, 2012. október, ISBN: 978-smartblock>

Windows Developer

Ezt a cikket a Windows Developer publikálta. A Windows Developer átfogó, gyártótól független információt nyújt a szoftver- és rendszerfejlesztés új trendjeiről és lehetőségeiről a Microsoft technológiáinak minden vonatkozásában.