Hádanka - SQL Server a zpracování dotazů

Tomáš Jecha, MVP, MCSD       27.04.2011       SQL, Databáze       12137 zobrazení

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

 

hodnocení článku

0       Hodnotit mohou jen registrované uživatelé.

 

Nový příspěvek

 

Diskuse: Hádanka - SQL Server a zpracování dotazů

Protože je to velikostně nejmenší index a obsahuje všechna idčka - tedy vše potřebné pro získání výsledku. Zároveň je i menší než vlastní tabulka.

nahlásit spamnahlásit spam 1 / 1 odpovědětodpovědět

Ano, to je správně.

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Diskuse: Hádanka - SQL Server a zpracování dotazů

Omyl, ve skutecnosi se pouzije fulltable scan, protoze tabulka neobsahuje dostatecne mnozstvi radek na to, aby se pouziti indexu vyplatilo.

nahlásit spamnahlásit spam -1 / 1 odpovědětodpovědět

Můžete to nějak podložit? Nepodařilo se mi to. Zkoušel jsem to s prázdnou i plnou tabulkou, obnovoval jsem statistiky, exekuční plán nacacheovaný nemám a ani v jednom případě se mi nepodařilo, aby si optimalizer vybral variantu s primárním indexem.

Navíc i kdyby si optimalizátor vybral v případě prázdné tabulky variantu s primárním klíčem, kvůli malému počtu řádků, hádanka se týká stavu, kdy si vybere druhý index a důvodu, proč to vůbec udělá.

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Jen pro kontrolu, tady je script na vytvoření tabulky:

create table Tabulka1
(
	Id int not null constraint PK_Tabulka1 primary key identity(1,1),
	Data1 int,
	Data2 int
)
go
create index IX_Tabulka1_Data1 on Tabulka1 (Data1)

A tady na samotný select:

-- prikaz vyvolat s volbou "Include Actual Execution Plan"
select Id from Tabulka1

Exekuční plán je:

Index Scan (NonClustered) [Tabulka1].[IX_Tabulka1_Data1] Cost: 100 %

SELECT Cost: 0 %

nahlásit spamnahlásit spam 0 odpovědětodpovědět
                       
Nadpis:
Antispam: Komu se občas házejí perly?
Příspěvek bude publikován pod identitou   anonym.

Nyní zakládáte pod článkem nové diskusní vlákno.
Pokud chcete reagovat na jiný příspěvek, klikněte na tlačítko "Odpovědět" u některého diskusního příspěvku.

Nyní odpovídáte na příspěvek pod článkem. Nebo chcete raději založit nové vlákno?

 

  • Administrátoři si vyhrazují právo komentáře upravovat či mazat bez udání důvodu.
    Mazány budou zejména komentáře obsahující vulgarity nebo porušující pravidla publikování.
  • Pokud nejste zaregistrováni, Vaše IP adresa bude zveřejněna. Pokud s tímto nesouhlasíte, příspěvek neodesílejte.

přihlásit pomocí externího účtu

přihlásit pomocí jména a hesla

Uživatel:  
Heslo:  

zapomenuté heslo

 

založit nový uživatelský účet

zaregistrujte se

 
zavřít

Nahlásit spam

Opravdu chcete tento příspěvek nahlásit pro porušování pravidel fóra?

Nahlásit Zrušit

Chyba

zavřít

feedback