Optymalizacja zapytań SQL i analiza planów wykonania

Optymalizacja zapytań SQL zaczyna się nie od pisania kodu, tylko od analizy planu wykonania. To właśnie EXPLAIN i EXPLAIN ANALYZE pokazują, czy baza idzie prostą ścieżką po indeksie, czy robi kosztowny skan sekwencyjny przez miliony wierszy.

W tym artykule przejdziesz przez cały proces: od czytania planów, przez diagnozowanie typowych wąskich gardeł, po konkretne przykłady optymalizacji w PostgreSQL i MySQL. Jeśli kiedykolwiek chciałeś zrozumieć, dlaczego baza robi coś głupiego — i jak sprawić, by tego nie robiła, to właśnie tu.

Dlaczego optymalizacja jest kluczowa

Opóźnienia rzędu milisekund w pojedynczym zapytaniu skalują się do minut przy tysiącach równoległych żądań. Optymalizacja zapytań SQL poprawia czas odpowiedzi, przepustowość i stabilność, a także obniża koszty infrastruktury I/O i CPU. Plany wykonania SQL ujawniają, gdzie silnik traci czas: skan sekwencyjny zamiast skanu indeksowego, niepotrzebne sortowanie, błędne oszacowania kardynalności.

Zaczynaj od pomiaru. EXPLAIN ANALYZE to podstawa analizy planu wykonania: pokazuje rzeczywiste czasy i liczby wierszy dla każdego operatora. Gdy już wiesz, który krok jest najdroższy, dobierasz indeksy, restrukturyzujesz JOIN-y lub zmieniasz warunki filtrów. Uzupełnieniem jest świadome projektowanie indeksów — zobacz Indeksy w SQL: teoria i praktyka.

Jak działa silnik zapytań w PostgreSQL i MySQL

Przepływ jest podobny: parser, optymalizator kosztowy, wykonawca. Optymalizator estymuje koszt planów na podstawie statystyk (selektywność, rozkłady wartości) i wybiera plan o najniższym koszcie. PostgreSQL stosuje przepisy przekształceń (rewriter), bogaty zestaw operatorów (Hash Join, Merge Join, Nested Loop, Bitmap Heap/Index Scan), równoległość i prunowanie partycji. MySQL 8.0+ ma EXPLAIN ANALYZE, histograms, hash join i potrafi używać batched key access; wciąż intensywnie polega na indeksach InnoDB.

Różnice mają znaczenie dla optymalizacji: w PostgreSQL CTE historycznie bywały materializowane (starsze wersje), co bywało wąskim gardłem; w MySQL wpływ collations i typów na użycie indeksu jest częstszy. Dobór strategii JOIN (np. Hash vs Nested Loop) zależy od kardynalności i selektywności, które z kolei wynikają z aktualności statystyk. Kontekst porównawczy: PostgreSQL vs MySQL: różnice, które mają znaczenie.

EXPLAIN i EXPLAIN ANALYZE – jak czytać wyniki

EXPLAIN pokazuje plan szacowany; EXPLAIN ANALYZE wykonuje zapytanie i drukuje rzeczywiste czasy i liczby wierszy. Patrz na cost= start..end, rows i width, a przy ANALYZE na actual time i rows. Rozjazd rows (estymacja vs rzeczywistość) wskazuje na problem ze statystykami lub selektywnością warunku. Kolejność operatorów mówi, skąd biorą się wiersze i ile pracy wykonuje każdy krok.

Kluczowe wnioski: Seq Scan na dużej tabeli zwykle oznacza brak selektywnego indeksu; Sort bez LIMIT może przetwarzać całą tabelę; Index Scan + Limit po kolumnie sortowania bywa optymalny. W PostgreSQL używaj EXPLAIN (ANALYZE, BUFFERS) do wglądu w I/O; w MySQL przydatny jest EXPLAIN FORMAT=JSON i EXPLAIN ANALYZE dla czasów na operatorach.

Typowe wąskie gardła i jak je znaleźć

• Sortowanie i brak indeksu pod ORDER BY + LIMIT. Objaw: Sort w planie, wysoki czas CPU. Rozwiązanie: indeks zgodny z predykatem i sortowaniem, ewentualnie covering index.
• Funkcja na kolumnie w WHERE (np. lower(email) = ...). Objaw: Seq Scan mimo indeksu. Rozwiązanie: indeks na wyrażeniu (PostgreSQL) albo normalizacja danych i kolumna pomocnicza.
• Niedokładne statystyki. Objaw: znaczny rozjazd estymacji rows vs actual rows. Rozwiązanie: ANALYZE, zwiększenie targetu statystyk dla problematycznych kolumn.
• Zły porządek JOIN przez błędną kardynalność. Objaw: duże przebiegi pętli Nested Loop. Rozwiązanie: poprawne predykaty, brak implicit castów, aktualne statystyki, czasem hinty (MySQL) lub rewriters.

Wielokrotne materializacje podzapytań i CTE też bywają kosztowne; analizuj ich wpływ na plan. Rozwinięcie znajdziesz w artykule Podzapytania i CTE w SQL.

Jak wykorzystywać indeksy efektywnie

Kolejność kolumn w indeksie złożonym ma znaczenie: najpierw wysoka selektywność i warunki równości, potem zakresy i kolumny sortowania. Dąż do pokrywania zapytań (pokrywanie indeksowe), by uniknąć skoku do tabeli; w PostgreSQL pomaga INCLUDE, w MySQL wskazówką jest Using index w EXPLAIN. Uważaj na nadmiar indeksów — spowalniają DML i zajmują pamięć.

Przykład: optymalizacja filtra + sortowania z LIMIT w PostgreSQL. Pierwszy plan zwykle sortuje wynik, drugi wykorzysta skan indeksowy zgodny z ORDER BY.

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- PostgreSQL: demonstracja wpływu indeksu na plan i czas
CREATE TEMP TABLE orders(
  id int,
  customer_id int,
  created_at timestamp,
  total numeric
);

INSERT INTO orders
SELECT g, (g % 1000), now() - (g || ' seconds')::interval, (g % 100)::numeric
FROM generate_series(1, 100000) AS g;

ANALYZE orders;

-- Bez indeksu: zwykle Seq Scan + Sort
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 10;

-- Indeks zgodny z filtrem i sortowaniem
CREATE INDEX ON orders (customer_id, created_at DESC);

-- Z indeksem: Index Scan + Limit, bez kosztownego sortowania
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 10;

Jeśli plan nadal się myli, sprawdź kolacje/typy (np. porównania tekstów), implicit casty i dopasowanie warunków do prefiksu indeksu. Więcej o typach indeksów i ich właściwościach: Indeksy w PostgreSQL: B-Tree, Hash, GIN, GiST i BRIN.

Antywzorce SQL, których warto unikać

• SELECT * w zapytaniach krytycznych. Pobierasz więcej danych niż trzeba i psujesz pokrywanie indeksowe. Zawsze wybieraj konkretne kolumny.
• Funkcje/operacje po lewej stronie porównania (np. date(created_at) = ...). Indeks nie zadziała; przenieś przekształcenie na stałą lub użyj zakresu.
• LIKE '%term' z wiodącym wildcardem na dużych tabelach. Rozważ trigramy (GIN) w PostgreSQL lub pełnotekstowe indeksy; inaczej będzie Seq Scan.
• Duże OR przez wiele kolumn. Często lepszy UNION ALL kilku prostszych zapytań, które wykorzystają różne indeksy, a potem deduplikacja.
• Implicit casty i niedopasowane typy (np. porównanie int z tekstem). Powodują brak skanu indeksowego.
• OFFSET n, LIMIT m przy głębokiej paginacji. Lepszy keyset pagination po stabilnym kluczu (WHERE (id, created_at) < (...)).

W MySQL unikaj przestarzałego SQL_CALC_FOUND_ROWS; w PostgreSQL rozważ zastąpienie nadmiarowych DISTINCT poprawnym warunkiem JOIN.

Narzędzia do analizy i profilowania zapytań

PostgreSQL: EXPLAIN (ANALYZE, BUFFERS, VERBOSE), pg_stat_statements do top-N zapytań, auto_explain do logowania wolnych planów, pgBadger do raportów ze slow query log, oraz widoki statystyczne (pg_stat_io, pg_statio_user_tables). Wizualizacja planów: explain.depesz.com lub explain.dalibo.com.

MySQL: EXPLAIN ANALYZE i EXPLAIN FORMAT=JSON, performance_schema, slow query log + pt-query-digest, SHOW STATUS dla wskaźników bufora. Pamiętaj o aktualizacji statystyk/histogramów i monitoruj cache hit ratio oraz temp tables na dysku. Do analizy złożonych raportów przydają się techniki analityczne – zobacz Funkcje okienkowe w SQL: PostgreSQL i MySQL.