Jak optimalizujete pomalé SQL dotazy?

Question

Accepted Answer

Optimalizace pomalých dotazů začíná **analýzou plánu provádění** (`EXPLAIN`) k nalezení úzkého místa, následovanou cíleným řešením — nejčastěji **přidáním indexů**, přepsáním dotazu a vyvarováním se běžných anti-pattern. Největší zisk obvykle přichází z превращения úplného prohledávání tabulky na vyhledávání pomocí indexu.

## Krok 1: analýza pomocí EXPLAIN

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;
-- shows the EXECUTION PLAN: scan type (Seq Scan = full scan, Index Scan = using an index),
-- estimated/actual rows, cost, and timing → reveals WHERE the time goes
```

`EXPLAIN` (nebo `EXPLAIN ANALYZE`) ukazuje, jak databáze provádí dotaz — zejména, zda provádí **úplné prohledávání tabulky** (pomalé) nebo používá **index** (rychlé). Vždy profilujte před optimalizací.

## Největší zisk: přidání indexů

```sql
-- if EXPLAIN shows a Seq Scan on a filtered/joined column → add an index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- turns a full scan into a fast index lookup
```

Nejčastější a nejvlivnější oprava: **přidejte indexy** na sloupce používané v `WHERE`, `JOIN` a `ORDER BY`. To změní úplné prohledávání tabulky na rychlé vyhledávání.

## Běžné anti-patterny k opravě

```sql
-- ❌ functions on a column prevent index use
WHERE UPPER(email) = 'X@Y.COM'        -- can't use an index on email
WHERE email = 'x@y.com'               -- ✅ can use the index

-- ❌ leading wildcard LIKE can't use an index
WHERE name LIKE '%smith'              -- ✅ 'smith%' (prefix) can use an index

-- ❌ SELECT * fetches unneeded columns
SELECT * → SELECT only_needed_columns  -- less data, may use a covering index

-- ❌ N+1 queries (querying in a loop) → use a single JOIN instead
```

## Další techniky optimalizace

```text
✓ Fix N+1 queries (one query per row) → use JOINs / batch into one query
✓ Add appropriate indexes (including COMPOSITE for multi-column filters)
✓ Reduce data scanned — filter early, select only needed columns, paginate
✓ Rewrite subqueries as JOINs where more efficient
✓ Use EXISTS instead of IN for existence checks on large subqueries
✓ Keep statistics updated (ANALYZE) so the planner makes good choices
✓ Consider denormalization / materialized views for expensive repeated queries
✓ Watch for missing indexes on FOREIGN KEYS (joins scan without them)
```

## Proč to má význam

Optimalizace dotazů je kritické znalosti pro výkon databáze — pomalé dotazy jsou hlavní příčinou špatného výkonu aplikace a porozumění jejich hledání a opravě je zásadní dovedností seniorní úrovně pro všechny, kdo pracují s databázemi ve velkém měřítku.

Základní disciplína je **analýza pomocí `EXPLAIN`** (zkoumání plánu provádění k nalezení skutečného úzkého místa — zejména zda dotaz provádí pomalé úplné prohledávání tabulky nebo rychlé vyhledávání indexu) před optimalizací, spíše než hádat — tento přístup řízený měřením je to, co činí optimalizaci účinnou.

**Jediný největší a nejčastější zisk je přidávání indexů** na filtrované, spojované a seřazené sloupce (změna úplného prohledávání tabulky na rychlé vyhledávání — často zlepšuje dobu dotazu o řády), což dělá znalost indexů centrální pro optimalizaci.

Porozumění **běžným anti-pattern** je rovněž důležité: funkce na sloupcích a vedoucí `LIKE` s maskou zabraňují používání indexu, `SELECT *` načítá zbytečná data a zejména **N+1 dotazy** (dotazování v smyčce místo jediného JOIN — časté, závažné zpomalení výkonu v kódu aplikace) — rozpoznání a oprava těchto problémů řeší nejčastější příčiny pomalých dotazů.

Další techniky (kompozitní indexy, snížení skenovaných dat, přepsání poddotazů jako JOIN, udržování aktualizovaných statistik, materializované pohledy pro nákladné dotazy) doplňují sadu nástrojů.

Protože výkon dotazu přímo ovlivňuje odezvu aplikace a škálovatelnost a protože systematický přístup (analýza pomocí EXPLAIN, pak oprava — obvykle přidáním indexů a eliminací anti-pattern jako N+1) je to, co udržuje databáze rychlé za zatížení, zvládnutí optimalizace dotazů je důležité, vysoce hodnocené seniorní znalosti, které odlišují vývojáře, kteří mohou vytvářet výkonné, škálovatelné aplikace podporované databází — často relevantní, kritická dovednost a běžné téma na pohovorech na výkon databází.