Indeks FOREIGN KEY

Indeks FOREIGN KEY to indeks automatycznie lub jawnie utworzony na kolumnie objętej ograniczeniem klucza obcego.

Rola indeksu przy ograniczeniu FOREIGN KEY

Indeks FOREIGN KEY jest strukturą danych powiązaną z kolumną albo zestawem kolumn, które uczestniczą w ograniczeniu klucza obcego. Samo ograniczenie FOREIGN KEY opisuje zależność między tabelą podrzędną a tabelą nadrzędną: wartości zapisane w kolumnie podrzędnej muszą odpowiadać wartościom istniejącym w kolumnie wskazywanej w tabeli nadrzędnej, z uwzględnieniem reguł dotyczących wartości NULL i definicji konkretnego silnika bazy danych. Indeks nie zastępuje ograniczenia. Nie zapewnia integralności referencyjnej, jeżeli FOREIGN KEY nie został faktycznie zdefiniowany.

Indeks na kolumnie klucza obcego może jednak znacząco ułatwiać wyszukiwanie wierszy powiązanych z konkretną wartością klucza nadrzędnego. Jest to istotne podczas wykonywania złączeń tabel, wyszukiwania rekordów podrzędnych oraz obsługi operacji, które zmieniają albo usuwają rekord w tabeli nadrzędnej. Gdy silnik musi sprawdzić, czy istnieją zależne wiersze, indeks może ograniczyć liczbę odczytywanych rekordów. Dokładny sposób wykonywania kontroli zależy od implementacji danego systemu bazodanowego i wybranego planu zapytania.

Automatyczne i jawne tworzenie indeksu

Różne silniki baz danych odmiennie traktują indeksy związane z kluczami obcymi. W niektórych systemach indeks na kolumnach tabeli podrzędnej może zostać utworzony automatycznie, w innych administrator lub programista musi zdefiniować go osobnym poleceniem CREATE INDEX. Nie można więc zakładać, że samo dodanie FOREIGN KEY zawsze powoduje powstanie widocznego indeksu. Obecność ograniczenia oraz obecność indeksu należy sprawdzać niezależnie w metadanych konkretnej bazy.

W tabeli nadrzędnej kolumny wskazywane przez FOREIGN KEY muszą być objęte odpowiednim kluczem głównym albo ograniczeniem zapewniającym unikatowość, zgodnie z wymaganiami danego silnika. Taki klucz zwykle ma własny indeks, ale nie jest tym samym co indeks na kolumnie klucza obcego w tabeli podrzędnej. Pierwszy identyfikuje dopuszczalne wartości po stronie nadrzędnej, a drugi przyspiesza odnajdywanie rekordów zależnych. W dokumentacji i narzędziach administracyjnych warto rozróżnić nazwę ograniczenia FOREIGN KEY, nazwę klucza nadrzędnego oraz nazwę indeksu.

Indeks pojedynczy, złożony i kolejność kolumn

Jeżeli klucz obcy obejmuje jedną kolumnę, typowy indeks może zawierać właśnie tę kolumnę. Przy kluczu złożonym indeks powinien uwzględniać wszystkie kolumny uczestniczące w relacji, w kolejności dobranej do sposobu filtrowania i łączenia danych. Kolejność ma znaczenie dla możliwości wykorzystania indeksu przez optymalizator. Indeks złożony z kolumnami A i B może być użyteczny przy warunku rozpoczynającym się od A, lecz nie musi zapewniać takiej samej korzyści dla wyszukiwania obejmującego wyłącznie B.

Nie zawsze trzeba tworzyć osobny indeks dla każdego ograniczenia. Kilka ograniczeń lub zapytań może korzystać z istniejącej struktury, jeżeli jej kolumny i kolejność odpowiadają używanym predykatom. Z drugiej strony indeks zawierający nadmiarowe kolumny zwiększa rozmiar bazy, koszt zapisu oraz pracę potrzebną przy INSERT, UPDATE i DELETE. Przy projektowaniu należy więc zestawić definicję relacji z rzeczywistymi zapytaniami, selektywnością wartości i częstotliwością modyfikacji tabeli. Sama obecność FOREIGN KEY nie jest wystarczającym powodem do tworzenia dowolnej liczby indeksów.

Kontrola planu zapytań i spójności relacji

Ocena indeksu FOREIGN KEY powinna obejmować zarówno strukturę schematu, jak i sposób wykonania konkretnych operacji. Przydatne jest sprawdzenie planu zapytania dla złączenia tabel nadrzędnej i podrzędnej, wyszukiwania rekordów według klucza obcego oraz usuwania lub aktualizowania wartości po stronie nadrzędnej. Plan może wskazać skan całej tabeli, użycie indeksu, koszt sortowania albo dodatkowe filtrowanie. Brak użycia indeksu nie zawsze oznacza błąd: przy małej tabeli skan może być tańszy niż dostęp indeksowy, a przy mało selektywnych wartościach optymalizator może wybrać inną metodę.

Podczas diagnostyki należy również sprawdzić, czy dane nie zawierają osieroconych rekordów, czy typy i długości powiązanych kolumn są zgodne oraz czy indeks nie został usunięty po zmianie schematu. W przypadku klucza złożonego trzeba zweryfikować kompletność i kolejność kolumn po obu stronach relacji. Należy odróżnić problem wydajnościowy od problemu integralności: wolne wyszukiwanie może wynikać z braku indeksu, natomiast odrzucenie zapisu z powodu nieistniejącej wartości nadrzędnej jest działaniem mechanizmu FOREIGN KEY. Prawidłowa analiza wymaga odczytu definicji tabel, ograniczeń, indeksów i planu wykonania w używanym systemie baz danych.