Indeksy a JOIN

Indeksy a JOIN opisują wpływ struktur indeksowych na wydajność operacji łączenia danych z kilku tabel.

Rola indeksów w operacji JOIN

Indeks jest uporządkowaną strukturą pomocniczą, która pozwala szybciej odnaleźć wiersze spełniające warunek wyszukiwania. W zapytaniu łączącym tabele indeks może ograniczyć liczbę rekordów odczytywanych po stronie kolumn użytych w predykacie JOIN. Przykładowo, gdy tabela zamowienia jest łączona z tabelą klienci przez warunek zamowienia.klient_id = klienci.id, indeks na klienci.id umożliwia szybkie wyszukanie pasującego klienta dla każdego przetwarzanego zamówienia. W praktyce kolumna będąca kluczem głównym jest zwykle indeksowana, natomiast kolumny kluczy obcych nie zawsze otrzymują indeks automatycznie.

Znaczenie indeksu zależy od planu wykonania wybranego przez optymalizator. Sam fakt istnienia indeksu nie oznacza, że zostanie on użyty. Jeżeli zapytanie zwraca dużą część tabeli, odczyt sekwencyjny może wymagać mniej operacji wejścia-wyjścia niż wielokrotne pobieranie pojedynczych wierszy z indeksu i tabeli. Optymalizator porównuje między innymi przewidywaną liczbę pasujących rekordów, koszt odczytu, rozmiar tabel, dostępne statystyki oraz kolejność wykonywania złączeń.

Indeksy a algorytmy łączenia tabel

Jednym z algorytmów jest nested loop join, w którym dla każdego wiersza z jednej tabeli wyszukiwane są pasujące rekordy w drugiej. Indeks na kolumnie łączenia tabeli wewnętrznej może w takim przypadku znacząco zmniejszyć koszt wyszukiwania. Rozwiązanie jest szczególnie użyteczne, gdy zewnętrzna tabela po filtrach dostarcza niewiele wierszy, a indeks pozwala szybko znaleźć odpowiadające im rekordy. Jeżeli jednak tabela zewnętrzna jest duża, wielokrotne wyszukiwanie może być kosztowne, nawet przy istniejącym indeksie.

Hash join buduje strukturę haszującą dla jednej strony złączenia i sprawdza w niej wiersze drugiej strony. Ten algorytm może działać bez indeksu na kolumnach łączenia, zwłaszcza przy większych zbiorach danych, ale wymaga dodatkowej pamięci i odpowiednich warunków wykonania. Merge join przetwarza dwa wejścia w uporządkowanej kolejności. Indeksy utrzymujące właściwy porządek mogą ograniczyć potrzebę sortowania, choć korzyść zależy od konkretnego planu oraz od tego, czy dane są już dostępne w wymaganej kolejności. O wyborze algorytmu decyduje silnik bazy danych, a nie sama deklaracja indeksu.

Projektowanie indeksów dla zapytań łączących

Indeks powinien obejmować kolumny rzeczywiście używane w warunkach łączenia i filtrowania. Przy indeksie złożonym kolejność kolumn ma znaczenie, ponieważ wiele silników efektywnie wykorzystuje przede wszystkim początkowy fragment klucza indeksu. Dla zapytania filtrującego po status i łączącego po klient_id indeks złożony może być zaprojektowany inaczej niż dla zapytania najpierw ograniczającego klient_id, a dopiero potem sprawdzającego status. Nie ma jednej kolejności poprawnej dla wszystkich zapytań; należy ją oceniać na podstawie predykatów, rozkładu wartości i planów wykonania.

Indeks może zawierać dodatkowe kolumny potrzebne do odczytu wyniku, dzięki czemu silnik ograniczy liczbę odwołań do tabeli bazowej. Taki efekt bywa określany jako indeks pokrywający, ale sposób deklarowania kolumn dodatkowych zależy od używanego silnika. Korzyści trzeba zestawić z kosztami: każdy indeks zwiększa zajmowane miejsce, wydłuża operacje INSERT, UPDATE i DELETE oraz wymaga utrzymywania aktualnej struktury. Nie należy tworzyć osobnego indeksu dla każdej kolumny występującej w zapytaniach bez sprawdzenia rzeczywistych planów i obciążenia.

Diagnostyka wydajności JOIN

Analizę należy rozpocząć od planu wykonania konkretnego zapytania. Narzędzie dostępne w danym silniku może prezentować plan szacowany albo plan z rzeczywistymi czasami, liczbą odczytanych wierszy i informacją o użytych indeksach. Warto porównać wartości szacowane z rzeczywistymi, ponieważ duża rozbieżność często wskazuje na nieaktualne statystyki, błędne założenia dotyczące rozkładu danych albo nietypową selektywność warunków. Sam czas całego zapytania nie wystarcza do wskazania przyczyny; trzeba sprawdzić operator o największym koszcie, liczbę przetwarzanych wierszy i operacje sortowania lub skanowania.

Indeks może nie zostać wykorzystany, gdy kolumna łączenia jest opakowana w funkcję, porównywana po niezgodnych typach danych albo poddawana niejawnemu rzutowaniu. Problematyczne bywają także warunki zawierające obliczenia po stronie kolumny oraz porównania, które nie pozwalają na bezpośrednie użycie uporządkowanego klucza. Po zmianie indeksu lub zapytania należy ponownie sprawdzić plan na danych o reprezentatywnym rozmiarze. W przypadku diagnostyki laptopa, komputera PC lub stacji roboczej istotne są również zasoby środowiska: pamięć operacyjna, szybkość nośnika, obciążenie procesora i równoległe procesy mogą zmieniać czas wykonania bez zmiany samego zapytania. Warszawskie Pogotowie Komputerowe zajmuje się profesjonalną naprawą laptopów, komputerów PC i podzespołów, w tym diagnostyką problemów sprzętowych wpływających na pracę oprogramowania.