SQL po šesté - používejte indexy!

Možná jste už někdy slyšeli něco o pojmu index. Jednou z možností, jak dosáhnout co nejlepších výsledků z hlediska provádění SQL příkazů, jsou totiž právě indexy. Pojďme se spolu blíže podívat, o co vlastně jde.

Obecně nelze říci, který databázový systém je nejlepší (ono to dost dobře nelze konstatovat v případě ničeho, vždy záleží na našich konkrétních požadavcích). Podstatným kritériem pro hodnocení je výkon systému. Možná jste už někdy slyšeli něco o pojmu index. Jednou z možností, jak dosáhnout co nejlepších výsledků z hlediska provádění SQL příkazů, jsou totiž právě indexy. Pojďme se spolu blíže podívat, o co vlastně jde.

Vysvětlení indexů není pouze záležitostí "čisté" syntaxe, proto co nejvíce věnujte pozornost vlastním příkladům a vysvětlením. Nejprve si připomeňme naši ukázkovou tabulku osoby, která má následující podobu:

Jméno příjmení rodné_číslo datum Počet_dětí
Karel Smolný 580815/8777 3.12.1992 2
Marek Jurda 650404/8956 2.11.1992 3
Martin Pavlásek 720303/5511 28.3.1990 3
Pavel Jirásek 551212/7877 26.3.1989 2
Pavla Nováková 785308/4569 28.6.1997 5
Pavla Nováková 555111/3042 28.5.1990 1
Romana Bourková 745226/8820 3.5.1995 5

Zadáme-li nyní velmi triviální dotaz na jméno, příjmení a datum nástupu všech osob, které se jmenují Marek (vybíraný atribut jméno je zbytečný, použit je pouze pro podání "ucelenější" informace),

SELECT jméno, příjmení, datum
FROM osoby
WHERE jméno = `Marek`
může být doba čekání na odpověď otázkou mžiku či (při existenci dalších sto tisíc záznamů) pár hodin. Pokud systém nebude předem znát informaci, které řádky uvedené podmínce vyhovují, musí zdlouhavě projít jeden záznam po druhém a pro každý z nich se rozhodnout, zda bude do výsledku zařazen či nikoli (dnes popisované postupy vyhodnocování dotazů jsou velmi zjednodušené; k detailům se dostaneme v některém z příštích dílů věnovaném optimalizaci jako takové). Pokud by však systém předem věděl, pro které záznamy má konkrétně sáhnout, nemusel by procházet všechny a odpověď by tak mohla být značně rychlejší. A z této úvahy (či nějaké podobné) vznikl index.

Odpověď na otázku "Co je to index?" ale není tak jednoduchá, jak by se na první pohled mohlo zdát. Nám však postačí, budeme-li pod indexem chápat databázovou strukturu (další, se kterou jsme se již setkali, je tabulka) a nebudeme-li se zabývat vlastní fyzickou realizací - ta je přímo závislá na konkrétním systému (používány bývají například tzv. B-stromy). Tento databázový objekt pak obsahuje informace o "indexovaném" atributu, které budou použity pro vyhodnocení dotazu. Celé si to můžeme představit například takto - když se ptáme na všechny osoby se jménem Marek a tento atribut je indexován, systém má k dispozici informace, které řádky tuto podmínku splňují a vybírá si pro další zpracování přímo je. Ještě jednou připomínám, že vlastní činnost zpracování dotazu (tedy včetně využití informací z indexů) je závislá na konkrétním systému - pro přesný popis činnosti je nutno se zahloubat do příslušné dokumentace. Pokud máme nad tabulkou více indexů, výběr vhodného bývá obvykle interní záležitostí (některé systémy, zejména jednodušší, specifikování indexu naopak vyžadují).

Ještě než se dostaneme k vlastním příkazům pro práci s indexy, uveďme si, že index může být jednoduchý (tedy obsahující pouze jeden atribut) či složený (obsahující více atributů). Vytvoření indexu je záležitostí příkazu CREATE INDEX, který má následující syntaxi:

CREATE INDEX <jméno_indexu>
ON <jméno_tabulky>
(<atribut [ASC¦DESC]> [, …])
Volby ASC a DESC (první je přednastavená) určují, zda jsou hodnoty v indexu seřazeny vzestupně či sestupně. Pro zrychlení našeho úvodního příkladu (indexován bude atribut jméno) můžeme založit index se jménem osobyijméno (je vhodné názvy odvozovat podle určitých pravidel, která neporušujeme) takto:
CREATE INDEX osobyijméno
ON osoby (jméno)
Složený index, například pro atributy příjmení a jméno, založíme obdobně:
CREATE INDEX Osobyipříjmeníjméno
ON osoby (příjmení,jméno)
V tento moment je nutné si uvědomit jeden fakt - pokud budeme mít založený pouze index osobyipříjmeníjméno a zadáme náš zkušební dotaz, ve většině systémů nebude tento index použit, protože podmínka neobsahuje atribut příjmení a index je vytvořen pro atributy v pořadí příjmení, jméno. Možná bude situace jasnější ve chvíli, kdy si představíme tabulku položky, kde máme uloženy položky objednávky. Každý záznam obsahuje atribut ID (číslo objednávky) a ID_POL (číslo položky objednávky). Položka objednávky je jednoznačně identifikovatelná již podle ID_P. Pokud je však vytvořen (pouze) index (ID,ID_P), při zpracování dotazu:
SELECT *
FROM položky
WHERE ID_P=25845
bude stejně nutné projít celou tabulku položky. Řešení je velmi jednoduché - známe-li ID, do podmínky je doplníme:
SELECT *
FROM položky
WHERE ID=878 AND
ID_P=25845
Možná se vám to celé zdá triviální a samozřejmé - během své praxe jsem se však již setkal s mnoha podobnými "chybami", které výkon systému velmi brzdily.

Někdy je možné index založit jako jedinečný (zejména v souvislosti s primárním klíčem). Další možností je tzv. CLUSTER. Pokud je v případě indexu zadána tato volba (pozice v příkazu je závislá na konkrétním systému, zpravidla se jedná o poslední klíčové slovo), data budou fyzicky uložena podle tohoto indexu. Je jasné, že index tohoto typu může být pro danou tabulku pouze jeden. Před využitím těchto voleb však doporučuji "hloubkovou" konzultaci s dodávanou dokumentací.

Ke zrušení (odstranění) indexu použijeme příkaz DROP INDEX, jehož jediným parametrem je jméno indexu.

DROP INDEX osobyipříjmeníjméno

Práce s indexy vyžaduje promyšlení daného dotazu. Záleží totiž také na tom, jak často je indexovaný atribut měněn. Pokud založíme index například na stavovém atributu dokladu (lze předpokládat dotazy podle stavu dokladu), který se nám často mění, bude sice vlastní zpracování příkazu SELECT velmi rychlé, neúnosně však může narůst režie na údržbu samotného indexu. Řešení takového problému patří do pokročilých technik (jen pro zajímavost - jedno z možných řešení je "přelévat" doklady do různých tabulek podle stavu). Nelze také jednoznačně říci, jaký počet indexů je ideální - doporučuje se co nejmenší - vždy je nutné volit mezi ziskem v provádění dotazů a ztrátou v důsledku provozní režie. Velký počet indexů navíc může způsobit "chybu" ve výběru vhodného indexu - zejména při komplikovaných dotazech a složených indexech. Výsledný efekt je pak přesně opačný.

Po dnešní části již tedy víme, co to vlastně index je a jakým způsobem se používá. Ještě jednou opakuji, že indexy mohou být (a také jsou) v jednotlivých systémech řešeny odlišně - nezapomínejte na dodávanou dokumentaci. Čas, který této oblasti věnujete, se vám mnohokrát v budoucnu vrátí jak v rychlosti tvorby systému, tak v rychlosti jeho provozu. Zanedbání bude mít za následek pravý opak.


Sledujte Živě na Facebooku

celkem 1

Poslední komentáře Komentáře

Pár hodin? Doba pokročila, už mám gigaherce Barte 11.  10.  2010 12:30
Přidat příspěvek Zobrazit vše


Další podobné články

Avast koupil Inmite a stane se velkým mobilním vývojářem

Avast koupil Inmite a stane se velkým mobilním vývojářem

Před 4 hodinami  |  Čížek Jakub  |  2
Chrome zkrátí výdrž vašeho notebooku s Windows

Chrome zkrátí výdrž vašeho notebooku s Windows

Chrome na Windows se může výrazně podepsat na spotřebě baterie laptopu a tabletu. O problému se ví už dlouhé roky, Google ale lapálii celou dobu považoval za výhodu.

Před 14 hodinami  |  Čížek Jakub  |  15

Jak chránit děti před temnou stránkou internetu

Jak chránit děti před temnou stránkou internetu

Internet je plný zajímavých informací a zábavy, má ale i své stinné stránky. Poradíme vám, jak ochránit svoje děti před nebezpečím internetového světa.

22.  7.  2014  |  Reichman Martin  |  12

Google připravuje nové prostředí Athena pro chromebooky

Google připravuje nové prostředí Athena pro chromebooky

21.  7.  2014  |  Čížek Jakub  |  4

DEJTE NÁM TIP NA ČLÁNEK



Aktuální číslo časopisu Computer
  • Testy nejnovějších produktů na českém trhu.
  • Informace ze světa internetu i bezpečnosti.
  • Plné verze programů zdarma pro všechny čtenáře.

Partnerská sekce pro IT profesionály
Microsoft TechNet/MSDN