Indexování stránek není pouze záležitost Google

17. února 2010

Vnímavost rozhraní je jeden z detailů, které musejí brát v potaz vývojáři webů při snaze, aby zážitky uživatelů byly pokud možno jen příjemné a uspokojující.

Aplikace, která reaguje rychle, posiluje uživatelův pocit, že ji má pod svou kontrolou. Ve snaze o co největší rychlost aplikace se však my, vývojáři, snadno dostaneme na scestí. Optimalizujeme obrázky a snažíme se zmenšovat velikost stránek. Porovnáváme výkon softwaru webových serverů, programovacích jazyků, pracovních rámců a hardwarových komponent, přestože rozdíly v těchto nástrojích budou možná minimální.

Je tu však jiný, často přehlížený prvek, který může mít někdy na výkon větší vliv, než cokoli jiného: návrh databáze. Když nějaká databáze postrádá indexy nad patřičnými sloupci, zaručeně budou následovat potíže s rychlostí, které budou pomalu, ale jistě vyvádět uživatele z pohody, jak bude narůstat objem dat. Naštěstí se dá tento problém snadno vyřešit.

Webové databáze mají na starost mnohem víc věcí než jen pasivně ukládat informace. Část jejich síly pochází z efektivního indexování záznamů. Index slouží jako mapa, identifikuje přesně, kde je ve velké hromadě kamení umístěný konkrétní malý kamínek. Když například Googlu zadám termín „web development“, identifikuje na dvě stě miliónů výsledků a zobrazí prvních deset – za čtvrt sekundy. Na Googlu však nepostupují tak, že by, když něco hledám, načítali všechny stránky výsledků a procházeli jejich obsahem: oni už mají stránky předem zanalyzované, a termín, který hledám, porovnávají oproti nějakému indexu, který se pouze odkazuje na původní obsah.

Opravdu je to tak podstatné?

Jistě! I v jednoduchém testovacím případě může chybějící index způsobit, že bude aplikace reagovat 20 až 60 krát pomaleji, než by měla. Vezměme jako ukázku nástin blogovací aplikace. Vytvoříme několik tabulek a naplníme je náhodně vygenerovanými daty:

  • articles (články)
  • articles_categories (spojovací tabulky pro články a kategorie)
  • categories (kategorie)
  • comments (komentáře)
  • users (uživatelé, neboli autoři)

Předpokládejme, že je blog poměrně nový. Máme zatím jen jediného autora, deset článků a ke každému článku pět komentářů. Databáze sice obsahuje indexy, ale jen na sloupci primárního klíče (ID) tabulek.

Spusťme nejprve jednoduchý dotaz, jímž najdeme všechny články konkrétního autora, jako vyhledávací termín použijeme jeho e-mailovou adresu.

SELECT * FROM articles 
INNER JOIN users
  ON articles.user_id = users.id
WHERE users.email = 'john.doe@example.com';

0.01 seconds

Nijak nepřekvapuje, že dotaz proběhl velmi rychle. Konec konců máme v databázi jen jednoho autora, takže v podstatě nezáleží na tom, že vyhledávací termín (e-mailová adresa) není indexovaný.

Zkusme složitější příklad. Následující dotaz najde všechny komentáře k článkům zadaného autora, včetně údajů o tom, do které kategorie jednotlivé články patří:

SELECT * FROM articles
INNER JOIN articles_categories
  ON articles_categories.article_id = articles.id
INNER JOIN categories
  ON articles_categories.category_id = categories.id
INNER JOIN users
  ON articles.user_id = users.id
INNER JOIN comments
  ON comments.article_id = articles.id
WHERE users.email = 'john.doe@example.com';

0.02 seconds

Opět dotaz nezabral téměř žádný čas. Ve skutečnosti však musel dotaz, aby získal výsledky, provést operaci, která je velmi náročná na zdroje, říká se jí úplný průchod tabulkou (full-table scan). Odezva byla rychlá jen proto, že máme tak málo dat. Zrekapitulujme, co jsme vlastně všechno od databáze požadovali:

  1. Identifikovat autora (uživatele), jehož e-mailová adresa je john.doe@example.com.
  2. Najít všechny články, jejichž user_id se shoduje s hodnotou id zvoleného uživatele.
  3. Najít všechny kategorie, jejichž ID je vypsané v tabulce articles_categories zároveň s article_id ze seznamu článků, které jsme už identifikovali.
  4. Nakonec vyhledat všechny komentáře, jejichž article_id je v tomto seznamu článků.

Ani jeden z těchto kroků ve skutečnosti nehledá záznamy podle jejich vlastních ID – ale podle ID jiných, připojených záznamů. Protože jsou indexované jen sloupce ID, musí databázový engine prozkoumat přinejmenším v některých z těchto tabulek všechny řádky, aby mohl hledání dokončit. Přesuňme se na okamžik o pár let dopředu. To už budeme mít na blogu takových 1 000 článků, 15 přispívajících autorů a k jednotlivým článkům průměrně 25 komentářů. Zopakujme první jednoduchý dotaz pro tento objemnější scénář:

SELECT * FROM articles 
INNER JOIN users
  ON articles.user_id = users.id
WHERE users.email = 'john.doe@example.com';

0.65 seconds

Jednoduchý dotaz se pořád ještě vejde do jedné sekundy, přesto je změna v době odezvy signifikantní. Takto velká prodleva už znamená, že se aplikace bude jevit lenivější a začne podkopávat původně dobré uživatelské zážitky. Mnohem markantnější je však rozdíl ve zpracování dat u druhého dotazu:

SELECT * FROM articles
INNER JOIN articles_categories
  ON articles_categories.article_id = articles.id
INNER JOIN categories
  ON articles_categories.category_id = categories.id
INNER JOIN users
  ON articles.user_id = users.id
INNER JOIN comments
  ON comments.article_id = articles.id
WHERE users.email = 'john.doe@example.com';

6.69 seconds

To už jsme se ocitli v nebezpečném teritoriu. Když se takto dlouhé dotazy spouštějí rutinně, mohou aplikaci ochromit. Rozhraní se zpomalí. Serverové procesy se budou zadrhávat, protože musejí čekat, než se dotazy dokončí. Prohlížečům může vypršet čekací doba, protože data pořád nepřicházejí. Protože databáze a webový server akceptují jen limitovaný počet simultánních připojení, bude k dispozici méně připojení pro přicházející požadavky, zatímco procesy čekají na nějaký dotaz.

Podívejme, co se stane, přidáme-li do tabulek indexy:

  • Jednoduchý dotaz: 0.01 seconds.
  • Složitý dotaz: 0.32 seconds.

Všimněte si, že jednoduchý dotaz se vykoná stejně rychle pro 1 000 článků, jako když jich bylo jen 15. Složitý dotaz je sice o něco pomalejší, je ale dvacetkrát rychlejší než v případě, kdy jsme neměli indexy, protože tehdy musel dotaz projít všemi řádky přinejmenším některých tabulek, a doba odezvy byla v přímé korelaci s objemem procházených dat. Tím, že jsme indexovali sloupce, které prohledáváme, docílili jsme mnohem efektivnějšího postupu. Je to jako když něco hledáte v kuchařce – vyhledáte vzadu „drůbež“, nebudete kuchařkou listovat a označovat oslím rohem každý list, kde se vyskytuje jídlo z drůbežího masa. Indexy šetří čas. Čím víc dat se prohledává, tím víc času se indexováním ušetří.

Co indexovat

Všeobecně se doporučuje indexovat v databázi všechny cizí klíče. Podobně jako sloupec user_id v tabulce articles, je cizí, neboli nevlastní klíč (foreign key) sloupec, který se odkazuje na ID (neboli primární klíč) jiné tabulky, spojuje tedy záznamy tabulek relací. Méně očividné je, že se mají indexy také aplikovat na všechny sloupce, jimiž se limitují dotazy, které prohledávají velký počet záznamů. V naší blogovací ukázce by možná bylo vhodné indexovat sloupec email tabulky users, protože budeme asi často identifikovat uživatele podle jejich e-mailových adres. Pokud víme, že aplikace nebude mít víc než tucet uživatelů, příliš indexem nezískáme. Očekáváme-li však, že bude uživatelská základna trvale narůstat, může být tento index velmi žádoucí, ne-li nezbytný. Pokaždé, když se přihlásí nějaký uživatel, možná budeme vydávat dotaz podobný tomuto:

SELECT password FROM users WHERE email = '$my_email'

Budeme-li spouštět dotaz tohoto druhu nad tabulkou s tisíci záznamů, mohli bychom se dostat do potíží, nebudeme-li mít index na sloupci email. V MySQL se takový index přidá příkazem:

ALTER TABLE users ADD INDEX (email);

Většina nástrojů pro správu databáze, které mají grafické rozhraní (jako je například phpMyAdmin), nabízí pro vytváření a správu indexů zabudované ovládací prvky. Index se také může odkazovat na několik sloupců, což se hodí, když často záznamy identifikujete pomocí nějaké kombinace atributů. Aby byl efektivnější následující dotaz, mohli bychom index založit na sloupcích email a password:

SELECT * FROM users WHERE email = '$my_email' 
  AND password = '$my_password';

Poznejte dobře své nástroje

Správně chápat indexy je na výsost důležité tehdy, spoléháme–li se na pracovní rámce (frameworks), aby psaly příkazy SQL za nás. Pracovní rámce jsou prospěšné z mnoha důvodů, přesto bychom měli být s nimi obezřetní. I když si nainstalujete Dreamweaver, i tak budete pořád muset rozumět XHTML a CSS, a budete-li budovat tabulky pomocí migračních skriptů Ruby on Rails, neeliminujete tím potřebu tabulky indexovat. A i když nějaké vyspělá platforma, jako je WordPress, vytvoří sama dobře indexované tabulky, pluginy, které pro ni instalujeme, to možná nedělají.

Nic se ale nemá přehánět. Příliš mnoho indexů může přinášet víc škody než užitku, protože serveru bude jistý čas trvat, než určí, který (nebo které) z nich má použít, aby splnil dotaz, který jsme mu zadali. Databáze také musí tyto indexy aktualizovat, když se do ní přidávají nové záznamy. Stejně jako při každém jiném aspektu návrhu databáze, vytvářejte jen to, co je nezbytné k řešení daného úkolu – nic víc, nic míň.

Vyšší úroveň

Indexování databázových tabulek je snadná cesta, jak rapidně zvýšit výkon, a v mnoha případech přinášejí indexy obrovité zisky, nejsou však samospasitelné, všechno nevyřeší. Přestože je optimalizace dotazů obšírné téma, existuje několik obecných vodítek, které s ní mohou pomoci.

Identifikujte problémové dítě

Nacházet úzká hrdla výkonu bývá obtížná úloha. Jedna realizovaná webová stránka může být založena na několika databázových dotazech, a vnímavost rozhraní může být velmi různá podle toho, jaká konkrétní data se načítají. Mnohé databázové servery naštěstí s touto úlohou velmi pomáhají, protože vytvářejí log pomalých dotazů. V MySQL stačí do konfiguračního souboru přidat následující řádky, aby se vytvářel průběžný log dotazů, které trvají déle než jednu sekundu:

long_query_time  = 1
log-slow-queries = /var/log/mysqld.slow.log

Najdete-li pak ve výsledném logu řádek podobný tomuto, odhalíte příležitost k lepšímu indexování:

# Query_time: 7  Lock_time: 0  Rows_sent: 296  
Rows_examined: 75872

Říká, že databázový server musel prozkoumat přes 75 000 řádků, aby našel méně než 300 výsledků. Tento druh disproporce obvykle indikuje, že dotaz vyžadoval operaci úplný průchod tabulkou (full table scan).

Vykonávací plán dotazu

Většina databázových serverů nabízí možnost podívat se na vykonávací plán dotazu – jinak řečeno, jak si databázový server poradí s úlohou, kterou jsme mu připravili. V MySQL to jde nanejvýš jednoduše, stačí dát slovo EXPLAIN před dotaz, který jsme zkopírovali a vložili z logu. Například:

EXPLAIN
SELECT * FROM articles
INNER JOIN articles_ca/tegories
  ON articles_categories.article_id = articles.id
INNER JOIN categories
  ON articles_categories.category_id = categories.id
INNER JOIN users
  ON articles.user_id = users.id
INNER JOIN comments
  ON comments.article_id = articles.id
WHERE users.email = 'john.doe@example.com';

V původním scénáři, jen s indexy na primárních klíčích, bude výsledek vypadat nějak takto (několik sloupců jsme kvůli lepší přehlednosti vynechali):

table type possible_keys key rows Extra
articles_categories ALL NULL NULL 1000
categories eq_ref PRIMARY PRIMARY 1
articles eq_ref PRIMARY PRIMARY 1
users eq_ref PRIMARY PRIMARY 1 Using where
comments ALL NULL NULL 25000 Using where

Říká mnoho o tom, jak databázový server zpracovává tabulky zahrnuté v dotazu. Sloupec type ukazuje, jak se porovnávají řádky, když se spojují tabulky. Tabulky označené ALL indikují, že se používá úplný průchod tabulkou. Sloupce s popisky possible_keys a key vypisují jisté indexy, které server považoval za potenciálně prospěšné pro splnění dotazu, resp. ty, pokud nějaké, které zvolil. Sloupec rows ukazuje, kolik řádků server myslí, že bude muset prozkoumat. Celkový počet potenciálních výsledků je součin všech hodnot v tomto sloupci (nikoli součet). Jinak řečeno, u tohoto dotazu server předjímá, že bude muset zpracovat možná až 25 miliónů kombinací řádků – ovšem skutečný počet závisí na tom, jak budou vypadat opravdová data.

Sloupec Extra obsahuje další náznaky, jak databáze zpracovává informace. V tomto případě ukazuje, které sady dat jsou omezené klauzulí WHERE. U složitějších dotazů, zvláště u těch, které zahrnují operace seskupování nebo řazení záznamů, můžeme hledat červené praporky v podobě frází Using filesort nebo Using temporary.

Porovnejte výstup příkazu EXPLAIN pro indexovanou verzi databáze:

table type possible_keys key rows Extra
users ref PRIMARY, email email 1 Using where
articles ref PRIMARY, user_id user_id 67
articles_categories ref category_id, joint_index, article_id article_id 1
categories eq_ref PRIMARY PRIMARY 1
comments ref article_id article_id 25 Using where

Všimněte si, že nové není jen to, že se používají indexy. Také pořadí operací je úplně jiné. Databázový server je natolik inteligentní, že s tím, co má zadáno, dělá, co se dá. V první řadě byl tedy vyvíjen tak, aby plánoval co nejefektivněji, když nejsou k dispozici indexy. Když ale k dispozici jsou, je jeho přístup úplně odlišný. Vynásobíme-li nyní hodnoty ve sloupci rows, dostaneme jen 1 675 potenciálních výsledků – nepatrný zlomek původní sady.

Vylaďte spojovací operace

Když získáváte najednou data z několika tabulek, znamená to, že tabulky spojujete pomocí sloupců, jimiž se propojují záznamy tabulek. V ukázkových dotazech uvedených výše jsme specifikovali tzv. vnitřní spojení, INNER JOIN, abychom vybrali jen ty řádky, které vyhovují všem zadaným podmínkám. Někdy jsou výhodnější jiné typy spojení, jako je levé spojení, LEFT JOIN, kdy se data vracejí i tehdy, když v jedné z připojených tabulek není odpovídající záznam. Obecně však tento přístup vyžaduje od databáze víc práce.

Například, pokud bychom chtěli vyhledat všechny články podle autorů a zahrnout i informaci o kategoriích, mohli bychom použít dotaz jako je tento, s vnitřním spojením tabulek:

SELECT * FROM articles 
INNER JOIN users
  ON articles.user_id = users.id
INNER JOIN articles_categories
  ON articles_categories.article_id = articles.id
INNER JOIN categories 
  ON articles_categories.category_id = categories.id 
WHERE users.email = 'john.doe@example.com';

Pokud v databázi dodržujeme silnou integritu dat, bude vše v pořádku. Co když ale rozhraní aplikace nepožaduje, aby autor zadal kategorii, když publikuje nějaký článek? V takovém případě dotazem uvedeným výše nedostaneme všechny články zvoleného autora. Články, které nemají uvedenou kategorii, budou z výsledků dotazu vyloučené, protože vnitřní spojení (inner join) požaduje, aby byly odpovídající záznamy v obou tabulkách. Dotaz ale můžeme přepsat takto:

SELECT * FROM articles 
INNER JOIN users
  ON articles.user_id = users.id
LEFT JOIN articles_categories
  ON articles_categories.article_id = articles.id
LEFT JOIN categories 
  ON articles_categories.category_id = categories.id 
WHERE users.email = 'john.doe@example.com';

Tato verze dotazu zachytí všechny články zadaného autora, protože se chybějící data o kategorii nahradí hodnotou NULL. Je to však náročnější operace. V ukázkové databázi trval dotaz s vnitřními spojeními 0,06 sekundy, zatímco s levými spojeními 0,29 sekundy – první byl tedy téměř pětkrát rychlejší. Protože levá spojení vracejí vždy nejméně to, co vnitřní spojení, používají se někdy „pro jistotu“, i když to není nutné.

Používejte jen to, co potřebujete

Aby byly ukázkové dotazy kratší, načítají se v nich data klauzulí SELECT *, která říká, aby databáze vrátila všechna data řádků vyhovujících dotazu. Téměř určitě to ale bude mnohem víc dat, než skutečně potřebujeme. Zejména je to nežádoucí u dlouhých textových sloupců, jako je sloupec obsahující příspěvky do blogu v tabulce articles. Ve druhém ukázkovém dotazu jsme chtěli získat všechny komentáře ke článkům zadaného autora. Dotaz je ale napsaný tak, že bude načítat kompletní obsahy článků (spolu se spoustou dalších dat), a to ne jednou pro každý článek, ale jednou pro každý komentář. Takže, má-li nějaký článek 300 komentářů, bude databáze načítat úplný text článku třistakrát. Mnohem rychlejší bude, když vybereme jen ty sloupce, o které se zajímáme, jako zde:

SELECT comments.* FROM articles
INNER JOIN [...]

Vskutku, tato změna ještě čtyřikrát urychlí předchozí ukázkový dotaz, na už slušných 0,08 sekundy.

Nejsnadnější změny obvykle stačí

Naštěstí to bývá tak, že největší zisky přinášejí nejsnadnější změny. Hlubší analýzy se obvykle požadují jen tehdy, potřebujete-li vyladit nějaké závažné úlohy. Proto následuje hrst „nejlepších praktik“ databázového designu, které urychlí a zefektivní většinu webových aplikací.

Mějte na paměti, že většina databázových serverů, jako jsou MySQL, PostgreSQL, či Microsoft SQL Server, vykonávají dotazy i implementují indexy odlišně. Nejlepší je, když se budete, co do podrobností, spoléhat na dokumentaci dané platformy, základní principy však zůstávají ve většině případů stejné. Všechny příklady citované výše jsme vyzkoušeli s MySQL 5.0. Určitě neuškodí připomenout, že existují jisté podtypy indexů se specifickými vlastnostmi – jako že si vynucují, aby byly hodnoty jedinečné, nebo že umožňují fulltextové vyhledávání.

Až příliš často se svaluje vina za mizerný výkon na hardware nebo na kód aplikací, a přitom, opět až příliš často, je pravý pachatel skrytý na databázové úrovni. Budete-li zde prezentovaná snadná opatření implementovat včas, předtím než vnímavost rozhraní začne pokulhávat, může těchto několik drobných krůčků způsobit obrovitý posun k lepším zážitkům vašich uživatelů.

Informace o překladu

Přeloženo se svolením autora / magazínu A List Apart. Zde naleznete další překlady.

About translation

Language of translation: Czech (for readers from Czech and Slovak republics). Translated with the permission of A List Apart and the author. Other translations.

Štítky: překlady

Mohlo by vás také zajímat

Nejnovější

5 komentářů

  1. Anonym

    Bře 1, 2010 v 20:49

    Ďakujem za preklad. Výborný článok. Určite mi pomohol.

    Odpovědět
  2. peter

    Kvě 12, 2010 v 18:45

    dobry clanok, vdaka za preklad
    skoda, ze clanok bol prekladany
    bol som chvilu v opojeni, ze aky dobry autor a ja si ho az teraz vsimnem

    v kazdom pripade by som len doplnil, ze castokrat ked sa vyhladava cez rozne lowercase funkcie, tak sa index nepouziva na dany stlpec
    aspon v DB2 to nie je
    ak ma niekto info o inych DB, prosim napiste

    Odpovědět
  3. peter

    Kvě 12, 2010 v 18:49

    chcel by som sa opytat, ci by sa nedalo nejako upravit, aby som si mohol nejako rychlo vyhladat vsetky clanky od autora prekladu

    Odpovědět
  4. Miroslav Kucera

    Kvě 12, 2010 v 21:34

    Vsechny clanky od tohoto prekladatele? V podstate staci zadat do hledani termin: RNDr. Jan Pokorný

    Odpovědět
  5. Čamo

    Úno 15, 2013 v 19:02

    Musím to skúsiť:
    Prepisujem svoj CMS a práve si lámem hlavu nad scriptom, ktorý generuje menu.
    Generujem ho z tabuľky, ktorá obsahuje klasickú stromovú štruktúru (parentID) pre každú položku. V tom menu bude samozrejme odkaz napríklad na blog alebo eshop. Tieto sekcie samozrejme môžu obsahovať obsiahly podstrom položiek. A chcem sa spýtať, či by bolo dobré rozdeliť tie sekcie do samostatných tabuliek, alebo ako to urobiť? Doteraz som mal všetko v jednej.

    Odpovědět

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *