Wskazówki dotyczące projektowania indeks filtrowane

Gdy kolumna ma tylko niewielkiej liczby odpowiednie wartości dla kwerend, można utworzyć filtrowane indeksu podzbiór wartości.Na przykład gdy wartości kolumna są przeważnie wartości NULL i kwerendy wybiera się tylko z wartości inne niż NULL, można utworzyć filtrowane indeksu dla wierszy danych innych niż NULL.Wynikowy indeks będzie mniejsza i kosztu do utrzymania niż indeks nieklastrowany pełnej tabela określone na tej samej kolumny klucz.

Na przykład bazy danych AdventureWorks ma tabela Production.BillOfMaterials z wierszami 2679.EndDate kolumna ta ma tylko 199 wiersze, które zawierają wartość inną niż NULL, a pozostałe wiersze 2480 zawierają wartości NULL.Następującym indeksie filtrowane powinien obejmować kwerendy, które zwracają w kolumnach wyznaczonych w indeksie i, należy zaznaczyć tylko wiersze z wartość inną niż NULL dla EndDate.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

Filtrowane indeksu FIBillOfMaterialsWithEndDate jest prawidłowa dla następującej kwerendy.Można wyświetlić plan wykonania kwerend do określania, jeśli optymalizator kwerendy używane przefiltrowane indeksu.Aby uzyskać informacje o wyświetlaniu plan wykonania kwerend Zobacz Analyzing a Query.

SELECT ProductAssemblyID, ComponentID, StartDate 
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL 
    AND ComponentID = 5 
    AND StartDate > '01/01/2008' ;
GO

Aby uzyskać więcej informacji na temat sposobów tworzenia indeksów filtrowane i zdefiniować wyrażenie predykatu filtrowane indeksu, zobacz CREATE INDEX (języka Transact-SQL).

Jeśli w tabela wierszy danych heterogenicznych, można utworzyć filtrowane indeksu dla jednej lub kilku kategorii danych.

Na przykład produktów wymienionych w tabela Production.Product każdego przypisanych do ProductSubcategoryID, które z kolei skojarzony z kategorii produktów, rowery, odzież, składniki lub akcesoria.Kategorie te są heterogenicznych, ponieważ ich wartości kolumna w tabela Production.Product nie są ściśle powiązane.Na przykład kolor, ReorderPoint, ListPrice, waga, klasy i styl ma unikatową charakterystykę dla każdej kategorii produktów.Załóżmy, że są częste kwerendy dla Akcesoria, które mają podkategorie 27-36.Można poprawić wydajność kwerendy Akcesoria tworząc filtrowane indeksu na podkategorie Akcesoria.

W poniższym przykładzie tworzony jest indeks filtrowane wszystkich produktów w podkategoriach Akcesoria w Production.Product Tabela.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIProductAccessories'
    AND object_id = OBJECT_ID ('Production.Product'))
DROP INDEX FIProductAccessories
    ON Production.Product;
GO
CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (ProductSubcategoryID, ListPrice) 
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

Filtrowane indeksu FIProductAccessories obejmuje następującą kwerendę, ponieważ wyniki kwerendy są zawarte w indeksie i planu kwerendy nie zawiera wyszukiwania tabela bazowa.Na przykład wyrażenie predykatu kwerendy ProductSubcategoryID = 33 jest podzbiorem predykat filtrowane indeksu ProductSubcategoryID >= 27 i ProductSubcategoryID <= 36, ProductSubcategoryID i ListPrice kolumny w predykacie kwerendy są obie kolumny klucz w indeksie, a nazwa jest przechowywana na poziomie poziom liścia indeks jako kolumna dołączone.

SELECT Name, ProductSubcategoryID, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;
GO

Widok jest wirtualny tabela, która przechowuje definicji kwerendy; ma szerszym celu i funkcji niż filtrowane indeksu.Aby uzyskać więcej informacji na temat widoków Zobacz Understanding Views i Scenarios for Using Views. W poniższej tabela porównano niektóre funkcje w widokach z tymi, które indeksy filtrowane.

* Aby proste porównania logikę predykatu zobacz Składnia klauzula WHERE w TWORZENIE INDEKSU.

** Dla złożonych porównań logicznych w predykatu zobacz Składnia klauzula WHERE WYBIERZ OPCJĘ.

Nie można utworzyć indeksu filtrowanych w widoku.Jednak optymalizator kwerendy może skorzystać z filtrowanym indeksu zdefiniowanych dla tabela, która odwołuje się do widoku.optymalizator kwerendy uznaje filtrowane indeksu dla kwerendy wybierające z widoku, jeśli wyniki kwerendy będą poprawne.Poniższy przykład tworzy widok z datami rozpoczęcia po 1 kwietnia 2000 i filtrowane indeksu z datami rozpoczęcia po 1 sierpnia 2000 roku.

W poniższym przykładzie kwerendy początkowej wybiera daty większe thanSeptember 1, 2000, które są wszystkie zawarte w filtrowanym indeksu i w filtrowanym widoku.Filtrowane indeksu FIBillOfMaterialsByStartDate optymalizator kwerendy bierze pod uwagę, ponieważ zawiera on poprawne wyniki kwerendy.

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20000901';
GO

W kolejnym przykładzie daty rozpoczęcia większa niż 1 czerwca 2000 wszystkich zawartych w widoku, ale nie w indeksie filtrowane wybierane przez kwerendę.optymalizator kwerendy nie traktuje filtrowane indeksu FIBillOfMaterialsByStartDate, ponieważ kwerenda może zwracać różne wyniki przy użyciu indeksu filtrowanego przy wybieraniu przez kwerendę w widoku w porównaniu z prawidłowych wyników.

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20000601';
GO

Indeksy filtrowane mają następujące zalety nad widoków indeksowanych:

• Zmniejsza koszty utrzymania indeksu.Na przykład procesor kwerend używa mniej zasobów PROCESORA do aktualizacji filtrowane indeksu niż widok indeksowany.

• Jakość ulepszona planu.Na przykład podczas kompilacji kwerendy optymalizator kwerendy uznaje, za pomocą filtrowanych indeksu w sytuacjach więcej niż równoważne widok indeksowany.

• Odtwarza indeks w trybie online.W czasie, gdy są one dostępne dla kwerendy można odbudować filtrowane indeksów.Indeks online rebuilds nie są obsługiwane w przypadku widoków indeksowanych.Aby uzyskać więcej informacji zobacz temat opcja PRZEBUDOWY ALTER INDEX (języka Transact-SQL).

• Indeksy nieunikatowe.Filtrowane indeksy mogą być nieunikatowy, widoki indeksowane muszą być unikatowe.

Z powyższych powodów zaleca się, używając filtrowanych indeksu, a nie utworzony widok indeksowany, jeśli jest to możliwe.Istnieje możliwość używania indeksu filtrowane zamiast widok indeksowany, gdy są spełnione następujące warunki: odwołuje się tylko jedną tabela w widoku kwerendy nie zwracają kolumny obliczane i predykat widoku używa Logika porównywania proste. Na przykład następujące wyrażenie predykatu jest dozwolona w definicji widoku, ale nie w filtrowanej indeksów, ponieważ zawiera LIKE operator.

WHERE StartDate > '20000701' AND ModifiedDate LIKE 'E%'

Jest najlepiej, aby dołączyć niewielką liczbę klucz lub dołączone kolumny w definicji indeksu filtrowane i włączenie tylko kolumny, które są niezbędne do optymalizator kwerendy do wyboru filtrowane indeks plan wykonania kwerend.optymalizator kwerendy można filtrowane indeks dla tej kwerendy, niezależnie od jest czy nie obejmuje kwerendy.optymalizator kwerendy jest jednak bardziej prawdopodobne, że wybierz filtrowane indeksu, jeśli dotyczy kwerenda.Aby uzyskać więcej informacji na temat kwerend obejmujących zobacz Tworzenie indeksów za pomocą zestawu kolumn.

W niektórych przypadkach filtrowane indeks obejmuje kwerendy bez dołączania kolumn w indeksie filtrowane wyrażenie jako kolumny klucz lub zawarte w definicji indeksu filtrowane.Następujące wskazówki wyjaśnić, kiedy powinna być kolumna w wyrażeniu filtrowane indeksu klucz i kolumna w definicji indeksu filtrowane.W przykładach odnoszą się do filtrowanej indeks FIBillOfMaterialsWithEndDate, który został utworzony wcześniej.

kolumna w wyrażeniu filtrowane indeksu nie musi być kluczem lub dołączona kolumna w definicji indeksu filtrowane, jeśli wyrażenie filtrowane indeksu jest odpowiednikiem predykat kwerendy, a kwerenda nie zwraca kolumna w wyrażeniu filtrowane indeksu wraz z wyniki kwerendy.Na przykład FIBillOfMaterialsWithEndDate obejmuje następujące kwerendy, ponieważ predykat kwerendy jest równoważne wyrażeniu filtru i EndDate nie jest zwracana wraz z wyniki kwerendy.FIBillOfMaterialsWithEndDate EndDate jako klucz nie jest konieczne i kolumna w definicji indeksu filtrowane.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

kolumna w wyrażeniu filtrowane indeks powinien być kluczem lub dołączona kolumna definicja indeksu filtrowane Jeśli predykat kwerendy wykorzystuje kolumna porównania, który nie jest odpowiednikiem wyrażenie filtrowane indeksu.Na przykład FIBillOfMaterialsWithEndDate jest prawidłowy dla następującej kwerendy, ponieważ powoduje zaznaczenie podzbiór wierszy z filtrowanym indeksu.Jednak nie obejmuje ona następujące kwerendy ponieważ EndDate jest używany do porównania EndDate > '20000101', który nie jest odpowiednikiem wyrażenie filtrowane indeksu. Procesor kwerend nie może wykonać tej kwerendy bez wyszukiwania wartości EndDate. Dlatego też EndDate powinien być klucz lub zawarte w definicji indeksu filtrowane kolumna.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20000101';
GO

kolumna w indeksie filtrowane wyrażenie powinien być klucz lub zawarte w definicji indeksu filtrowane kolumna, jeśli kolumna w wyniku kwerendy ustawiono.Na przykład FIBillOfMaterialsWithEndDate nie obejmuje następujące kwerendy, ponieważ funkcja zwraca wartość EndDate kolumna w wynikach kwerendy. Dlatego też EndDate powinien być klucz lub zawarte w definicji indeksu filtrowane kolumna.

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

Klucz podstawowy tabela nie musi być kluczem lub zawarte w definicji indeksu filtrowane kolumna.Wszystkie indeksy nieklastrowany, łącznie z filtrowanym indeksy automatycznie znajduje się klucz podstawowy.

Jeśli określony operator porównania w indeksie filtrowane wyrażenie filtrowane indeksu skutkuje konwersję danych bezpośrednia lub pośrednia wystąpi błąd, jeśli konwersja nastąpi po lewej stronie operatora porównania.Rozwiązaniem jest wpisanie wyrażenie indeksu filtrowane z operator konwersji danych (CAST lub CONVERT) po prawej stronie operator porównania.

Poniższy przykład tworzy tabela z różnymi typami danych.

W następującej filtrowane definicja indeksu kolumna b Domyślnie jest konwertowany do typu danych Liczba całkowita z porównanie z stała 1. Generuje komunikat o błędzie 10611, ponieważ konwersja nastąpi na lewej stronie operator w filtrowanej predykatu.

USE AdventureWorks;
GO
IF EXISTS ( SELECT name from sys.indexes 
    WHERE name = N'TestTabIndex'
    AND object_id = OBJECT_ID (N'dbo.TestTable'))
DROP INDEX TestTabIndex on dbo.TestTable
GO
CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;
GO

Rozwiązaniem jest konwersja stała się tego samego typu co kolumna po stronie prawej b, jak pokazano w poniższym przykładzie:

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);
GO

Przenoszenie Konwersja danych z lewej strony po prawej stronie operator porównania może zmienić znaczenie konwersji.W powyższym przykładzie, podczas dodawania po prawej stronie operator CONVERT porównanie zmiany z porównania całkowitą varbinary porównanie.

The sys.sql_expression_dependencies catalog view tracks each kolumna in the filtered index wyrażenie as a referencing dependency.Nie można upuścić, zmienić ich nazwy lub zmianę definicji kolumna tabela, która jest zdefiniowana w wyrażeniu filtrowane indeksu

Filtrowane indeksy są użyteczne wówczas, gdy dobrze podzbiór danych, kwerendy odwołujące się do instrukcji SELECT w kolumnach.Przykłady:

• Rzadkie kolumny, które zawierają tylko kilka wartości inne niż NULL.

• Heterogeniczne kolumny zawierające kategorie danych.

• Kolumny zawierającej wartości, takie jak kwot w dolarach, godziny i daty.

• Partycje tabela, które są definiowane przez logikę proste porównania wartości kolumn.

Koszty eksploatacji zmniejszonej filtrowane indeksów są najbardziej zauważalny, gdy liczba wierszy w indeksie jest małe w porównaniu z indeksu tabela pełnej.Filtrowane indeks zawiera większość wierszy w tabela, można koszt więcej, aby zachować niż indeks tabela pełnej.W takim przypadek należy używać indeksu tabela pełnej zamiast indeksu filtrowane.

Filtrowane indeksy są zdefiniowane w jednej tabela i obsługują tylko operatory porównania proste.Wyrażenie filtru, który odwołuje się do wielu tabel lub ma złożonej logiki, należy należy utworzyć widok.

Ogólnie rzecz biorąc Database Engine i narzędzia zapewniają obsługę tego samego dla filtrowanych indeksy, które zapewniają nieklastrowany indeksów tabela pełnej, biorąc pod uwagę filtrowane indeksów, jako specjalny rodzaj ponownego zbudowania indeksów nie klastrowanych. Poniższa lista zawiera uwagi na temat narzędzi i funkcji, w pełni obsługują, nie obsługują lub ograniczyła obsługę filtrowane indeksów.

• Indeks ALTER obsługuje indeksy filtrowane.Aby zmodyfikować wyrażenie filtrowane indeksu, należy użyć CREATE INDEX WITH DROP_EXISTING.

• Brakujących funkcji indeksów nie sugerować filtrowane indeksów.

• The Database Engine Tuning Advisor considers filtered indexes when recommending index tuning advice and can recommend an is not null filtered index.

• Operacje online indeksu obsługuje indeksy filtrowane.

• Wskazówki tabela obsługuje filtrowane indeksy, ale ma pewne ograniczenia, które nie mają zastosowania do innych niż filtrowane indeksów.Te zostały omówione w następnej sekcji.

W niektórych przypadkach kwerend parametrycznych nie zawiera wystarczających informacji czas kompilacji dla optymalizator kwerendy wybrać filtrowane indeksu.Może istnieć możliwość od nowa napisać kwerendę brakujących informacji.W poniższym przykładzie optymalizator kwerendy nie bierze pod uwagę filtrowane indeksu FIBillOfMaterialsWithComponentID dla instrukcja SELECT, ponieważ wartości parametru @p i @q nie są znane czas kompilacji. W poniższym przykładzie kwerendy jest uruchamiany z SHOWPLAN_XML ustawiona na ON tak, aby niedopasowanych filtrowane indeksy dla kwerend parametrycznych można wyświetlać w danych wyjściowych SHOWPLAN_XML.

The UnmatchedIndexes element and Parameterization subelement in the SHOWPLAN_XML output indicate that the filtered index was not a match for the query.Aby uzyskać informacje na temat wyświetlania danych wyjściowych SHOWPLAN_XML zobacz XML Showplans.

Rozwiązaniem jest, aby zmodyfikować kwerendę tak, aby wyniki kwerendy są puste, gdy wyrażenie parametryczne nie jest podzbiorem predykat filtru.Poniższa kwerenda przedstawia tej zmiany.Dodając ComponentID in (533, 324, 753) wyrażenia klauzula WHERE, wyniki kwerendy są gwarantowane jest podzbiór filtrowane wyrażenie predykatu. Z tej zmiany optymalizator kwerendy można rozważyć indeksu filtrowane FIBillOfMaterialsWithComponentID dla następujących elementów SELECT Instrukcja.

USE AdventureWorks;
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WHERE ComponentID in (533, 324, 753)
    AND (ComponentID = @p OR ComponentID = @q);
GO
SET SHOWPLAN_XML OFF;
GO

Parametryzacja proste

optymalizator kwerendy można używać filtrowane indeksu, mimo że nie obejmuje kwerendy, według klucz wyszukiwania w celu pobrania pozostałych kolumn, które filtrowane indeksu nie obejmuje.Aby uzyskać więcej informacji na temat klucz wyszukiwania zobacz Key Lookup Showplan Operator. optymalizator kwerendy można zastosować takie rozwiązanie, jeśli szacowana liczba wyszukiwań klucz jest mała.Następująca kwerenda używa wskazówką indeksu wymusić procesor kwerend do używania FIBillOfMaterialsWithEndDate z zakładki wyszukiwania dla EndDate. Wyszukiwanie klucz występuje dla EndDate > @date porównanie w predykacie kwerendy.

USE AdventureWorks;
GO
DECLARE @date AS DATE;
SET @date = '20000825'
SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WITH ( INDEX (FIBillOfMaterialsWithEndDate) )
WHERE EndDate > @date;
GO

Zwróć uwagę, że EndDate > @Date nie jest dokładnym odpowiednikiem wyrażenie filtrowane indeksu EndDate IS NOT NULL. Filtrowane indeks jest nadal prawidłowe dla tej kwerendy parametryczne, ponieważ zwraca podzbiór wierszy, zdefiniowanych przez wyrażenie filtrowane indeksu.