Indexy servera SQL Server - kľúč k optimálnemu výkonu databázy

Najvážnejšie obchodné aplikácie stále používajú na ukladanie údajov systémy relačných databáz, ako je napríklad SQL Server. Aby údaje, ktoré sú tam uložené, boli prístupné aj s vysokým výkonom, sú indexy veľmi rozhodujúcim faktorom - od návrhu databázy po dopyt. MS SQL Server však teraz ponúka celú škálu rôznych foriem indexov: Okrem klasických zoskupených a nezoskupených indexov existujú indexy pre špeciálne oblasti použitia, ako sú napríklad fulltextové indexy, indexy XML a priestorové indexy. Ďalej boli neskôr pridané indexy columnstore pôvodne určené pre dátové sklady, ktoré je možné najneskôr v tretej generácii efektívne využiť aj pre ďalšie oblasti.

Všeobecné fungovanie indexov

Aby ste lepšie pochopili, ako fungujú indexy, je užitočné pochopiť, ako server SQL Server ukladá údaje na disk. Údaje sa ukladajú do súborov s príponou .mdf alebo .ndf. Tieto sú rozdelené na pamäťové stránky s veľkosťou po 8 kB, čo je tiež najmenšia jednotka, v ktorej server SQL Server číta alebo zapisuje údaje. Osem po sebe nasledujúcich pamäťových stránok tvorí každá takzvaný rozsah. Zatiaľ čo pamäťová stránka obsahuje iba údaje z jednej tabuľky, rozsahy sú jednotné aj zmiešané, ktoré obsahujú pamäťové stránky z rôznych tabuliek. Napríklad, ak dopyt načíta všetky osoby s konkrétnym priezviskom z tabuľky, ktorá nemá index, musí načítať všetky pamäťové stránky, ktoré obsahujú údaje v tejto tabuľke.

Aby bolo možné tieto dotazy vykonávať efektívnejšie, sú užitočné zodpovedajúce štruktúry, ktoré zaisťujú rýchlejšie nájdenie príslušných pamäťových stránok, a teda nie všetky pamäťové stránky. Presne to robia indexy.

Klastrované indexy

V najjednoduchšom prípade je samotná tabuľka zoradená podľa konkrétneho stĺpca (v prípade tabuľky osôb napríklad podľa priezviska). Presne to robí klastrovaný index. Toto sa však nerobí vo forme jednoduchého zoznamu, ale vo forme binárneho stromu, v ktorom každý uzol stromu používa svoju vlastnú stránku pamäte, ktorá obsahuje abecedne zoradený zoznam mien, doplnený odkazom na stránku pamäte, v ktorej ďalej na vyhľadajte toto meno (alebo meno, ktoré nasleduje za ním podľa abecedy). To môže - v závislosti od počtu dátových záznamov v tabuľke - pokračovať v niekoľkých fázach, až kým sa nakoniec neodkáže na pamäťovú stránku, na ktorej sú uložené úplné osobné údaje. To znamená, že predtým, ako sa nájdu príslušné riadky, je potrebné prečítať iba niekoľko údajových stránok.

Zatiaľ čo sa priezvisko môže v tabuľke osôb určite vyskytnúť niekoľkokrát, v praxi sa pre zoskupený index zvyčajne používa jedinečný stĺpec, ktorý často zodpovedá primárnemu kľúču tabuľky, t. J. Kľúču, pomocou ktorého je možné jasne identifikovať všetky riadky tabuľky. Preto sa pri vytváraní primárneho kľúča implicitne vygeneruje zoskupený index, pokiaľ nie je výslovne uvedené kľúčové slovo NONCLUSTERED (Zoznam 1).

Najbežnejšou variantou pre klastrovaný index je stĺpec Id typu int (alebo bigint pre veľké tabuľky) so špecifikáciou identity, ktorá zaručuje, že stĺpcu sú automaticky priradené jedinečné čísla. Ako alternatíva sa často používajú stĺpce typu uniqueidentifier, ktoré sa tiež ako predvolené obmedzenie dodávajú s jedinečnými - ale nie spojitými - hodnotami prostredníctvom funkcie NewId (). To je obzvlášť užitočné v distribuovaných prostrediach, kde sa vytvárajú nové riadky v rôznych kópiách databázy, ktoré sa potom zlúčia neskôr bez toho, aby sa prekrývali primárne kľúče.
Veľkou nevýhodou zoskupených indexov je, že sa dajú triediť iba v jednom poradí. Dotaz, ktorý vyhľadáva v inom stĺpci (napríklad krstnom mene), nebude mať prospech z indexu v stĺpci Priezvisko. Na vyriešenie tohto problému sa používa n nezoskupených indexov.

Zobrazenie plánov vykonania

Za účelom kontroly použitia indexov v dotazoch ponúka SQL Server Management Studio možnosť zobraziť očakávané aj skutočné plány vykonávania dotazov. Obe možnosti sa dajú najľahšie aktivovať prostredníctvom zodpovedajúcich možností v ponuke dotazov alebo alternatívne prostredníctvom panela nástrojov. Je potrebné poznamenať, že zobrazenie očakávaného plánu vykonania nevykoná samotný dotaz, zatiaľ čo skutočný plán vykonania je možné zobraziť až po vykonaní dotazu. V graficky zobrazených plánoch vykonávania, ktoré je možné čítať vpravo hore, môžete rýchlo zistiť, či (a ako) sa pristupuje k indexu alebo k samotnej tabuľke. Pri „ako“ by sa mali zaznamenať kľúčové slová „skenovať“ a „hľadať“. Hľadanie indexu znamená, že príslušné riadky sa nájdu binárnym vyhľadávaním v indexovom strome, zatiaľ čo skenovanie indikuje úplné prečítanie tabuľky alebo indexu, čo je vo väčšine prípadov výrazne časovo náročnejšie. Podrobnejšie informácie o plánoch implementácie nájdete v odkazoch na tento článok [1], [2], [3].

Nezoskupené indexy

Nezoskupené indexy predstavujú ďalšiu dátovú štruktúru, ktorá sa tiež spravuje v stromovej štruktúre, ktorej listy však neobsahujú skutočné riadkové údaje, ale odkaz na adresu údajov. Zjednodušene povedané, nezoskupený index sa chová ako index na konci knihy: je zoradený sám, aby ste mohli rýchlo vyhľadať konkrétny výraz. Po vyhľadaní výrazu nasleduje iba číslo stránky, na ktorej je výraz k dispozícii. Na vyhľadanie požadovaných informácií je potrebná ďalšia zmena na príslušnej stránke knihy. Pri databázovom indexe je odkaz na skutočné údaje samozrejme o niečo zložitejší ako jednoduché číslo stránky. Tento proces, známy ako vyhľadávanie riadkov, používa identifikátor riadku (skrátene RID), ktorý sa skladá z troch častí:

Číslo databázového súboru (pretože tabuľku je možné rozdeliť na niekoľko súborov)
Číslo stránky v pamäti
Počet dátových záznamov na tejto stránke pamäte

To však platí, iba ak tabuľka nemá zoskupený index, a preto je uložená ako neusporiadaná „halda“. Ak na druhej strane existuje zoskupený index, kľúčová hodnota zoskupeného indexu sa uvedie ako odkaz na úroveň listu nezoskupeného indexu. Pred nájdením skutočných údajov je potrebné prekonať ďalší indexový strom. (Dodatočné úsilie pri tomto takzvanom kľúčovom vyhľadávaní je však zvyčajne pomerne malé.)

Obrázok 1: Strom indexov pre tabuľku bez zoskupeného indexu

Veľkou výhodou nezoskupených indexov je, že pre tabuľku ich môže byť niekoľko, ktoré sú zoradené podľa rôznych stĺpcov. Potom môžete vyhľadávať rôzne kritériá s vysokým výkonom (Obr).

Pretože indexy, ktoré nie sú zoskupené, sú dodatočnými dátovými štruktúrami, treba si vždy uvedomiť skutočnosť, že na jednej strane vyžadujú dodatočnú pamäť a na druhej strane pri každej zmene samotných údajov je potrebné aktualizovať všetky indexy, v ktorých sú zmenené stĺpce obsiahnuté. Pokiaľ je to možné, nemali by ste vytvárať samostatný index pre každý stĺpec tabuľky, ale vždy zvážte, pre ktoré stĺpce má index zmysel a od ktorých je možné upustiť. Ako základné pravidlo si môžete pamätať, že ďalšie indexy môžu urýchliť prístupy na čítanie, ale spomaliť prístupy na zápis. Rozhodnutie, pre ktoré stĺpce má index zmysel, sa dá urobiť na základe nasledujúcich kritérií:

Ak sa v stĺpci často vyhľadáva alebo filtruje, hovorí to v prospech indexu.
To isté platí pre stĺpce, ktoré sa používajú ako stĺpce cudzieho kľúča, pretože sa filtrujú počas JOIN.
Ak stĺpec obsahuje iba niekoľko rôznych hodnôt (jedna hovorí aj o nízkej selektivite), hovorí to skôr proti indexu. To platí najmä pre stĺpce, ktoré používajú bitový dátový typ.
Ak sa tabuľka väčšinou číta a mení sa len zriedka, je prijateľných viac indexov.
Ak je tabuľka primárne zapísaná (napr. Protokolová tabuľka) a číta sa iba zriedka, počet indexov by sa mal udržiavať na čo najnižšej úrovni.

Kombinované indexy

Jedným zo spôsobov, ako udržať nízky počet indexov, ktoré sa majú spravovať, ale tiež dosiahnuť čo najefektívnejšiu použiteľnosť indexov, je použitie kombinovaných indexov, ktoré sú definované v niekoľkých stĺpcoch. Ak je v tabuľke osôb napríklad samostatný index pre stĺpce krstného mena a priezviska, nasledujúci dotaz bude ťažko môcť použiť oba indexy:

VYBERTE * OD OSOBY. OSOBY

WHERE FirstName = 'John' AND LastName = 'Wood'

Namiesto toho bude server SQL Server automaticky používať index, od ktorého sa očakáva nižší počet stránok, ktoré sa majú čítať (index v stĺpci s vyššou selektivitou). Pretože však môže existovať niekoľko záznamov pre meno aj priezvisko, bolo by ešte efektívnejšie mať index, ktorý obsahuje oba stĺpce, t. J. Kombinovaný index, ktorý je možné vytvoriť nasledovne:

VYTVORIŤ NEUKLASTOVANÝ INDEX IX_Person_LastName_FirstName

ON Osoba. Osoba (Priezvisko, Meno)

Zatiaľ čo názov indexu (IX_Person_LastName_FirstName) je iba bežnou konvenciou pomenovania, rozhodujúce je poradie stĺpcov v definícii indexu. Ak dáte priezvisko na prvé miesto, index sa dá efektívne použiť aj vtedy, keď dopyt vyhľadáva iba priezvisko (pretože toto je primárne kritérium triedenia pre index). Ak namiesto toho hľadáte iba krstné meno, je možné použiť aj index, ale nie prostredníctvom efektívneho prístupu prostredníctvom binárneho stromu indexov (Index Seek). Namiesto toho je potrebné prečítať celý index (indexové skenovanie), aby sa našli všetky možné kombinácie obsahujúce krstné meno, ktoré hľadáte (čo je vo väčšine prípadov stále efektívnejšie ako čítanie celej tabuľky bez indexu).

Pretože hľadanie iba krstného mena sa v praxi bude vyskytovať oveľa menej často ako hľadanie priezviska, odporúča sa vyššie zvolené poradie stĺpcov, ktoré má vedľajší efekt, ktorý môžete urobiť bez samostatných indexov pre stĺpce krstného mena a priezviska.

Krycie indexy a zahŕňajú stĺpce

Pri vysvetľovaní nezoskupených indexov sa poukázalo na to, že existujú odkazy na skutočné údajové záznamy na úrovni listov binárneho indexového stromu (buď vo forme zoskupeného indexového kľúča, alebo ako viacdielna adresa zložená z čísla súboru, stránky pamäte a čísla riadku). V ideálnom prípade sú však všetky dopytované stĺpce obsiahnuté v samotnom indexe, takže nie je potrebné riadiť sa týmto odkazom. Ak je nasledujúci dotaz vykonaný s existujúcim kombinovaným indexom v stĺpcoch Priezvisko a Meno:

potom je možné index prehľadávať pomocou stĺpca Priezvisko, ale ten už obsahuje požadované Priezvisko, takže je možné vynechať normálne potrebné vyhľadávanie celého údajového riadku. Jeden potom hovorí o krycom indexe (vo vzťahu k dotazu), pretože ten pokrýva všetky stĺpce pre dotaz.

Aby bolo možné čo najčastejšie využívať výhodu krycieho indexu bez toho, aby ste museli príliš často meniť poradie samotného indexu, môžu byť stĺpce integrované do indexu ako takzvané zahrnúť stĺpce. To znamená, že hodnoty týchto stĺpcov sú obsiahnuté na úrovni listov indexového stromu, ale nie sú brané do úvahy pri triedení indexu (preto stačí, ak stĺpce existujú iba na úrovni listov a nie na uzloch vyššie). Pre posledný zobrazený dopyt by stačil nasledujúci index:

Možno definovať aj niekoľko zahrňujúcich stĺpcov, ktorých poradie je potom irelevantné, pretože nie sú pre triedenie relevantné. Ak by ste do indexu mali zahrnúť všetky stĺpce (na zoradenie alebo na zahrnutie stĺpcov), simulovali by ste zoskupený index, ale s nevýhodou, že sa potom použije priestor pre odkaz na samotný dátový riadok. čo je v tomto variante zbytočné, pretože je úplne obsiahnuté v indexe. Toto sa teda rozhodne neodporúča, najmä preto, že indexy je možné využívať efektívnejšie, čím sú menšie.

Filtrované indexy

Od verzie SQL Server 2008 existuje s filtrovanými indexmi ďalšia možnosť udržiavať index čo najkompaktnejší a tak minimalizovať počet stránok na čítanie z pamäte. Index je doplnený klauzulou WHERE, takže index je potrebné vytvoriť iba pre riadky, ktoré spĺňajú zadanú podmienku. To následne vedie k obmedzeniu, že podmienky filtra sa dajú použiť iba v kombinácii s nezoskupenými indexmi.

Typickým prípadom použitia filtrovaných indexov sú tabuľky so stĺpcami, ktoré nie sú vyplnené vo veľkej časti riadkov:

KDE MiddleName NIE JE NULL

Indexované zobrazenia

Od verzie 2005 ponúka server SQL Server aj možnosť vytvárania indexov na základe zobrazení. Vo výsledku sa údaje požadované zobrazením ukladajú nadbytočne ako kópia, ale sú zoradené podľa indexových kritérií. Tento koncept sa preto často nazýva zhmotnený pohľad (tento pojem je obzvlášť bežný v prostredí Oracle). Hlavnou výhodou takéhoto indexovaného zobrazenia je, že všetky údaje, ktoré sa majú vyhľadať, sú už prepojené a sú v správnom poradí zoradenia. Dotaz teda musí mať prístup iba k indexovanému zobrazeniu bez toho, aby najskôr spojil údaje z niekoľkých tabuliek pomocou podmienok JOIN. Vynechajú sa aj ďalšie vyhľadávania riadkov alebo kľúčov, pretože - za predpokladu, že sa vytvorí príslušné zobrazenie - všetky stĺpce, na ktoré sa má vyhľadávať, sú obsiahnuté v zobrazení, a teda aj v jeho indexe.

Aby bolo možné používať indexované zobrazenia, je potrebné dodržať niekoľko obmedzení:

Samotné zobrazenie musí byť vytvorené pomocou možnosti WITH SCHEMABINDING, ktorá zabráni zmene dátovej štruktúry stĺpcov použitých v zobrazení.
Index v zobrazení musí byť vytvorený ako UNIQUE CLUSTERED INDEX.
Index sa nesmie filtrovať, to však neznamená žiadne skutočné obmedzenie, pretože kritérium filtra sa dá ľahko prispôsobiť pohľadu použitému indexom.

Zoznam 2 ukazuje, ako je možné vytvoriť indexované zobrazenie v dvoch krokoch. Po vytvorení zobrazenia s dodatkom SCHEMABINDING sa pre toto zobrazenie vygeneruje JEDINEČNÝ CLUSTEROVANÝ INDEX. Ak teraz v zobrazení vykonáte jednoduchý dotaz, môžete z plánu vykonávania určiť, že bol prečítaný iba samotný index, a nie tabuľky za ním:

Zaujímavým vedľajším účinkom indexovaných pohľadov je, že sa dajú použiť, aj keď tabuľky za nimi nie sú adresované výslovne, ale skôr pohľadom. To sa dá ľahko overiť pohľadom na plán vykonávania nasledujúceho dotazu:

Pri vytváraní plánu vykonania SQL Server automaticky zistí, že existuje vhodné indexované zobrazenie, ku ktorému je možné získať prístup rýchlejšie ako k použitým tabuľkám a ich indexom.

Záver

V prvej časti tejto série článkov boli vytvorené základné informácie a bol uvedený prehľad „klasických“ variantov indexu, ktoré sú k dispozícii už dlhšiu dobu. Ďalšia časť tejto série sa zameriava na udržiavanie indexov a niekoľko pokročilejších možností (napríklad kompresia indexov).

Odkazy a literatúra

[1] Panther, Robert: „Optimizing SQL queries“, entwickler.press, jún 2014, ISBN: 978-3868021233

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

[3] Fritchey, Grant: „SQL Server Execution Plans“, Simple Talk Publishing, október 2012, ISBN: 978-smartblock>

Windows Developer

Tento článok bol publikovaný v programe Windows Developer. Windows Developer poskytuje komplexné, výrobcom neutrálne informácie o nových trendoch a možnostiach vývoja softvéru a systémov pre technológie Microsoft.