Kolejność pól w złożonej kolejności indeksu z polami o wysokiej selektywności i niskiej selektywności

Mam tabelę SQL Server z ponad 3 miliardami wierszy. Jedno z moich zapytań zajmuje bardzo dużo czasu, dlatego rozważam jego optymalizację. Zapytanie wygląda następująco:

SELECT [Enroll_Date]
      ,Count(*) AS [Record #]
      ,Count(Distinct UserID) AS [User #]
  FROM UserTable
  GROUP BY [Enroll_Date]

[Data rejestracji] to kolumna o niskiej selektywności z mniej niż 50 możliwymi wartościami, a kolumna UserID to kolumna o wysokiej selektywności z ponad 200 milionami różnych wartości. Na podstawie moich badań uważam, że powinienem utworzyć nieklastrowany indeks kompozytowy na tych dwóch kolumnach i teoretycznie kolumna o wysokiej selektywności powinna być pierwszą kolumną. Ale nie jestem pewien w moim przypadku, czy to zadziałałoby, ponieważ używam kolumny o niskiej selektywności w grupie według klauzuli.

Ta tabela nie ma indeksu klastrowego.

Jako alternatywę dla rozwiązania @ AaronBertrand (jeśli nie możesz lub nie chcesz utworzyć widoku indeksowanego), polecam utworzenie indeksu na (Enroll_Date, UserID). Jeśli tego typu pytania są bardzo częste w tabeli, prawdopodobnie powinien to być nawet indeks klastrowany.

Zasadniczo nie zalecałbym indeksów o wysokiej selektywności jako ogólnej „najlepszej praktyki”, ale raczej przyjrzyjmy się, który indeks zapewni twojemu zapytaniu najlepszą wydajność.

Włączony indeks (Enroll_Date, UserID)zapewni kwerendy wysoce zoptymalizowany, nieblokujący plan zapytań dzięki Stream Aggregates.

„Nieblokowanie” w tym kontekście oznacza, że ​​zapytanie nie musi buforować żadnych znaczących ilości danych (jak na przykład sortowanie lub agregacja skrótów), co oznacza, że ​​(a) natychmiast zwraca wiersze i ( b) praktycznie nie zużywa pamięci roboczej.

Odpowiedź Aaronsa to świetne rozwiązanie. Odpowiem na pytanie, zakładając, że nie chcesz przyjąć takiego podejścia.

Kwerenda, którą opublikowałeś, zwykle będzie wykonywana najpierw przez grupowanie (Enroll_Date, UserID), a następnie ponowne (Enroll_Date). Ta optymalizacja jest nowością w SQL Server 2012. Ma zastosowanie w przypadku pojedynczego COUNT DISTINCT.

Indeks tych dwóch kolumn w określonej kolejności (Enroll_Date, UserID)wystarczy, aby uzyskać skuteczny plan, który łączy skanowanie indeksu do dwóch kolejnych agregatów strumienia. Odwrotna kolejność nie umożliwiłaby tego planu.

Dlatego skorzystaj z zamówienia (Enroll_Date, UserID). Nie masz tutaj wyboru.

Brzmi jak idealny scenariusz dla widoku indeksowanego, który pozwala płacić za obliczenia i agregacje w czasie zapisu zamiast w czasie zapytania.

CREATE VIEW dbo.MyIndexedView
WITH SCHEMABINDING
AS 
  SELECT Enroll_Date, UserID, RawCount = COUNT_BIG(*)
  FROM dbo.UserTable
  GROUP BY Enroll_Date, UserID;
GO

CREATE UNIQUE CLUSTERED INDEX CIX_miv ON dbo.MyIndexedView(Enroll_Date, UserID);

To zajmie trochę czasu, i oczywiście będzie wymagało konserwacji podczas wszystkich operacji DML, podobnie jak indeks w tabeli podstawowej.

Teraz zapytanie dotyczące tego widoku byłoby dość podobne - każdy wiersz w widoku reprezentuje teraz odrębną kombinację użytkownik / data, dzięki czemu liczbę można obliczyć za pomocą pojedynczej LICZBY (*), podczas gdy całkowita liczba wierszy w tabeli podstawowej wynosi już częściowo dla Ciebie zagregowane, teraz wystarczy je dodać za pomocą SUMA na datę:

SELECT Enroll_Date, 
  [Record #] = SUM(RawCount),
  [User #] = COUNT(*)
FROM dbo.MyIndexedView WITH (NOEXPAND)
GROUP BY Enroll_Date; 

Dodano wskazówkę NOEXPAND, po zapamiętaniu tego i tego .

Mogę powiedzieć bez wątpienia, że ​​to zapytanie będzie szybsze niż twoje obecne zapytanie (ale nie o ile), z wyjątkiem rzadkiego przypadku, w którym masz dokładnie jednego użytkownika na każdą datę (w którym to przypadku ta sama ilość danych będzie miała do przeczytania), a kolumny, o których wiemy, są jedynymi kolumnami w indeksie tabeli podstawowej. Nie możemy powiedzieć, czy zwiększenie wydajności w czasie odczytu jest warte dodatkowej pracy, która wpłynie na zapisywanie twojego obciążenia - musisz to przetestować, aby zmierzyć kompromis (żaden indeks nie jest darmowy).

A jeśli często używasz tych samych wspólnych klauzul WHERE przeciwko Enroll_Date dla określonych, dobrze zdefiniowanych zakresów (powiedzmy, bieżący kwartał lub rok do tej pory), możesz dodać pasujące przefiltrowane indeksy, które jeszcze bardziej zmniejszają to we / wy (ale zawsze istnieje kompromis).

Możesz także rozważyć umieszczenie indeksu klastrowego w tabeli podstawowej. To nie wydaje się być jednym z tych bardzo rzadkich przypadków użycia, które korzystają ze sterty.