Jak optymalizujesz wolne zapytania SQL?

Question

Accepted Answer

Optymalizacja wolnych zapytań rozpoczyna się od **analizy planu wykonania** (`EXPLAIN`) w celu znalezienia wąskiego gardła, a następnie zastosowania celowych poprawek — najczęściej **dodawania indeksów**, przepisywania zapytania i unikania typowych anti-wzorców. Zwykle największy zysk daje się uzyskać, zamieniając pełne skanowania tabel na wyszukiwania za pomocą indeksów.

## Krok 1: analiza 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` (lub `EXPLAIN ANALYZE`) pokazuje, jak baza danych wykonuje zapytanie — krytycznie, czy wykonuje **pełne skanowanie tabeli** (wolne) czy używa **indeksu** (szybkie). Zawsze profiluj przed optymalizacją.

## Największy zysk: dodawanie indeksów

```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
```

Najczęstsza i najbardziej wpływowa poprawka: **dodaj indeksy** na kolumnach używanych w `WHERE`, `JOIN` i `ORDER BY`. To zamienia pełne skanowania tabel na szybkie wyszukiwania.

## Typowe anti-wzorce do naprawy

```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
```

## Inne techniki optymalizacji

```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)
```

## Dlaczego to ważne

Optymalizacja zapytań to krytyczna wiedza dla wydajności bazy danych — wolne zapytania są główną przyczyną słabej wydajności aplikacji, a umiejętność ich znajdowania i naprawiania jest niezbędną umiejętnością na poziomie senior dla każdego pracującego z bazami danych na dużą skalę.

Fundamentalną dyscypliną jest **analiza za pomocą `EXPLAIN`** (badanie planu wykonania w celu znalezienia rzeczywistego wąskiego gardła — krytycznie, czy zapytanie wykonuje wolne pełne skanowanie tabeli czy szybkie wyszukiwanie indeksu) przed optymalizacją, zamiast zgadywania — to podejście oparte na pomiarach to to, co sprawia, że optymalizacja jest efektywna.

**Pojedynczym największym i najczęstszym zyskiem jest dodawanie indeksów** na kolumnach filtrowanych, połączonych i sortowanych (zamieniając pełne skanowania tabel na szybkie wyszukiwania — często poprawiając czas zapytania o rzędy wielkości), co czyni wiedzę o indeksach centralną dla optymalizacji.

Zrozumienie **typowych anti-wzorców** jest równie ważne: funkcje na kolumnach i wiodące wieloznaczne `LIKE` uniemożliwiają użycie indeksu, `SELECT *` pobiera zbędne dane, a szczególnie **zapytania N+1** (zapytania w pętli zamiast jednego JOIN — częsty, poważny zabójca wydajności w kodzie aplikacji) — rozpoznawanie i naprawianie tych problemów rozwiązuje najczęstsze przyczyny wolnych zapytań.

Inne techniki (indeksy złożone, zmniejszanie ilości skanowanych danych, przepisywanie podkwerend jako JOIN, utrzymywanie statystyk na bieżąco, zmaterializowane widoki dla drogich zapytań) uzupełniają zestaw narzędzi.

Ponieważ wydajność zapytań bezpośrednio wpływa na responsywność i skalowalność aplikacji, a ponieważ systematyczne podejście (analiza za pomocą EXPLAIN, następnie naprawa — zwykle przez dodawanie indeksów i eliminowanie anti-wzorców takich jak N+1) to to, co utrzymuje bazy danych szybkie pod obciążeniem, opanowanie optymalizacji zapytań jest ważną, wysokowartościową wiedzą na poziomie senior, która odróżnia deweloperów mogących budować wydajne, skalowalne aplikacje wspierane przez bazy danych — umiejętność często istotna, krytyczna i popularny temat rozmów kwalifikacyjnych dotyczący wydajności baz danych.