Brak indeksu klastrowego jest już częścią indeksu klastrowanego

Debuguję wolno działające zapytanie, aw planie wykonania sugerowany jest indeks nieklastrowany o wpływie 51,6648. Jednak indeks nieklastrowany obejmuje tylko kolumny, które są już w indeksie klastrowym złożonego klucza podstawowego (PK).

Czy może tak być z powodu kolejności kolumn w indeksie? tzn. jeśli kolumny w indeksie klastrowym nie są uporządkowane od najbardziej selektywnego do co najmniej, to czy istnieje możliwość, że indeks nieklastrowany poprawi wydajność?

Ponadto indeks nieklastrowany zawiera tylko dwie z trzech kolumn PK z trzecią dodaną jako kolumnę uwzględnioną. Czy jest includeinny powód, dla którego użycie indeksu nieklastrowego może być bardziej optymalne?

Poniżej znajduje się przykład struktur tabel, z którymi pracuję:

Stoły

Retailers (
    RetailerID int PK, 
    name ...)

Retailer_Relation_Types (
    RelationType smallint PK, 
    Description nvarchar(50) ...)

Retailer_Relations (
    RetailerID int PK FK, 
    RelatedRetailerID int PK FK, 
    RelationType smallint PK FK, 
    CreatedOn datetime ...)

Tabela Retailer_Relationsma następujący złożony indeks PK i sugerowany indeks-

CONSTRAINT PK_Retailer_Relations 
PRIMARY KEY CLUSTERED (
    RetailerID ASC, 
    RelatedRetailerID ASC, 
    RelationType ASC
    ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX <NameOfIndex> 
ON Retailer_Relations (
    RetailerID, 
    RelationType
    ) 
INCLUDE (
    RelatedRetailerID
    )

Tabela Retailer_Relations ma następujący złożony indeks PK i sugerowany indeks-

Podczas gdy brakujące indeksy mogą być pomocne i na pewno mogą działać, nie poświęcę zbyt wiele czasu na brakujące indeksy, te wskazówki są tworzone w szacowanym planie wykonania, a nie w rzeczywistym planie wykonania.

Mówiąc dokładniej, te wskazówki dotyczące indeksu opierają się na założeniu obniżenia kosztów Query Bucks ™ wykorzystywanych przez operatorów w planie. Optymalizator oblicza szacunkowe koszty i odpowiednio dodaje brakujące wskazówki dotyczące indeksu.

W rezultacie mogą się bardzo mylić. Jeśli nie masz pewności, czy to pomoże, najlepiej przetestować sytuację przed i po. Możesz to zrobić, dodając instrukcję SET STATISTICS IO, TIME ON;przed uruchomieniem zapytania.

Ponadto można użyć narzędzia statystycznego, aby ułatwić czytanie tych statystyk.

Czy może tak być z powodu kolejności kolumn w indeksie?

To prawda, utworzenie brakującego indeksu może poprawić selektywność zapytań, na przykład jeśli twoje zapytanie wygląda następująco:

SELECT  RelatedRetailerID
FROM Retailer_Relations 
WHERE
RetailerID = 5 AND
RelationType = 20;

lub tak:

SELECT  RelatedRetailerID
FROM Retailer_Relations 
ORDER BY
RetailerID,
RelationType;

Powodem tego jest to, że oba indeksy mogą szukać na RetailerID, ta część się nie zmieni. Ale co jeśli dodatkowe filtry / kolejność zostaną zastosowane w RelationType? Byłoby to wszędzie w indeksie klastrowym, ponieważ byłaby to trzecia wartość klucza, a nie druga wartość klucza. I jak wiemy, jest to druga kluczowa wartość w NCI.

Dobrze, ale kiedy lub jak indeks nieklastrowany poprawi zapytanie?

Kilka przypadków może być:

• Jeśli relationsType odfiltrowuje wiele wartości, rezydualne operacje we / wy mogą być wysokie, co może powodować potrzebę indeksu nieklastrowanego (zapytanie nr 1)
• Kolejność na dwóch kolumnach występuje (w jedną stronę), a zestaw wyników jest duży (zapytanie nr 2).
• Jak wspomniano @AaronBertrand: jeśli różnica wielkości CI w porównaniu z NCI jest znaczna, dodanie NCI zmniejszy liczbę stron czytanych przez zapytania, które z niej korzystają.

Uwaga dodatkowa NCI

Na marginesie, dodanie kolumn klucza do listy włączeń w NCI nie jest dokładnie potrzebne, ponieważ kolumny kluczy CI są automatycznie uwzględniane we wszystkich indeksach nieklastrowych.

Możesz to zrobić, jeśli nie masz pewności, czy indeks klastrowany pozostanie taki sam, i chcesz, aby kolumna była zawsze uwzględniana.

Jeśli chodzi o samo zapytanie, jeśli dodałeś plan wykonania za pomocą PasteThePlan , moglibyśmy podać więcej informacji na temat indeksowania / ulepszania zapytania.

Testowanie

Utwórz tabelę i dodaj kilka wierszy

CREATE TABLE Retailer_Relations (
    RetailerID int , 
    RelatedRetailerID int , 
    RelationType smallint, 
    CreatedOn datetime,
    CONSTRAINT PK_Retailer_Relations 
PRIMARY KEY CLUSTERED (
    RetailerID ASC, 
    RelatedRetailerID ASC, 
    RelationType ASC
    ) ON [PRIMARY])


    DECLARE @I Int = 1
    WHILE @I < 1000
    BEGIN
    INSERT INTO Retailer_Relations(RetailerID,RelatedRetailerID,RelationType,CreatedOn)
    VALUES(@I,@I,@I,GETDATE()
    )
    set @I += 1
    END

Zapytanie nr 1

    SELECT  RelatedRetailerID
FROM Retailer_Relations 
WHERE
RetailerID = 5 AND
RelationType = 20;

Zaplanuj bez indeksu Tutaj

Podczas wyszukiwania, wykonuje wyszukiwanie na RetailerID. Następnie wydaje resztkowy predykat We / Wy na RelationType

Dodaj indeks

CREATE NONCLUSTERED INDEX IX_TEST
ON Retailer_Relations (
    RetailerID, 
    RelationType
    ) 
INCLUDE (
    RelatedRetailerID
    )

Predykat resztkowy zniknął, wszystko dzieje się w predykacie szukania w obu kolumnach.

Plan wykonania

Przy drugim zapytaniu dodana pomocność indeksu staje się jeszcze bardziej oczywista:

SELECT  RelatedRetailerID
FROM Retailer_Relations 
ORDER BY
RetailerID,
RelationType;

Planuj bez indeksu za pomocą operatora Sortuj:

Planuj z indeksem, używając indeksu usuwa operator sortowania