Optimalizace dotazů v produkčním prostředí

Proč optimalizovat dotazy přímo v produkci

Optimalizace dotazů v produkčním prostředí je disciplína na pomezí databázového návrhu, observability a provozního inženýrství. Cílem není pouze dosáhnout nižší latence jednotlivých SQL dotazů, ale především stabilního chování systému pod reálnou zátěží, předvídatelné spotřeby zdrojů a dodržení SLO (latence, chybovost, dostupnost). Tento text systematizuje postupy pro analýzu, měření, návrh indexů a schémat, práci s plánovačem dotazů a provozními omezeními tak, aby změny byly bezpečné i při vysoké dostupnosti.

Metodika: měřit–rozumět–jednat–validovat

• Měřit: konsolidujte metriky (latence p50/p95/p99, počet dotazů/s, CPU, IOPS, cache hit ratio), logy slow queries a profily dotazů.
• Rozumět: analyzujte exekuční plány, kardinalitní odhady, čekání (wait events), zámky a contention.
• Jednat: aplikujte minimální, reverzibilní změny (index, hint, úprava dotazu, limit zdrojů, parametry plánovače).
• Validovat: A/B porovnání plánu a latencí, kontrola regrese, rollback plán. Optimalizace je hotová až po potvrzení na produkčních datech.

Observabilita a profilace dotazů

• Telemetry pipeline: centrální agregace slow query logs, metrik a tracing s korelačním ID napříč službami.
• Top N: remediace začíná zaměřením na nejdražší dotazy podle času/CPU/IO kumulativně, ne podle počtu.
• Sampling: u vysoké frekvence dotazů vzorkujte s prioritou dlouhých a chybových.
• Canary validace: dočasné přesměrování části provozu na upravený dotaz či jinou indexovou strategii.

Analýza exekučního plánu

Čtení plánu je základ. Hledejte operátory Seq Scan na velkých tabulkách bez selektivního filtru, kartézské Nested Loop s vysokou kardinalitou, nevhodné pořadí spojování, chybějící predicate pushdown či hash join vyvolaný kvůli chybějícímu indexu.

• Kardinalitní odhady: výrazná odchylka mezi odhadem a skutečností indikuje zastaralé statistiky nebo korelované predikáty.
• Strategie spojování: Nested Loop pro malé vnější vstupy + index na vnitřní tabulce; Hash Join pro střední dataset; Merge Join při seřazených vstupech.
• Filtry a projekce: posuňte filtry co nejníže, projekce omezte na potřebné sloupce (covering indexy).

Indexy: konstrukce, selektivita a údržba

• Selektivita: index má smysl, pokud výrazně omezuje počet řádků; u nízké selektivity zvažte složený index s diskriminantem.
• Pořadí sloupců: sloupce s vyšší selektivitou dejte do popředí; pro rozsahové dotazy umístěte rovnostní predikáty dříve než BETWEEN/>/<.
• Covering index: zahrňte do indexu i sloupce z SELECT; snižujete tím počet přístupů do hlavní tabulky (heap fetch).
• Partial/filtered index: indexujte pouze relevantní subset (například status=’ACTIVE‘), čímž ušetříte místo i IO.
• Funkční a výrazové indexy: pro predikáty s funkcí/normalizací (LOWER(email), DATE(ts)).
• Údržba: monitorujte bloat, fragmentaci a autovacuum/autoupdate statistik; plánujte reindex či nastavení fillfactor u tabulek s intenzivními zápisy.

Návrh schématu: normalizace versus denormalizace

Normalizace minimalizuje anomálie a zlepšuje aktualizace, ale může vést k nadměrnému počtu spojení (joinů). Opatrně denormalizujte pro read-heavy scénáře: materiálované sloupce, materialized views, předpočítané agregace. Každou denormalizaci doprovodí deklarativní konzistence (triggery, joby) a SLA aktualizace.

Parametrizace, cache plánů a „parameter sniffing“

• Bind variables: vždy používejte parametrizaci; chrání proti SQL injection a umožňuje opakované použití plánu.
• Parameter sniffing: plán vytvořený pro atypickou hodnotu může zhoršit běžné případy. Řešení: OPTIMIZE FOR, RECOMPILE, plan guides, histogramy nebo rozdělení dotazu podle kategorií.
• Stárnutí plánů: rozumná politika invalidace a aktualizace statistik zajišťuje stabilní kardinalitu.

Optimalizace JOINů a přístupových cest

• Anti-join přes NOT EXISTS je zpravidla efektivnější než NOT IN s NULL hodnotami.
• Sémantické přeuspořádání: přesuňte selektivní tabulky na začátek plánu; menší vnější vstup = levnější Nested Loop.
• Semijoin: pro EXISTS nepotřebujete duplicitní řádky; zabraňte zbytečným DISTINCT.

Agregace, skupiny a okna

• Předfiltrujte před agregací; zvažte GROUPING SETS/ROLLUP/CUBE pro více agregací v jednom průchodu.
• Window funkce: někdy mohou nahradit subdotazy, ale pozor na ORDER BY s velkým frame, který zvyšuje IO nároky.
• Předpočítané agregace: pro dashboardy ukládejte výsledky do materiálovaných pohledů s řízenou obnovou.

Paginace a limitace dat

• Keyset pagination: WHERE id > ? ORDER BY id LIMIT N škáluje lépe než OFFSET u velkých stránek.
• Hard limit: chraňte databázi před full scan dotazy bez limitu – SLA query budget, kontrola na API vrstvě.
• Projektujte méně sloupců: omezte šířku řádků a přenos dat, zejména u JSON/BLOB sloupců.

Partitioning a životní cyklus dat

• Range/Hash/List partitioning zlepšuje údržbu (retence, vacuum) a umožňuje partition pruning.
• Hot/Cold data: oddělte nedávná „hot“ data od historických „cold“; používejte různé indexy a storage třídy.
• Rolling window: periodické přidávání a odstraňování partition je levnější než hromadné mazání.

Transakční izolace, zámky a čekání

• Izolační úroveň: volte nejnižší úroveň, která zajišťuje byznys konzistenci (např. READ COMMITTED vs. SERIALIZABLE).
• Krátké transakce: držte transakce co nejkratší; oddělte čtení od dlouhých zápisů a dávkových operací.
• Pořadí operací: konzistentní pořadí přístupu ke zdrojům minimalizuje výskyt deadlocků.
• Indexy pro FK: cizí klíče bez indexu způsobují zámky a plné skeny při mazání či aktualizacích.

ORM a generované dotazy

• N+1 problém: řešte eager loadingem, JOIN FETCH či explicitními IN dávkami.
• Explicitní SQL: pro kritické části preferujte ručně psané dotazy s kontrolou exekučního plánu.
• Batching: slučujte INSERT/UPDATE do dávkových operací, dávejte pozor na limity transakcí a logování.

Cache a vrstvy čtení

• Vrstvení: in-memory cache (L1), sdílená cache (L2), read-repliky; definujte pravidla konzistence a invalidace.
• Cache aside: aplikace odpovídá za invalidaci cache; prevenci před thundering herd zajišťuje request coalescing.
• Cache výsledků dotazů: používejte opatrně u dat závislých na uživateli či ACL.

Konfigurační parametry databáze

• Memory pools: velikost sdílených bufferů, paměti pro řazení a hash; málo → výpadky na disk, moc → tlak na OS cache.
• Autovacuum/autoanalyze: nastavte prahy dle churnu tabulek; sledujte zpoždění a dopad na latenci.
• Paralelismus: povolte paralelní plány pouze pro dlouhé dotazy; omezte počet workerů, aby nevznikalo contention.

Bezpečné nasazování změn v produkci

• Online indexy a migrace: využívejte variant CONCURRENTLY/ONLINE; vždy mějte připravený rollback.
• Feature flag: přepínání mezi starým a novým dotazem bez nutnosti redeploye.
• Progressive delivery: canary/b>lu–green nasazení s porovnáním latencí a chybovosti.

Tabulka: rychlá rozhodovací matice pro optimalizaci

Specifika hlavních databází

• PostgreSQL: pg_stat_statements, auto_explain, pečlivé nastavení work_mem, maintenance_work_mem, autovacuum. Využijte BRIN pro časové rozsahy, GIN pro fulltext/JSONB.
• MySQL/MariaDB: sledujte InnoDB buffer pool hit, performance_schema, EXPLAIN ANALYZE; dejte pozor na implicitní konverzi typů a kolace, které mohou rušit indexy.
• SQL Server: Query Store, Actual Execution Plan, indexy s INCLUDE, správa parametrů a OPTIMIZE FOR, READ COMMITTED SNAPSHOT pro snížení blokování.

Práce s datovými typy a kolacemi

• Konzistence typů: vyhněte se implicitním castingům v predikátech (např. porovnání textu s číslem).
• Kolace: case-insensitive vyhledávání řešte deterministicky (uložené normalizované hodnoty + index) nebo nativními indexy s odpovídající kolací.
• Šířka řádku: štíhlé tabulky zlepšují cache locality a propustnost.

Batch processing a online workload

• Joby s limitem: rate limit a krátká transakční okna; sleep jitter minimalizuje odchylky.
• Prioritizace: oddělte resource pools (workload management), aby dávky nevymazaly online provoz.
• Incrementalizace: místo kompletního přepočtu používejte change data capture a inkrementální agregace.

Bezpečnost a compliance versus výkon

• Šifrování: TDE a TLS mají režii; offloadujte CPU, sledujte dopad na p99 a nastavte opakované použití session.
• Řízení přístupu: pohledy a row-level security mění plány; navrhněte indexy odpovídající RLS predikátům.

Procesní stránka optimalizací

• Change management: každá změna má tiket, hypotézu, plán testů, metriky úspěchu a připravený rollback.
• Runbooky: standardní postup pro regresi výkonu, navýšení logovací úrovně, sběr potřebných dat.
• Postmortem: bez hledání viníka, s akčními body (indexy, limity, alerty, testy).

Kontrolní seznam před nasazením optimalizace

• Existuje srovnání plánů před/po