Dnes mě napadla jedna hádanka týkající se optimalizace dotazů v SQL Serveru. Tady je:
Máme tabulku se sloupci:
- Id (primary key, clustered index)
- Data1 (nonclustered index)
- Data2 (bez indexu)
Otázka zní:
Proč se v dotazu select Id from Tabulka použije scanování podle indexu nad sloupcem Data1 a ne nad primárním klíčem Id?
Řešení:
SQL Server se snaží hledat nejlepší (nejrychlejší a nejméně pamětově náročný) způsob jak získat data, která potřebuje.
Obecně, pokud napíšete, že chcete získat pouze sloupec Id, tak se SQL Server snaží najít datový zdroj, který tuto hodnotu poskytne a zároveň bude k získání potřeba co nejméně čtení a přeskakování v paměti.
Clustered index (v našem případě je to primární klíč Id) je index, který určuje, jak jsou řádky tabulky za sebou uloženy, jak jsou seřazeny v paměti. Není to tedy jen rejstřík pro vyhledávání, ale definice dat tabulky. V našem případě jsou za sebou seřazeny podle primárního klíče Id všechny sloupce definující tabulku (tady Id, Data1 a Data2).
Pokud by tedy SQL Server při čtení Id použil tento index, musel by vždy přečíst sloupec Id a přeskočit sloupce Data1 a Data2. A takto by prošel celou tabulku. Tím pádem by přečetl (i když s přeskakováním) rozsah datových stránek přes celou tabulku.
Naproti tomu nonclustered index je jen jakýsi rejstřík, který je seřazený podle indexované hodnoty (v našem případě Data1) a primárním klíčem, který této hodnotě odpovídá (a našem případě Id). Jinými slovy tento index je seznamem všech hodnot Data1 a Id. SQL Server tak při čtení tohoto indexu bude mít k dispozici všechny Id a zároveň přečte menší počet datových stránek, protože nebude přeskakovat všechny zbylé sloupce (Data1 a Data2), ale jen sloupce zahrnuté v indexu (tedy jen indexovaný Data1). Tím pádem se bude procházet menší počet datových stránek a celý proces bude tak rychlejší.
SQL Server jistě tento způsob procházení použije jen, pokud to bude výhodné. Když například rozšíříte příkaz select o čtení sloupce Data2, už se použije čtení pomocí primárního klíče, protože tuto hodnotu nemáme k dispozici v indexu nad sloupcem Data1 a jeho použití by tak nebylo dále výhodné.