Plan wykonania dla JOIN

Plan wykonania dla złączenia pokazuje wybrany algorytm — pętlę zagnieżdżoną, scalanie lub mieszanie.

Co pokazuje plan wykonania dla JOIN

Plan wykonania dla JOIN opisuje sposób, w jaki silnik bazy danych połączy wiersze z dwóch lub większej liczby tabel zgodnie z warunkiem zapisanym w klauzuli ON. Optymalizator wybiera kolejność dostępu do tabel, metodę złączenia, używane indeksy oraz operacje przygotowujące dane, takie jak sortowanie, filtrowanie albo budowa struktur pomocniczych. Plan może być przedstawiony jako drzewo operatorów: węzły niżej dostarczają dane operatorom położonym wyżej, a wynik końcowy trafia do klienta wykonującego zapytanie.

Najczęściej analizuje się trzy rodzaje algorytmów złączenia: nested loops join, czyli złączenie pętli zagnieżdżonych, merge join, czyli złączenie przez scalanie, oraz hash join, czyli złączenie mieszające. Nazwy i szczegółowe właściwości operatorów mogą różnić się między silnikami SQL, ale ich podstawowa idea jest podobna. Plan może być planem szacowanym, utworzonym bez uruchamiania zapytania, albo planem rzeczywistym, zawierającym dane z wykonania, na przykład faktyczną liczbę odczytanych wierszy i czas operatorów.

Nested loops, merge join i hash join

Nested loops join przetwarza wiersze z jednego źródła jako zbiór zewnętrzny i dla każdego z nich wyszukuje pasujące wiersze w drugim źródle. Przydatny jest wtedy, gdy zbiór zewnętrzny jest niewielki, a po stronie wewnętrznej istnieje selektywny indeks na kolumnie używanej w warunku złączenia. Przykładowo, dla połączenia tabel orders i customers wyszukanie klienta po kluczu głównym może wymagać pojedynczego dostępu indeksowego dla każdego przetwarzanego zamówienia. Jeżeli jednak zbiór zewnętrzny jest duży, a każde wyszukanie powoduje kosztowny skan, liczba operacji może szybko wzrosnąć.

Merge join wymaga, aby oba wejścia były uporządkowane według kolumn złączenia. Porządek może pochodzić z odpowiednich indeksów albo z wcześniejszego operatora sortowania. Następnie silnik przechodzi przez oba uporządkowane strumienie i porównuje ich wartości, nie musi więc wielokrotnie przeszukiwać całych tabel. Metoda ta jest korzystna przy dużych zbiorach danych, szczególnie gdy wejścia są już posortowane lub wynik ma być dalej używany w operacjach wymagających takiego porządku.

Hash join buduje tablicę mieszającą dla jednego wejścia, zwykle mniejszego, a następnie odczytuje drugie wejście i sprawdza, czy jego wartości odpowiadają kluczom zapisanym w tablicy. Algorytm ten nie wymaga posortowania danych i często jest stosowany przy dużych złączeniach równościowych, takich jak a.customer_id = c.id. Jeżeli struktura nie mieści się w przydzielonej pamięci, silnik może wykonywać operacje pośrednie na dysku, co zwiększa czas zapytania. Hash join nie jest ogólnym rozwiązaniem dla warunków opartych wyłącznie na nierównościach, takich jak < albo >.

Indeksy, selektywność i kolejność operatorów

Na wybór planu wpływają statystyki opisujące rozkład wartości oraz przewidywana liczba wierszy spełniających filtry. Selektywność określa, jak mocno warunek ogranicza zbiór danych. Warunek zwracający kilka wierszy może uzasadniać użycie indeksu i nested loops, natomiast warunek zwracający znaczną część tabeli może prowadzić do skanów oraz hash join. Sam fakt istnienia indeksu nie oznacza, że zostanie on użyty, ponieważ odczyt dużej części tabeli przez indeks może być droższy niż sekwencyjny skan.

W planie warto sprawdzić, czy filtr został zastosowany przed złączeniem, czy dopiero po jego wykonaniu. Wczesne odrzucenie niepotrzebnych wierszy zmniejsza ilość danych przekazywanych do kolejnych operatorów. Istotna jest również kolejność złączeń: połączenie najpierw małych, silnie przefiltrowanych zbiorów może ograniczyć rozmiar danych wejściowych dla następnych JOIN-ów. Optymalizator może zmienić kolejność logicznie równoważnych złączeń, dlatego kolejność tabel zapisana w kodzie zapytania nie zawsze odpowiada kolejności wykonania.

Diagnozę należy oprzeć na różnicy między estymowaną a rzeczywistą liczbą wierszy. Duża rozbieżność oznacza, że statystyki, założenia o niezależności predykatów albo wartości parametrów nie odzwierciedlają danych. Taki błąd może spowodować wybór nested loops dla bardzo dużego zbioru albo przydzielenie zbyt małej pamięci dla hash join. W praktyce analizuje się także odczyty logiczne, operacje wejścia-wyjścia, czas CPU, sortowania, materializację wyników oraz ewentualne zapisy struktur tymczasowych.

Jak czytać plan i szukać przyczyny wolnego JOIN

Analizę zaczyna się od operatora zwracającego wynik i przechodzi w dół drzewa aż do źródeł danych. Należy ustalić, które tabele są skanowane, gdzie następuje filtrowanie oraz na jakich kolumnach wykonywane jest dopasowanie. Warto odróżnić koszt samego operatora JOIN od kosztu jego wejść, ponieważ złączenie może być tylko miejscem, w którym ujawnia się wcześniejszy problem z nadmiernym odczytem danych. Należy też zweryfikować, czy warunek złączenia nie powoduje niezamierzonego mnożenia wierszy, na przykład przez połączenie relacji wiele-do-wielu bez dodatkowego ograniczenia.

Zmiana zapytania powinna wynikać z obserwacji planu, a nie z samej nazwy operatora. Możliwe działania obejmują dodanie indeksu obejmującego kolumny używane w warunku JOIN i filtrach, aktualizację statystyk zgodnie z możliwościami konkretnego silnika, ograniczenie liczby zwracanych kolumn oraz usunięcie zbędnych złączeń. Trzeba sprawdzić także typy danych po obu stronach porównania, ponieważ niejawna konwersja może uniemożliwić efektywne użycie indeksu. Po każdej zmianie należy porównać plan oraz rzeczywiste czasy i odczyty dla reprezentatywnego zestawu danych.

Plan wykonania nie jest stałą właściwością tekstu zapytania. Może zmienić się po zmianie rozmiaru tabel, statystyk, indeksów, parametrów lub konfiguracji pamięci. Dlatego poprawna ocena obejmuje zarówno przypadek małej liczby pasujących wierszy, jak i przypadek obciążenia typowy dla produkcji. Szczególną uwagę trzeba zwrócić na parametryzowane zapytania, wartości skrajne oraz różnice między planem szacowanym a rzeczywistym. Dopiero zgodność przewidywań z obserwowanym przebiegiem pozwala wiarygodnie ocenić, czy wybrany algorytm JOIN odpowiada charakterystyce danych.