MySQL - indexy a EXPLAIN: prečo je dotaz pomalý
Dotaz, ktorý na vývojovom počítači s pár stovkami riadkov trvá milisekundy, môže na produkcii s miliónom riadkov trvať sekundy. Najčastejšia príčina je chýbajúci alebo nevhodný index. V návode si ukážeme, ako príčinu nájsť príkazom EXPLAIN a ako index navrhnúť tak, aby ho databáza naozaj použila.
Čo je index
Index funguje ako register na konci knihy: namiesto listovania celou knihou (v databáze full table scan) nájdete heslo v zoradenom registri a skočíte rovno na správnu stranu. MySQL (InnoDB) ukladá indexy ako B-strom – zoradenú štruktúru, v ktorej sa hodnota nájde v niekoľkých krokoch aj medzi miliónmi riadkov.
Daň za to: index zaberá miesto na disku a každý INSERT, UPDATE a DELETE ho musí aktualizovať. Index preto pridávajte tam, kde ho používajú dotazy, nie „pre istotu“ na každý stĺpec.
Testovacia tabuľka
CREATE TABLE objednavky (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
zakaznik_id INT UNSIGNED NOT NULL,
stav VARCHAR(20) NOT NULL,
vytvorena DATETIME NOT NULL,
suma DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
Predstavme si, že tabuľka má milión riadkov. Primárny kľúč id má index automaticky, nič iné zatiaľ nie.
EXPLAIN – čo databáza naozaj robí
Stačí pred dotaz napísať EXPLAIN. Dotaz sa nevykoná, MySQL iba vypíše plán, ako by ho vykonal:
EXPLAIN SELECT id, suma FROM objednavky WHERE zakaznik_id = 42;
| type | possible_keys | key | rows | Extra |
|---|---|---|---|---|
| ALL | NULL | NULL | 998 213 | Using where |
Najdôležitejšie stĺpce výstupu:
- type – spôsob prístupu.
ALLznamená prečítanie celej tabuľky. Lepšie súrange(rozsah v indexe),ref(vyhľadanie v indexe) aconst(jeden riadok podľa kľúča). - key – index, ktorý MySQL zvolil.
NULL= žiadny. - rows – odhad počtu riadkov, ktoré bude treba prečítať.
- Extra – doplnkové informácie. Pozor na
Using filesort(zoradenie mimo indexu) aUsing temporary(dočasná tabuľka).
Jednoduchý index
CREATE INDEX ix_objednavky_zakaznik ON objednavky (zakaznik_id); EXPLAIN SELECT id, suma FROM objednavky WHERE zakaznik_id = 42;
| type | key | rows | Extra |
|---|---|---|---|
| ref | ix_objednavky_zakaznik | 12 | NULL |
Namiesto takmer milióna riadkov stačí prečítať dvanásť.
Zložený index a poradie stĺpcov
Častý dotaz: objednávky zákazníka v určitom stave, od najnovšej.
SELECT id, vytvorena, suma FROM objednavky WHERE zakaznik_id = 42 AND stav = 'zaplatena' ORDER BY vytvorena DESC LIMIT 10;
Pomôže zložený index cez viac stĺpcov. Rozhoduje však poradie:
CREATE INDEX ix_objednavky_zak_stav_datum
ON objednavky (zakaznik_id, stav, vytvorena);
Zložený index je zoradený najprv podľa prvého stĺpca, v rámci neho podľa druhého atď. – ako telefónny zoznam zoradený podľa priezviska a potom mena. Z toho plynú pravidlá:
- Index sa používa zľava. Pomôže dotazom s podmienkou na
zakaznik_id, nazakaznik_id + stavaj na všetky tri stĺpce. Dotazu len sostav = …nepomôže – v telefónnom zozname tiež nenájdete všetkých „Jánov“ bez listovania celým zoznamom. - Stĺpce s rovnosťou (
=) dajte dopredu, stĺpec s rozsahom (>,BETWEEN) alebo zoradením až za ne. Po prvom rozsahu sa ďalšie stĺpce indexu na vyhľadávanie už nevyužijú. - Index
(zakaznik_id, stav, vytvorena)zároveň pokryje aj samotné(zakaznik_id). Pôvodný jednoduchý index sa stal nadbytočným a môžete ho zmazať.
V našom dotaze je vytvorena posledná, takže riadky prídu z indexu už zoradené – v EXPLAIN zmizne Using filesort a vďaka LIMIT 10 MySQL skončí po desiatich riadkoch.
Pokrývajúci index
Ak index obsahuje všetky stĺpce, ktoré dotaz potrebuje, MySQL nemusí čítať samotnú tabuľku vôbec. V EXPLAIN to spoznáte podľa Using index v stĺpci Extra:
-- Súčet za zákazníka a stav – všetko potrebné je v indexe CREATE INDEX ix_objednavky_zak_stav_suma ON objednavky (zakaznik_id, stav, suma); EXPLAIN SELECT stav, SUM(suma) FROM objednavky WHERE zakaznik_id = 42 GROUP BY stav;
V InnoDB obsahuje každý index automaticky aj primárny kľúč, takže id do neho pridávať netreba.
Kedy sa index nepoužije
-- 1. Funkcia nad stĺpcom: index nad vytvorena sa nepoužije SELECT * FROM objednavky WHERE YEAR(vytvorena) = 2026; -- Správne: rozsah SELECT * FROM objednavky WHERE vytvorena >= '2026-01-01' AND vytvorena < '2027-01-01'; -- 2. LIKE so zástupným znakom na začiatku SELECT * FROM zakaznici WHERE email LIKE '%@gmail.com'; -- index nepomôže SELECT * FROM zakaznici WHERE email LIKE 'jan.%'; -- index pomôže -- 3. Porovnanie textového stĺpca s číslom (implicitná konverzia) SELECT * FROM zakaznici WHERE telefon = 421900123456; -- telefon je VARCHAR SELECT * FROM zakaznici WHERE telefon = '421900123456'; -- správne -- 4. OR cez rôzne stĺpce – často lepšie dva dotazy spojené cez UNION SELECT * FROM objednavky WHERE zakaznik_id = 42 OR stav = 'storno';
Ďalší prípad: ak podmienke vyhovuje veľká časť tabuľky (napr. stav = 'zaplatena' platí pre 90 % riadkov), MySQL index zámerne nepoužije – prečítať celú tabuľku je vtedy rýchlejšie.
EXPLAIN ANALYZE
Od MySQL 8.0.18 existuje EXPLAIN ANALYZE, ktorý dotaz naozaj vykoná a pri každom kroku ukáže skutočný čas a počet riadkov. Ak sa odhad (rows) výrazne líši od skutočnosti, pomôže obnoviť štatistiky príkazom ANALYZE TABLE objednavky;.
EXPLAIN ANALYZE SELECT id, vytvorena, suma FROM objednavky WHERE zakaznik_id = 42 AND stav = 'zaplatena' ORDER BY vytvorena DESC LIMIT 10;
Pozor: keďže sa dotaz vykoná, na UPDATE a DELETE ho spúšťajte iba na testovacej databáze.
Ako nájsť pomalé dotazy
MySQL vie zapisovať dotazy dlhšie než zadaný čas do súboru slow query log:
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 0.5; -- sekundy SHOW VARIABLES LIKE 'slow_query_log_file';
Po niekoľkých hodinách prevádzky v ňom budete mať zoznam kandidátov na optimalizáciu – a pri každom stačí začať príkazom EXPLAIN.
Zhrnutie
EXPLAINpred dotazom ukáže, či sa používa index (type,key,rows).- V zloženom indexe dajte dopredu stĺpce porovnávané cez
=, za ne rozsah alebo zoradenie. - Nepoužívajte funkcie nad indexovaným stĺpcom vo
WHERE– prepíšte podmienku na rozsah. - Pokrývajúci index (
Using index) ušetrí čítanie tabuľky. - Nadbytočné indexy mažte – spomaľujú zápis.