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

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ůvodní článek: Indexing the Web – It’s Not Just Google’s Business (A List Apart).
• Překlad: RNDr. Jan Pokorný.
• Odborná a jazyková spolupráce: Miroslav Kučera.

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

About translation

• Original article: Indexing the Web – It’s Not Just Google’s Business (A List Apart).
• Translation: RNDr. Jan Pokorný.
• Language and expert collaboration: Miroslav Kučera.

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