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ě). Někdy (obvykle pro EXISTS()) také EXPLAIN jen zobrazí celkový počet řádek v tabulce nebo jeho polovinu (neboli průměr), což ale obvykle jen znamená, že skutečný počet řádek není možno ani odhadnout – někdy naopak ale může naznačovat „full scan“ (viz dále).

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 (nebo jinými) 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 použijete LIMIT nebo EXISTS()) a u každé ověřit, jestli splňuje zadané WHERE podmínky.

Index je další tabulka (vlastně strom, viz další odstavec), která obsahuje všechny hodnoty daného sloupce a u každé hodnoty čísla řádků, které danou hodnotu obsahují (zde si nepleťte čísla řádků s id nebo PRIMARY indexem). 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

Níže 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]

Poznámka: Někdy může dojít k tomu, že vytvoříte dotaz, který používá sloupce a a c, takže nemůže použít index [a, b, c, d], ale když se zamyslíte, tak třeba zjistíte, že hodnota sloupce b bude v tomto dotazu vždy stejná a když ji tedy přidáte do dotazu, vrácené řádky se nezmění, ale dotaz bude moci použít již existující index. Příklad: hledáte ženy (gender = "FEMALE"), které mají alespoň jedno dítě (childrenCount > 0). V tomto případě se dá předpokládat, že taková žena musí být dospělá a tudíž tato podmínka (tedy isAdult = TRUE) nezmění vrácený výsledek a přitom díky jejímu přidání lze použít index [gender, isAdult, childrenCount] (pro tento příklad vylučme nezletilé matky; ty můžeme vybírat jinde :).

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 zobrazit 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ůže i vytvoření jednosloupcových indexů urychlit dotazy, 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é se nalézají v obou indexech (matematicky a česky průnik množin). 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).

Boolean

Boolean je v MySQL uložen jako tinyint, kde 0 == false a !0 == true (což zahrnuje hodnoty -128 až -1 a 1 až 127). Z toho důvodu je index na boolean sloupci stejně účinný jako číslo. Navíc pokud dodržíte premisu, že FALSE => 0 a TRUE => 1, pak bude mít takový index mohutnost 2 a bude tedy vhodné umístit ho na začátek indexu (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.

Načítání dat z indexu

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). Velký přínost má také u MyISAM tabulek, které načítají index do RAM zatímco tabulka zůstává na disku, takže přečtením dat z indexu se zcela vyloučí polalý přístup na disk.

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.

Paměť vs. HDD

Velký vliv na to, jak rychle dokáže databáze projít index resp. celou tabulku má také to, zda je tabulka či index uložen na HDD (nebo dnes i SSD) a nebo se vejde do RAM paměti.

Dva nějčastěji používané enginy pracují s RAM odlišně:

• Tabulka MyISAM ukládá indexy do RAM a samotná data nechává na disku. V tomto případě se tedy vyplatí vytvářet indexy i pro velké dotazy, protože to urychlí procházení.
• Tabulka InnoDB se snaží uložit do RAM jak indexy tak i samotná data. Pokud ale nemá dostatek paměti, nechá jak data tak i indexy na disku. To pak znamená, že velké tabulky se budou vždy prohledávat z disku a tudíž má DB větší tendenci provádět full scan (tedy projití dat) místo prohledávání indexu.

Vezměme si pro příklad tabulky users se seznamem uživatelů a logins ukládající datum a čas všech přihlášení, které uživatelé provedli. Při 10 milionech userů a 1 miliardě loginů bude mít tabulka users kolem 2GB dat (~200B na řádek) a dalších 8GB indexů (protože se používá pro nejrůznější hledání). Tabulka loginů bude mít kolem 20GB (20B na řádek = BIGINT id + INT user_id + TIMESTAMP), ale stačí jí jen index na user_id, který bude mít jen 1GB (pozn: záměrně jsem z příkladu vyloučil PRIMARY index nad BIGINT, který by měl 12GB, ale rozbil by následující výpočet :).

Pokud bude chtít obě tabulky uložit jako InnoDB, abyste je mohli provázat přes relace, budete potřebovat server s alespoň 32GB volné paměti. Pokud ale tabulku logins převedete na MyISAM, bude jí stačít 1GB a serveru jen 2GB volné paměti. Pokud na MyISAM naopak převedete tabulku users, ušetříte jen minimum (2GB z 10GB) a naopak způsobíte, že tabulka přestane indexy používat a bude provádět full scan pro většinu dotazů (protože projít 2GB sekvenčních dat je mnohem rychlejší než hledat v 8GB stromových dat přes random access).

V příkladu je vidět, že tabulka users by měla být InnoDB, protože provádí náročná hledání a tudíž je potřeba, aby byla uložena v RAM. Proto by také server měl mít alespoň 12GB volné RAM, aby do ní mohl tabulku uložit. Také je jasné, že každý další index, který do users přidíte, zvýší nároky na RAM o několik GB.

Naproti tomu tabulka logins bude mít mnohem vyšší nároky na paměť, ale u podobné tabulky se obvykle neprovádí žádná náročná hledání, takže může být klidně uložena na MyISAM a fakt, že přijdete o relační propojení na users nehraje takovou roli (v použítá JOIN to nebrání a hlídání neexistujících klíčů nemá u podobé tabulky význam). Díky MyISAM pak tabulka zabere v RAM jen pár GB.

Co naopak příliš nemá vliv na rychlost hledání, je rychlost disku. V dnešní době můžete používat SATAIII, SCSI, SAS nebo NVME v kombinaci s rychlými HDD nebo SSD (někoho také může napadnou vytvořit RAID1 pro zvýšení rychlost). Všechny tyto úvahy jsou spíše jen teoretické a rychlejší HDD/SSD nikdy nebude mít takový vliv jako A) uložení tabulky nebo indexu do RAM a B) optimalizace dotazů pomocí indexů a dalších technik.

Pro úplnost: RAID1 sice zvýší rychlost sekvenčního čtení na dvojnásobek, ale to využijete jen při full scan defragmentované tabulky; naopak prohledávání indexu bude pomalejší, protože používá random access a RAID má vyšší přístupovou dobu, protože řadič musí hledat, na kterém disku je požadovaný blok. SSD disky mohou naopak zlepšit hledání v indexu díky své nízké přístupové době. Pozor ale na to, že i když výrobci SSD uvádí rychlost čtení 500+MB/s (SATAIII) nebo 1500+MB/s (NVME), často je tento údaj měřen nad diskovou cache (o velikosti 2GB nebo 4GB), a při hledání v tabulce nebo indexu větším než tato cache se neprojeví (pak má často i NVME jen 50 až 100MB/s). Je tedy výhodnější do serveru koupit další/větší RAM než rychlejší SSD, protože RAM bude vždy rychlejší a cenově není o tolik dražší.

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

Nicméně omezení nebo vynucení indexu byste neměli používat pro samotné dotazy (z aplikace, webu, apod.), ale spíše je používejte v kombinaci s EXPLAIN k tomu, abyste zjistili výkonnost jednotlivých indexů (tedy kolik řádek bude muset DB projít po jejich použití). Díky tomu pak můžete získat lepší představu o tom, jak DB jednotlivé indexy používá a jak je můžete dále vylepšit (např. přidat další sloupce pro rychlejší hledání, změnit jejich pořadí pro snížení mohutnosti nebo naopak odebrat sloupce, které výrazně nemění počet řádek).

Příklad: pokud máte dotaz, který hledá pomocí 3 sloupců, vytvořte 6 indexů s různým pořadím těchto sloupců a všechny je uveďte do USE INDEX. Příkaz EXPLAIN vám pak řekne, které pořadí sloupců je pro DB nejlepší a který index byste měli zachovat (ostatní pak smažte, abyste ušetřili místo). Pro více než 3 sloupce pak není potřeba vytvářet všechny kombinace, ale jen ty, pro které to má smysl (prohodit sloupce s podobnou mohutností apod.).

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ší kapitoly). 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.

Operátor IN – seznam id místo IN(SELECT)

Výše uvedené rady mají jedno úskalí: operátor IN je velmi pomalý, pokud použijete IN(SELECT) nad velkou (vnější) tabulkou a/nebo složitým sub-query. Problém ale není v operátoru IN, ale v tom, jak MySQL zpracovává sub-query. 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).

Ukažme si to na příkladu:

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

Takto zadaný příkaz bude trvat velmi dlouho, pokud je v tabulce users hodně záznamů (řádově milony uživatelů). Kvůli tomu, že MySQL zpracovává z vnějšku dovnitř, tento příkaz provede to, že nejprve vybere všechny uživatele z tabulky users (tedy několik milionů) a pak teprve pro každý z nich bude volat sub-query. To znamená, že se bude muset provést několik milonů hledání českých skupin, i když dotaz vždy vrátí stejný výsledek.

Naopak operátor IN je velmi rychlý, pokud do něj zadáte statický seznam id, které chcete najít. Pak se z operátoru IN stává jednoduché hledání rozsahu, které je nad indexem velmi rychlé (jak jsme si ukázali výše).

Pokud tedy voláte SQL z nějakého jiného jazyka (např. PHP), můžete dotaz rozdělit na dva, čímž výrazně urychlíte jejich volání.

$czechGroups = GROUPS::table()
                  ->select('id')
                  ->where('language = "cz"')
                  ->fetchAll();
$czechGroupIds = array_column($czechGroups, 'id');
$czechUsers = USERS::table()
                  ->select('*')
                  ->where("group IN ($czechGroupIds)")
                  ->fetchAll();

V tomto PHP příkladu (s fiktivním SQL builderem) nejprve jedním dozatem vybereme idčka všechny skupin s českým jazykem, pomocí nativní funkce array_column převedeme dvoudimenzionální pole na jednoduché pole se seznamem id a následně vybereme uživatele, kteří patří do některé z českých skupin.

Poznámka: funkce array_column je dostupná od PHP 5.5. Od verze 7.0 ji můžete použít i na pole objektů, což je nutné u některých SQL builderů a frameworků (např. Nette Database), které řádky vrací jako instance objektů (např. ActiveRow).

Přímo v MySQL by to samé mělo jít udělat pomocí proměnné (např. SELECT INTO), ale prakticky jsem to nezkoušel, takže nevím, jestli je následující příklad správný:

SELECT id FROM groups WHERE language = "cz" INTO @czechGroups;
SELECT  * FROM users  WHERE group IN (@czechGroups);

I když voláme dva SQL dotazy, bude to mnohem rychlejší než jeden, protože druhý dotaz jen odešle statický seznam id, které nás zajímají, a DB je vybere podle PRIMARY indexu místo aby pro každý řádek volala ten samý sub-query.

Pozor na to, že pokud by první dotaz vracel miliony záznamů (skupin), trvalo by dlouho přenesení dat do PHP (nebo jiného jazyka) a následné odesílání zpět v druhém dotazu, což by neůměrně zatěžovalo DB. V takovém případě je lepší rozdělit první dotaz na tzv. batch a zpracovávat data postupně:

$offset = 0;
$limit = 10000;
while ($czechGroups = GROUPS::table()
                  ->select('id')
                  ->where('language = "cz"')
                  ->limit($offset, $limit)
                  ->fetchAll()
) { //WHILE
    $czechGroupIds = array_column($czechGroups, 'id');
    $czechUsers[] = USERS::table()
                  ->select('*')
                  ->where("group IN ($czechGroupIds)")
                  ->fetchAll();
    $offset += $limit;
} //WHILE
$czechUsers = array_merge(...$czechUsers);

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 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() 
;

Sub-select místo EXISTS() ve velkých dotazech

V předchozí kapitole jsem psal, že sub-select v podmíce lze nahradit za operátor EXISTS(). To má ale jedno úskalí v tom, že někdy, při použití EXISTS() (zatím jsem nezjistil kdy konkrétně, jen vím, že se to děje u dotazů pracujích s miliony záznamů z několika tabulek současně), může databáze upřednostnit projití celé tabulky (tzv. full scan) místo použití indexu. Tento případ je často provázen tím, že v příkazu EXPLAIN uvidíte na první místě tabulku „sub-query X„, která se ukládá do temporary table (info ve sloupci Extra) a následně se hledání provádí projitím celé dočasné tabulky (což v EXPLAIN poznáte podle toho, že je tam tabulka uvedena znovu níže se stejným počtem řádek).

Důvodem může být to, že u operátoru EXISTS() databáze dopředu předpokládá, že pokud hledaný řádek neexistuje, bude muset projít celou tabulku (resp. celý index) tak jako tak. Proto se ani nesnaží hledat vhodný index a rovnou provede full scan. Pokud tento případ (tedy očekávání full scanu) nastane, v dotazu EXPLAIN to poznáte podle toho, že ve sloupci ROWS je uvedena polovina celkového počtu řádek v tabulce (tedy průměrná hodnota předpokládající že ~50% dotazů najde hledaný řádek celkem rychle, ale zbylých ~50% hledání bude muset projít celou tabulku).

Problém je ještě více znatelný, pokud použijete operátor NOT EXISTS(), protože ten přímo říká, že vás zajímají řádky, k nimž neexistují provázaná data a aby to DB dokázala, bude muset skutečně projít celou tabulku (nebo index).

Pokud k něčemu takovému dochází, nebo máte podezření, že je v dotaz zpomalován operátorem EXISTS(), zkuste ho nahradit sub-selectem (viz níže) a porovnejte EXPLAIN a dobu spuštění dotazů (každý dotaz spusťte alespoň 3x, abyste vyloučili vliv cachování části dotazu).

Příklad:

//Hledání uživatelů, kteří se alespoň jednou přihlásili:
SELECT user.id FROM users
WHERE EXISTS(SELECT 1 FROM actions WHERE login.user_id = user.id AND action = "login")

//Stejný dotaz, potencionálně rychlejší použitím indexu
SELECT user.id FROM users
WHERE (SELECT 1 FROM actions WHERE login.user_id = user.id AND action = "login" LIMIT 1) IS NOT NULL

Pro sub-selekt, který nahrazuje EXISTS, použijte tyto pravidla: 1) ponechte SELECT 1, protože vás nezajímá co dotaz našel , 2) na konec přidejte LIMIT 1 pro urychlení dotazu a 3) kontrolujte výsledek jako IS NOT NULL (=EXISTS) nebo IS NULL (=NOT EXISTS), protože dotaz vrátí buď 1, když najde nějaký řádek nebo NULL, když nic nenajde.

Můj názor (který nemusí být správný), proč je v tomhle případě sub-select rychlejší, je ten, že DB u EXIST() předpokládá, že řádek existovat nebude (nemusí) a proto raději sáhne k full scan; naopak v případě sub-selektu ví, že nebude muset získávat další data z tabulku a bude jí stačit spočítat záznamy v indexu, a proto ho upřednostní.

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ě chcete 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 pomoci 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 1 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íte, že se hodnota mění a díky tomu se zátěž rozloží v čase místo toho, 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 tabulku s daty za posledních deset let a každý rok do ní přibude 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 23:59:59'
;
DELETE FROM records 
        WHERE created BETWEEN '2010-01-01' AND '2010-12-31 23:59:59';

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á hledání. 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.

(De)Fragmentace tabulky a indexu

K fragmentaci tabulky může docházek ze dvou důvodů:

• z tabulky se často vymazávají záznamy (např. stará data)
• nad tabulkou testujete různé indexy, které následně vymažete

V obou případech dochází k tomu, že DB sice řádky a/nebo indexy označí jako smazané, ale místo, které zabírali na disku nemůže uvolnit. Později sice může toto místo použít pro nová data a/nebo indexy, ale tím právě vzniká fragmentace, což znamená, že data nejsou seřazena (obykle podle primárního klíče) a indexy jsou zpřeházené, takže se v nich hledá pomaleji.

Tabulka a indexy jsou obvykle uloženy uvnitř jednoho velkého souboru, takže defragmentace disku nepomůže. MySQL přímo nepodporuje defragmentaci tabulky, ale příkaz ALTER TABLE v některých případech tabulku kopíruje (do tmp table), přičemž data (ne)chtěně seřadí a defragmentuje.

Defragmentaci bez změny tabulky můžete provést dvěma způsoby:

//pokud znáte jaký engine tabulka používá:
ALTER TABLE tbl_name ENGINE=INNODB; //uveďte správný ENGINE

//Pokud engine neznáte:
ALTER TABLE tbl_name FORCE;

Pamatujte, že ALTER TABLE v obou případech nejprve zkopíruje tabulku to tmp table a následně zapíše data seřazená zpět. Např. u 10GB tabulky bude muset tedy zapsat 20GB dat (pokud nemá 10GB volných v RAM)