Indeks na kluczu obcym
Czy klucz obcy zakłada indeks?
Istnieje przekonanie, że założenie klucza obcego zakłada również indeks. Przekonanie, powiem to bez chwili zawahania, niesłuszne. Klucz obcy nie zakłada indeksu. Skąd zatem to przekonanie? Wydaje mi się, że przez analogię do klucza głównego. Klucz główny zakłada indeks, więc wydawać by się mogło, że obcy też założy. A obcy zakładać nie chce. Więcej, zakładając więzy unikalności również niejawnie tworzymy indeks. Dlaczego zatem nie tworzy się on przy kluczu obcym i kiedy jest on potrzebny? Dlaczego nie jest tworzony domyślnie? Przede wszystkim dlatego, że nie zawsze jest potrzebny. A skoro nie jest potrzebny, bazy danych nie tworzą go. Tak jest nie tylko w SQL Server, ale także w Oracle i wielu innych popularnych bazach danych. Zapamiętajmy: klucz obcy nie tworzy indeksu. Kiedy taki indeks może się przydać, przekonamy się w kolejnych podpunktach.
Relacja jeden do wielu
Relacje między tabelami mogą być różne. Przyjrzyjmy się jednej z możliwości: dany jest rekord nadrzędny, a szukamy wszystkich jego dzieci. Aby łatwiej zrozumieć zależność, posłużę się realnymi tabelami: Konta (tabela nadrzędna) oraz Wpłaty (tabela podrzędna, przypisana do konkretnego konta). Tabele zostaną uzupełnione tak, aby kont było 10000, a na każdym koncie po 3 wpłaty. Popatrzmy na poniższy listing:
(
Id int IDENTITY,
Numer varchar(20) NOT NULL,
CONSTRAINT PK_Oplaty_Id
PRIMARY KEY CLUSTERED (Id),
CONSTRAINT UQ_Konta_Numer
UNIQUE (Numer)
)
CREATE TABLE Wplaty
(
Id int IDENTITY,
IdKonta int NOT NULL,
Kwota decimal(9,2) NOT NULL,
Tytul nvarchar(50) NOT NULL,
CONSTRAINT PK_Wplaty_Id
PRIMARY KEY (Id),
CONSTRAINT FK_Wplaty_IdKonta
FOREIGN KEY (IdKonta)
REFERENCES Konta(Id)
)
SET NOCOUNT ON
BEGIN TRANSACTION
DECLARE @i int=0;
DECLARE @parentID int;
WHILE @i<10000
BEGIN
INSERT INTO Konta
VALUES ('1111-1111-1111-'+CAST(@i as varchar(4)));
SET @parentID = @@IDENTITY;
INSERT INTO Wplaty
VALUES (@parentID, 100.0, 'Wplata 1'),
(@parentID, 100.0, 'Wplata 2'),
(@parentID, 100.0, 'Wplata 3');
SET @i += 1;
END
COMMIT
Spróbujmy wykonać teraz jedną z podstawowych operacji - wyświetlmy informacje o wszystkich wpłatach na wskazane numerem konto:
JOIN Wplaty W ON K.Id=W.IdKonta
WHERE K.Numer='1111-1111-1111-3333'
Pomyślmy, jak w takim przypadku może być zrealizowana cała operacja. Najpierw trzeba znaleźć konto (bardzo łatwo, po kluczu unikalnym), a następnie dopasować do niego wszystkie wpłaty, które mają klucz obcy ustawiony na identyfikator konta. Jak ta druga część operacji będzie przebiegała? Zeskanowana będzie cała tabela wpłat, bo nie ma żadnej lepszej możliwości wskazania właściwych rekordów.
Rys. 1. Brak indeksu na kluczu obcym w relacji jeden do wielu.
Wiadomo, że operacje pełnego skanowania tabeli nie są dobre (chyba, że tabela jest mała lub operacja wykonywana jest sporadycznie). Tutaj przydaje się indeks. Spróbujmy założyć prosty indeks na kluczu obcym:
Operacja zwróci te same wyniki, ale zrealizowana zostanie nieco inaczej. Tak jak wcześniej, wyszukany zostanie rekord konta, a następnie, dzięki indeksowi IX_Wplaty_IdKonta, namierzone zostaną trzy rekordy wpłat. Namierzone w sensie odnalezienia klucza klastrowanego, który doczepiany jest do najniższego poziomu wszystkich indeksów nieklastrowanych. Za pomocą tego klucza klastrowanego wyszukane zostana pozostałe atrybuty (kolumny) rekordów wpłat.
Rys. 2. Indeks na kluczu obcym w relacji jeden do wielu.
Operacja doszukiwania nie jest oczywiście darmowa, ale o niebo lepsza niż przeszukiwanie kilkudziesięciu rekordów wpłat. Przyjrzyjmy się statystykom zapytań przed założeniem indeksu i po:
(3 row(s) affected)
Table 'Wplaty'. Scan count 1, logical reads 159, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Konta'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Po założeniu indeksu
(3 row(s) affected)
Table 'Wplaty'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Konta'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
W tym przypadku różnica jest ogromna: 159 odczytów logicznych wobec 8 odczytów logicznych. Różnica jest dwudziestokrotna. Czy zatem zawsze należy takie indeksy zakładać? Wszystko zależy od selektywności danego indeksu. Zauważmy, że spośród 30000 rekordów tylko 3 są właściwe. Jeden na 10000. Dlaczego różnica w odczytach jest tak mała? To właśnie przez selektywność. Gdy indeks wskaże zbyt dużo rekordów spełniających predykat złączenia, wtedy operacja doszukiwania przy pomocy klucza klastrowanego moze być bardziej kosztowana niż pełne skanowanie tabeli.
Popatrzmy zatem na jeszcze jeden przykład, o znacznie niższej selektywności:
DELETE FROM Konta
SET NOCOUNT ON
BEGIN TRANSACTION
DECLARE @i int=0;
DECLARE @parentID int;
WHILE @i<100
BEGIN
INSERT INTO Konta
VALUES ('1111-1111-1111-'+CAST(@i as varchar(4)));
SET @parentID = @@IDENTITY;
DECLARE @j int=0;
WHILE @j<100
BEGIN
INSERT INTO Wplaty
VALUES (@parentID, 100.0, 'Wplata 1'),
(@parentID, 100.0, 'Wplata 2'),
(@parentID, 100.0, 'Wplata 3');
SET @j += 1;
END
SET @i += 1;
END
COMMIT
SELECT * FROM Konta K
JOIN Wplaty W ON K.Id=W.IdKonta
WHERE K.Numer='1111-1111-1111-33'
Tym razem SQL Server, nawet w przypadku obecności indeksu, wybierze pełne skanowanie tabeli! A skoro wybiera pełne skanowanie tabeli - indeks nie jest potrzebny! Ot i cała selektywność. Pewnym rozwiązaniem jest użycie indeksu pokrywającego, to jest takiego, który zawiera wszystkie kolumny wymagane przez instrukcję SELECT. W tym przypadku są to wszystkie kolumny, ale najczęściej potrzeby są mniejsze. Indeks pokrywający na wszystkich kolumnach jest równoważny z utworzeniem kopii głównej tabeli z indeksem klastrowanym ale... poukładany nieco inaczej. Indeks pokrywający sprawia, że nie jest konieczne doczytywanie rekordów, a więc jednokrotne przejście przez indeks klastrowany dla każdego pasującego rekordu.
Relacja wiele do jednego
Przekonaliśmy się, że w relacji jeden do wielu indeks na kluczu obcym może się przydać. Tym razem relacja będzie odwrotna. Mając dany rekord (rekordy), próbujemy odszukać wartość przezeń wskazywaną. Przykładem takiej relacji jest związek pracownika z zajmowanym stanowiskiem (zakładamy, że pracownik może zajmować tylko jedno stanowisko). Popatrzmy na przykładowe tabele przechowujące pracowników i ich stanowiska uzupełnione przykładowymi danymi:
(
ID int IDENTITY,
Name nvarchar(20),
CONSTRAINT PK_Positions PRIMARY KEY CLUSTERED (ID)
)
CREATE TABLE Employee
(
ID int IDENTITY,
FirstName nvarchar(20) NOT NULL,
LastName nvarchar(20) NOT NULL,
Position int NOT NULL,
CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED (ID),
CONSTRAINT FK_Employee_Position
FOREIGN KEY (Position) REFERENCES Position(ID)
)
INSERT INTO Position VALUES
(N'Programmer'),
(N'Tester'),
(N'Architect'),
(N'Driver'),
(N'Janitor'),
(N'Cook'),
(N'Salesman'),
(N'Butcher');
TRUNCATE TABLE Employee
SET NOCOUNT ON
DECLARE @i int=0;
WHILE @i<100
BEGIN
INSERT INTO Employee VALUES
(N'FirstName'+CAST(@i as nvarchar(4)),
N'LastName'+CAST(@i as nvarchar(4)),
@i%8+1);
SET @i += 1;
END
Przy takiej konstrukcji tabel najczęściej będą się pojawiały następujące zapyania:
--Pobierz stanowisko wskazanego pracownika
SELECT E.FirstName, E.LastName, P.Name FROM Employee E
JOIN Position P ON E.Position=P.ID
WHERE E.ID=10
--Znajdź stanowisko pracownika znając jego nazwisko
SELECT E.FirstName, E.LastName, P.Name FROM Employee E
JOIN Position P ON E.Position=P.ID
WHERE E.LastName LIKE 'FirstName10'
Dla takich zapytań zakładanie indeksu na kluczu obcym jest całkowicie niepotrzebne! Złączenie wychodzi z tabeli Employee, gdzie znajduje się klucz obcy, razem z pozostałymi kolumnami rekordy pracownika, a samo dopasowniae stanowiska będzie zawsze korzystało z klucza głównego tabeli Position. Kiedy indeks na kluczu obcym może się przydać? Wtedy, gdy w warunku WHERE znajduje się wartość tego klucza obcego lub optymalizator uzna, że złączenie zrealizowane zostanie od strony stanowiska. Popatrzmy na poniższe zapytanie:
SELECT E.FirstName, E.LastName, P.Name FROM Employee E
JOIN Position P ON E.Position=P.ID
WHERE P.Name LIKE 'Janitor'
Okazuje się, że SQL Server nawet w tym przypadku przeprowadzi złączenie od strony tabeli Employee wykonując jej pełne skanowanie. Dlaczego tak się dzieje? Znów problem tkwi w selektywności. Więcej czasu zajęłoby doczytywanie pozostałych kolumn z pierwszej tabeli niż pełne przeszukanie wszystkich wartości. Okazuje się, że przy tak skonstruowanej relacji rzadkością jest konieczność założenia indeksu na kluczu obcym. Założenie indeksu jest wręcz szkodliwe, bo, co jasne, SQL Server musi go cały czas utrzymywać. Nie dzieje się to bez wpływu na wydajność. Reasumując, klucz można założyć gdy przynajmniej te dwa warunki są spełnione:
- Bardziej selektywny warunek WHERE nałożony jest od strony "jednego" w relacji wiele do jednego.
- Wartości w tabeli "wiele" w relacji wiele do jednego są mocno zróżnicowane.
Im bliższe unikalności są wartości klucza obcego w tabeli "wiele", tym lepiej. Ideałem jest relacja jeden do jednego, jako szczególny przypadek relacji wiele do jednego.
Wpływ indeksu na inne operacje wykonywane
Czy indeks ma wpływ tylko na operacje SELECT? Oczywiście, że nie! SQL Server, podobnie zresztą jak i inne silniki relacyjnych baz danych, korzystają z indeksów przy operacjach UPDATE, DELETE i MERGE. Gdy usuwamy rekord, SQL Server musi sprawdzić, czy takie usunięcie nie "osierocić" rekordu. Gdybyśmy w pierwszym przypadku zechcieli usunąć konto, SQL mógłby skorzystać z indeksu i spradwdzić, czy klucz obcy, a w zasadzie jego wartość nie jest wykorzystywana. Rozważmy dwa przypadki:
- Brak indeksu na kolumnie IdKonta. Przy próbie usunięcia konta SQL Server musi zbadać, czy żaden z rekordów wpłaty nie jest w relacji z kontem. Przy odrobinie szczęścia może to być jeden rekord, który znajduje się na początku skanowanego obszaru. Co jednak wtedy, gdy konto nie ma wpłat? Nie da się tego stwierdzić bez przeszukania wszystkich rekordów.
- Indeks na kolumnie IdKonta jest założony. Przy próbie usunięcia konta SQL Server sięga do indeksu i błyskawicznie stwierdza, czy wartość klucza obcego jest użyta, czy nie.
Podobne rozważania można przeprowadzić przy innych operacjach, tj. UPDATE i MERGE. Warto przy okazji wspomnieć, że operacje modyfikujące dane są traktowane przez użytkowników końcowych po macoszemu. Użytkownik najczęściej jest w stanie zaakceptować dłuższe oczekiwanie podczas usuwania czy zapisywania danych. Takie samo oczekiwanie przy przeglądaniu i wyświetlaniu danych może już go irytować.
Warto jeszcze wspomnieć o operacji kaskadowego usuwania (ON DELETE CASCADE) i modyfikowania rekordów (ON UPDATE CASCADE). Znów, tak jak przy zwykłym usuwaniu i aktualizacji, SQL Server może z takich indeksów skorzystać.
Wnioski
Kolumny klucza obcego nie powinny być indeksowane automatycznie. Gdyby takie indeksowanie było konieczne, bazy danych robiłyby to bez pytania i nikt nie fatygowałby się, aby analizować konieczność lub bezcelowość tworzenia indeksów na kolumnach kluczy obcych. Jak to zwykle bywa (coś za często to powtarzam), nie ma żelaznych reguł. Wszystko zależy od tego, jakie zapytania będziemy wykonywać (co znajdzie się w warunku WHERE), jaka byłaby selektywność indeksu na kolumnie klucza obcego (to dotyczy wszystkich indeksów) oraz jakie inne operacje mogą odnieść korzyść z takiego indeksu. To wszystko wymaga zastanowienia się i nikt człowieka tutaj nie zastąpi. Indeksów nie powinno się zakładać automatycznie, ale w wielu przypadkach jest to bardzo dobre rozwiązanie, co również starałem się pokazać. Warto zajrzeć do jeszcze innego wpisu, w którym pokazuję, jak znaleźć podejrzane kolumny klucza obcego: Jak znaleźć niepoindeksowane kolumny klucza obcego. Zachęcam jednocześnie do dzielenia się pytaniami i spostrzeżeniami w komentarzach.
Kategoria:IndeksyOptymalizacja SQLSQL Server
Brak komentarzy - bądź pierwszy