dotNETportal.cz

Víte, jaký je rozdíl mezi CLUSTERED INDEX, UNIQUE INDEX a PRIMARY KEY? Jak zvolit správný typ indexu a jak tím bude ovlivněn výkon databáze? Jakým způsobem SQL Server ukládá data do fyzických datových souborů a jak je využívá pro čtení? To jsou témata, kterým se v tomto článku budu věnovat. Považuji je ze nutnou znalost pro správný návrh a optimalizaci datových struktur v SQL Serveru.

Struktura datového souboru

SQL Server v běžné konfiguraci ukládá stav databáze do datových MDF souborů a log změn do LDF souborů transakčního logu. Vzhledem k tématu článku nás bude nyní zajímat jen datový soubor. Vzhledem k rozsahu témat účelově zjednoduším některá z detailů – například extends nebo LOB a overflow data.

Datový soubor je pravidelně rozdělen po 8KB na pages (stránky). Tyto jsou jejich základní typy:

• Prázdné stránky
• Speciální – například hlavičky datového souboru a další jinam nezařazené – zde pro nás nejsou důležité a nebudu se jim věnovat
• Globální alokační mapy (GAM, SGAM, PFS) – informace o použitých / volných stránkách pro celý soubor
• Alokační mapy – seskupují související stránky jednoho indexu
• Datové – obsahují řádky dat
• Index – obsahují strom indexu

Prázdné stránky nejsou využité a SQL Server je může v případě potřeby použít, pokud bude třeba alokovat novou stránku. Pokud volné stránky dojdou, zvětší se celý datový soubor a tím se získá nový rozsah stránek připravených k použití.

Globální alokační mapy nesou právě informace, které stránky jsou volné a které využité. Vyskytují se v pevných intervalech a využívají se mimo jiné při hledání další volné stránky při zápisu nových dat. Poměr prázdných stránek uvidíte i ve vlastnostech databáze v polích Size (celková velikost datového souboru) a Space Available (velikost nevyužívaných stránek).

Základní fyzickou strukturou pro ukládání řádků jsou indexy (dříve a interně stále označovány jako tzv. rowsets). Index má podle potřeby alokovaný určitý počet stránek (podle množství a velikosti dat) a jejich seznam udržuje právě alokační mapa. Kterou má index minimálně jednu alokační mapu. Díky alokační mapě tedy SQL Server ví, které stránky patří jakému indexu.

Datové stránky obsahují jednotlivé datové řádky v rámci indexu. Datové stránky obsahují navíc odkaz na předchozí a následující stránku, pokud se všechny řádky nevejdou na jednu stránku. Vytváří tak řetěz všech stránek daného indexu. Například pokud SQL Server čte z indexu všechna data, stačí mu načíst první stránku a pak pomocí odkazů na další pokračovat tak dlouho, než dojde na konec.

Stránky stromu indexu jsou doplněním datových stránek sloužících pro rychlejší vyhledávání. Struktura je uložena pomocí algoritmu b-tree (nikoliv binary tree) a odkazuje na datové stránky. Při použití těchto stránek je nutné, že uložená data v datových stránkách budou seřazeny vybraného sloupce nebo sloupců, aby bylo podle něj/nich možné vyhledávat. 

Může to být trochu matoucí – index musí vždy obsahovat datové stránky, ale nemusí nutně obsahovat strom indexu. Pokud máme pouze datové stránky, označujeme takový index jako HEAP. Důležité pro nás je:

• index typu HEAP dovoluje pouze sekvenční procházení dat – od začátku dokonce nebo naopak, data v HEAP struktuře nejsou nijak seřazena
• běžný INDEX musí být seřazen podle jednoho nebo více sloupců jeho dat; zabírá více místa a je náročnější na zápis (režie se řazením a stromem), ale ve většině scénářů je výhodnější, než HEAP; dovoluje totiž snadno a rychle podle seřazeného sloupce vyhledávat

Shrnutí

Základní struktura na ukládání řádků je index. Ten může být buď tupé uložiště neseřazených dat – HEAP. Nebo může být index uložený se stromem indexu, seřazený podle jednoho nebo více sloupců.

Velikost datové části indexu roste poměrově s množstvím uložených dat. Pokud se nejedná o HEAP, pak navíc velikost stromu indexu roste s velikostí dat, podle který se řadí. Tedy menší bude strom indexu seřazený podle čísla, než podle dlouhého řetězce.

Metadata tabulek

Mějme modelový příklad – databáze a v ní jsme právě vytvořili novou tabulku. SQL server uloží do datového souboru databáze informace o jméně tabulky, sloupcích, primárním klíči, indexech a obecně všem, co o ní potřebuje vědět.

Fyzicky se tyto metadata ukládají do systémových tabulek. Přímo tyto tabulky nejsou běžným způsobem přístupné, ale můžeme do nich nahlížet skrze tzv. systémové pohledy. Příkladem je sys.tables a sys.columns obsahující zjednodušené informace o tabulkách a sloupcích. Důležité je, že o tyto systémové tabulky se server stará sám a nemusíme (a vlastně ani nemůžeme) nijak zasahovat do jejich struktury.

Data tabulek

Z pohledu optimalizací a návrhu je nutné rozlišovat mezi logickým pohledem na data a fyzickou strukturou na disku.

Pokud například přidáte nový index, logická struktura tabulky se nezmění – stále bude mít stejné sloupce a data tabulky se nezmění. Nicméně bude ovlivněn způsob uložení na disku a způsob jakým se budou data z disku číst a zapisovat, což může mít přímý dopad na výkon.

Typy indexů z pohledu tabulek

Logický pohled na data budu záměrně označovat jako tabulky – tedy běžná tabulka, tak jak ji známe. Každá taková tabulka se fyzicky na disku skládá z jednoho nebo více indexů popsaných výše.

Při čtení z tabulky se SQL Server rozhodne, který index nebo skupina indexů se využije tak, aby bylo čtení co nejméně náročné. Stejně tak při zápisu vybírá, které indexy je nutné v rámci konkrétních změn upravit.

Každá tabulka má:

• vždy jeden hlavní index, který je buď typu HEAP nebo běžný index označovaný jako CLUSTERED INDEX – tento hlavní index obsahuje vždy všechny řádky i sloupce tabulky
• a 0 až n vedlejších indexů označovaných jako NONCLUSTERED INDEX – mohou obsahovat pouze některé sloupce a některé řádky

Strukturu hlavního indexu typu HEAP použije SQL Server automaticky v případě, kdy tabulce nenadefinujeme žádný CLUSTERED INDEX. Ve většině případů je ale výhodnější CLUSTERED INDEX definovat a vyhnout se tak typu HEAP. Navíc CLUSTERED INDEX se automaticky vytvoří při zakládání tabulky, pokud zvolíme primární klíč.

Nastavení indexů

Když nad tabulkou vytváříme indexy, můžeme měnit toto nastavení:

• Sloupec nebo sloupce, podle kterých bude seřazen – pokud je sloupců více, je důležité pořadí – pokud například seřadíte telefonní seznam první podle jména a pak teprve příjmení bude hledání jen podle příjmení náročnější, než kdyby bylo řazení první podle příjmení
• Typ CLUSTERED a NON CLUSTERED – maximálně 1 CLUSTERED a libovolné množství NON CLUSTERED indexů
• UNIQUE – nedovoluje mít duplicitní hodnoty ve vybraných sloupcích řazení; tento příznak mohou mít oba typy indexů; při zápisu dat pouze SQL Server v indexu pokusí nejprve vyhledat existující hodnotu a zobrazí chybu v případě snahy zapsat neunikátní hodnoty; tento příznak se využívá v případě nastavení primárních klíčů
• Filtr rozsahu – lze nastavit jen u NON CLUSTERED, protože CLUSTERED musí vždy obsahovat všechna data – dovoluje omezit podle podmínky pro které řádky se bude index vytvářet; používá se pro optimalizaci výkonu při zápisu a velikosti indexu na disku
• Included columns – lze nastavit jen u NON CLUSTERED, protože CLUSTERED již obsahuje všechny sloupce – dovoluje přidat sloupce, které budou součástí datových stránek u indexu

Obsah indexů na disku

Hlavní uložiště (CLUSTERED INDEX nebo HEAP) obsahuje vždy všechny sloupce a řádky tabulky. Dá se brát jako hlavní referenční uložiště řádků tabulky, na které se všechny další NON CLUSTERED indexy odkazují. Existují tyto 3 varianty, jak NON CLUSTERED indexy odkazují na řádky v hlavním indexu:

• Pokud je hlavní uložiště HEAP, uchovává se v datové části NON CLUSTERED indexu přímý odkaz na pozici v datovém souboru (index stránky a pozice na stránce) – to je ale nepříjemný fakt z pohledu výkonu, protože každá reorganizace stránek na disku nebo záznamů na stránce vyžaduje úpravy i všech NON CLUSTERED indexů aby pointery na datové stránky odkazovali na správné místo; to je hlavní důvod, proč nepoužívat typ HEAP
• Pokud je hlavní uložiště CLUSTERED INDEX s příznakem UNIQUE, odkazují se na něj všechny NON CLUSTERED indexy právě přes tuto hodnotu, protože je unikátní a jednoznačně identifikuje řádek, který je možné pomocí tohoto klíče rychle dohledat; toto je zároveň důvod proč se nedoporučuje používat velké CLUSTERED INDEX sloupce, jejich hodnota se kopíruje i do datových stránek NON CLUSTERED indexů jako identifikace řádku, který reprezentují
• Pokud je hlavní uložiště CLUSTERED INDEX bez příznaku UNIQUE, chová se odkazování podobným způsobem, až na rozdíl, kdy se vyskytnou dva záznamy s duplicitou ve sloupcích nastavených v CLUSTERED indexu – v té chvíli interně SQL Server přidá sloupci ještě 4B identifikátor čísla řádku, aby ho byl schopný jednoznačně identifikovat

Všechny NON CLUSTERED indexy tedy v datové části obsahují:

• Sloupce, podle kterých jsou seřazeny
• Sloupce, podle kterých je seřazen hlavní CLUSTERED INDEX (+4B identifikátor, pokud není hodnota unikátní) nebo přímý odkaz na specifickou stránku v případě HEAP
• Sloupce, které jsou přidány pomocí nastavení include columns

Sestavování dotazů

Pokud spustíme dotaz proti tabulce, SQL Server se snaží najít nejlepší způsob, jak jej vykonat vzhledem k indexům a nastavení tabulky. Obvykle si lze logicky odvodit, jak se server zachová. Zároveň lze v SSMS zobrazit tlačítkem Display Estimated Execution Plan / Include Actual Execution Plan zobrazit předpokládaný/skutečný plán vykonání dotazu.

Zde je velmi důležité znát indexy na tabulce a jejich nastavení. Při analýze dotazů ignorujte, zda se jedná o CLUSTERED nebo NON CLUSTERED – zaměřte se jen na sloupce, které obsahují a podle jakých sloupců jsou seřazené. V některých případech je samozřejmě dobré vzít v potaz i další konfiguraci, ale pro základ stačí jejich sloupce a řazení.

Při čtení obvykle nalezneme tyto typy čtení:

• INDEX SCAN / TABLE SCAN – sekvenční čtení celé nebo části indexu nebo HEAP od začátku
• INDEX SEEK – vyhledání konkrétního záznamu nebo rozsahu záznamů pomocí stromu indexu
• KEY LOOKUP – byl použitý NON CLUSTERED index pro vyhledání identifikátoru a dotahují se hodnoty ostatních sloupců z jiného indexu (obvykle CLUSTERED)

Ukázkový dotaz

Jako modelovou situaci mějme tabulku [Customer]:

• Má sloupce [Id] INT, [Company] NVARCHAR(500), [Name] NVARCHAR(500), [Email] VARCHAR(300)
• Unikátní CLUSTERED INDEX na sloupci [Id]
• NON CLUSTERED index na sloupci [Company] s included column [Name]

Zvažte následující dotazy a vysvětlení jejich exekučního plánu:

SELECT [Id], [Company], [Name], [Email] FROM [Customer]

• Je potřeba načíst všechny sloupce a všechny řádky
• Použije se CLUSTERED INDEX, který obsahuje všechny data a provede se INDEX SCAN (sekvenční čtení indexu) přes celou tabulku.

SELECT [Id], [Company], [Name], [Email] FROM [Customer] WHERE [Id] = 123

• Je potřeba načíst všechny sloupce a jeden řádek (protože server ví, že na [Id] je unikátní index a výsledek bude jen jen)
• Proto se použije CLUSTERED INDEX, který obsahuje všechny data
• Provede se INDEX SEEK (vyhledání ve stromu indexu) jednoho konkrétního řádku podle [Id] sloupce.

SELECT [Id], [Company], [Name], [Email] FROM [Customer] WHERE [Name] = ‘John Smith’

• Je potřeba načíst všechny sloupce a neznámý počet řádků (server neví, kolik Johnů Smithů je v tabulce)
• Proto se použije CLUSTERED INDEX, který obsahuje všechny data
• Provede se INDEX SCAN (sekvenční čtení indexu) pro prohledání celé tabulky a nalezení všech výskytů.

SELECT [Id] FROM [Customer] WHERE [Name] = ‘John Smith’

• Je potřeba načíst pouze sloupce [Id] a [Name]
• To dokáže plně pokrýt index na sloupci [Company] – ten totiž obsahuje [Id] jako odkaz do hlavního indexu a zároveň [Name] jako included column
• SQL Server preferuje vždy čtení datově menšího indexu (obsahující méně sloupců) před větším, protože není potřeba číst tolik stránek z disku
• Ve výsledku se tedy provede INDEX SCAN a projde se celý NON CLUSTERED index. U odpovídajících řádků přečte [Id] a není vůbec nutné číst z hlavního indexu.

SELECT [Id], [Company], [Name], [Email] FROM [Customer] WHERE [Company] = ‘dotNETcollege’

• Je potřeba načíst všechny sloupce a neznámý počet řádků (server neví, kolik zákazníků z dotNETcollege je v tabulce)
• Pravděpodobně bude použitý NON CLUSTERED INDEX pro vyhledání všech [Id] záznamů podle příslušného WHERE predikátu
• Dále se provede KEY LOOKUP do hlavního CLUSTERED INDEX, kde se postupně podle všech [Id] načtou zbývající sloupce

Kdy SQL Server nepoužije NON CLUSTERED INDEX?

Může se stát, že SQL Server se rozhodne nepoužít vámi vytvořený index a raději zvolí na první pohled nepříliš efektivní SCAN celého jiného indexu. Nejčastější důvody jsou:

• Očekáváte KEY LOOKUP, protože filtrujete podle sloupce v indexu a místo toho je použitý SCAN hlavního indexu? Pravděpodobně server usoudil, že výraz WHERE predikátu by vrátil příliš mnoho záznamů a kdyby pro každý z nich dohledával řádek v hlavním indexu, bude to příliš náročné a raději projde celou tabulku.
• Pokud nebyl použitý očekávaný index, možné je jen v tabulce zatím příliš málo řádku a v tu chvíli raději SQL Server přečte celou tabulku, než aby dohledával záznamy přes strom indexu. Vždy je totiž nejmenší objem dat, se kterým se pracuje, celá stránka a proto je často sekvenční čtení výhodnější.

Primární klíče

Na závěr bych rád zdůvodnil, proč jsem v celém článku nezmínil primární klíče. Primární klíč je totiž víc formální záležitost, kterou lze plně zastoupit běžným indexem, než funkce navíc.

Pokud na tabulce založíme primární klíč, je to interně běžný index, který omezuje nastavení na:

• Lze vytvořit jen jeden na tabulku
• Je unikátní
• Nedovoluje aplikovat filtr (aby vždy pokryl všechny řádky)
• Nedovoluje nastavit included columns
• Můžeme si alespoň zvolit, zda bude uložen jako CLUSTERED nebo NON CLUSTERED index.

Pojmy CLUSTER INDEX a PRIMARY KEY se často pletou, protože většina tabulek má primární klíč nastavený právě nad CLUSTERED indexem – obvykle sloupce [Id] a podobně a proto pojmy splývají.

Závěrem tedy doporučuji při optimalizacích a ladění zcela vypustit pojem primárního klíče a orientovat se pouze na fyzické indexy a jejich nastavení. Stejně to dělá i SQL Server a je mu zcela jedno, zda je na sloupci unikátní index nebo primární klíč.