Jak zoptymalizować zapytania SQL bez zgadywania: plan wykonania, statystyki i wąskie gardła

Od „wolno działa” do konkretu: jak podchodzić do optymalizacji zapytań

Subiektywne „wolno” kontra mierzalny problem

Zgłoszenie „zapytanie działa wolno” nie mówi nic. Dla jednego „wolno” to 500 ms, dla innego 30 sekund. Dla systemu produkcyjnego istotne jest nie tylko to, ile trwa zapytanie, ale także dlaczego tak trwa: czy ograniczeniem jest CPU, dysk, blokady, sieć czy samo zapytanie SQL.

Pierwszy krok to przejście z poziomu wrażeń na poziom pomiarów. Minimum to:

• Czas wykonania (elapsed time) – od wysłania do otrzymania ostatniego wiersza.
• Zużycie CPU – czas procesora po stronie serwera bazy danych.
• I/O – liczba odczytów/zapisów z dysku lub logicznych odczytów stron.
• Blokady i oczekiwania – czy zapytanie czeka na inne transakcje.

Dopiero po zebraniu tych danych można uczciwie powiedzieć, czy problem leży w samym SQL, strukturze danych, konfiguracji serwera czy w obciążeniu konkurencyjnymi procesami. Optymalizacja zapytania SQL zaczyna się zawsze od identyfikacji mierzalnego wąskiego gardła.

Cykl optymalizacji: zmierz → zdiagnozuj → zmień → zweryfikuj

Optymalizacja zapytań to proces iteracyjny, a nie jednorazowy „tuning”. Najbardziej praktyczny cykl pracy wygląda tak:

1. Zmierz – uruchom zapytanie w kontrolowanym środowisku, zbierz plan wykonania, statystyki I/O, czas, CPU.
2. Zdiagnozuj – przeanalizuj plan wykonania, sprawdź statystyki, indeksy, blokady, konkurencję.
3. Wprowadź zmianę – zmodyfikuj zapytanie, indeks lub parametry (np. aktualizacja statystyk).
4. Zweryfikuj – ponownie zmierz to samo zapytanie w podobnych warunkach.

Bez powrotu do pomiaru po każdej zmianie łatwo pogorszyć wydajność lub rozwiązać lokalny problem kosztem innej części systemu. Dobry nawyk to zapisywanie wersji zapytań i ich wyników (czas, I/O, plan) choćby w prostym notatniku lub systemie kontroli wersji.

Dlaczego przepisywanie SQL „na czuja” zwykle nie działa

Popularny odruch przy problemach z wydajnością to „przepiszę zapytanie inaczej”. Często kończy się to kosmetyką: drobna zmiana składni, użycie aliasu, zamiana IN na EXISTS bez sprawdzenia planu wykonania. Taka praca przypomina zgadywanie – czasem trafi się poprawa, ale bez zrozumienia mechanizmów jest to niestabilne.

System bazodanowy posiada optymalizator zapytań, który sam szuka najlepszego planu w oparciu o statystyki. Dwa różne zapytania semantycznie równoważne (np. z CTE vs podzapytaniem) mogą skończyć się tym samym planem wykonania – albo zupełnie różnymi planami, zależnie od statystyk i parametrów. Bez patrzenia w plan wykonania nie ma kontroli nad rzeczywistym działaniem.

Efektywna optymalizacja polega na tym, by dać optymalizatorowi dobre dane i dobre „warunki brzegowe”: poprawne statystyki, sensowne indeksy, stabilne wzorce parametrów. Same kosmetyczne przepisywania SQL bez analizy planu to strata czasu.

Podstawowe narzędzia: EXPLAIN, EXPLAIN ANALYZE i logi wolnych zapytań

W zależności od silnika bazy danych (PostgreSQL, MySQL, SQL Server, Oracle itd.) nazwy narzędzi się różnią, ale idea jest ta sama. Do kluczowych mechanizmów należą:

• EXPLAIN – generuje szacowany plan wykonania zapytania, pokazując, jak optymalizator zamierza je wykonać.
• EXPLAIN ANALYZE (lub odpowiednik) – uruchamia zapytanie i łączy plan z rzeczywistymi danymi (czas, liczba wierszy na każdym etapie).
• Plan cache – pamięć buforująca wygenerowane plany, dzięki czemu kolejne wykonania podobnych zapytań nie wymagają ponownej kompilacji.
• Log slow queries – dziennik wolnych zapytań (np. w MySQL/PG: slow query log; w SQL Server: Extended Events/Query Store), źródło realnych kandydatów do optymalizacji.

Dopiero połączenie tych narzędzi – logów, planów, statystyk – pozwala naprawdę zoptymalizować zapytania SQL bez zgadywania, bo decyzje opierają się na twardych danych, a nie na wyczuciu składniowym.

Jak działa optymalizator zapytań i co to znaczy „koszt”

Optymalizator kosztowy: wejścia i wyjścia

Większość współczesnych silników baz danych używa optymalizatora kosztowego (CBO – Cost Based Optimizer). Jego zadanie jest proste w definicji, złożone w realizacji: dla danego zapytania SQL znaleźć plan wykonania o najniższym koszcie spośród dopuszczalnych planów.

Optymalizator wykorzystuje jako wejście:

• Statystyki – informacje o tabelach, kolumnach i indeksach (liczba wierszy, rozkład wartości, liczba różnych wartości, histogramy).
• Metadane – definicje indeksów, kluczy, constraintów, typów danych.
• Parametry zapytania – konkretne wartości filtrów, które wpływają na selektywność.
• Konfigurację serwera – np. koszt odczytu z dysku vs. koszt operacji w pamięci.

Na tej podstawie CBO generuje różne warianty planu:

• kolejność łączenia tabel,
• rodzaje joinów (hash, nested loop, merge),
• wybór indeksów (seek/scan),
• strategie sortowania i agregacji.

Z każdego wariantu obliczany jest koszt, a następnie wybierany jest plan o koszcie minimalnym (w sensie modelu optymalizatora, nie zawsze ludzkiej intuicji).

Czym jest „koszt” planu i jak go interpretować

„Koszt” w planie wykonania nie jest czasem w milisekundach. To wartość relatywna, zależna od modelu kosztowego danego silnika bazy danych. Typowy koszt uwzględnia:

• przewidywaną liczbę operacji I/O (odczyt/zapis stron),
• koszt CPU (operacje porównań, sortowanie, haszowanie),
• inne czynniki (np. koszt komunikacji między węzłami w klastrze).

Koszt służy do porównywania planów między sobą, nie do mierzenia rzeczywistego czasu. Plan o koszcie 100 nie jest „trzy razy gorszy” od planu o koszcie 33 w sensie milisekund. Istotne jest, że w danym momencie optymalizator uznał plan A (koszt 100) za „droższy” od planu B (koszt 50) i dlatego wybrał B, zakładając, że rzeczywistość będzie zbliżona do tych założeń.

Jeśli w jednym środowisku (np. testowym) ten sam plan ma inny koszt niż w produkcji, nie jest to nic dziwnego – wpływają na to statystyki, liczba wierszy, konfiguracja I/O. Porównywać należy koszt różnych planów w tym samym środowisku, a nie między serwerami.

Cardinality – klucz do dobrego planu wykonania

Jednym z najważniejszych elementów w całym procesie jest cardinality estimation, czyli szacowanie liczby wierszy, które przepłyną przez każdy operator planu. Optymalizator przewiduje:

• ile wierszy spełni warunki WHERE,
• ile wierszy wygeneruje dany join,
• ile wierszy pozostanie po agregacji.

Na podstawie tych przewidywań decyduje np., czy warto użyć indeksu (seek) czy pełnego skanu tabeli (scan), czy opłaca się zamienić nested loop na hash join, czy lepiej sortować, czy użyć indeksu wspierającego sortowanie. Jeśli cardinality jest oszacowane błędnie (np. optymalizator spodziewa się 100 wierszy, a dostaje 1 000 000), cały model kosztowy się rozjeżdża, a wybrany plan bywa skrajnie nieoptymalny.

Dlatego analiza planu wykonania powinna zawsze obejmować porównanie predicted (estimated rows) do actual rows. Różnice rzędu kilku procent są naturalne, ale jeśli widzisz różnice rzędu kilku rzędów wielkości, problem leży w statystykach lub w nietypowych warunkach zapytania.

To samo zapytanie, różne plany: przykład zależny od statystyk

Spotykany scenariusz: w testach zapytanie działa błyskawicznie, w produkcji zamula system. SQL ten sam, indeksy te same. Różni się tylko… ilość danych i statystyki.

Załóżmy tabelę orders z kolumną status. W testach tylko niewielki procent zamówień ma status „OPEN”, więc optymalizator chętnie używa indeksu na status. W produkcji 80% zamówień to „OPEN”. Statystyki w testach sugerują wysoką selektywność, w produkcji – bardzo niską. Ten sam SQL może w testach prowadzić do planu:

• Index Seek po status = 'OPEN',
• Nested Loop dołączenia kolejnych tabel.

W produkcji, przy innych statystykach, optymalizator może wybrać:

• Full Table Scan po orders,
• Hash Join z inną tabelą.

Bez analizy planu wykonania i statystyk można nieskończenie długo „przepisywać SQL” lub obwiniać środowisko. Tymczasem źródło różnic to inne rozkłady danych i inne cardinality estimation.

Plan wykonania krok po kroku: jak go czytać i interpretować

Formy planów: tekst, graf, JSON – wspólny mianownik

Systemy bazodanowe prezentują plany wykonania w różnych formatach:

• Tekstowy (np. EXPLAIN w PostgreSQL/MySQL) – drzewo operatorów indendowane spacjami.
• Graficzny (np. SQL Server Management Studio) – pudełka i strzałki reprezentujące przepływ danych.
• JSON/XML – przydatne do automatycznej analizy i narzędzi profilujących.

Niezależnie od formatu, plan wykonania to zawsze drzewo operatorów, gdzie liśćmi są odczyty danych (scan/seek), a wyżej znajdują się operacje pośrednie: joiny, sorty, agregacje, filtry. Dane „płyną” od liści do korzenia planu.

Podstawowe pytania przy czytaniu planu:

• Jakie tabele są czytane i jak (scan vs seek, jaki indeks)?
• Jak łączone są dane (typ joinów, warunki)?
• Gdzie i jak są stosowane filtry (WHERE)?
• Gdzie występują sortowania, agregacje, operacje pośrednie?
• Jak zmienia się liczba wierszy na kolejnych etapach planu?

Najważniejsi operatorzy w planie wykonania

Nazwy operatorów różnią się między silnikami, ale można je zgrupować w kilka kategorii. Przykładowe nazwy podane są orientacyjnie (SQL Server / PostgreSQL):

• Scan – pełne skanowanie tabeli lub indeksu:
  • Table/Heap Scan – odczyt wszystkich stron tabeli.
  • Index Scan – przejście po całym indeksie (czasem lepsze niż skan tabeli, ze względu na mniejszy rozmiar).
• Seek – selektywny odczyt z indeksu na podstawie klucza lub zakresu:
  • Index Seek – szybkie dojście do konkretnego obszaru indeksu.
• Joiny:
  • Nested Loop Join – dla każdej wiersza z zewnętrznego źródła szuka dopasowań w wewnętrznym. Idealny, gdy jedno źródło jest małe, a drugie dobrze zindeksowane.
  • Hash Join – buduje tabelę mieszającą (hash) z jednego źródła i sondowanie z drugiego; dobry przy dużych zbiorach bez odpowiednich indeksów.
  • Merge Join – wymaga posortowanych wejść; bardzo wydajny przy dużych, już posortowanych zbiorach.
• Sort – sortowanie danych, zwykle kosztowna operacja, zwłaszcza na dużych zbiorach bez indeksu wspierającego kolejność.
• Aggregate – operacje agregujące (GROUP BY, SUM, COUNT). Mogą być wykonywane różnymi algorytmami (hash aggregate, stream aggregate).
• Filter – stosowanie warunków filtrujących (często wplecione w inne operatory).

Jak wyłapać wąskie gardła bez patrzenia na cały plan naraz

Rozbudowane plany wykonania potrafią być przytłaczające. Zamiast analizować wszystko linijka po linijce, lepiej zacząć od zidentyfikowania wąskich gardeł i wracać w dół drzewa.

Praktyczny sposób analizy:

• Korzeń planu – operator na samej górze (np. SELECT, INSERT, Hash Aggregate). Tutaj zwykle widać sumaryczny koszt.
• Najdroższe dziecko – w graficznych planach często od razu oznaczone procentem kosztu. W planach tekstowych trzeba patrzeć na szacowany koszt (czasem cost=.. lub Estimated Subtree Cost).
• Schodzenie w dół – powtarzanie schematu: zawsze interesuje nas gałąź kumulująca największy koszt i/lub największą liczbę wierszy.

Podczas tego „schodzenia po drzewie” opłaca się zadawać kilka prostych pytań:

• Czy ten operator przerzuca dużo danych (miliony wierszy)?
• Czy na tym etapie dane są już przefiltrowane, czy dopiero „przepychamy surowy wsad”?
• Czy w tym miejscu pojawia się sortowanie lub kosztowna agregacja?

Jeśli filtr jest stosowany po dużym joinie lub sortowaniu, często da się wygrać, „przesuwając” filtr niżej (bliżej źródła danych) przez zmianę zapisu SQL lub dołożenie indeksu pokrywającego warunki.

Estimated vs actual – gdzie plan mija się z rzeczywistością

Plan szacowany (estimated) jest generowany na podstawie statystyk, plan rzeczywisty (actual) pokazuje, co naprawdę się wydarzyło w czasie wykonania. Różnice między nimi są najcenniejszym sygnałem do optymalizacji.

Krytyczne pola do porównania (nazwy zależne od silnika):

• Estimated Rows vs Actual Rows – liczby wierszy przewidziane vs obsłużone.
• Estimated I/O/CPU Cost vs realne czasy z profilu / trace.
• Estimated Row Size (rozmiar wiersza) – wpływa na liczbę stron I/O.

Jeżeli widzisz operator z Estimated Rows = 100, a Actual Rows = 1 000 000, to nie jest „lekka rozbieżność”. To sygnał, że optymalizator myli się o kilka rzędów wielkości i cała reszta planu bazuje na złej informacji. W takiej sytuacji poprawa statystyk albo zmiana konstrukcji zapytania daje zwykle większy efekt niż dodanie kolejnego indeksu „na ślepo”.

Statystyki jako źródło prawdy (czasem fałszywej)

Jakie statystyki są potrzebne, by cardinality miało sens

Statystyki opisują dane widziane przez optymalizator. W większości silników baz danych składają się one z kilku elementów:

• liczba wierszy w tabeli (row count),
• liczba stron danych (data pages / relpages),
• liczba różnych wartości w kolumnie (NDV – number of distinct values),
• wartości minimalne/maksymalne,
• histogram rozkładu (często w formie przedziałów z liczbami wierszy),
• czasem statystyki wielokolumnowe (korelacje między kolumnami).

Na tej bazie optymalizator próbuje odpowiedzieć na pytanie: „jaki procent tabeli spełni warunek col = ? albo col BETWEEN ? AND ??”. Gdy dane są równomiernie rozłożone, proste statystyki wystarczą. Problemy zaczynają się, gdy:

• rozkład jest silnie skośny (np. 90% wierszy ma ten sam status),
• kolumny są ze sobą silnie skorelowane (np. country i city),
• filtry używają funkcji na kolumnie (np. WHERE LOWER(email) = ...).

Nieaktualne statystyki – cichy zabójca planów

Statystyki tworzy się zwykle automatycznie (np. po stworzeniu indeksu) i aktualizuje przy istotnych zmianach danych. Jeśli baza nie ma włączonego auto-update albo dane zmieniają się w skokach, optymalizator zaczyna „wierzyć” w stary obraz tabeli.

Typowe symptomy:

• po masowym imporcie/usunięciu danych zapytania nagle robią się wolne,
• to samo zapytanie po UPDATE STATISTICS (lub odpowiedniku) nagle przyspiesza bez żadnych zmian SQL.

Uwaga: masowe UPDATE STATISTICS w produkcji potrafi być samo w sobie kosztowne i może zmienić plany dla wielu zapytań jednocześnie. Warto mieć dla krytycznych tabel sensowną strategię:

• częstsze odświeżanie dla tabel silnie modyfikowanych,
• rzadsze dla tabel referencyjnych (słowniki, konfiguracje),
• czasem ręczne odświeżenie przed dużą operacją wsadową.

Histogramy i selektywność – kiedy statystyki kolumnowe nie wystarczą

Histogram to próba opisania, jak wartości w kolumnie są rozłożone. Dzięki niemu optymalizator wie, że np. status = 'OPEN' występuje znacznie częściej niż status = 'CANCELLED'. To z kolei przekłada się na decyzję: indeks czy pełny skan.

Problem pojawia się, gdy filtr obejmuje kilka kolumn jednocześnie, np.:

WHERE country = 'PL' AND city = 'Warszawa'

Jeśli optymalizator zna tylko niezależne statystyki dla country i dla city, założy często niezależność rozkładów (tzw. independence assumption). W praktyce, dla danej pary (country, city) rozkład jest mocno skorelowany – część miast występuje tylko w konkretnych krajach. Bez statystyk wielokolumnowych (multi-column statistics) oszacowanie cardinality będzie chybione.

Tip: jeśli filtrowanie i joiny stale używają tej samej kombinacji kolumn, a cardinality się „rozjeżdża”, statystyki złożone często pomagają bardziej niż dokładanie nowych indeksów.

Indeksy w kontekście planu – nie każda kolumna musi być „na indeksie”

Seek, scan i lookup – co naprawdę robi optymalizator z indeksem

Indeks w planie to nie tylko Index Seek. Pojawiają się też:

• Index Scan – przejście po całym indeksie; bywa szybsze niż skan tabeli, jeśli indeks jest węższy (mniej kolumn) i lepiej się mieści w pamięci.
• Key Lookup / RID Lookup – losowy dostęp do tabeli bazowej po znalezieniu klucza w indeksie.
• Bitmap index / Bitmap Heap Scan (np. PostgreSQL) – łączenie kilku indeksów bitową maską.

Częsty antywzorzec: indeks pokrywający filtr, ale nie zawierający kolumn potrzebnych w selekcie. Plan wtedy wygląda mniej więcej tak:

• Index Seek po kolumnach filtrujących,
• następnie mnóstwo Key Lookup do tabeli bazowej po każdej pasującej pozycji.

Przy niewielkiej liczbie wierszy takie lookupy są akceptowalne. Przy większych – robi się losowe I/O, które zabija wydajność. W takiej sytuacji albo:

• przebudowuje się indeks na pokrywający (ang. covering index) – dodając brakujące kolumny jako INCLUDE / kolumny niekluczowe,
• albo akceptuje się Index Scan/Table Scan i optymalizuje w innym miejscu planu (np. przez wcześniejsze filtrowanie).

Kolejność kolumn w indeksie – filtr, join, sort

Przy projektowaniu indeksu wielokolumnowego liczy się kolejność. W uproszczeniu:

• najpierw kolumny o wysokiej selektywności używane w filtrach/joinach,
• potem kolumny wspierające sortowanie lub grupowanie,
• na końcu kolumny często zwracane, ale mało selektywne (jako INCLUDE / only-included).

Przykład:

SELECT  o.order_id, o.customer_id, o.order_date
FROM    orders o
WHERE   o.customer_id = @customerId
AND     o.order_date >= @fromDate
ORDER BY o.order_date DESC;

Indeks typu:

CREATE INDEX IX_orders_customer_date
ON orders(customer_id, order_date DESC)
INCLUDE(order_id);

pozwoli:

• użyć seek po customer_id + zakresie order_date,
• zwrócić order_id bez lookupów,
• wyeliminować osobne sortowanie (dane już są w odpowiedniej kolejności).

To jest moment, gdzie plan wykonania jasno pokaże, czy indeks realnie pomaga: sortowanie zniknie, a główną operacją stanie się tani Index Seek.

Indeksy „na wszystko” – dlaczego to nie działa

Czasem spotyka się bazy, w których każda większa kolumna ma osobny indeks. Efekt: ogromny narzut na zapisy (INSERT/UPDATE/DELETE) i rozdmuchany storage, a plany nadal są marne.

Powód jest prosty: optymalizator zwykle wybiera jeden indeks na tabelę dla danego skanu/seeku (wyjątek: bitmapy i rzadko stosowane strategie łączenia wielu indeksów). Jeśli filtr używa trzech kolumn na raz, a każda ma osobny indeks jednokolumnowy, to wybór jest ograniczony:

• użycie jednego z indeksów i reszta filtrów jako Filter na wczytanych danych,
• albo pełny skan tabeli.

Lepsza strategia: kilka dobrze przemyślanych indeksów wielokolumnowych dla najcięższych zapytań zamiast dziesiątek indeksów „na wszelki wypadek”.

Joiny, filtrowanie i sortowanie – kontrola przepływu danych

Dlaczego kolejność joinów ma znaczenie (nawet jeśli SQL jej nie pokazuje)

Pisząc:

FROM A
JOIN B ON ...
JOIN C ON ...

nie definiuje się jeszcze faktycznej kolejności łączenia danych. Optymalizator może połączyć najpierw B z C, a dopiero potem wynik z A, jeśli tak wyjdzie taniej. Decyzję opiera właśnie na cardinality.

Uproszczona zasada: najpierw łączymy te tabele, które po joinie dają najmniej wierszy, aby późniejsze operacje (kolejne joiny, sortowania, agregacje) pracowały na mniejszym zbiorze. Błędne oszacowanie cardinality powoduje, że optymalizator dobiera złą kolejność i pompuje zbędne dane przez kosztowne operacje.

Wybór typu joinu – nested loop, hash, merge w praktyce

Skrótowe praktyczne zasady (przy poprawnych statystykach):

• Nested Loop – dobry, gdy jedna strona jest mała (kilka–kilkanaście tysięcy wierszy) i istnieje indeks wspierający wyszukiwanie po stronie dużej tabeli.
• Hash Join – preferowany przy dużych zbiorach bez odpowiednich indeksów; płaci się za budowę hasha w pamięci (lub na dysku), ale zyskuje równomierny czas.
• Merge Join – idealny, gdy obie strony są już posortowane po kluczu joinu; praktycznie brak dodatkowego kosztu sortowania i bardzo dobra skalowalność.

Jeśli w planie widać Nested Loop na milionach wierszy bez sensownego indeksu po stronie wewnętrznej, to sygnał, że albo brakuje indeksu, albo cardinality estimation jest błędne i optymalizator „myślał”, że join będzie mały.

Filtry „wcześniej” vs „później” – pushdown filtrów

Filtry stosowane jak najbliżej źródła danych (tabeli) zmniejszają ilość wierszy przerzucanych przez plan. Teoretycznie optymalizator potrafi „przepchnąć” warunki WHERE w dół (predicate pushdown), ale są konstrukcje, które mu to utrudniają, np.:

• funkcje niedeterministyczne (GETDATE(), RAND()),
• złożone podzapytania korelowane,
• warunki ukryte w funkcjach użytkownika.

Przykład problematyczny:

WHERE CONVERT(VARCHAR(10), order_date, 120) = '2023-01-01'

Taki warunek praktycznie uniemożliwia użycie indeksu po order_date, więc pojawi się Scan i filtr dopiero na wczytanych wierszach. Przepisanie na:

WHERE order_date >= '2023-01-01'
  AND order_date < '2023-01-02'

daje optymalizatorowi szansę na indeksowy Seek z minimalnym zakresem.

Sortowanie i grupowanie – kiedy ORDER BY naprawdę boli

Sortowanie i agregacje są z natury kosztowne, bo wymagają zebrania większego fragmentu danych przed wypchnięciem ich dalej w planie. Optymalizator robi dwie rzeczy, żeby to złagodzić: próbuje użyć istniejącego porządku danych (indeksy, strumień po joinie) oraz ograniczyć liczbę wierszy, które w ogóle muszą przejść przez Sort / Hash Aggregate / Stream Aggregate.

Charakterystyczne objawy problemów z sortowaniem w planie:

• operator Sort z wysokim kosztem i dużą liczbą wierszy wejściowych,
• sorty „pośrednie” na kolumnach, które i tak nie są używane w finalnym ORDER BY,
• sorty wykonywane przed joinem lub agregacją, gdzie dałoby się je wykonać po redukcji danych.

Częsta zmiana, która poprawia plan, to przepchnięcie filtrów i części agregacji przed sortowanie albo wykorzystanie indeksu, który już zapewnia wymagany porządek. Przykład:

SELECT  TOP (100)
        o.order_id, o.customer_id, o.order_date, o.total_amount
FROM    orders o
WHERE   o.status = 'PAID'
ORDER BY o.order_date DESC;

Jeśli jedyny indeks to IX_orders_status(order_status), pojawi się:

• Index Seek / Scan po status,
• drogi Sort po order_date na dużym zbiorze.

Indeks:

CREATE INDEX IX_orders_status_date
ON orders(status, order_date DESC)
INCLUDE(total_amount, customer_id);

pozwoli wyeliminować sortowanie, bo plan skorzysta z naturalnego porządku indeksu. Dodatkowo filtr na status stanie się selektywny, więc mniej wierszy przepłynie przez dalszą część planu.

GROUP BY i DISTINCT – agregacje kontra plan wykonania

GROUP BY i DISTINCT redukują dane, co zwykle jest korzystne, ale sposób implementacji ma duże znaczenie. Typowe operatory:

• Hash Aggregate – buduje strukturę w pamięci; dobrze skaluje się przy braku sortowania wejścia, ale jest wrażliwy na błędne cardinality (może wylać na dysk),
• Stream Aggregate – wymaga posortowanego wejścia po kolumnach grupowania, ale jest bardzo tani pamięciowo i CPU.

Optymalizator wybiera Stream Aggregate, jeśli wejście jest już posortowane albo da się je tanio posortować. Tu znowu wchodzą w grę indeksy: indeks zgodny z GROUP BY (te same kolumny, ta kolejność) często pozwala zredukować duży Hash Aggregate do taniego Stream Aggregate bez dodatkowego sortu.

Przykład dość typowy w raportach:

SELECT  customer_id,
        COUNT(*) AS orders_count
FROM    orders
WHERE   order_date >= @fromDate
GROUP BY customer_id;

Dwa warianty planu:

• brak indeksu po customer_id – skan, filtr po dacie, potem Hash Aggregate,
• indeks IX_orders_date_customer(order_date, customer_id) – seek po dacie, potem Stream Aggregate bez dodatkowego sortowania.

Różnica w kosztach robi się dramatyczna, gdy tabela rośnie. Plan wykonania jasno pokaże, że przepływ wierszy po agregacji jest ten sam, ale ścieżka dojścia do tej redukcji jest zupełnie inna.

ORDER BY z TOP / LIMIT – optymalizacja pod „pierwsze N rekordów”

Połączenie ORDER BY z TOP / LIMIT / FETCH FIRST zmienia strategię optymalizatora. Często nie trzeba sortować całości, a jedynie tyle, by znaleźć pierwsze N rekordów. Silniki potrafią stosować algorytmy typu „partial sort” (sort dopóki nie będzie jasne, które rekordy wchodzą do TOP).

Plan zdradza tę optymalizację poprzez operator sortujący z adnotacją, że jest ograniczony (np. Top-N Sort w PostgreSQL). Ułatwia to życie przy paginacji:

SELECT  order_id, order_date, total_amount
FROM    orders
WHERE   customer_id = @customerId
ORDER BY order_date DESC
OFFSET  @offset ROWS FETCH NEXT @pageSize ROWS ONLY;

Bez sensownego indeksu plan musi przejść przez dużo danych i sortować szerokie zbiory. Dobrze dobrany indeks (np. (customer_id, order_date DESC)) plus stabilne filtrowanie po kliencie sprawia, że sortowanie sprowadza się do wąskiego zakresu i taniego seeku.

Parametryzacja, cache planów i sniffing – kiedy plan jest dobry tylko czasem

Jak działa cache planów – dlaczego to w ogóle ma znaczenie

Silniki SQL (SQL Server, PostgreSQL, Oracle) starają się reuse’ować plany wykonania, żeby nie kompilować ich przy każdym wywołaniu. Parametryzowane zapytanie:

SELECT  *
FROM    orders
WHERE   customer_id = @customerId;

dostaje zwykle jeden plan, który jest stosowany dla wielu wartości @customerId. To świetne dla CPU, ale potrafi zabić wydajność, gdy rozkład danych jest skrajnie nierównomierny (np. kilku „dużych” klientów i wielu małych).

Mechanizm ten działa tak:

1. pierwsze wykonanie zapytania kompiluje plan na podstawie aktualnych parametrów i statystyk,
2. plan trafia do cache i jest identyfikowany m.in. przez tekst zapytania oraz „kształt” parametrów,
3. kolejne wywołania używają dokładnie tego samego planu (dopóki nie zostanie usunięty z cache lub nie zostaną wymuszone zmiany).

Jeśli pierwsze wykonanie trafi w „typowego” klienta – super. Jeśli w „potwora”, który ma 80% wszystkich danych – plan będzie przeliczony pod ten skrajny przypadek i stanie się kulą u nogi dla reszty użytkowników.

Parametr sniffing – co się dzieje przy pierwszym wywołaniu

„Parameter sniffing” (podejrzenie parametru) to zjawisko, gdy optymalizator używa rzeczywistej wartości parametru z pierwszego wykonania do oszacowania cardinality. W praktyce:

• dla pierwszej wartości powstaje zazwyczaj bardzo dobry plan,
• dokładnie ten sam plan może być fatalny dla innych wartości.

Typowy przykład:

CREATE PROCEDURE GetOrders
  @CustomerId INT
AS
BEGIN
  SELECT  *
  FROM    orders
  WHERE   customer_id = @CustomerId;
END;

Jeśli procedura zostanie pierwszy raz odpalona dla małego klienta, optymalizator wybierze prawdopodobnie Index Seek po customer_id i Nested Loop w joinach dalej. Przy dużym kliencie ten sam plan zacznie powodować masę losowych I/O, a może lepszy byłby pełny skan + Hash Join. Plan z cache tego nie uwzględnia.

W planie wykonania widać to po:

• drastycznej różnicy między Estimated Number of Rows a Actual Number of Rows przy operatorach,
• „dziwnych” wyborach joinów i indeksów, które wyglądają sensownie tylko dla jednego scenariusza.

Parametryzacja „prosta” vs „bezpośrednia” (simple vs forced)

Niektóre silniki (np. SQL Server) mają tryby parametryzacji:

• simple – parametryzowane są tylko wybrane wzorce zapytań; część jest kompilowana „per literal”,
• forced – wszystko, co się da, jest parametryzowane, żeby zminimalizować liczbę kompilacji.

Brzmi dobrze, ale przy agresywnej parametryzacji rośnie ryzyko, że jeden plan będzie musiał pasować do bardzo różnych przypadków. Wtedy częściej pojawiają się:

• plany „średnie” – ani dobre dla małych, ani dla dużych zestawów danych,
• próby kompensowania tego przez hinty lub rozbijanie logiki na kilka zapytań.

Przy diagnozowaniu wydajności ważne jest więc nie tylko, jaki jest plan, ale również, dla jakich parametrów został skompilowany. W niektórych narzędziach plan zawiera sekcję z „sniffed parameter values” – to pierwsza rzecz do sprawdzenia, gdy zachowanie zapytania jest „raz szybkie, raz wolne”.

Strategie radzenia sobie z parameter sniffing

Zamiast ślepo wyłączać sniffing, lepiej świadomie nim zarządzać. Kilka praktycznych podejść:

• Rekompilacja selektywna – dopisek typu OPTION (RECOMPILE) lub odpowiednik w danym silniku wymusza kompilację przy każdym wykonaniu. Dobre dla krótkich, rzadko wywoływanych zapytań, fatalne dla ciężkich raportów wykonywanych co sekundę.
• „Bezpieczne” parametry przy kompilacji – wzorce, w których do kompilacji używa się typowych / medianowych wartości zamiast skrajnych (np. poprzez dodatkową warstwę logiki, która wybiera scenariusz na podstawie liczności danych).
• Rozbicie procedury na warianty – osobne procedury (lub gałęzie) dla „małych” i „dużych” klientów, gdzie każda kompiluje się do innego planu: jedna nastawiona na seeki, druga na skany i hash joiny.
• Wymuszenie planu – tam, gdzie rozkład jest stabilny, a wiemy, że pewien plan jest „zawsze wygrywający”, można go przypiąć (plan guides, forced plan). Ryzykowne przy szybko zmieniających się danych.

Tip: jeśli problem dotyczy kilku krytycznych procedur, pierwszym ruchem zwykle jest analiza histogramów/statystyk dla kolumn parametryzowanych, a dopiero potem majstrowanie przy sniffingu. Często okazuje się, że wystarczy zaktualizować statystyki lub dodać statystyki złożone.

„Parameter-sensitive” planing – jeden tekst, wiele planów

Nowsze wersje niektórych silników (np. SQL Server z funkcją PSP, PostgreSQL z niestandardowymi rozszerzeniami) wprowadzają mechanizmy parameter-sensitive, w których zapytanie może mieć w cache więcej niż jeden plan, zależnie od zakresu parametrów.

Mechanizm działa uproszczając:

1. pierwsze wykonania budują plan jak zwykle,
2. silnik obserwuje, że dla różnych zakresów wartości kardynalność różni się na tyle, że plan przestaje być adekwatny,
3. tworzy alternatywne plany i przypisuje im warunki użycia (np. @CustomerId IN (duzi klienci) vs reszta).

Dla osoby czytającej plan oznacza to, że trzeba patrzeć nie tylko na sam plan, ale też na kontekst, w jakim został użyty: które „ramię” planu zostało aktywowane przy konkretnym wywołaniu. W diagnostyce przydaje się porównanie planów dla „małego” i „dużego” przypadku w narzędziach historycznych (Query Store, pg_stat_statements + auto_explain itp.).

Dynamic SQL i kompilacja „per tekst”

Dynamiczny SQL kompilowany jest osobno dla każdej unikalnej treści. Jeśli parametry są wstrzykiwane bezpośrednio do tekstu:

DECLARE @sql NVARCHAR(MAX) = N'
SELECT  *
FROM    orders
WHERE   customer_id = ' + CAST(@CustomerId AS NVARCHAR(20));

EXEC(@sql);

każda wartość klienta tworzy w zasadzie nowe zapytanie. Z punktu widzenia cache planów oznacza to:

• brak reuse’u planu – obciążenie CPU rośnie przez kompilacje,
• silnik nie ma sensownego materiału statystycznego do ustalenia typowych scenariuszy.

Bezpieczniejszy i bardziej wydajny jest dynamiczny SQL parametryzowany:

DECLARE @sql NVARCHAR(MAX) = N'
SELECT  *
FROM    orders
WHERE   customer_id = @CustomerId';

EXEC sp_executesql @sql, N'@CustomerId INT', @CustomerId = @CustomerId;

Plan powstaje wtedy dla jednej postaci tekstu i może być współdzielony między wywołaniami. Nadal trzeba uważać na sniffing, ale mechanizm cache działa na naszą korzyść, a nie przeciwko nam.

Parametry a warunki opcjonalne – klasyczny antywzorzec z „OR”

Często spotykany wzorzec w procedurach raportowych to warunki opcjonalne na parametrach:

WHERE (@CustomerId IS NULL OR customer_id = @CustomerId)
  AND (@Status IS NULL OR status = @Status)
  AND (@FromDate IS NULL OR order_date >= @FromDate);

Z biznesowego punktu widzenia wygodne, z punktu widzenia optymalizatora – koszmar. Plan musi być „uniwersalny”, nie da się go skompilować osobno dla kombinacji „jest klient, nie ma daty”, „jest data, nie ma klienta” itd. W efekcie:

• indeksy używane są mniej agresywnie (częściej pojawia się Scan),
• cardinality estimation staje się mniej precyzyjne, bo warunki OR i IS NULL trudniej dobrze oszacować.

Praktyczny kompromis to rozbicie logiki na kilka kwerend wewnątrz procedury, każdą z osobnym planem:

Najczęściej zadawane pytania (FAQ)

Jak zacząć optymalizację wolnego zapytania SQL?

Punkt startowy to zawsze pomiar, a nie przepisywanie zapytania „na oko”. Zanim cokolwiek zmienisz, zmierz: całkowity czas wykonania (elapsed time), zużycie CPU, liczbę operacji I/O (odczyty/zapisy stron) oraz to, czy zapytanie czeka na blokady lub inne zasoby.

Następnie wygeneruj plan wykonania (EXPLAIN, SHOWPLAN itp.) i – jeśli to możliwe – jego wersję z realnymi danymi (EXPLAIN ANALYZE, Actual Execution Plan). Dopiero na tym materiale da się stwierdzić, gdzie faktycznie jest wąskie gardło: w samym SQL, indeksach, statystykach czy obciążeniu serwera.

Co to jest plan wykonania zapytania SQL i jak go czytać?

Plan wykonania (execution plan) to opis kroków, jakie silnik bazy danych wykona, żeby zwrócić wynik zapytania. Pokazuje m.in. kolejność operacji, sposób łączenia tabel (join), użyte indeksy oraz szacowaną liczbę wierszy na każdym etapie (cardinality).

Przy analizie planu najpierw szukaj najdroższych operatorów (najwyższy koszt, dużo wierszy): pełne skany dużych tabel, powtórne sortowania, joiny, które „puchną” w liczbę wierszy. Kluczowe jest porównanie Estimated Rows (szacowane) z Actual Rows (rzeczywiste) – duże rozjazdy wskazują na problem ze statystykami lub selektywnością filtrów.

Czym różni się EXPLAIN od EXPLAIN ANALYZE i kiedy którego używać?

EXPLAIN pokazuje plan szacowany – optymalizator podaje, jak zamierza wykonać zapytanie i czego się spodziewa (koszt, przewidywana liczba wierszy). To szybkie i bezpieczne narzędzie, bo samo zapytanie zwykle nie jest w pełni wykonywane (albo wykonuje się w trybie „symulacji”, zależnie od silnika).

EXPLAIN ANALYZE (lub odpowiednik) faktycznie uruchamia zapytanie i dokleja do planu rzeczywiste czasy i liczby wierszy. Dzięki temu widać, gdzie założenia optymalizatora odbiegają od rzeczywistości. Używaj EXPLAIN ANALYZE na środowisku testowym lub z ograniczonym zakresem danych – może mocno obciążyć produkcję.

Co oznacza „koszt” w planie zapytania SQL i czy da się go przeliczyć na czas?

Koszt (cost) w planie to wartość względna, używana wewnętrznie przez optymalizator kosztowy (CBO – Cost Based Optimizer). Odzwierciedla przewidywaną liczbę operacji I/O, pracy CPU i innych czynników, ale nie jest to czas w milisekundach. Służy do porównywania alternatywnych planów dla tego samego zapytania.

Plan o koszcie 100 nie oznacza czasu trzy razy dłuższego niż plan o koszcie 33. Interpretuj koszt lokalnie: w jednym środowisku patrz, który z dostępnych planów ma niższy koszt i czy faktyczny czas wykonania to potwierdza. Porównywanie wartości kosztu między różnymi serwerami zwykle nie ma sensu, bo różnią się statystyki i konfiguracja.

Dlaczego to samo zapytanie SQL działa szybko w testach, a wolno w produkcji?

Najczęstsza przyczyna to inne statystyki i inny rozkład danych. Optymalizator buduje plan na podstawie informacji o liczbie wierszy, selektywności kolumn, histogramów itp. Jeśli w testach masz mało danych i „ładny” rozkład, a w produkcji tabele są większe i bardziej jednostronne (np. 80% wierszy ma ten sam status), optymalizator wybierze inne plany.

Efekt: w testach widzisz szybki indeks seek na status, w produkcji – pełny skan tabeli, bo z punktu widzenia produkcyjnych statystyk filtr nie jest selektywny. Rozwiązaniem jest dbałość o aktualne statystyki, reprezentatywny zestaw testowy oraz analiza planów z obu środowisk, a nie porównywanie samej treści SQL.

Czym jest cardinality w SQL i dlaczego ma taki wpływ na wydajność?

Cardinality to szacowana liczba wierszy przepływających przez dany operator w planie (np. wynik filtra WHERE, wynik joinu, liczba wierszy po GROUP BY). Optymalizator opiera na tych szacunkach wszystkie decyzje: wybór indeksu, rodzaj joinu, sposób sortowania czy agregacji.

Jeśli cardinality jest radykalnie zaniżona lub zawyżona (np. optymalizator spodziewa się setek wierszy, a dostaje miliony), cały model kosztowy się rozjeżdża. Wtedy pojawiają się plany, które „na papierze” są tanie, a w praktyce duszą serwer. Tip: w planach zawsze zestawiaj Estimated Rows z Actual Rows – różnice o kilka rzędów wielkości to sygnał, że trzeba przyjrzeć się statystykom, typom danych lub warunkom filtrującym.

Czy przepisywanie zapytań SQL (np. zmiana IN na EXISTS) naprawdę przyspiesza działanie?

Kosmetyczne zmiany składni rzadko są kluczem do wydajności. Dla wielu równoważnych semantycznie konstrukcji optymalizator i tak generuje identyczny lub bardzo podobny plan wykonania, o ile statystyki i parametry wejściowe są te same. Zmiana IN na EXISTS może coś zmienić, ale tylko w kontekście konkretnego silnika, wersji i danych – bez analizy planu to czyste zgadywanie.

Realne zyski daje raczej:

• poprawa statystyk (aktualizacja, lepsze histogramy),
• sensowne indeksy zgodne z typowymi filtrami i joinami,
• stabilne wzorce parametrów (unikanie skrajnych przypadków, które „psują” plan),
• redukcja zbędnych danych już na wejściu (filtry, projekcja kolumn).

Bez spojrzenia w plan wykonania trudno ocenić, czy przepisywanie SQL rzeczywiście pomogło, czy tylko zmieniło kosmetycznie składnię.

Co warto zapamiętać

• Zgłoszenie „zapytanie działa wolno” trzeba zamienić na twarde metryki: czas wykonania (elapsed time), zużycie CPU, I/O oraz informacje o blokadach i oczekiwaniach – dopiero wtedy widać faktyczne wąskie gardło.
• Optymalizacja zapytań to cykl iteracyjny: zmierz → zdiagnozuj → wprowadź zmianę → zweryfikuj, z konsekwentnym porównywaniem czasu, planu i statystyk po każdej modyfikacji.
• Przepisywanie SQL „na czuja” (zmiana składni bez analizy planu wykonania) zwykle nie rozwiązuje problemu, bo optymalizator i tak może wygenerować ten sam lub nawet gorszy plan.
• Kluczem jest współpraca z optymalizatorem: aktualne statystyki, sensowne indeksy i przewidywalne wzorce parametrów dają mu lepsze dane wejściowe niż losowe poprawki w składni zapytania.
• Podstawowe narzędzia diagnostyczne to: EXPLAIN (szacowany plan), EXPLAIN ANALYZE (rzeczywisty plan z czasami i liczbą wierszy), cache planów oraz log wolnych zapytań – dopiero ich zestaw pozwala optymalizować bez zgadywania.
• Koszt planu wykonania to wartość relatywna używana do porównywania wariantów wewnątrz silnika (model kosztowy), a nie bezpośredni czas w milisekundach; służy do wyboru najlepszego planu, nie do mierzenia wydajności.
• Analiza wąskich gardeł powinna rozróżniać źródło problemu: samo zapytanie, strukturę danych (np. brak indeksu), konfigurację serwera lub konkurencyjne obciążenie – inaczej łatwo „tuningować” nie ten element, który naprawdę boli.