Jak znaleźć niepoindeksowane kolumny klucza obcego
Czy kolumna klucza obcego powinna być indeksowana?
Jakis czas temu zamieściłem wpis na temat indeksowania kolumn klucza obcego i starałem się odpowiedzieć na pytanie, kiedy taki indeks moze się przydać i kiedy jest całkowicie bezużyteczny. Wpis można zobaczyć tutaj: Indeks na kluczu obcym. W wielu miejscach można przeczytać, że powinno się je zakładać wręcz automatycznie. Moje zdanie jest inne, co próbowałem we wspomnianym wpisie pokazać. Pokazałem też, że taki indeks może być dobrym rozwiązaniem. W wielu przypadkach dostajemy gotową bazę danych i trudno jest analizować tabela po tabeli wszystkie nieindeksowane kolumny. W takich okolicznościach przyrody przydaje się skrypt. Jak pobrać podejrzane kolumny?
Klucze obce - indeksy = podejrzenie
Popatrzmy na pokazany poniżej skrypt i przeanalizujmy go:
sys.tables T
JOIN sys.schemas S ON t.[schema_id]=S.[schema_id]
JOIN sys.foreign_keys FK ON T.[object_id]=FK.parent_object_id
JOIN sys.foreign_key_columns FKC ON FK.[object_id] = FKC.constraint_object_id
JOIN sys.columns C ON FKC.parent_object_id=C.[object_id] AND FKC.parent_column_id=C.column_id
WHERE FKC.constraint_column_id=1
EXCEPT
SELECT S.name+'.'+T.name+'.'+C.name FirstIndexColumn FROM
sys.tables T
JOIN sys.schemas S ON t.[schema_id]=S.[schema_id]
JOIN sys.indexes I ON I.[object_id]=T.[object_id]
JOIN sys.index_columns IC ON I.index_id=IC.index_id AND I.[object_id]=IC.[object_id]
JOIN sys.columns C ON I.[object_id]=C.[object_id] AND C.column_id=IC.column_id
WHERE IC.index_column_id=1
Operacja przebiega dwuetapowo. Najpierw wyszukiwane są wszystkie pierwszeKlucz obcy może się składać z kilku kolumn, w takich przypadkach brana jest tylko jedna, pierwsza kolumna. kolumny kluczy obcych w całej bazie danych. Sama nazwa kolumny nie jest unikatowa i może nas zmylić, dlatego do wartości identyfikującej kolumnę dołączana jest nazwa schematu i nazwa tabeli. To etap pierwszy - otrzymujemy zbiór 1.
Drugi etap to wyszukanie wszystkich indeksów, a w zasadzie wszystkich pierwszych kolumn indeksu (patrz uwaga do pierwszego etapu). Znów do nazwy kolumny z indeksu dołączana jest nazwa schematu i nazwa tabeli. Otrzymujemy zbiór 2.
Teraz od wartości w zbiorze 1 wystarczy tylko odjąć wartości ze zbioru 2. Ta prosta różnica zbiorów wskaże nam te kolumny, które są kluczem obcym, ale nie są kolumnami indeksu. To są te podejrzane kolumny, które warto przeanalizować w pierwszej kolejności.
Klucz obcy na dwóch kolumnach
Wspomniałem, że klucz obcy może być założony na wielu kolumnach, najczęsciej dwóch, sporadycznie trzech, rzadko czterech i więcej. Ograniczeń nie ma. Pokazane rozwiązanie jest trochę ułomne, bo zakłada, że pierwsza kolumna klucza i pierwsza kolumna indeksu jest decydująca. To w pewnym sensie prawda, bo to właśnie pierwsza kolumna indeksu najbardziej decyduje o jego układzie. Każda kolejna brana jest pod uwagę tylko wtedy, gdy pierwsza jest równa. W wielu przypadkach taki prosty skrypt jest wystarczający, ale nie jest doskonały. Jednym z rozwiązań jest pobranie wszystkich kolumn klucza obcego i skumulowanie ich w jednej wartości (konkatenacja). Podobnie można zrobić z kolumnami indeksu. Jak wykonać taką konkatenację pokazałem między innymi tutaj: GROUP_CONCAT w SQL Server. Można to oczywiście zrobić w jednym zapytaniu, ale jego czytelność pozostawia wiele do życzenia.
Mam nadzieję, że pokazany skrypt komuś się przyda. Uwagi można zgłaszać w komentarzach.
Kategoria:IndeksyOptymalizacja SQLSQL Server
Brak komentarzy - bądź pierwszy