SQL Indexy – Teorie indexování jednoduše

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.

SQL Extents

Zdroj: https://technet.microsoft.com/en-us/library/ms190969(v=sql.105).aspx

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.

SQL heap

Zdroj: Itzik Ben-Gan, Dejan Sarka, Ron Talmage. Querying Microsoft SQL Server 2012. Microsoft press, Vydání 2012. 752 stran. ISBN 0735666059

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ů.

Zdroj: Itzik Ben-Gan, Dejan Sarka, Ron Talmage. Querying Microsoft SQL Server 2012. Microsoft press, Vydání 2012. 752 stran. ISBN 0735666059

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

_______________________________________________________________________________________________
Intelligent technologies - podniková řešení a školení
SQL Indexy – Teorie indexování jednoduše
5 (100%) 1 vote

2 thoughts on “SQL Indexy – Teorie indexování jednoduše

  1. JankaP

    Chapem, ze je to len informacny clanok, ale uz ked sa pouzivaju pojmy, mali by sa pouzit spravne. Posielam info k castiam, ktore boli v clanku nespravne uvedene.
    Heap samozrejme mozu mat noclustered indexy a stale to budu heaps. Preto nemusime scanovat vzdy celu heap tabulku.
    Heap tabulky samozrejme mozu mat viac ako jeden IAM.
    Primary Key je constraint, nie clustered index.
    Ak netrafime spravny stlpec, rozdiel v scane clustrovaneho indexu a heap tabule nie je.
    Aj B-Tree samozrejme ma IAM, pretoze IAM sluzi hlavne na track space allocation, tak ako je uvedene aj na vasom obrazku.

    Ale inac chvalim
    Janka

  2. Honza Zedníček Post author

    Ahoj Janka, preju uspesny rok 2019 a diky za uzitecny koment. Snazim se byt precizni, ale obcas mi do clanku proklouzne nejaka chybicka 🙂 Zejmena ve vecech kde nejsem tolik silny, jako je teorie. Je tomu tak jak pises, fajn postrehy.
    Co se tyce toho primary key, tak tam jsem se spatne vyjadril. Bylo tim mysleno to, že by default je v SQL Server pri vytvoreni primarniho klice vytvoren unique clustered index (jako by byla pouzita konstrukce PRIMARY KEY CLUSTERED). Pri vytvoreni lze ale vynutit option NONCLUSTERED a pokud by to nekdo udelal tak by se cluster index nezalozil.

    Mej se a ahoj
    Honza

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

This site uses Akismet to reduce spam. Learn how your comment data is processed.