Správné indexování tabulek v SQL Server je základem pro dobrý výkon databází při dotazování. Chcete-li vytvořit vhodné sql indexy, tak je potřeba pochopit, jakým způsobem SQL Server ukládá data do tabulek/indexů. Neméně důležité je také vědět, jak k těmto údajům následně přistupuje při dotazování.

Jak SQL server organizuje data fyzicky?

V SQL Server je nejmenší jednotkou pro čtení a zápis do db objektů file page, do které SQL Server organizuje data. Každá taková sql file page má 8 KB a vztahuje se k jednomu objektu (například k tabulce nebo indexu). Jednotlivé file pages jsou dále organizovány do Extents (rozsahů), kde každý extent je složen z 8 file pages.

Data jsou dále na nejvyšší úrovni organizovány do 2 typů souborů, v rámci tohoto článku se zaměřujeme na první z nich (MDF)

• MDF soubory kde leží data
• LDF soubory transakčního logu

Klíč jak pochopit sql indexy? Logická organizace dat v SQL Server

V úvodu jsme zmínili, jakým způsobem se data ukládají fyzicky na disk a nyní se dostáváme konečně k tomu, jak jsou organizována logicky. Jak již bylo popsáno, tak data jsou uložena ve file pages, kterých existují mraky a musí existovat systém jak se v nich SQL server orientuje.

Rychlost SQL dotazů je přímo závislá na tom, jakým způsobem dokáže SQL engine přiřadit jednotlivé file pages k nějaké tabulce, na kterou se dotazujeme. Právě tato rychlost závisí na tom, jak jak jsou tabulky logicky organizovány.

K tomu slouží speciální systémové objekty zvané Index allocation maps (IAM) a každá tabulka má přiřazen minimálně jeden takový objekt. Tyto objekty pracují na principu linkování – linkuje jednotlivé file pages s tabulkami. Podle toho kolik má tabulka IAM rozeznáváme 2 způsoby organizace – Heap a balanced tree

Heap – halda, hromada

Heap je označení pro tabulky, které nejsou logicky organizovány vůbec nijak 🙂 a mají pouze 1 IAM (tzv. first IAM). Můžete si to představit jako byste šli pro knížku do knihovny, která není nijak organizovaná. Museli byste se probrat všemi knihami než byste tu svou našli.

Tabulku tohoto typu dostanete vždy pokud ji založíte bez primárního klíče a bez indexů. Je to jednoduše hromada neorganizovaných file pages. Pokud do takové tabulky provedeme dotaz s podmínkou nebo se pokusíme  o JOIN s jinou tabulkou, tak SQL server musí scanovat celou hromadu (heap), každou file page zvlášť => a to trvá hooodně dlouho.

Balanced tree

To organizace dat jako balanced tree je jiné kafe. Vždy když nad tabulkou vytvoříte clustered index (např. primární klíč), tak automaticky dochází k organizaci tabulky jako balanced tree. Tato architektura vytváří clustery a proto je hledání záznamů v tabulce mnohem rychlejší. SQL Server nemusí scanovat celou tabulku jako v případě heap, ale hledá po jednotlivých clusterech. Sql indexy tak fungují podobně jako když v knihovně hledáme knižky podle žánrů a autorů.

V dalších článcích se podívám na fragmentaci indexů a také ukážu skript, jak lze indexy automaticky opravit přes SQL skript