Indexy pro urychlení MySQL dotazů

Pokud máte pomalý MySQL dotaz, který data vybírá, filtruje nebo řadí podle více sloupců, můžete ho urychlit pomocí indexu. Způsobů, jakým index vytvořit přesně pro konkrétní dotaz je několik a vlastně žádný není úplně správný.

Můžete ale použít následující kroky pro jeho vytvoření.

Vysvětlení

Základem analýzy MySQL dotazů je p¨říkaz EXPLAIN, který příkaz neprovede, ale pouze vypíše, jaké tabulky a indexy bude muset databáze použít pro získání výsledku.

EXPLAIN SELECT * FROM table WHERE a = 1 AND b = 2;

Ve výpisu vás jako první zajímá sloupec rows. Čím více řádek musí databáze projít, aby získala výsledek, tím pomalejší je dotaz. Počet řádek je právě to, co potřebujete omezit, aby byl dotaz rychlejší. Uvědomte si, že počet řádek zobrazený v příkazu EXPLAIN je pouze přibližný, protože databáze ve skutečnosti příkaz nevykonává, takže ani neví, kolik přesně řádek získá jeho provedením. Počet se může lišit až o řády (tedy desetkrát víc nebo méně).

U víceřádkového výpisu (pokud jsou v dotazu JOINy nebo sub-SELECTy) je pak důležitý součin všech řádek. Dotaz, který v prvním kroku projde 10’000 a pak již hledá po jedné řádce bude rychlejší než dotaz, který v prvním kroku projde jen 1000 řádek, ale v druhém musí projít 100 řádek (takže celkem projde 100’000 řádek).

Někdy – pokud jedna aplikace běží nad více databázemi – může být potřeba pro jeden dotaz vytvořit i několik indexů podle toho, jaká data vybírá. Pokud dotaz vybírá z malé tabulky, stačí mu menší index s pár sloupci, zatímco na velkou tabulku bude potřeba index s více sloupci.

Co je to index?

Pokud provedete dotaz bez indexu, musí databáze projít všechny řádky tabulky od první do poslední (s výjimkou, kdy zadáte LIMIT) a u každé ověřit, jestli splňuje zadané WHERE podmínky.

Index je další tabulka (vlastně strom), která obsahuje všechny hodnoty daného sloupce a u každé hodnoty čísla řádků, které danou hodnotu obsahují. Při použití indexu tedy stačí databázi v indexu najít správnou hodnotu a získá seznam všech řádek, které má vybrat.

Pokud má index více sloupců (multi-column), obsahuje v první úrovni všechny hodnoty prvního sloupce a v druhé úrovni hodnoty druhého sloupce vyskytující se v řádkách, které mají danou hodnotu v prvním sloupci.

Pro příklad vezměme tabulku lidí, kde máme sloupce pohlaví a plnoletost:

[
    'muž' => [
        'děti' => [5, 8, 12, ...],
        'dospělý' => [1, 3, 9, ...],
    ],
    'žena' => [
        'děti' => [2, 6, 10, ...],
        'dospělý' => [3, 4, 7, 11, ...],
    ],
] //dospělí muži jsou na řádkách 1, 3, 9, atd.

Sestavení indexu

Uvedený postup nemusí být 100% účinný a vždy je potřeba se podívat na výsledek EXPLAIN před a po změně indexu.

Kroky budou uvedeny v pořadí, v jakém budete sloupce přidávat do indexu, takže sloupec (nebo sloupce) z prvního kroku bude jako první v indexu, sloupec z druhého kroku jako druhý, atd.

Pamatujte na to, že databáze nemusí použít index celý. To znamená, že pokud má databáze k dispozici index [a,b,c,d], tak ho může použít i když jsou v dotazu použity jen sloupce a a b. Nemůže ho ale použít, pokud jsou v dotazu sloupce a a c. Indexy tedy můžete sdílet mezi dotazy, které používají různý počet sloupců, ale ve stejném pořadí.

... WHERE a = 1 AND b > 5 ORDER BY c;
... WHERE a > 5 ORDER BY b;
//oba dotazy mohou použít index [a,b,c]

Příprava indexů

Jako úplně první krok byste měli zkusmo vytvořit indexy na všech sloupcích (tedy pro každý sloupec jeden index). Tím databázi umožníte zhodnotit, které všechny indexy přicházejí v úvahu a zobrazí je v příkazu EXPLAIN ve sloupci key.

Tím získáte představu o tom, kterými sloupci byste měli začít při sestavování indexu. Samozřejmě to není definitivní, a podle následujícího popisu můžete do indexu přidat i sloupce, které si databáze primárně nevybrala.

V některých případech můžete i tohle pomoci, protože databáze může použít tzv. spojení indexů, kdy nejprve najde všechny řádky podle jednoho indexu, pak všechny řádky podle druhého a nakonec vypíše řádky, které našel v obou indexech. Tento způsob ale nikdy nebude rychlejší než index s více sloupci.

Optimalizace

Před tím, než databáze začne hledat hodnoty v indexu, pokusí se podmínky zoptimalizovat a omezit ji pouze na hodnoty, které skutečně může v indexu najít a které má smysl hledat, aby se tím omezil počet řádek. Pokud by hledáním určité hodnoty v indexu naopak počet řádek stoupl (i když třeba jen o 1), databáze ho nepoužije.

V prvním kroku se z WHERE vyhází všechny podmínky, pro něž neexistuje index a nahradí je hodnotou TRUE (to samé jako nesmyslné a často používané 1=1). Následně optimalizátor projde podmínky a vyhází ty, jejichž kombinace nedávají smysl.

WHERE 1=1 AND a = 10        => hledá jen a = 10
WHERE a = 10 OR TRUE        => hledá vše, index [a] se nepoužije
WHERE a > 5 OR a = 10       => hledá jen a > 5, což zároveň najde a = 10
WHERE a > 5 AND a = 10      => hledá jen a = 10 - jediné splňuje i a > 5
WHERE a > 10 AND a = 5      => nic nehledá, podmínka nedává smysl

WHERE (a > 5 OR a = 10) AND (b IS NOT NULL AND b BETWEEN 5 AND 10)
// hledá "a > 5 AND b BETWEEN 5 AND 10" v indexu [a, b]

Někdy tak databáze nepoužije váš index, i když jste do něj zahrnuli sloupce z podmínky, protože po optimalizaci tyto sloupce z podmínky vypadli (a budou se vyhodnocovat až po projití indexu nad konkrétními řádky).

Hledání konkrétní hodnoty

Číslo

Nejjednodušší operace je porovnání hodnoty sloupce proti konkrétní číselné hodnotě a proto ji MySQL řadí na první místo.

WHERE a = 1 AND b = 2

Z takového dotazu vám pak vyjde index [a, b].

Pozor na to, že tohle platí jen pro sloupce, které mají skutečně číselnou hodnotu, tedy INT, BOOL, atd. Sloupce, které se jako číslo tváří, ale jsou uloženy jako řetězec (např. DECIMAL, FLOAT, atd.) index použít nemohou nebo jen v omezené míře (tedy v případě, že nic lepšího není).

Tento případ platí jen pokud je ve WHERE každý sloupec použit jen jednou a pokud jsou všechny hodnoty spojeny operátorem AND. V jiném případě jde o hledání rozsahu a nebude tedy zařazeno na první místo (viz dále).

NULL

Hned za porovnáním čísla je porovnávání proti NULL, protože stejně jako číslo představuje jednu konkrétní hodnotu:

WHERE a IS NULL AND b = 2

Z tohoto můžete udělat index [b, a].

Podobně funguje i operátor IS NOT NULL, akorát že opačně – přeskočí řádky, které obsahují NULL a pak prochází zbytek. Tento operátor se ale řadí mezi rozsahy.

ENUM

Jako číslo může být považován i ENUM, i když databáze ho může a nemusí brát v úvahu podle toho, kolik dalších podmínek v dotazu je. Musíte se tedy podívat na EXPLAIN, co říká.

WHERE a = 'BLUE' AND b = 'DOLPHIN' //Může použít index [a, b]
WHERE a = 1 AND b = 2 AND c = 'RED' //použije jen [a, b] místo [a, b, c]

Mohutnost

Při určení pořadí sloupců v indexu je potřeba se podívat na mohutnost sloupce (anglicky cardinality) v indexu. Laicky řečeno mohutnost je počet hodnot, které jsou v daném sloupci uloženy. Např. sloupec obsahující pohlaví (1 = muž, 2 = žena) má mohutnost 2, zatímco usa_country bude mít mohutnost (zhruba) 50 (v USA je 50 států).

U ENUM může být mohutnost větší, protože může zahrnovat hodnotu NULL a také speciální prázdnou hodnotu (ENUM index 0), která se uloží, pokud vložíte řádek s neznámou hodnotou. Naopak může být menší, pokud nejsou všechny hodnoty použity, protože zobrazuje pouze skutečně použité hodnoty.

Pokud jako první dáte do indexu sloupec s menší mohutností, rychleji tak omezíte počet řádek, které musí databáze projít a urychlíte tak získání výsledku. V praxi to znamená, že pokud jako první dáte do indexu pohlaví a zeptáte se na ženy, databáze přeskočí první polovinu indexu obsahující muže a má to rychle. Pokud naopak jako první dáte usa_country a zeptáte se na 47. stát, databáze bude muset projít a přeskočit předcházejících 46 států a nějaký čas jí to zabere. Čím větší je rozdíl mohutností u sloupců, tím výraznější zrychlení bude po jejich správném seřazení.

Předchozí laické vysvětlení je ale potřeba upřesnit, protože mohutnost ve skutečnosti vyjadřuje složitost použití indexu pro vyhledání požadovaných řádek. U malých tabulek to zhruba odpovídá počtu hodnot, ale u tabulek s miliony záznamy bude určitou dobu trvat použití indexu i jen s dvěma hodnotami a proto bude mohutnost větší.  Pokud tedy tabulka obsahuje 1 milion mužů a 1 milion žen, může být mohutnost indexu gender klidně 1000 (místo očekávaných 2), protože vyjadřuje to, že pro nalezení všech žen bude muset DB nejprve přeskočit milion řádek indexu obsahující muže. Nutné je ale poznamenat, že mohutnost sloupce usa_country by u téže tabulky byla třeba 100000, takže pořád je omezení podle gender rychlejší.

Rozdílná situace nastane, pokud v dotazu kombinujete dvě tabulky, kde první má milion záznamů a index gender a druhá má 100 záznamů a index usa_country. Pak by mohutnost pohlaví byla 1000 zatímco u států jen 50 a hledání podle státu v druhé tabulce by bylo tedy mnohem rychlejší. V tomto případě ale není zase potřeba řešit, v jakém pořadí dát sloupce do indexu, ale pouze vysvětluje proč DB upřednostnila určitý index pro hledání.

Textové hodnoty

U textových sloupců můžete indexovat pouze tzv. prefix. To uděláte tak, že za jméno sloupce uvedete počet znaků, které se mají do indexu zahrnout, např. usa_country(3). Tím zajistíte, že bude mít index menší mohutnost (protože Alabama a Alaska bude mít jednu hodnotu a New York, New Jersey a New Hampshire také) a rychleji omezí počet řádek.

Prefixový index samozřejmě můžete použít jen pro porovnávací podmínky v dotazu. Pokud používáte full-text hledání (LIKE), nelze tento index použít. Pro tento případ má MyISAM (a InnoDB od verze 5.6) speciální FULL-TEXT index, který se ale používá příkazem MATCH(sloupec) AGAINST (slovo).

Datum

Není to příliš časté, ale může se stát, že hledáte řádky z konkrétního dne. I v tomto případě může databáze použít index na datumovém sloupci, ale je potřeba brát v úvahu mohutnost daného sloupce (365 hodnot za každý rok) a podle toho ho správně zařadit. Na úplně prvním místě bude málokdy.

Připojení tabulek

Při JOIN zadáváte do ON obvykle porovnávací podmínky (=) stejně jako do WHERE (vlastně je jedno, jestli je zadáte do ON nebo do WHERE – jde jen o lepší čitelnost dotazu pro programátory) a tak lze na ně také použít indexy. Jde to ale jen v případě, že je index na obou porovnávaných sloupcích a oba indexy mají stejný typ.

Samozřejmě v tomto případě nemůžete sestavit jeden index, ale musíte mít samostatný index v každé z tabulek:

SELECT *
FROM table1
INNER JOIN table2 ON table1.ref = table2.id
WHERE table1.a = 10 AND table2.a > 10

V tomto případě můžete do tabulky table1 dát index [a, ref] a do tabulky table2 index [id, a].

I zde je potřeba zvažovat pořadí sloupců, protože podmínky v ON i WHERE se zpracovávají společně. Pokud tedy sloupec použitý ve WHERE má menší mohutnost než ten z ON, je potřeba uvést jako první ten z WHERE a opačně.

Pozor na to, že indexy pro připojení lze použít jen v případě, že připojovací podmínka vychází z hlavní tabulky (té z FROM). Pokud tomu tak není, nelze index použít. Obejít to ale jde přeuspořádáním tabulek v dotazu:

//zde index použít nejde, protože je připojení 1 -> 2 -> 3
SELECT *
FROM table1
INNER JOIN table2 ON table1.ref = table2.id
INNER JOIN table3 ON table2.ref = table3.id

//Po prohození již index použít půjde přes připojení 1 <- 2 -> 3
SELECT table1.*, table2.*, table3.*
FROM table2
INNER JOIN table1 ON table1.ref = table2.id
INNER JOIN table3 ON table2.ref = table3.id

Pokud připojujete tabulku přes sloupec s jiným typem (např. SMALLINT vs. BIGINT nebo INT(5) vs. INT(10)), nemůže databáze použít index a musí v připojované tabulce projít všechny řádky. Sloupce CHAR a VARCHAR jsou z pohledu indexu srovnatelné, ale musí mít stejnou délku (např. [VAR]CHAR(10)) a stejné kódování (např. UTF-8), aby je šlo použít. V některých případech lze použít index i při porovnávání číselného (INT) a textového (VARCHAR) sloupce, ale pouze v případě, že řetězcový sloupec obsahuje pouze číselné hodnoty (což ale spíše napovídá o špatném návrhu tabulky a lepší optimalizace dosáhnete změnou typu sloupce).

Seskupení

Úplně nejvyšší prioritu v indexu mají sloupce, které se nacházejí v GROUP BY nebo DISTINCT. Pokud má být databáze schopna použít index na dotaz obsahující seskupení, musí index začínat přesně těmi sloupci a přesně ve stejném pořadí, jako je dané seskupení. Jedinou výjimku tvoří výše uvedené porovnání proti konstantní hodnotě, které může (znovu opakuji jako jediné) narušit pořadí sloupců.

SELECT a, b, c, d, e
FROM table
WHERE a > 10 AND b > 5 AND c in (1,2,3) AND d = 10 AND e IS NULL
GROUP BY b, c, a

Index musí být [b, c, a, d, e] aby splnil pořadí sloupců pro seskupení. Index [a, b, c, d, e] obsahuje sloupce ve špatném pořadí, index [e, b, c, a, d] začíná sloupcem, který v seskupení není, a index [b, c, d, e] zase neobsahuje všechny sloupce nutné pro seskupení.

Jediné další možnosti pro index jsou ty s jinak umístěným sloupcem d, který se v podmínce porovnává proti pevné hodnotě a tak ho lze umístit na libovolné místo v indexu – databáze totiž bude vědět, jak daný sloupec v indexu přeskočit, aby mohla vytvořit seskupení. Další platné indexy tedy jsou [d, b, c, a, e], [b, d, c, a, e] a [b, c, d, a, e].

Výše uvedené indexy mohou být použity i pro dotaz:

SELECT DISTINCT b, c, a 
FROM table 
WHERE a > 10 AND b > 5 AND c in (1,2,3) AND d = 10 AND e IS NULL

Aby seskupení přes index fungovalo, musí být všechny sloupce z jedné tabulky (a tedy z jednoho indexu) a další sloupce v indexu (d a e z příkladu) musí být porovnány proti konstantě (databáze je při seskupení přeskočí) nebo se nesmí v dotazu vůbec vyskytovat (např. platný by byl index [b, c, a, d, e, f, g, h]). Pokud by ale dotaz obsahoval SELECT f (bez f ve WHERE), WHERE f > 0 nebo WHERE f IS NOT NULL, již by index použít nešlo.

Pokud databáze nenajde žádný klíč, podle kterého by mohla provést seskupení, prohledá tabulku podle jiného indexu podle podmínky a zkopíruje nalezené řádky do dočasné tabulky (temporary table). Následně nad touto tabulkou provede seskupení a případné seřazení. To je pochopitelně pomalejší.

Porovnání rozsahu

Číslo

Poté, co databáze vyčerpá porovnání proti konkrétní hodnotě, začne pokukovat po rozsazích. Tím jsou myšleny základní operátory jako větší (>) a menší (<), včetně jejich kombinace s rovná se (>= a <=), tak i speciální jako BETWEEN...AND nebo negativní není rovno (<> nebo !=). Také sem patří IS NOT NULL, které vlastně hledá hodnoty větší než NULL.

WHERE a > 5 AND b < 10 AND c BETWEEN 100 AND 1000

Zde můžete použít index [a, b, c], pomocí kterého databáze odfiltruje řádky mimo požadované rozsahy.

Rozsah

Kromě operátorů může rozsah vzniknout kombinací sloupců nebo hodnot v podmínce.

Jedním takovým případem je použití operátorů OR, IN nebo BETWEEN na tentýž sloupec:

WHERE a > 10 AND (b = 1 OR b = 2) //použijte index [a, b]
WHERE a > 10 AND b IN (1, 2)      //tentýž případ, jen jinak zapsaný

WHERE a = 10 OR a IN (1,2)        //je jedno, jak je kombinujete
WHERE a > 10 OR a BETWEEN 5 AND 8 //pořád je to jeden rozsah

Jak je vidět, je jedno, jak složitou podmínku použijete, ale dokud probíhá hledání nad jedním sloupec, může databáze použít jeden index k vyfiltrování požadovaných hodnot.  Samozřejmě čím větší mohutnost sloupce a/nebo čím víc hodnot hledáte, tím bude hledání pomalejší.

Datum

Pro porovnávání rozsahu můžete použít i sloupce typu DATE, TIMESTAMP, atd.

WHERE start >= '2010-01-01' AND end BETWEEN '2015-07-01' AND '2015-12-31'

Zde můžete pomocí indexu [start, end] (nebo [end, start] – záleží na verzi DB) výrazně omezit počet řádek jen na ty z 6 požadovaných měsíců.

Pozor na to, že indexy nad datumovými nebo časovými sloupci mívají obecně velkou mohutnost, protože považují každý den nebo sekundu za samostatnou hodnotu. Optimalizaci pak můžete provést tak, že před časový sloupec uvedete nějaký číselný – a pokud takový nemáte, můžete si pomoci jeho vytvořením.

Např. výše uvedený dotaz na 6 měsíců můžete urychlit tím, že přidáte sloupec end_year:

INSERT INTO table  (start, end, end_year) 
             VALUES($start, $end, YEAR($end))

Dotaz pak upravíme na:

WHERE 
    start >= '2010-01-01' 
    AND end BETWEEN '2015-07-01' AND '2015-12-31'
    AND end_year = 2015

A z výše uvedených faktů vám musí být jasné, že index [end_year, end] bude mnohem rychlejší, protože mohutnost end_year je v řádu jednotek nebo desítek (tedy rovna počtu roků v DB) zatímco u end bude 365 * počet let.

LIKE a další řetězce

Mezi porovnání rozsahů se počítá i použití operátoru LIKE za předpokladu, že hledaná hodnota nezačíná znakem %.

WHERE a = 10 AND b > 5 AND c LIKE "xyz%"

V tomto případě můžete použít index [a, b, c(3)], abyste našli všechny řádky splňující dané pravidlo.

Stejně můžete použít i další operátory pracující s řetězci.

WHERE a > 'xyz' AND b BETWEEN 'f' AND 'k' AND c NOT LIKE "xy%"

Pro všechny tyto podmínky je použitelný index [a(3), b(1), c(2)].

Řazení

Hned za porovnáváním rozsahů je řazení, které je vlastně porovnávání dvou hodnot jako větší/menší.

WHERE a > 5 AND b = 10
ORDER BY c, d

Zde můžete použít index [b, a, c, d], který nejprve profiltruje řádky a následně je seřadí podle předem daného pořadí.

Pozor na to, že indexy jsou vždy řazené vzestupně, takže je není možné použít, pokud řadíte sloupce v různých směrech:

WHERE a > 5 AND b = 10 
ORDER BY c DESC, d

Jelikož sloupce c řadíme sestupně a sloupec d vzestupně, nelze index použít. Pokud bychom ale řadili oba sestupně, může databáze prostě přečíst index odzadu (pozpátku), což je ale pomalejší než normální (dopředné) čtení.

Poznámka: při vytváření indexu můžete použít následující definici:

 CREATE INDEX index_a_b_c_d ON table (a, b, c DESC, d ASC)

Tím definujete, že sloupec c lze podle indexu seřadit sestupně. Bohužel ale většina MySQL/MariaDB verzí tuto definici ignoruje a vytvoří index vzestupně. Plně podporované by sestupné indexy měli být v MySQL 8.0 (poslední verze je Release Candidate z 23. ledna 2018).

Pozor na to, že řazení podle indexu funguje i opačně. To znamená, že pokud v dotazu neuvedete konkrétní řazení, databáze vrátí řádky v pořadí podle použitého indexu:

SELECT a, b
FROM table
WHERE a > 5 AND c IS NOT NULL
//použitím indexu [c, a] seřadí data podle "ORDER BY c ASC, a ASC"
//ale podle indexu [a, c] je seřadí jako "ORDER BY a ASC, c ASC"

Pozor na to, že v každé verzi může databáze upřednostnit jiný index nebo použít další optimalizace, takže pak bude vracet výsledky v jiném pořadí. Nikdy nespoléhejte na to, že řádky budou seřazeny na základě použitého indexu! Pokud chcete mít řádky seřazené, vždy uveďte ORDER BY.

Vybrané řádky

I když se to může zdát podivné, pokud váš dotaz vybírá konkrétní sloupce (v malém počtu), můžete dotaz urychlit tím, že je také uvedete do indexu. Databáze pak nemusí pro výsledek koukat do samotné tabulky, ale získá hodnoty přímo z indexu. Také si uvědomte, že pokud vybíráte sloupec, pomocí něhož již filtrujete nebo řadíte, už v indexu jednou je.

SELECT a, b, c, d, e, f
WHERE a > 10 AND b = 5
ORDER BY d, c

Pokud použijete index [b, a, d, c, e, f], použije databáze sloupce a a b pro filtrování, sloupce d a c pro seřazení a následně vypíše všechny sloupce z indexu, protože jsme do něj přidali i hodnoty sloupců e a f. Celý dotaz pak bude probíhat nad indexem bez přístupu do samotné tabulky.

Toto má samozřejmě smysl jen pro opravdu hodně velké tabulky s velkým množství sloupců (a vy jich potřebujete vybrat jen pár) nebo s velkým množstvím řádek (miliony) a vy jich pomocí indexu vyberete jen pár (desítky).

Minimum a maximum

Index lze úspěšně použít i v případě, že v SELECT používáte funkce MIN() a/nebo MAX() (ale na žádné jiné jako COUNT() nebo SUM()):

SELECT min(c), max(c)
WHERE a = 10
GROUP BY b

V tomto dotazu může databáze použít index [a, b, c] pro nalezení nejmenší a největší hodnoty bez procházení tabulky díky tomu, že hodnoty v indexu jsou vždy seřazeny (takže minimum je první a maximum poslední). Podmínkou použití indexu je, že funkce MIN() i MAX() musí používat pouze jeden sloupec (pro výběr SELECT MIN(c), MAX(d) by index použít nešel) a tento sloupec musí být v indexu ihned za sloupcem z GROUP BY.

SELECT c, min(d)
WHERE a = 10
GROUP BY b
ORDER BY e

Index musí být [a, b, d, e, c] přesně v tomto pořadí (resp. sloupec a může být i na druhém nebo třetím místě – viz index pro seskupení).

Ještě existují další případy, kdy lze použít index, ale ty se málokdy používají. Dotazy, které mohou využít index [a, b]:

SELECT COUNT(DISTINCT a) FROM table;
SELECT AVG(DISTINCT a) FROM table;
SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM table;

Primární index

Jako primární index se zpravidla používá sloupec id nebo podobný, což je většinou správné. Primární index ale určuje, jak se budou řadit data v samotné tabulce. Pokud je tedy id v tabulce pouze pomocný údaj a samotná data hledáte podle jiného sloupce (např. jmeno), může dotazy urychlit nastavení primárního indexu na daný sloupec (a na id použít pouze UNIQUE). Samozřejmě to lze použít jen v případě, že sloupec jmeno obsahuje unikátní hodnoty.

I primární index může mít více sloupců, čímž můžete obejít problém s unikátností hodnot nebo zrychlit výchozí řazení dat.

SELECT *
FROM users
WHERE name = $name
order by age

Pokud tento dotaz představuje 99% dotazů do tabulky users, můžete primární filtr nastavit na [name, age, id], čímž data seřadíte tak, jak je bude databáze potřebovat a zároveň přidáním id zajistíte, že nedojde ke kolizi po přidání dvou stejně se jmenujících a stejně starých úživatelů.

InnoDB index

Pokud máte tabulku typu InnoDB, všechny indexy budou (na svém konci) obsahovat i sloupce z primárního indexu, i když je přímo do daného indexu neuvedete. Pro vás to znamená, že sloupce z primárního indexu nemusíte přímo do indexu uvádět, protože se tam sami vnutí.

SELECT id FROM table WHERE a = 10 AND b IS NULL AND id > 0 ORDER BY id

Pokud máte primární index nad sloupcem id, stačí vám sekundární index [a, b] a databáze stejně nebude muset sahat do tabulky, protože efektivně použije index [a, b, id].

Na druhou stranu tohle může zabránit použití indexu na seskupení:

SELECT id FROM table WHERE a = 10 AND b > 0 AND id > 0 GROUP BY b

V tomto dotazu nad InnoDB nebude možno použít index [a, b], který by v jiné tabulce použít šel (a je konstantní a tak se přeskočí), protože efektivně bude použit index [a, b, id] a id se v dotazu porovnává proti rozsahu.

Vynucení indexu

Někdy se může stát, že vytvoříte index, o kterém víte, že je pro daný dotaz ideální (obsahuje všechny vybírané sloupce a má správné řazení), ale databáze tvrdohlavě používá jiný.

V tomto případě můžete použití vašeho indexu vynutit:

SELECT a,b,c,d 
FROM table USE INDEX (index_a_b_c_d)
WHERE a = 1 AND b = 2
ORDER BY c

Do závorky za USE INDEX() můžete uvést jeden nebo i více indexů (oddělených čárkami) a databáze si pak jeden z nich vybere. Parametr říká, že databáze NESMÍ použít žádný jiný index než ty uvedené v parametru. Parametr se zadává za jménem tabulky za nebo místo aliasu (AS).

Parametr USE INDEX() je pouze tzv. nápověda (hint), což znamená, že databáze může použít (pouze) uvedený index, ale také nemusí, pokud jí přijde lepší projít celou tabulku (full scan). Pokud chcete zamezit procházení celé tabulky, použijte místo toho FORCE INDEX(), který použití vynutí v jakémkoliv případě. I FORCE INDEX() má však jednu výjimku a tou je případ, kdy databáze nenajde způsob, jak index použít pro získání výsledku dotazu (např. pokud index obsahuje jiné sloupce než jaké používá dotaz).

Pokud chcete naopak vyloučit určitý index z dotazu, můžete použít IGNORE INDEX(), který zakáže použití vyjmenovaných indexů.

Pokud vám nevadí, když databáze použije určitý index pro WHERE, ale chcete ho vyloučit pro řazení nebo sloučení, můžete parametr rozšířit. Všechny možnosti (JOIN, GROUP, ORDER) platí pro všechny typy (USE, FORCE, IGNORE):

... FROM table USE INDEX FOR JOIN (index) WHERE ...
... FROM table FORCE INDEX FOR GROUP BY (index) WHERE ...
... FROM table IGNORE INDEX FOR ORDER BY (index) WHERE ...

... JOIN table AS t1 USE INDEX FOR JOIN (index) ON t1.id = t.id ...

Důvody nepoužití indexu

Databáze nemusí využití index pro hledání konkrétní hodnoty nebo rozsahu, pokud množství řádek, které by index odfiltroval, je výrazně (o několik řádů) menší než kolik je počet řádek očekávaných ve výsledku.

Vezměme si pro příklad tabulku users, ve které je milion uživatelů a tisíc z nich je označeno jako deleted = 1.  Pokud do dotazu přidáte WHERE deleted = 1 nebo WHERE deleted > 0, pak může databáze pomocí indexu vybrat oněch 1000 smazaných uživatelů a dotaz urychlit.

Pokud ale naopak do dotazu přidáte WHERE delete = 0, použitím indexu by se počet uživatelů zmenšil z milionu na 999’000 (tedy o desetinu procenta), což nemá smysl, protože projití (skoro) celého indexu je pomalejší, než projití (úplně) celé tabulky.

Pokud tedy databáze (resp. její optimalizátor) už dopředu odhadne, že použití určitého indexu bude pomalejší, než použití jiného indexu nebo projití celé tabulky (tedy projití primárního indexu), nepoužije ho. V tomto případě sice můžete použít FORCE INDEX(), ale pravděpodobně tím způsobíte zpomalení.

 Co nedělat

  1. každý index zabírá místo na disku. Čím více sloupců a hodnot je v indexu, tím více místa zabírá. A může to být i řádově více, než kolik má samotná tabulka. Vytváření zbytečných nebo zbytečně velkých indexů může zaplnit váš disk mnohem rychleji a/nebo může zabránit načtení tabulky do paměti.
  2. Pokud má index (řádově) stejnou mohutnost jako je počet řádek v tabulce, databáze upřednostní projití tabulky, protože index je v tomto případě pomalejší. To ale neplatí v určitých případech jako je použití indexu pro seskupení nebo seřazení – pak ale zpravidla stačí menší index jen s danými sloupci.

Další optimalizace

Kromě indexů můžete dotazy urychlit jinými způsoby zápisu, které vrátí stejný výsledek (stejné řádky), ale v často řádově kratším čase.

Sub-select místo JOIN pro výběr dat

Pokud potřebujete ke každé položce (řádce) získat další data z jiné tabulky, většinou se k tomu používá JOIN:

SELECT 
    u.name AS user, 
    g.name AS group
FROM users AS u
LEFT JOIN groups AS g
    ON u.group = g.id

Tento dotaz vypíše jména všech uživatelů a jméno skupiny, do které patří. Problém dotazu je ten, že pokud máte velké množství uživatelů i skupin, bude muset dotaz projít všechny kombinace (počet uživatelů * počet skupin) a porovnat jejich id (v tomto případě asi ne, protože group.id bude pravděpodobně primární index a proto bude hledání rychlé, ale uvažujme případ, kdy daný sloupec index nemá).

Stejného výsledku dosáhneme i tím, že místo JOIN použijeme sub-select (tedy select místo sloupce; česky „vnořený výběr“):

SELECT 
    u.name AS user, 
    (SELECT name FROM groups AS g WHERE g.id = u.group) AS group
FROM users AS u

Tento dotaz nejprve vypíše všechny uživatele a pak ke každému zjistí, do jaké skupiny patří. Všimněte si, že stejnou podmínku, jakou jsme v prvním dotazu uváděli do ON ... nyní uvádíme jako WHERE ... u sub-selectu. To je důležité proto, aby se data správně spárovala.

Tuto optimalizaci lze ale použít jen případě, že propojení tabulek je 1:1 (tedy každý uživatel může patřit jen do jedné skupiny. Sub-select totiž vždy musí vracet pouze jednu hodnotu (tedy jeden řádek); v opačném případě skončí celý dotaz chybou a nic nevrátí.

Sub-query místo JOIN pro podmínku

Pokud potřebujete z jedné tabulky získat data v závislosti na hodnotě v jiné tabulce, ale hodnota z druhé tabulky vás nezajímá (nepotřebujete ji tedy získat ve výsledku), můžete použít sub-query v podmínce.

 SELECT
    u.id AS id, 
    u.name AS user
FROM users AS u
LEFT JOIN groups AS g
    ON u.group = g.id 
WHERE g.language = 'cz'

Tento dotaz získává id a jméno uživatele z tabulky uživatelů, ale vybírá jen uživatele patřící do skupiny s českým jazykem. Všimněte si, že z tabulky groups ale nevracíme žádná data v SELECT sloupec ..., ale pouze ji používáme ve ... WHERE podmínce. I v tomto případě ale bude muset databáze projít všechny řádky, tedy (počet uživatelů * počet skupin) a bude to trvat dlouho.

Pomocí sub-query (česky „vnořený dotaz„) dotaz urychlíme:

SELECT id, name
FROM users
WHERE users.group IN (
    SELECT id
    FROM groups
    WHERE users.group = groups.id 
        AND groups.language = 'cz'
) 

Tento upravený dotaz vybere všechny uživatele a následně zjistí, kteří z nich patří do skupiny s českým jazykem, čímž je dotaz o něco rychlejší (jak ho ještě více urychlit viz další kapitola). Při použití sub-query musíte dát pozor, abyste správně spárovali tabulky stejnou podmínkou, jakou byste použili v ON ... při JOINu.

Také si všimněte, že zatímco u prvního dotazu jsme museli používat SELECT u.id AS id, u.name AS name, protože obě tabulky obsahují stejně pojmenované sloupce, u druhého dotazu to již není potřeba, protože tabulka groups se k dotazu nepřipojuje.

Podmínka Exists() místo sub-selectu

Operátor IN(SELECT) nebo obecně většinu sub-query v podmínce můžete nahradit operátorem EXISTS() (ano, je to operátor, i když zápisem vypadá spíše jako funkce).

Vezměme výše vytvořený dotaz pro získání uživatelů z českých skupin:

SELECT id, name
FROM users
WHERE users.group IN (
    SELECT id
    FROM groups
    WHERE users.group = groups.id 
        AND groups.language = 'cz'
)  

Pokud máte relativně malé množství skupin, dotaz bude rychlý. Pokud ale množství skupin naroste na tisíce nebo dokonce miliony, bude dotaz trvat dlouho, protože pro každého uživatele bude muset vypsat všechny (české) skupiny a pak zjistit, jestli uživatel do některé z nich patří. To proto, že MySQL vyhodnocuje podmínky tzv. „z vnějšku dovnitř“ – tedy nejprve získá všechny řádky vnějšího dotazu (users) a pak teprve pro každý řádek vyhodnocuje vnořený dotaz (groups).

Stejný výsledek vrátí i následující dotaz, který bude ale rychlejší díky tomu, že pro každého uživatele vybere jen tu jednu skupinu, do které patří, a pak teprve ověří, jestli má skupina požadovaný jazyk:

SELECT id, name
FROM users
WHERE EXISTS (
   SELECT 1
   FROM groups
   WHERE users.group = groups.id AND groups.language = 'cz' 
)

Při použití operátoru EXISTS() musíte opět dát pozor, abyste nezapomněli uvést propojovací podmínku (ON ... z JOIN), protože jinak by fungovala podmínka ve smyslu „pokud má kterákoliv skupina český jazyk, vyber všechny uživatele nezávisle na jejich skupině„, což je nesmysl.

Všimněte si, že sub-query uvádíme jako SELECT 1, protože operátor EXISTS() kontroluje pouze to, zda sub-query vrátí nebo nevrátí nějaký řádek, ale nezajímají ho vrácená data, takže hodnota 1 je stejná jako SELECT * nebo cokoliv jiného. Operátor EXISTS() to stejně ignoruje.

Další výhoda operátoru EXISTS() je v tom, že automaticky ukončí sub-query po nalezení prvního řádku, takže není potřeba (resp. se to ani nedoporučuje pro lepší čitelnost) uvádět LIMIT 1 a sub-query přesto vždy vrátí pouze jeden řádek (nebo žádný, pokud podmínka neplatí). Toto platí díky tomu, že jde o operátor; kdyby to byla funkce, musela by počkat, až dotaz vrátí všechny nalezené řádky a pak teprve ověřit, jestli nějaké existují. Operátor funguje jinak a proto může sub-query ukončit.

Operátor EXISTS() nelze (takto jednoduše) použít jako náhradu IN(SELECT) v případě, že jeden z propojovacích sloupců (tedy users.group nebo groups.id v příkladu) může mít hodnotu NULL protože podmínky NULL IN (X) a X IN (NULL) jsou platné, ale X = NULL nikoliv (bylo by nutno použít X IS NULL). Pro konkrétní případy můžete (resp. musíte) podmínku upravit tak, aby platila podle potřeby, např. users.group = groups.id OR users.group IS NULL.

Opakem operátoru EXISTS() je NOT EXISTS(), který je splněn v případě, že sub-query nevrátí ani jeden řádek.

Poznámka: Pokud chcete operátor EXISTS() použít společně s Nette Database Explorer, budete muset vnitřní dotaz uzavřít do Literálu, protože jinak by Nette zkoušelo nevhodně najít reference mezi tabulkami z vnějšího a vnořeného dotazu:

//použití EXISTS() v Nette:
$this->getDatabase()->getTable('users')
    ->select('id, name')
    ->where('EXISTS(?)', new SqlLiteral('SELECT 1 FROM groups'
        . ' WHERE users.group = groups.id AND groups.language = ?,
        ['cz'] //parametry pro Literál jako pole!
    ) //konec ->where() 
;

Groupování

Pokud je propojení naopak 1:n, lze sub-select použít jen v kombinaci s nějakou funkcí, která vrací jednu hodnotu a obvykle ji používáte s GROUP BY:

SELECT 
    g.name AS group, 
    COUNT(u.id) AS user_count
FROM groups AS g
INNER JOIN users AS u
    ON u.group = g.id
GROUP BY g.id

Tento dotaz vypíše počet uživatelů v každé skupině.

Kvůli použití JOIN ale nejprve vypíše všechny uživatele pro každou skupinu a pak teprve je bude počítat – počet řádek před grupováním tedy bude (počet skupin * počet uživatelů), což u tabulky s milionem uživatelů bude trvat velmi dlouho (nezávisle na tom, zda máte na sloupci users.group indexy či nikoliv).

Dotaz, který vrátí stejný výsledek v nesrovnatelně kratším čase, protože nejprve vypíše skupiny a pak jen spočte počet uživatelů pro každou z nich, vypadá následovně:

SELECT 
 g.name AS group, 
 (SELECT COUNT(id) FROM users WHERE group = g.id) AS user_count
FROM groups AS g

Zde bude počet nalezených řádek roven pouze počtu skupin a součet bude probíhat jen pro dané uživatele – tedy pokud máte index na users.group. V opačném případě nic neušetříte, protože se pro každou skupinu bude muset projít celá tabulka uživatelů.

Průběžný součet

Představte si situaci, kdy máte eshop a chcete přilákat zákazníky tím, že jim pošlete slevový kupón (např. 100Kč sleva při nákupu nad 1000Kč). Samozřejmě nechceme kupón posílat všem, ale jen těm, kteří za poslední měsíc nic nekoupili. Také chceme vyloučit dlouhodobě nečinné zákazníky, takže kupón pošleme jen tomu, kdo za poslední 3 měsíce utratili alespoň 10’000Kč nebo provedl alespoň 10 nákupů za poslední půlrok nebo v posledním roce měli alespoň jeden nákup za více než 5’000Kč. Zároveň nemá smysl posílat kupón na zrušené účty nebo zákazníkům, kteří se odhlásili z odběru „spamu“ (tedy newsletter, výhodně nabídky apod.). Kdo nějaký eshop skutečně má, určitě vymyslí i další podmínky. A samozřejmě chce provést kontrolu každý den, abyste měli co největší šanci získat zpět co nejvíce zákazníků.

Když si všechny podmínky projdete a vypíšete z nich SQL dotaz, zjistíte, že ať se snažíte sebevíc, dotaz bude trvat dlouho (tedy pokud máte slušně fungující eshop s tisíci zákazníků a nákupů). Kvůli různým výpočtům průměrných hodnot nebo počtů za určitou dobu totiž databáze nemůže použít indexy a bude muset projít všechny zákazníky a nákupy.

V tomto případě můžete databázi pomocí tím, že místo vytváření indexů na tabulkách rovnou vytvoříte nové tabulky, do kterých si budete průběžně ukládat součty a/nebo průměry podle toho, co potřebujete.

Například pro podmínku „uživatelé, kteří v posledním půl roce měli nákup za 5’000Kč“ stačí vytvořit tabulku, do které zapíšete id všech nákupů s částknou nad 5’000Kč a datumem (INSERT INTO nakupy_nad_5tisic VALUES($user_id, NOW())). Pak Cronem každý den vymažete záznamy starší než půl roku (DELETE FROM nakupy_nad_5tisic WHERE date < (DATE_SUB(CURDATE(), INTERVAL 6 MONTH)). Díky tomu bude tabulka malá a najít nákup konkrétního zákazníky bude hračka (např. ... WHERE EXISTS( SELECT * FROM nakupy_nad_5tisic WHERE user_id = user.id)).

Stejně tak např. výpočet průměrné měsíční útraty můžete získat tak, že si budete do tabulky ukládat součet všech nákupů zákazníka za daný měsíc (INSERT INTO mesicni_nakupy VALUES ($user_id, YEAR(NOW()), MONTH(NOW()), $castka) ON DUPLICATE KEY UPDATE castka = castka + $castka;).

Pointa těchto pomocných tabulek spočívá v tom, že požadovanou hodnotu musíte tak jako tak spočítat, ale není potřeba ji počítat teprve až v okamžiku, kdy ji potřebujete, ale hned v okamžiku, kdy více, že se hodnota mění a díky tomu se zátěč rozloží v čase místo aby zatížila server v jeden okamžik. Zadruhé pokud kontrolu provádíte častěji (např. každý den) než se hodnota skutečně mění (např. každý zákazník nakupuje průměrně jednou týdně), nemusíte znovu počítat hodnotu, která se od poslední kontroly nezměnila.

Pro co nejrychlejší zpracování je hlavní, aby pomocné tabulky měli správně nastaveny indexy, pomocí kterých přesně vyfiltrujete jen ty podstatné řádky (konkrétní zákazník, požadované období nebo částka, apod.), a/nebo aby se tabulky pravidelně promazávali a stále si tak udržovali štíhlou linii (tedy rychlost prohledání).

Odlévání záznamů

Výše jsem uváděl, že index slouží k tomu, že přeskočí nesouvisející řádky a prohledává jen ty, které se týkají požadovaného rozsahu. Pokud ale máte tabulky s daty za posledních deset let a každý rok do ní přibyde milion záznamů, znamená to, že při každém dotazu bude muset databáze přeskočit deset milionů záznamů. I když je to rychlejší, než kdyby je musela projít, pořád je to deset milionů záznamů, které zabírají nějaké místo a i jejich pouhé přeskočení trvá drahocené milisekundy.

Zrychlení tedy můžete dosáhnout tím, že stará a méně používaná data uložíte do jiné tabulky, kde budou stále k dispozici:

CREATE TABLE records_2010 
    AS SELECT * FROM records 
        WHERE created BETWEEN '2010-01-01' AND '2010-12-31'
;
DELETE FROM records WHERE created BETWEEN '2010-01-01' AND '2010-12-31';

Pozor na to, že tímto záznamy znepřístupníte z programu nebo budete muset program upravit tak, aby věděl v jaké tabulce má hledat starší záznamy.

Opačný přístup je ten, že např. každý rok (nebo jiné období) vytvoříte novou tabulku a nové záznamy budete vkládat vždy do té nejnovější:

CREATE TABLE records_2018 LIKE records_2017;

SET @current_records = CONCAT('records_', YEAR(CURDATE());
INSERT INTO @current_records VALUES(...);

Samozřejmě nemusíte vytvářet každý rok novou tabulku, ale můžete mít jen tabulku pro aktuální záznamy (např. za posledních 6 měsíců) a ostatní přelévat do druhé, kde budou všechny záznamy od počátku věků.

INSERT INTO records_old
    SELECT * FROM records
        WHERE created < DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
DELETE FROM records WHERE created < DATE_SUB(CURDATE(), INTERVAL 6 MONTH)

Tím dosáhnete toho, že získání nového záznamu bude rychlé, ale u starého bude nějakou dobu trvat – což nemusí vadit, pokud váš program jen zřídka pracuje se staršími záznamy. Výhoda je ta, že program nemusí zjišťovat, v jaké tabulce daný záznam hledat, ale jen propojí dvě tabulky (resp. pokud ho nenajde v první, prohledá druhou).

Konkrétní implementace záleží na situaci. Např. faktury můžete rozdělovat na ty z aktuálního roku, které potřebujete pro platby, dodání, atd., ty z minulého roku, které potřebujete pro daňová přiznání, inventury, apod., pak ty za posledních X let, které potřebujete pro případ kontroly (kde X by mělo být snadno změnitelné) a pak ty starší (tzv. promlčené), které můžete vymazat nebo uchovávat jen pro statistické účely.

Uvědomte si, že pokud máte např. denní nebo měsíční zálohy celé databáze, není potřeba v ní uchovávat staré záznamy, které potřebujete „jen pro statistické účely“ nebo podobné jednorázové účely. Vždy totiž můžete stará data obnovit ze zálohy (což nemusí být ani do hlavní databáze, ale můžete to udělat např. lokálně), provést, co potřebujete, a pak záznamy zase vymazat.

Napsat komentář

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