Pomalé SQL dotazy: jak číst EXPLAIN a kdy přidat index

Aplikace byla rychlá, dokud tabulka neměla milion řádků. Teď stránka načítá tři sekundy a nikdo neví proč. Ve většině případů je viníkem dotaz, který prochází celou tabulku, protože mu chybí vhodný index. Tento návod ukazuje, jak takový dotaz najít, změřit a opravit.

1. Najděte pomalé dotazy

PostgreSQL

Nejlepší přehled dává rozšíření pg_stat_statements. Do postgresql.conf přidejte:

shared_preload_libraries = 'pg_stat_statements'

Po restartu serveru v databázi spusťte:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sloupce mean_exec_time a total_exec_time existují od PostgreSQL 13; ve starších verzích se jmenují mean_time a total_time. Řaďte podle celkového času – dotaz za 5 ms volaný milionkrát denně je větší problém než jeden report za 2 sekundy.

Pro logování jednotlivých pomalých dotazů slouží log_min_duration_statement = 500 (v milisekundách).

MySQL / MariaDB

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5

Log pak shrnete nástrojem mysqldumpslow -s t /var/log/mysql/slow.log.

2. Přečtěte plán dotazu

Mějme tabulku objednávek a dotaz:

SELECT * FROM orders
WHERE customer_id = 4821 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

V PostgreSQL spusťte:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

ANALYZE dotaz skutečně provede a ukáže reálné časy. Pozor: u UPDATE nebo DELETE ho tedy opravdu provede – zabalte ho do BEGIN; ... ROLLBACK;.

Bez indexu uvidíte zhruba:

Limit  (actual time=812.4..812.5 rows=20 loops=1)
  ->  Sort  (actual time=812.4..812.4 rows=20 loops=1)
        ->  Seq Scan on orders  (actual time=0.03..790.1 rows=37 loops=1)
              Filter: ((customer_id = 4821) AND (status = 'paid'))
              Rows Removed by Filter: 2999963

Na co se dívat:

Co vidíte	Co to znamená
`Seq Scan` na velké tabulce	Prochází se celá tabulka
Vysoké `Rows Removed by Filter`	Čte se mnohem víc, než se vrací
`rows=` odhad vs. skutečnost se liší řádově	Zastaralé statistiky, spusťte `ANALYZE orders;`
`Sort` s `external merge`	Řazení nestačí v paměti (`work_mem`)
`Index Scan` / `Index Only Scan`	Dobře, používá se index

V MySQL použijte EXPLAIN (od MySQL 8.0.18 i EXPLAIN ANALYZE). Ve výstupu sledujte sloupec type – ALL znamená full scan, ref nebo range práci s indexem – a sloupec rows s odhadem počtu čtených řádků. Using filesort ve sloupci Extra značí řazení mimo index.

3. Navrhněte správný index

Pro dotaz výše je ideální složený index:

CREATE INDEX idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);

Pravidla pro pořadí sloupců:

Nejdřív sloupce porovnávané rovností (=), tedy customer_id, status.
Pak sloupec pro řazení nebo rozsah (created_at).
Index se dá použít „zleva“: index na (a, b, c) pomůže dotazu filtrujícímu podle a nebo a, b, ale ne podle samotného b.

Po vytvoření indexu by plán měl ukázat Index Scan using idx_orders_customer_status_created a čas v řádu desetin milisekundy, protože databáze přečte jen 20 řádků už ve správném pořadí.

Vytvoření indexu na produkci

V PostgreSQL běžné CREATE INDEX blokuje zápisy do tabulky. Na živém systému použijte:

CREATE INDEX CONCURRENTLY idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);

Nesmí běžet uvnitř transakce a pokud selže, zůstane po něm neplatný index, který je třeba smazat a vytvořit znovu. V MySQL 8 s InnoDB probíhá přidání indexu online (ALGORITHM=INPLACE, LOCK=NONE).

4. Proč se index nepoužije

Funkce nad sloupcem: WHERE LOWER(email) = '...' nevyužije index na email. Řešením je funkční index CREATE INDEX ON users (LOWER(email));.
Implicitní přetypování: porovnání textového sloupce s číslem v MySQL index obejde.
LIKE '%slovo' s procentem na začátku běžný B-tree index nevyužije. Pro fulltext použijte pg_trgm nebo fulltextový index.
Nízká selektivita: pokud podmínka vrací třeba 40 % tabulky, je sekvenční čtení opravdu rychlejší a plánovač má pravdu.
OR přes různé sloupce často vede na full scan; někdy pomůže přepsat na UNION ALL.

5. Nepřehánějte to

Každý index zpomaluje INSERT, UPDATE a DELETE a zabírá místo. Nepoužívané indexy v PostgreSQL najdete takto:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Než index smažete, ověřte, že statistiky nebyly nedávno resetovány a že index nevynucuje unikátnost.

Postup v kostce

Najděte dotazy s největším celkovým časem.
EXPLAIN ANALYZE a hledejte full scany a velké počty vyřazených řádků.
Přidejte cílený složený index (na produkci CONCURRENTLY).
Znovu změřte a porovnejte plány.

Výstup EXPLAIN ANALYZE je často dlouhý a nepřehledný. Můžete ho vložit do ColdHost AI v režimu Řešení problémů spolu se schématem tabulky a nechat si navrhnout index – a pak ho stejně ověřit měřením.