Jak se vyznat v indexech na SQL serveru

Indexy na SQL serveru patří mezi základní objekty databázového návrhu. Bohužel jsou často vytvářeny až následně v době výkonnostních problémů, přitom jsou velmi důležité pro tvorbu optimálního exekučního plánu a mohou výrazně zvýšit výkon databázového řešení. V současné době je v SQL serveru k dispozici nepřeberné množství druhů indexů od těch základních – clustered indexu a nonclustered indexu, přes xml indexy, indexy pro prostorová data, columnstore indexy (od verze 2014 již také ve variantě clustered a nonclustered), fulltext indexy a také inmemory optimalizované indexy, které souvisí s novinkami SQL Serveru 2014.

Indexy jsou využívány pro vyhledání a modifikaci požadovaných v co nejkratším čase a za použití minima systémových prostředků. Dobře navržené indexy mohou pomoci s konkurenčními dotazy a jejich optimalizací a v neposlední řadě jsou také použity pro datovou integritu – unikátní hodnoty na sloupci, nebo kombinaci sloupců. Indexy nejsou pro běh serveru vyžadovány, tabulky mohou existovat bez nich, a přesto bude možné číst veškerá data. SQL server, když se oprostíme od detailů, má jen dvě možnosti jak připravit výsledek dotazu

  • Přečíst (scan) všechny jednotlivé řádky všech tabulek, na které se dotaz odkazuje, od prvního až po poslední řádek
  • Vyhledat (seek) požadovaná data, za pomoci indexů

Procházet jednotlivé řádky tabulky a kontrolovat hodnoty sloupců je možné a bude to i malých tabulek rychlé, ale co v případě že má tabulka statisíce řádků, nebo desítky milionů řádků a více, vždyť ty největší tabulky mohou mít i triliony řádků (https://passmedia.sqlpass.org/media/24hours/2012_fall/PDF/06.pdf)

Pojďme se nyní podívat jak základní clustered a nonclustered indexy fungují.

Clustered Indexy

Clustered indexy jsou používány v hojné míře, aniž by si to mnohdy administrátor nebo vývojář uvědomil, při vytvoření primárního klíče pomocí designeru totiž SQL Server automaticky pro daný sloupec vytvoří clustered index. Jak vlastně samotný index vypadá? Index je organizovaný balancovaný strom (B-Tree). Každá datová stránka použitá pro index je označována jako index node. Index je tvořen hierarchií těchto stránek, kdy kořenová stránka a všechny stránky v meziúrovních (tzv. intermediate levels, tedy mimo kořen a listy stromu) obsahují indexové položky. Listy stromu, tedy poslední stránky v hierarchii obsahují u clustered indexu samotné datové stránky s daty tabulky. Indexové řádky v kořenu a intermediate stránkách indexu obsahují pár hodnot klíč-ukazatel, který ukazuje na další mezi úrovňovou stránku, nebo datovou stránku indexu. Na stejné úrovni jsou navíc stránky svázány do obousměrného seznamu.

grid.ai
Obr. 1 - Clustered Index (zdroj msnd.microsoft.com)

Nonclustered Indexy

NCI mají obdobnou strukturu jako clustered indexy, ale v na rozdíl od nich v koncových stránkách nejsou data tabulky, ale pouze odkazy. Každý řádek indexu obsahuje pár klíč-odkaz, ve kterém je tzv. row locator, tedy odkaz na datový řádek v datové stránce, kde jsou uložena požadovaná data. Tento odkaz může být v závislosti na strukturách v databázi buď proti clustered indexu, je-li vytvořen nebo proti struktuře heap, což je označení pro tabulku, u které není vytvořen clustered index.

grid.ai
Obr. 2 - Nonclustered Index (zdroj msdn.microsoft.com)

Selektivita a hustota indexu

Při volbě indexu pro zpracování dotazu je zvažováno několik charakteristik, které určují, jestli je index pro daný konkrétní dotaz vhodný či nikoli. Prvním z nich je selektivita. Do určité míry selektivita i hustota indexu popisují unikátnost hodnot v indexu. Selektivita určuje kolik řádků je vráceno pomocí dotazu v poměru všech řádků tabulky. Selektivita ve vazbě na query optimizer také určuje, jaký bude vytvořený plán pro získání řádků pro výsledek. Jako příklad si vezmeme tabulku se seznamem zákazníků, u kterých máme sloupec region. Je-li většina zákazníků situována v regionu1 a pouze malé množství zákazníků v regionu2, poté dotaz obsahující filtr WHERE region = „Region2“ je dotazem s vysokou selektivitou, protože bude vráceno pouze malé množství řádků v porovnání s celkovým množstvím řádků tabulky.

Hustota indexu popisuje výskyt duplicitních hodnost v rozsahu od 0 po 1.0, kdy hodnota je určená pomocí vztahu hustota = 1/množství unikátních hodnot na sloupci. Vysoká hustota indexu je typická pro sloupce s malým množstvím hodnot, kdy se vyskytuje mnoho opakování několika hodnot.

Údržba indexu

Vlivem operací nad daty dochází ke fragmentaci indexů. U indexů dochází jak k interní, tak k externí fragmentaci. Interní fragmentace pouze popisuje stav, kdy index využívá více místa než je nutné, jak na úrovni koncových, tak nekoncových uzlů stromu. Více alokovaného místa pro index znamená více IO operací pro přečtení dat, více paměťových stránek nutných pro držení dat v buffer manažeru a v neposlední řadě více spotřebovaného místa na diskovém úložišti. Interní fragmentace vzniká nejčastěji modifikací dat, ať už to vkládáním nebo změnou, která vede na situaci, kdy v datové stránce není další volné místo pro nový řádek a musí být vytvořena stránka nová a správně navázána do stromové struktury (tzv. page split), nebo mazáním dat, kdy datová stránka nezůstane zcela zaplněná. Interní fragmentace může ale být cílem návrhu, v případě velkého množství nově vkládaných řádků nedochází k tak častému rozdělení stránek a vkládání nových do struktury. Interní fragmentaci můžeme kontrolovat pomocí dvou parametrů a to FILLFACTOR a PAD_INDEX.

Externí fragmentace je také způsobena vkládáním nových datových stránek (page splits). Jedná se o stav, kdy datové stránky a extenty (extent je tvořen 8 stránkami) nejsou v ideálním pořadí. Ideální pořadí extentů je takové, kdy jsou stránky seřazeny logicky (podle klíčů indexu) podle toho jak jsou tyto stránky fyzicky seřazeny v datovém souboru.

Fragmentaci indexu je možné snížit pomocí REBUILD nebo REORGANIZE operací nad indexy.

Závěrem

V dnešním úvodním díle seriálu o indexech na SQL Serveru jsme si pouze ukázali strukturu a údržbu dvou základních druhů indexů – clustered indexu a nonclustered indexu. V dalších dílech se na indexy podíváme detailněji, včetně jejich využití.

- Marek Chmel, MVP