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;
typepossible_keyskeyrowsExtra
ALLNULLNULL998 213Using where

Najdôležitejšie stĺpce výstupu:

  • type – spôsob prístupu. ALL znamená prečítanie celej tabuľky. Lepšie sú range (rozsah v indexe), ref (vyhľadanie v indexe) a const (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) a Using 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;
typekeyrowsExtra
refix_objednavky_zakaznik12NULL

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, na zakaznik_id + stav aj na všetky tri stĺpce. Dotazu len so stav = … 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

  • EXPLAIN pred 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.
O autorovi

Ing.Jaroslav Vadel

Som zakladateľom www.projectik.eu. Hrám sa na programátora, ktorý ovláda: c#,cpp,java,unity3d,php,html,NI testand,NI Vision Builder,Cognex In-Sight,NI LabView "Naprogramovať program, ktorý funguje vie každy. Ale to, že program funguje ešte neznamena, že je napísany správne "

Súvisiace články