Co to jest „Indeks częściowego dopasowania”?

Próbuję dowiedzieć się więcej na temat operatora planu zapytań „sprawdzanie referencji klucza obcego” wprowadzonego w SQL Server 2016. Nie ma na ten temat zbyt wielu informacji. Microsoft ogłosił to tutaj, a ja blogowałem o tym tutaj . Nowego operatora można zobaczyć, usuwając wiersz z tabeli nadrzędnej z 254 lub więcej przychodzącymi odwołaniami do kluczy obcych: dbfiddle link .

Istnieją trzy różne liczby wyświetlane w szczegółach operatora:

• Liczba odwołań do kluczy obcych to liczba przychodzących kluczy obcych.
• Brak pasującej liczby indeksów to liczba przychodzących kluczy obcych bez odpowiedniego indeksu. Sprawdzenie, czy zaktualizowana lub usunięta tabela nie naruszy tego ograniczenia, będzie wymagać skanowania tabeli podrzędnej.
• Nie wiem, co reprezentuje indeks częściowego dopasowania .

Co to jest indeks częściowego dopasowania w tym kontekście? Nie byłem w stanie uzyskać żadnego z poniższych:

• Filtrowane indeksy
• Umieszczenie kolumny klucza obcego jako INCLUDEkolumny dla indeksu
• Indeksuje z kolumną klucza obcego jako drugą kolumną klucza
• Indeksy jednokolumnowe dla kluczy obcych o wielu kolumnach
• Tworzenie wielu indeksów obejmujących, aby umożliwić plan „przyłączenia indeksu” dla klucza obcego z wieloma kolumnami

Dan Guzman wskazał, że klucze obce wielu kolumn mogą pasować do indeksów, nawet jeśli klucze indeksu są w innej kolejności niż kolumny kluczy obcych. Jego kod jest tutaj, na wypadek, gdyby ktoś mógł go użyć jako punktu wyjścia, aby dowiedzieć się więcej na temat indeksów częściowego dopasowania.

Rozmawiałem z ludźmi o wiele mądrzejszymi ode mnie i wkrótce to udokumentujemy ™.

Tymczasem rzeczywista definicja tego:

PartialMatchingIndexCount odzwierciedla liczbę referencji, które można sprawdzić za pomocą wyszukiwania indeksów, ale klucz indeksu nie obejmuje wszystkich sprawdzanych kolumn. Na przykład odpowiednie elementy ForeignKeyReferenceCheck zawierają zarówno element Seek Predicates, jak i element Predicate.

Dodatkowo:

Jeśli ta liczba jest większa niż 0, istnieje potencjalny problem z wydajnością, w przypadku, gdy częściowe dopasowania skutkują dużą liczbą wierszy.

Po kilku kolejnych poszukiwaniach w Google udało mi się wymyślić post, w którym wspomniano o „indeksach częściowego dopasowania” i kluczach obcych

W blogu z 1 marca 2013 r. W blogu Code Carlosa Klappa zatytułowanymForeign Keys without Indexes znajduje się procedura przechowywana, Util_FKsWithoutIndexesktóra wyszukuje klucze obce, które nie mają odpowiednich indeksów dla relacji FK. (Wygląda na to, że ten blogger podniósł to z SQL Server Central The Ultimate Index-Less Foreign-Key Finder(15 października 2009) ). Blog mówi:

Wyszukuje ograniczenia klucza obcego, które nie mają w pełni pasujących indeksów.

Najlepsze indeksy częściowego dopasowania są generowane za pomocą MatchCounts i porównań kolumn

Generuje szablon UTWÓRZ INDEKS dla każdego klucza obcego bez indeksu pasującego lub indeksu częściowego dopasowania.

Dostosuj w razie potrzeby (dodaj obejmuje, jeśli chcesz, aby był klastrowany, powinien być częścią klucza podstawowego lub jeśli chcesz scalić z innym indeksem)

FK brakujące indeksy pełnego dopasowania mogą poważnie zaszkodzić wydajności DELETES w tabeli, do której istnieje odwołanie, ze względu na skanowanie tabeli w celu sprawdzenia integralności referencyjnej, a także SELECTS w tabelach odwołań, w których kolumny klucza obcego znajdują się w predykatach WHERE lub JOIN (Wpłynie to na to, czy ograniczenie istnieje, czy nie). Sprawdza to tylko pierwsze N ​​kolumn indeksu, gdzie N jest liczbą kolumn w ograniczeniu klucza obcego.

Kolejność kolumn indeksu nie jest poza tym weryfikowana (FK dwukolumnowy, który ma 1 pasującą kolumnę w drugiej kolumnie indeksu 3-kolumnowego, zostanie wyprowadzony jako częściowe dopasowanie)

Jeśli twoja baza danych nie ma ograniczeń klucza obcego, to narzędzie będzie dla ciebie bezwartościowe.

Wiele baz danych ma częściowe pokrycie ograniczeniami klucza obcego. Działa to tylko w powiązanych tabelach, w których deklarowane są ograniczenia.

Jeśli dobrze to rozumiem, jeśli relacja FK nie ma indeksu pasującego do każdej kolumny w jakimś potrzebnym indeksie, mogą istnieć pewne indeksy, które mają niektóre kolumny. Na przykład, jeśli związek FK ma trzy kolumny ( a, b, c) ale nie ma indeksu z tych samych trzech kolumnach, może istnieć indeks, który ma ( a, b) lub ( a, c) lub ( b, c) i może pomóc z zapytaniami, ale będzie wymagają niektórych skanowań indeksu do wierszy z brakującą kolumną.

Jeśli w ogóle nie ma indeksów, które mogłyby obsługiwać ograniczenie FK, wówczas „Licznik indeksów częściowego dopasowania” wyniesie zero ( 0) lub przynajmniej nie zwiększy tej liczby.