Optymalizacja JOIN

Optymalizacja złączeń to dobór indeksów i kolejności tabel tak, by optymalizator wybrał najtańszy plan wykonania.

Jak optymalizator ocenia złączenie tabel

Optymalizacja JOIN polega na takim przygotowaniu zapytania, aby połączenie wierszy z dwóch lub większej liczby tabel wymagało możliwie mało odczytów, porównań i operacji sortowania. Warunek złączenia najczęściej odwołuje się do klucza głównego i klucza obcego, na przykład łączy identyfikator klienta z tabeli zamówień z identyfikatorem w tabeli klientów. Wynik zależy nie tylko od treści instrukcji SQL, ale również od rozmiaru tabel, rozkładu wartości, dostępnych indeksów oraz aktualnych statystyk.

Optymalizator kosztowy analizuje możliwe plany wykonania i szacuje ich koszt. Bierze pod uwagę między innymi przewidywaną liczbę wierszy po filtrach, selektywność warunków, koszt odczytu stron danych oraz ilość pamięci potrzebnej na sortowanie lub budowę struktur pomocniczych. Kolejność tabel zapisana w zapytaniu nie musi być kolejnością używaną podczas wykonania. Silnik może rozpocząć od tabeli z bardziej selektywnym filtrem, a następnie dołączać kolejne źródła danych, jeśli taki wariant prowadzi do mniejszego zbioru pośredniego.

Indeksy wspierające warunki ON i WHERE

Najważniejszym elementem optymalizacji jest zwykle indeks obejmujący kolumny wykorzystywane w warunku złączenia. Jeżeli tabela zamówień jest łączona z tabelą klientów przez kolumnę customer_id, indeks na customer_id w tabeli zamówień może ograniczyć liczbę wierszy wyszukiwanych dla każdego klienta. Klucz główny jest zazwyczaj indeksowany automatycznie, lecz obecność indeksu po stronie klucza obcego nie powinna być zakładana bez sprawdzenia definicji schematu.

Indeks musi odpowiadać rzeczywistemu sposobowi filtrowania i sortowania danych. Przy zapytaniu filtrującym po statusie, a następnie łączącym po customer_id, użyteczny może być indeks złożony, którego kolejność kolumn uwzględnia oba warunki. Nie oznacza to jednak, że każda dodatkowa kolumna poprawia wydajność. Indeksy zajmują miejsce, wymagają aktualizacji podczas INSERT, UPDATE i DELETE, a zbyt duża ich liczba zwiększa koszt modyfikacji tabeli. Niska selektywność kolumny, na przykład pola przyjmującego tylko kilka powtarzalnych wartości, może sprawić, że pełny odczyt tabeli będzie tańszy niż użycie indeksu.

Nested loop, hash join i merge join

Jednym z typowych sposobów realizacji JOIN jest nested loop join, czyli złączenie zagnieżdżonych pętli. Silnik wybiera tabelę zewnętrzną, a dla każdego jej wiersza wyszukuje pasujące rekordy w tabeli wewnętrznej. Taki algorytm może być korzystny, gdy pierwszy zbiór jest mały, a po stronie wewnętrznej istnieje skuteczny indeks. Bez odpowiedniego dostępu indeksowego wielokrotne skanowanie drugiej tabeli może jednak prowadzić do dużej liczby odczytów.

Hash join tworzy strukturę haszującą dla jednego ze zbiorów, a następnie szuka w niej dopasowań z drugiego zbioru. Jest przydatny przy większych zbiorach i równościowym warunku złączenia, lecz wymaga pamięci. Jeżeli dostępna pamięć jest niewystarczająca, część pracy może zostać przeniesiona do plików tymczasowych, co zwiększa koszt operacji. Merge join wykorzystuje uporządkowane dane: obie strony są odczytywane w kolejności zgodnej z kluczem złączenia. Gdy dane nie są już posortowane ani dostępne przez odpowiednie indeksy, konieczne sortowanie może zmienić opłacalność tego planu.

Weryfikacja planu i bezpieczne strojenie zapytania

Oceny optymalizacji nie należy opierać wyłącznie na czasie pojedynczego uruchomienia. Należy sprawdzić plan wykonania oraz porównać wartości szacowane z rzeczywistymi, jeśli używane narzędzie udostępnia taki pomiar. Szczególnie istotne są rozbieżności między przewidywaną a faktyczną liczbą wierszy, wybór skanowania zamiast wyszukiwania indeksowego, koszt sortowania, użycie plików tymczasowych i liczba odczytanych stron. Do analizy można wykorzystać mechanizm planu wykonania dostępny w danym silniku, na przykład EXPLAIN lub jego odpowiednik.

Przed zmianą indeksu albo przepisaniem zapytania trzeba sprawdzić, czy problem występuje dla typowych oraz największych zestawów danych. Warto aktualizować statystyki zgodnie z możliwościami używanego silnika, usuwać niepotrzebne warunki powodujące konwersje typów i unikać funkcji nakładanych na kolumny używane w JOIN, gdy uniemożliwia to efektywne wykorzystanie indeksu. Należy także zweryfikować, czy złączenie nie zwielokrotnia wierszy z powodu relacji jeden-do-wielu. Sama poprawa czasu wykonania nie wystarcza, jeśli zmieniony warunek zwraca inny wynik niż wymagany przez logikę aplikacji. Gdy spowolnienie aplikacji korzystającej z takich zapytań wynika z lokalnego komputera, a nie z samej bazy danych, Warszawskie Pogotowie Komputerowe zajmuje się profesjonalną diagnostyką i naprawą laptopów oraz komputerów PC w tym zakresie.