Tworzenie filtrowanych indeksów

Artykuł
03/05/2025

Dotyczy:SQL Server Azure SQL Database Azure SQL Managed Instance SQL Database w usłudze Microsoft Fabric

W tym artykule opisano sposób tworzenia filtrowanego indeksu przy użyciu programu SQL Server Management Studio (SSMS) lub języka Transact-SQL. Filtrowany indeks jest zoptymalizowanym indeksem nieklastrowanym magazynu wierszy opartym na dysku, szczególnie dostosowanym do obsługi zapytań wybranych z dobrze zdefiniowanego podzestawu danych. Używa predykatu filtru do indeksowania części wierszy w tabeli. Dobrze zaprojektowany indeks filtrowany może poprawić wydajność zapytań i zmniejszyć koszty konserwacji indeksu i przechowywania w porównaniu z indeksami w pełnej tabeli.

Przefiltrowane indeksy mogą zapewnić następujące korzyści w stosunku do indeksów w pełnej tabeli:

Zwiększona wydajność zapytań i jakość planu.

Dobrze zaprojektowany filtrowany indeks poprawia wydajność zapytań i jakość planu wykonywania, ponieważ jest mniejszy niż indeks nieklastrowany całej tabeli i ma odfiltrowane statystyki. Przefiltrowane statystyki są dokładniejsze niż statystyki pełnej tabeli, ponieważ obejmują tylko wiersze w filtrowanym indeksie.
Obniżone koszty konserwacji indeksu.

Indeks jest zachowywany tylko wtedy, gdy instrukcje języka manipulowania danymi (DML) wpływają na dane w indeksie. Filtrowany indeks zmniejsza koszty konserwacji indeksu w porównaniu z indeksem nieklastrowanym w pełnej tabeli, ponieważ jest mniejszy i jest utrzymywany tylko w przypadku zmiany danych w indeksie. Istnieje możliwość posiadania dużej liczby filtrowanych indeksów, zwłaszcza gdy zawierają one dane, które są zmieniane rzadko. Podobnie, jeśli filtrowany indeks zawiera tylko często modyfikowane dane, mniejszy rozmiar indeksu zmniejsza koszt aktualizowania statystyk.
Obniżone koszty magazynowania indeksów.

Utworzenie filtrowanego indeksu może zmniejszyć magazyn dysków dla indeksów nieklastrowanych, gdy indeks pełnej tabeli nie jest konieczny. Można zastąpić pełnotabelowy indeks nieklastrowany wieloma indeksami filtrowanymi bez znacznego zwiększenia wymagań dotyczących pamięci masowej.

Zagadnienia dotyczące projektowania

Jeśli kolumna zawiera tylko kilka odpowiednich wartości dla zapytań, można utworzyć filtrowany indeks w podzestawie wartości. Wynikowy indeks będzie mniejszy i tańszy w utrzymaniu niż indeks nieklastrowany pełnej tabeli zdefiniowany na tych samych kolumnach kluczy.

Rozważmy na przykład filtrowany indeks w następujących scenariuszach danych. W każdym przypadku klauzula WHERE zapytania powinna być podzbiorem klauzuli WHERE indeksu filtrowanego, aby korzystać z filtrowanego indeksu.

Gdy wartości w kolumnie mają głównie wartość NULL, a zapytanie wybiera tylko wartości inne niż NULL. Można utworzyć filtrowany indeks dla wierszy danych innych niż NULL.
Gdy wiersze w tabeli są oznaczone jako przetworzone przez powtarzający się przepływ pracy lub proces kolejki. W miarę upływu czasu większość wierszy w tabeli zostanie oznaczona jako przetworzone. Filtrowany indeks dla wierszy, które nie zostały jeszcze przetworzone, przyniesie korzyści cyklicznemu zapytaniu wyszukującemu takie wiersze.
Gdy tabela zawiera heterogeniczne wiersze danych. Można utworzyć filtrowany indeks dla co najmniej jednej kategorii danych. Może to zwiększyć wydajność zapytań dotyczących tych wierszy danych, zawężając fokus zapytania do określonego obszaru tabeli. Ponownie wynikowy indeks będzie mniejszy i będzie kosztować mniej w utrzymaniu niż nieklastrowany indeks obejmujący całą tabelę.

Ograniczenia i ograniczenia

Nie można utworzyć filtrowanego indeksu w widoku. Jednak optymalizator zapytań może korzystać z filtrowanego indeksu zdefiniowanego w tabeli, do którego odwołuje się widok. Optymalizator zapytań uwzględnia filtrowany indeks zapytania, który wybiera z widoku, jeśli wyniki zapytania będą poprawne.
Nie można utworzyć filtrowanego indeksu w tabeli, gdy kolumna dostępna w wyrażeniu filtru jest typem danych CLR.
Przefiltrowane indeksy mają następujące zalety w stosunku do indeksowanych widoków:
- Obniżone koszty konserwacji indeksu. Na przykład procesor zapytań używa mniejszej liczby zasobów procesora CPU, aby zaktualizować filtrowany indeks niż widok indeksowany.
- Ulepszona jakość planu. Na przykład podczas kompilacji zapytania optymalizator zapytań rozważa użycie filtrowanego indeksu w większej liczbie sytuacji niż równoważnego widoku indeksowanego.
- Ponowne kompilowanie indeksu online. Indeksy filtrowane można ponownie skompilować, gdy są dostępne dla zapytań. Ponowne kompilowanie indeksu online nie jest obsługiwane w przypadku widoków indeksowanych. Aby uzyskać więcej informacji, zobacz opcję REBUILD dla ALTER INDEX (Transact-SQL).
- Indeksy inne niż unikatowe. Indeksy filtrowane mogą nie być unikatowe, natomiast widoki indeksowane muszą być unikatowe.
Indeksy filtrowane są definiowane w jednej tabeli i obsługują tylko proste operatory porównania . Jeśli potrzebujesz wyrażenia filtru, które odwołuje się do wielu tabel lub ma złożoną logikę, należy utworzyć widok. Filtrowane indeksy nie obsługują operatorów LIKE.
Kolumna w filtrowanym wyrażeniu indeksu nie musi być kluczem ani uwzględniona kolumną w filtrowanej definicji indeksu, jeśli filtrowane wyrażenie indeksu jest równoważne predykatowi zapytania, a zapytanie nie zwraca kolumny w filtrowanym wyrażeniu indeksu z wynikami zapytania.
Kolumna w wyrażeniu indeksu filtrowanego powinna być kluczem lub kolumną dołączoną w definicji indeksu filtrowanego, jeśli predykat zapytania używa kolumny w porównaniu, które nie jest równoważne wyrażeniu indeksu filtrowanego.
Kolumna w filtrowanym wyrażeniu indeksu powinna być kluczem lub dołączona kolumna w definicji filtrowanego indeksu, jeśli kolumna znajduje się w zestawie wyników zapytania.
Klucz w indeksie klastrowanym tabeli nie musi być kluczem ani kolumną dołączoną do definicji indeksu filtrowanego. Klucz indeksu klastrowanego jest automatycznie uwzględniany we wszystkich indeksach nieklastrowanych, w tym indeksach filtrowanych. Dowiedz się więcej na temat architektury indeksu i przewodnika projektowania.
Jeśli operator porównania określony w filtrowanym wyrażeniu indeksu filtrowanego powoduje niejawną lub jawną konwersję danych, wystąpi błąd, jeśli konwersja występuje po lewej stronie operatora porównania. Rozwiązaniem jest zapisanie filtrowanego wyrażenia indeksu za pomocą operatora konwersji danych (CAST lub CONVERT) po prawej stronie operatora porównania.
Zapoznaj się z wymaganymi opcjami SET do tworzenia filtrowanego indeksu w składni CREATE INDEX (Transact-SQL).
Filtry nie mogą być stosowane do klucza podstawowego lub unikatowych ograniczeń, ale można je stosować do indeksów za pomocą właściwości UNIQUE.
Nie można utworzyć filtrowanego indeksu w obliczonej kolumnie.

Uprawnienia

Wymaga uprawnienia ALTER w tabeli lub widoku. Użytkownik musi być członkiem sysadmin stałej roli serwera lub db_ddladmin i db_owner stałych ról bazy danych. Aby zmodyfikować filtrowane wyrażenie indeksu, użyj CREATE INDEX WITH DROP_EXISTING.

Tworzenie filtrowanego indeksu za pomocą programu SSMS

W Eksploratorze obiektów wybierz znak plus, aby rozwinąć bazę danych zawierającą tabelę, w której chcesz utworzyć filtrowany indeks.
Wybierz znak plus, aby rozwinąć folder Tables.
Wybierz znak plus, aby rozwinąć tabelę, w której chcesz utworzyć filtrowany indeks.
Kliknij prawym przyciskiem myszy folder Indeksy, wskaż polecenie Nowy indeksi wybierz pozycję Indeks nieklasterowany....
W oknie dialogowym Nowy indeks na stronie Ogólne wprowadź nazwę nowego indeksu w polu nazwa indeksu.
Pod kolumnami kluczy indeksu wybierz Dodaj....
W oknie dialogowym Wybierz kolumny ztable_name zaznacz pole wyboru lub pola wyboru kolumny lub kolumn tabeli, które mają zostać dodane do indeksu.
Wybierz pozycję OK.
Na stronie Filtr w obszarze Wyrażenie filtruwprowadź wyrażenie SQL, którego użyjesz do utworzenia filtrowanego indeksu.
Wybierz pozycję OK.

Tworzenie filtrowanego indeksu przy użyciu Transact-SQL

Przykłady kodu w tym artykule korzystają z przykładowej bazy danych AdventureWorks2022 lub AdventureWorksDW2022, którą można pobrać ze strony głównej Przykładów programu Microsoft SQL Server i projektów społeczności.

W Eksploratorze Obiektów nawiąż połączenie z wystąpieniem silnika bazy danych.
Na pasku Standardowa wybierz pozycję Nowe zapytanie.
Skopiuj poniższy przykład i wklej go w oknie zapytania, a następnie wybierz pozycję Wykonaj.

USE AdventureWorks2022;
GO

DROP INDEX IF EXISTS FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

Filtrowany indeks FIBillOfMaterialsWithEndDate jest prawidłowy dla tego zapytania. Możesz wyświetlić planu wykonywania zapytania, aby określić, czy optymalizator zapytań używał filtrowanego indeksu.

USE AdventureWorks2022;
GO

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

Następne kroki

Aby dowiedzieć się więcej na temat tworzenia indeksów i powiązanych pojęć, zobacz następujące artykuły:

Udostępnij za pośrednictwem