Czy potrzebuję osobnych indeksów dla każdego typu zapytania, czy też może działać jeden indeks wielokolumnowy?

Wiem już nieco odpowiedź na to pytanie, ale zawsze mam wrażenie, że muszę jeszcze coś więcej na ten temat.

Zasadniczo rozumiem, że ogólnie rzecz biorąc, pojedynczy indeks, który zawiera wszystkie pola, o które możesz pytać / sortować w danym momencie, raczej nie będzie użyteczny, ale widziałem tego typu rzeczy. Na przykład ktoś pomyślał: „Cóż, jeśli po prostu umieścimy wszystkie te rzeczy w indeksie, baza danych może go użyć, aby znaleźć to, czego potrzebuje”, nie widząc nigdy planu wykonania niektórych z uruchomionych zapytań.

Wyobraź sobie taki stół:

id int pk/uid
name varchar(50)
customerId int (foreign key)
dateCreated datetime

Mogę zobaczyć jeden indeks na tym name, customerIdi dateCreatedpolami.

Ale rozumiem, że taki indeks nie byłby używany w zapytaniu, takim jak na przykład:

SELECT [id], [name], [customerId], [dateCreated]
   FROM Representatives WHERE customerId=1 
   ORDER BY dateCreated

W przypadku takiego zapytania wydaje mi się, że lepszym pomysłem byłby indeks zawierający pola customerIdi dateCreated, przy czym customerIdpole byłoby „pierwsze”. Spowodowałoby to utworzenie indeksu, w którym dane byłyby zorganizowane w taki sposób, że zapytanie to mogłoby szybko znaleźć to, czego potrzebuje - w kolejności, w jakiej potrzebuje.

Inną rzeczą, którą widzę, być może tak często jak pierwszą, są indywidualne indeksy na każdym polu; tak, po jednym na name, customerIdi dateCreatedpola.

W przeciwieństwie do pierwszego przykładu, tego rodzaju układ wydaje mi się czasem przynajmniej częściowo przydatny; plan wykonania zapytania może pokazywać, że przynajmniej używa indeksu customerIddo wyboru rekordów, ale nie używa indeksu z dateCreatedpolem do ich sortowania.

Wiem, że jest to szerokie pytanie, ponieważ konkretną odpowiedzią na każde zapytanie w dowolnym zestawie tabel jest zazwyczaj sprawdzenie, co plan wykonania powie, że zrobi, a w przeciwnym razie uwzględnienie specyfiki tabel i zapytań w konto. Wiem także, że zależy to od częstotliwości uruchamiania zapytania, a nie narzutu związanego z utrzymywaniem dla niego określonego indeksu.

Ale przypuszczam, że to, o co pytam, jest ogólnym „punktem wyjścia” dla indeksów, czy pomysł posiadania określonych indeksów dla określonych, często pobieranych zapytań i pól w klauzulach WHERE lub ORDER BY ma sens?

Masz rację, że twoje przykładowe zapytanie nie użyłoby tego indeksu.

Planista zapytań rozważy użycie indeksu, jeśli:

• wszystkie zawarte w nim pola są przywoływane w zapytaniu
• niektóre pola zaczynają się od początku

Nie będzie można korzystać z indeksów rozpoczynających się od pola nieużywanego przez zapytanie.

Na przykład:

SELECT [id], [name], [customerId], [dateCreated]
   FROM Representatives WHERE customerId=1 
   ORDER BY dateCreated

uwzględniałby indeksy takie jak:

[customerId]
[customerId], [dateCreated]
[customerId], [dateCreated], [name]

ale nie:

[name], [customerId], [dateCreated]

Jeśli znajdzie jedno [customerId]i drugie, [customerId], [dateCreated], [name]decyzja o preferowaniu jednej z nich będzie zależeć od statystyk indeksu, które zależą od oszacowania bilansu danych w polach. Jeśli [customerId], [dateCreated]zostały zdefiniowane, powinno to być lepsze niż pozostałe dwa, chyba że podasz konkretną wskazówkę dotyczącą indeksu przeciwnego.

Z mojego doświadczenia wynika również, że jeden indeks jest zdefiniowany dla każdego pola, choć rzadko jest to optymalne, ponieważ dodatkowe zarządzanie potrzebne do aktualizacji indeksów podczas wstawiania / aktualizacji oraz dodatkowe miejsce potrzebne do ich przechowywania są marnowane, gdy połowa mogą się nigdy nie przyzwyczaić - ale jeśli twoja baza danych nie zauważy dużych obciążeń zapisu, wydajność nie będzie brzydko śmierdzieć nawet przy nadmiarze indeksów.

Konkretne indeksy dla częstych zapytań, które w innym przypadku byłyby powolne ze względu na skanowanie tabel lub indeksów, są na ogół dobrym pomysłem, ale nie przesadzaj, ponieważ możesz wymieniać jeden problem z wydajnością na inny. Jeśli [customerId], [dateCreated]na przykład zdefiniujesz jako indeks, pamiętaj, że narzędzie do planowania zapytań będzie mogło go użyć w przypadku zapytań, które wykorzystywałyby indeks tylko [customerId]wtedy, gdy jest obecny. Chociaż użycie po prostu [customerId]byłoby nieco bardziej wydajne niż użycie indeksu złożonego, można to złagodzić, uzyskując dwa indeksy konkurujące o miejsce w pamięci RAM zamiast jednego (chociaż jeśli cały normalny zestaw roboczy łatwo mieści się w pamięci RAM, ta dodatkowa konkurencja pamięci może nie być problem).

Aby odpowiedzieć na twoje pierwotne pytanie, tak, indeksy muszą być zaprojektowane wokół zapytań , a nie tylko tabeli . Kolejność pól w indeksie jest niezwykle ważna. Zaprojektowanie jednego indeksu jako optymalnego dla wielu zapytań jest trudniejsze i będziesz musiał dokonać kompromisów.

Jeśli chodzi o twój drugi punkt, tak, kilka indeksów na pojedynczych polach jest denerwująco powszechne. Cały czas widzę to w moim środowisku i zwykle jest to dla mnie czerwona flaga, że ​​zespół programistów nie współpracował z DBA przy projektowaniu odpowiednich indeksów.

Moja strategia projektowania indeksów polega na indeksowaniu:

• Pola używane w GDZIE (w kolejności selektywności)
• Pola używane w ORDER BY
• Dołącz inne pola (jeśli to konieczne), aby utworzyć indeks obejmujący

Na przykład:

SELECT [id], [name], [customerId], [dateCreated]
   FROM Representatives WHERE customerId=1 
   ORDER BY dateCreated

Prawdopodobnie zaprojektowałbym indeks na (CustomerID, dateCreated) INCLUDE (identyfikator, nazwa). Ten indeks obejmuje, że zapytanie nigdy nie musi trafić do oryginalnej tabeli, co znacznie poprawia wydajność.

Ten przykład jest jednak prawie zbyt prosty. Naiwny indeks just (CustomerID) działałby prawie równie dobrze (zakładając, że każdy klient ma tylko jedno powtórzenie, więc wymagane będzie tylko jedno wyszukiwanie zakładek do tabeli). Korzystne może być nawet wykonanie indeksu klastrowego (CustomerID, ID), w zależności od tego, jakie inne zapytania działają na tabeli.